EDB PostgreSQL Advanced Server v15 Setup on Azure RHEL 9.4 with pgAdmin

[santosh]

EDB PostgreSQL Advanced Server v15 Setup on Azure RHEL 9.4 with pgAdmin

Overview
This guide covers installing and configuring EDB PostgreSQL Advanced Server v15 on Azure RHEL 9.4, setting up systemd service, configuring remote connections and connecting via pgAdmin.

Environment

• OS: Red Hat Enterprise Linux 9.4 on Azure
  
• Database: EDB PostgreSQL Advanced Server v15
  
• Port: 5444
  
• pgAdmin: Web-based pgAdmin interface
  
• Database User: enterprisedb
  

Table of Contents

• Step 1 - Install EDB PostgreSQL
  
• Step 2 - Initialize the Database
  
• Step 3 - Create Systemd Service
  
• Step 4 - Configure Remote Connections
  
• Step 5 - Setup Firewall Rules
  
• Step 6 - Install PostgreSQL Client
  
• Step 7 - Configure pgAdmin
  
• Step 8 - Test Connectivity
  

Step 1 - Install EDB PostgreSQL Advanced Server
Run on: Azure RHEL 9.4 Server

Add the EDB repository:

Code:

curl -1sLf 'https://downloads.enterprisedb.com/YOUR_REPO_TOKEN/enterprise/setup.rpm.sh' | sudo -E bash

Install EDB PostgreSQL Advanced Server v15:

Code:

sudo dnf install edb-as15-server

Verify installation:

Code:

/usr/edb/as15/bin/postgres --version

Step 2 - Initialize the Database
Run on: Azure RHEL 9.4 Server

Create the data directory:

Code:

sudo mkdir -p /usr/edb/as15/data
sudo mkdir -p /usr/edb/as15/logs

Switch to enterprisedb user and initialize:

Code:

sudo su - enterprisedb
/usr/edb/as15/bin/initdb -D /usr/edb/as15/data
exit

Step 3 - Create Systemd Service
Run on: Azure RHEL 9.4 Server

Create the service file:

Code:

sudo nano /etc/systemd/system/edb-as15.service

Add the following configuration:

Code:

[Unit]
Description=EDB PostgreSQL Advanced Server 15
After=network.target

[Service]
Type=forking
User=enterprisedb
ExecStart=/usr/edb/as15/bin/pg_ctl -D /usr/edb/as15/data -l /usr/edb/as15/logs/server.log start
ExecStop=/usr/edb/as15/bin/pg_ctl -D /usr/edb/as15/data stop
ExecReload=/usr/edb/as15/bin/pg_ctl -D /usr/edb/as15/data reload
PIDFile=/usr/edb/as15/data/postmaster.pid

[Install]
WantedBy=multi-user.target

Reload systemd and start service:

Code:

sudo systemctl daemon-reload
sudo systemctl start edb-as15
sudo systemctl enable edb-as15

Verify service status:

Code:

sudo systemctl status edb-as15

Expected output:

Code:

edb-as15.service - EDB PostgreSQL Advanced Server 15
  Active: active (running)

Step 4 - Configure Remote Connections
Run on: Azure RHEL 9.4 Server

Edit postgresql.conf:

Code:

sudo nano /usr/edb/as15/data/postgresql.conf

Set the following values:

Code:

listen_addresses = '*'
port = 5444

Edit pg_hba.conf to allow remote access:

Code:

sudo nano /usr/edb/as15/data/pg_hba.conf

Add your bastion or client IP:

Code:

# Allow specific IP access
host    all    all    YOUR_BASTION_IP/32    md5

# Or allow entire subnet
host    all    all    10.170.0.0/24    md5

Reload PostgreSQL to apply changes:

Code:

sudo systemctl reload edb-as15

Step 5 - Setup Firewall Rules
Run on: Azure RHEL 9.4 Server

Open port 5444 in firewalld:

Code:

sudo firewall-cmd --permanent --add-port=5444/tcp
sudo firewall-cmd --reload

Verify port is open:

Code:

sudo firewall-cmd --list-all

Verify PostgreSQL is listening:

Code:

netstat -tuln | grep 5444

Expected output:

Code:

tcp    0    0 0.0.0.0:5444    0.0.0.0:*    LISTEN

Step 6 - Install PostgreSQL Client
Run on: Bastion Server

Install psql client:

Code:

sudo dnf install postgresql

Verify client installation:

Code:

psql --version

Step 7 - Configure pgAdmin
Run on: Web Browser

7.1 Access pgAdmin

• Open your pgAdmin URL in browser
  
• Login with your pgAdmin credentials
  

7.2 Add New Server

• In left sidebar right-click on Servers
  
• Select Create > Server
  

7.3 General Tab Settings

• Name: EDB-Advanced-Server
  

7.4 Connection Tab Settings

• Host name/address: YOUR_EDB_SERVER_IP
  
• Port: 5444
  
• Maintenance database: edb
  
• Username: enterprisedb
  
• Password: YOUR_ENTERPRISEDB_PASSWORD
  
• Check Save password if desired
  

7.5 Save and Connect

• Click Save
  
• Server should appear under Servers in sidebar
  
• Expand to browse databases
  

Step 8 - Test Connectivity
Run on: Bastion Server

Test connection using psql:

Code:

psql -h YOUR_EDB_SERVER_IP -p 5444 -U enterprisedb -d edb

If successful you will see the psql prompt:

Code:

edb=#

Test basic query:

Code:

SELECT version();
\l
\du
\q

Troubleshooting

• Connection refused: Check firewall rules and port 5444 is open
  
• Auth failed: Verify pg_hba.conf has correct IP and auth method
  
• Service not starting: Check logs at /usr/edb/as15/logs/server.log
  
• pgAdmin can't connect: Verify network connectivity between pgAdmin and EDB server
  

Check service logs:

Code:

sudo journalctl -u edb-as15 -f
tail -f /usr/edb/as15/logs/server.log

Additional Resources

• pgAdmin Documentation: https://www.pgadmin.org/docs/
  
• EDB Documentation: https://www.enterprisedb.com/docs/
  
• EDB PostgreSQL Advanced Server v15 Release Notes
  

Summary

• EDB PostgreSQL Advanced Server v15 installed on Azure RHEL 9.4
  
• Database initialized and systemd service created
  
• Remote connections configured on port 5444
  
• Firewall rules configured to allow port 5444
  
• pgAdmin configured to connect to EDB server
  
• Connectivity tested successfully via psql
  
• Always use strong passwords for database users
  
• Restrict pg_hba.conf to specific IPs only
  
• Monitor logs regularly for security and performance