Table of Contents

Lab Setup

Step 1: Install PostgreSQL 17

Run as root:

# Install the repository RPM
[root@pgdba01 ~]# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the built-in PostgreSQL module
[root@pgdba01 ~]# dnf -qy module disable postgresql

# Install the server
[root@pgdba01 ~]# dnf install -y postgresql17-server

# Install contrib (extensions)
[root@pgdba01 ~]# dnf install -y postgresql17-contrib

Check the installed version:

[root@pgdba01 ~]# /usr/pgsql-17/bin/postgres --version
postgres (PostgreSQL) 17.x

Note: Installing the package only installs the software. It does not create a database cluster. We do that in Step 3.

[root@pgdba01 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux release 9.5 (Plow)

[root@pgdba01 ~]# cp /etc/yum.repos.d/pgdg-redhat-all.repo /etc/yum.repos.d/pgdg-redhat-all.repo.bkp
[root@pgdba01 ~]# sed -i 's/\$releasever_major\.\$releasever_minor/$releasever_major/g' /etc/yum.repos.d/pgdg-redhat-all.repo
[root@pgdba01 ~]# grep -m3 baseurl /etc/yum.repos.d/pgdg-redhat-all.repo
[root@pgdba01 ~]# dnf clean all
[root@pgdba01 ~]# dnf repolist

Step 2: Create Directories

Run as root. The mount points already exist, so we create one folder per purpose inside each of them:

[root@pgdba01 ~]# usermod -d /home/postgres -m postgres
[root@pgdba01 ~]# restorecon -Rv /home/postgres
Relabeled /home/postgres from system_u:object_r:postgresql_db_t:s0 to system_u:object_r:user_home_dir_t:s0
Relabeled /home/postgres/17 from system_u:object_r:postgresql_db_t:s0 to system_u:object_r:user_home_t:s0
Relabeled /home/postgres/17/backups from system_u:object_r:postgresql_db_t:s0 to system_u:object_r:user_home_t:s0
Relabeled /home/postgres/17/data from system_u:object_r:postgresql_db_t:s0 to system_u:object_r:user_home_t:s0
Relabeled /home/postgres/.bash_profile from unconfined_u:object_r:postgresql_db_t:s0 to unconfined_u:object_r:user_home_t:s0
Relabeled /home/postgres/.bash_history from unconfined_u:object_r:postgresql_db_t:s0 to unconfined_u:object_r:user_home_t:s0
[root@pgdba01 ~]# getent passwd postgres
postgres:x:26:26:PostgreSQL Server:/home/postgres:/bin/bash
[root@pgdba01 ~]#
[root@pgdba01 ~]# passwd postgres
Changing password for user postgres.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@pgdba01 ~]#

# Create directories

mkdir -p /pgdata/pgsql-17/data
mkdir -p /pglogs/pgsql-17/logs
mkdir -p /pgarch/pgsql-17/arch
mkdir -p /pgbackup/pgsql-17/backup

chown -R postgres:postgres /pgdata /pglogs /pgarch /pgbackup /tsdata
chmod 700 /pgdata/pgsql-17/data /pglogs/pgsql-17/logs /pgarch/pgsql-17/arch /pgbackup/pgsql-17/backup

Step 3: Initialize the Database

Run as the postgres user. --data-checksums turns on data page checksums (off by default in PostgreSQL 17), and --auth-host=scram-sha-256 makes password logins over the network use SCRAM instead of trust:

[root@pgdba01 ~]# su - postgres
[postgres@pgdba01 ~]$ /usr/pgsql-17/bin/initdb -D /pgdata/pgsql-17/data --data-checksums --auth-local=peer --auth-host=scram-sha-256 --pwprompt
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

Enter new superuser password:             <---- Asks you to set the postgres database password during initdb
Enter it again:

fixing permissions on existing directory /pgdata/pgsql-17/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /usr/pgsql-17/bin/pg_ctl -D /pgdata/pgsql-17/data -l logfile start

[postgres@pgdba01 ~]$

Step 4: Start PostgreSQL Manually

Stay logged in as the postgres user, edit .bash_profile and add below entries

[postgres@pgdba01 ~]$ cp .bash_profile /pgbackup/pgsql-17/backup/
[postgres@pgdba01 ~]$ > .bash_profile
[postgres@pgdba01 ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
export PGHOME=/usr/pgsql-17
export PGDATA=/pgdata/pgsql-17/data
export PATH=$PGHOME/bin:$PATH

[postgres@pgdba01 ~]$ . .bash_profile
[postgres@pgdba01 ~]$
[postgres@pgdba01 ~]$ echo $PGDATA
/pgdata/pgsql-17/data
[postgres@pgdba01 ~]$
[postgres@pgdba01 ~]$ which pg_ctl
/usr/pgsql-17/bin/pg_ctl
[postgres@pgdba01 ~]$

Start the server. -l is the file that receives the start-up messages:

[postgres@pgdba01 ~]$ /usr/pgsql-17/bin/pg_ctl -D /pgdata/pgsql-17/data -l /home/postgres/pg_start.log start
waiting for server to start.... done
server started
[postgres@pgdba01 ~]$ 

[postgres@pgdba01 ~]$ pg_ctl status
pg_ctl: server is running (PID: 5218)
/usr/pgsql-17/bin/postgres "-D" "/pgdata/pgsql-17/data"
[postgres@pgdba01 ~]$

Step 5: Enable Archive Mode and Logging

Run as the postgres user. First keep a backup copy of the configuration file, then append our settings at the end. When a parameter appears more than once, PostgreSQL uses the last value.

[postgres@pgdba01 ~]$ cp /pgdata/pgsql-17/data/postgresql.conf /pgdata/pgsql-17/data/postgresql.conf.bkp
[postgres@pgdba01 ~]$ grep -v '^#' /pgdata/pgsql-17/data/postgresql.conf.bkp | grep '^[A-Za-z0-9]' > /pgdata/pgsql-17/data/postgresql.conf


[postgres@pgdba01 ~]$ cat >> /pgdata/pgsql-17/data/postgresql.conf << 'EOF'

# Added by DBA
listen_addresses = '*'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
hot_standby = on
wal_log_hints = on
archive_mode = on
archive_command = 'test ! -f /pgarch/pgsql-17/arch/%f && cp %p /pgarch/pgsql-17/arch/%f'
logging_collector = on
log_directory = '/pglogs/pgsql-17/logs'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
# End
EOF

⚠️ Note: listen_addresses only opens the network port. To let other machines log in you must also add rules to pg_hba.conf (see Host-Based Authentication (pg_hba.conf)).

Step 6: Restart PostgreSQL

listen_addresses, wal_level, archive_mode, max_wal_senders, max_replication_slots and logging_collector need a restart. A reload is not enough. Run as postgres:

[postgres@pgdba01 ~]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2026-11-13 12:50:07.615 UTC [5515] LOG: redirecting log output to logging collector process
2026-11-13 12:50:07.615 UTC [5515] HINT: Future log output will appear in directory "/pglogs/pgsql-17/logs".
done
server started
[postgres@pgdba01 ~]$

[postgres@pgdba01 ~]$ pg_ctl status
pg_ctl: server is running (PID: 5515)
/usr/pgsql-17/bin/postgres "-D" "/pgdata/pgsql-17/data"
[postgres@pgdba01 ~]$

Step 7: Verify the Configuration

Connect with psql and check the settings. Then force a WAL switch to prove that archiving works:

[postgres@pgdba01 ~]$ psql

postgres=# SHOW data_directory;
 data_directory
-----------------
 /pgdata/pgsql-17/data

postgres=# SHOW port;
postgres=# SHOW data_checksums;
postgres=# SHOW archive_mode;
postgres=# SHOW archive_command;
postgres=# SHOW archive_timeout;
postgres=# SHOW logging_collector;
postgres=# SHOW log_directory;
postgres=# SELECT pg_switch_wal();

Then check from the OS that the archived WAL file arrived and that the log file was created:

# WAL Directory
[postgres@pgdba01 ~]$ ls -l /pgdata/pgsql-17/data/pg_wal/

# Archive log Directory
[postgres@pgdba01 ~]$ ls -lrth /pgarch/pgsql-17/arch/

To list the archived files with size and time from inside PostgreSQL:

postgres=# SELECT f AS wal_file,
       (pg_stat_file('/pgarch/pgsql-17/arch/' || f)).size AS size_bytes,
       (pg_stat_file('/pgarch/pgsql-17/arch/' || f)).modification AS modified_time
FROM pg_ls_dir('/pgarch/pgsql-17/arch') f
WHERE f ~ '^[0-9A-F]{24}$'
ORDER BY modified_time DESC;

💡 Tip: If nothing appears in /pgarch/pgsql-17/arch after pg_switch_wal(), look at pg_stat_archiver (SELECT * FROM pg_stat_archiver;) and the PostgreSQL log in /pglogs/pgsql-17/logs. The usual causes are a wrong path in archive_command or wrong ownership on /pgarch.

Step 8 (Optional): Manage PostgreSQL with systemctl

Optional step. Do this only if you want PostgreSQL to start automatically at boot and to be managed with systemctl. Skip it if you prefer to start and stop PostgreSQL manually with pg_ctl.

8.1 Stop the manual instance. Run as postgres. Do not run the same server with both pg_ctl and systemctl, because systemd cannot start it while the port and data directory are in use:

[postgres@pgdba01 ~]$ pg_ctl stop
[postgres@pgdba01 ~]$ exit

8.2 Point the service to the new PGDATA. Run as root:

[root@pgdba01 ~]# grep -i Environment=PGDATA /usr/lib/systemd/system/postgresql-17.service
Environment=PGDATA=/var/lib/pgsql/17/data/

[root@pgdba01 ~]# sed -i 's|Environment=PGDATA=/var/lib/pgsql/17/data/|Environment=PGDATA=/pgdata/pgsql-17/data/|' /usr/lib/systemd/system/postgresql-17.service
[root@pgdba01 ~]# grep -i Environment=PGDATA /usr/lib/systemd/system/postgresql-17.service
Environment=PGDATA=/pgdata/pgsql-17/data/

[root@pgdba01 ~]# systemctl daemon-reload

⚠️ Note: A PostgreSQL package update can overwrite this service file. After an update, check the Environment=PGDATA line again.

9.3 Enable, start, check, stop. Run as root:

# Start automatically at every boot
[root@pgdba01 ~]# systemctl enable postgresql-17

# Start now
[root@pgdba01 ~]# systemctl start postgresql-17
[root@pgdba01 ~]# systemctl status postgresql-17
   Active: active (running)

# Stop
[root@pgdba01 ~]# systemctl stop postgresql-17