1. Enable EPEL and CRB Repositories

dnf config-manager --set-enabled crb
dnf -y install epel-release epel-next-release

If the command dnf config-manager is not found, run:

dnf install 'dnf-command(config-manager)' -y

2. Install PostgreSQL 15

dnf module -y install postgresql:15

3. Initialize the Database Cluster

postgresql-setup --initdb

4. Modify Configuration Files

# Edit /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'    # Line 60
max_connections = 1000    # Line 65

# Edit /var/lib/pgsql/data/pg_hba.conf
host    all             all             10.32.161.0/24          scram-sha-256    # Add at the end

5. Start and Enable PostgreSQL Service

systemctl enable --now postgresql

6. Manage Users and Databases

# Switch to postgres user
su - postgres

# Create a new user
createuser cent

# Set password for the user
psql -c "alter user cent with password 'password';"

# Create a database owned by the user
createdb testdb -O cent

# Test connection
psql -h 10.32.161.131 -d testdb -U cent

# List users
psql -c "select usename from pg_user;"

# List databases
psql -l

# Connect to database
psql testdb
testdb=> \du     # Display roles
testdb=> \dt     # List tables
testdb=> \q      # Quit

# Drop database
dropdb testdb

7. Backup and Restore Operations

# Switch to postgres user
su - postgres

# Backup a specific database with a designated user
pg_dump -U cent --format=t -d testdb > pg_testdb.tar

# Backup all databases as superuser
pg_dumpall -f /backups/pg_DB_all.sql

# Restore database with a specified user
pg_restore -U cent -d testdb pg_testdb.tar

# Restore all databases
psql -f /backups/pg_DB_all.sql

8. Set Up Streaming Replication

1. Environment Setup

HostnameIP AddressRolepg-161-13110.32.161.131Primarypg-161-13210.32.161.132Standby

2. Primary Node Configuration

# Edit /var/lib/pgsql/data/postgresql.conf
...
listen_addresses = '*'
max_connections = 1000
wal_level = replica
synchronous_commit = on
max_wal_senders = 10
synchronous_standby_names = '*'
...

# Edit /var/lib/pgsql/data/pg_hba.conf
...
#host    replication     all             127.0.0.1/32            ident    # Comment out
#host    replication     all             ::1/128                 ident    # Comment out
host    all             all             10.32.161.0/24          scram-sha-256
host    replication     rep_user        10.32.161.131/32        scram-sha-256    # Add
host    replication     rep_user        10.32.161.132/32        scram-sha-256    # Add

# Create replication user
# su - postgres
$ createuser --replication -P rep_user
Enter password for new role:     # Enter password
Enter it again:     # Confirm password

# Restart service
systemctl restart postgresql

3. Standby Node Configuration

# Stop the service
systemctl stop postgresql

# Clear data directory
rm -rf /var/lib/pgsql/data/*

# Fetch data from primary node
# su - postgres
$ pg_basebackup -R -h 10.32.161.131 -U rep_user -D /var/lib/pgsql/data -P
Password:     # Enter replication user's password
23088/23088 kB (100%), 1/1 tablespace

# Update configuration
# vi /var/lib/pgsql/data/postgresql.conf
...
listen_addresses = '*'    # Line 60
hot_standby = on    # Line 335
...

# Start the service
systemctl start postgresql

4. Check Replication Status on Primary Node

# su - postgres
$ psql -c "select usename, application_name, client_addr, state, sync_priority, sync_state from pg_stat_replication;"
 usename  | application_name |  client_addr  |   state   | sync_priority | sync_state 
----------+------------------+---------------+-----------+---------------+------------
 rep_user | walreceiver      | 10.32.161.132 | streaming |             1 | sync
(1 row)