Install PostgreSQL 13 on RHEL 8.x

Overview

• Implement PostgreSQL as single server or standalone
• Implement on Red Hat Enterprise Linux 8.x
• Applying some standard configuration align with CIS Benchmark
• Applying PostgreSQL tuning base on server specs
• Server specs:
  • Hostname: pgdb01
  • IP: 192.168.100.31/24
  • OS: Red Hat Enterprise Linux 8.x
  • Additional Disk: 20GB (nvme0n2) for PGDATA
  • Purpose: Standalone PostgreSQL Database server
  • Installed: postgresql13-server, pgaudit

Prerequisites

• In case we do not have Red Hat subscription, follow Red Hat local repository here
• Configure additional disk to store PostgreSQL data “/pgdata“

lsblk
pvcreate /dev/nvme0n2
vgcreate vg_pgdata /dev/nvme0n2
lvcreate -l 100%VG vg_pgdata -n lv_pgdata
mkfs.xfs /dev/vg_pgdata/lv_pgdata
echo "$(blkid /dev/vg_pgdata/lv_pgdata | awk '{print $2}') /pgdata     xfs     defaults    0 0" >> /etc/fstab
mkdir /pgdata
mount -a
df -h

• Install the PostgreSQL repository RPM. You can check the latest here

sudo dnf install -y \
    https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

• Disable the built-in PostgreSQL module

sudo dnf -qy module disable postgresql

Installing PostgreSQL (version 13)

• Installing PostgreSQL server version 13, pgaudit and selinux-policy-minimum

sudo dnf install -y \
    postgresql13-server \
    pgaudit15_13 \
    selinux-policy-minimum

• Set database variable “PGDATA=/pgdata” and “PGPORT=5432” but of course you can change at your will

cat <<'EOF' | sudo tee -a ~/.bashrc
export PGDATA=/pgdata
export PGPORT=5432
EOF
source ~/.bashrc
echo "Variable verification PGDATA is $PGDATA and PGPORT is $PGPORT"

• Set “PGDATA” directory owner and SELinux

sudo chown -R postgres:postgres $PGDATA
sudo chmod o-rwx $PGDATA
sudo semanage fcontext -a -t postgresql_db_t "$PGDATA(/.*)?"
sudo restorecon -Rv $PGDATA

• Update PostgreSQL daemon service to use the “PGDATA” directory

sudo sed -i "[email protected]^Environment=PGDATA=.*@[email protected]" /usr/lib/systemd/system/postgresql*.service
sudo sed -i "[email protected]^PGDATA=.*@[email protected]" /var/lib/pgsql/.bash_profile
sudo systemctl daemon-reload

• Initialize the first database

sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

• Enable automatic start

sudo systemctl enable postgresql-13 --now

• Verify PostgreSQL installation and status

sudo systemctl status postgresql-13
sudo -u postgres psql -c "select version();"

Configuring PostgreSQL

echo "umask 077" >> /var/lib/pgsql/.bash_profile
groupadd pg_wheel && gpasswd -a postgres pg_wheel

mkdir -p /var/log/postgres && \
chown postgres:postgres /var/log/postgres && \
chmod 750 /var/log/postgres

sudo -u postgres psql <<'EOF'
-- Ensure the log destinations are set correctly --
alter system set log_destination = 'csvlog';
-- Ensure the logging collector is enabled --
alter system set logging_collector = 'on';
-- Ensure the log file destination directory is set correctly --
alter system set log_directory='/var/log/postgres';
-- Ensure the filename pattern for log files is set correctly --
alter system set log_filename='postgresql-%d%m%Y.log';
-- Ensure the log file permissions are set correctly --
alter system set log_file_mode = '0600';
-- Ensure 'log_truncate_on_rotation' is enabled --
alter system set log_truncate_on_rotation = 'on';
-- Ensure the maximum log file lifetime is set correctly --
alter system set log_rotation_age='1d';
-- Ensure the maximum log file size is set correctly --
alter system set log_rotation_size = '0';
-- Ensure the correct syslog facility is selected --
alter system set syslog_facility = 'LOCAL1';
-- Ensure the program name for PostgreSQL syslog messages is correct --
alter system set syslog_ident = 'postgres';
-- Ensure the correct messages are written to the server log --
alter system set log_min_messages = 'warning';
-- Ensure the correct SQL statements generating errors are recorded --
alter system set log_min_error_statement = 'error';
-- Ensure 'debug_print_parse' is disabled --
alter system set debug_print_parse='off';
-- Ensure 'debug_print_rewritten' is disabled --
alter system set debug_print_rewritten = 'off';
-- Ensure 'debug_print_plan' is disabled --
alter system set debug_print_plan = 'off';
-- Ensure 'debug_pretty_print' is enabled --
alter system set debug_pretty_print = 'on';
-- Ensure 'log_connections' is enabled --
alter system set log_connections = 'on';
-- Ensure 'log_disconnections' is enabled --
alter system set log_disconnections = 'on';
-- Ensure 'log_error_verbosity' is set correctly --
alter system set log_error_verbosity = 'verbose';
-- Ensure 'log_hostname' is set correctly --
alter system set log_hostname='off';
-- Ensure 'log_line_prefix' is set correctly --
alter system set log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h ';
-- Ensure 'log_statement' is set correctly --
alter system set log_statement='ddl';
-- Ensure 'log_timezone' is set correctly --
alter system set log_timezone = 'UTC';
-- Ensure the PostgreSQL Audit Extension (pgAudit) is enabled --
alter system set shared_preload_libraries = 'pgaudit';
EOF

echo "pgaudit.log='ddl,write'" >> $PGDATA/postgresql.auto.conf
echo '%pg_wheel ALL= /bin/su - postgres' > /etc/sudoers.d/postgres
chmod 600 /etc/sudoers.d/postgres

sudo -u postgres psql <<'EOF'
-- Set listen address to any address --
alter system set listen_addresses = '*';
-- Set password encryption with md5 --
alter system set password_encryption = 'md5';
EOF

sudo -u postgres psql -c "alter system set port = '$PGPORT';"
semanage port -a -t postgresql_port_t -p tcp $PGPORT

cp -p $PGDATA/pg_hba.conf{,_`date +"%d%m%Y"`}
cat > $PGDATA/pg_hba.conf <<'EOF'
# TYPE         DATABASE        USER        ADDRESS         METHOD
# Only local be able to access Postgres with "peer"
local    all    all        peer

# Also allow the host unrestricted access to connect to itself
host    all     all    127.0.0.1/32    trust
host    all     all    ::1/128         trust
EOF

# Restart PostgreSQL service to apply the change
sudo systemctl restart postgresql-13

# Do not forget to set/change your postgres role password
sudo -u postgres psql -p $PGPORT -p $PGPORT -c "alter role postgres with password '<change your password>';"

Tuning PostgreSQL

• Find your tuning PostgreSQL configuration, please follow PGTune calculation
• Tune your database base on server specs

• Copy tuning configuration from “ALTER SYSTEM” and apply to PostgreSQL database

sudo -u postgres psql <<'EOF'
ALTER SYSTEM SET
 max_connections = '1000';
ALTER SYSTEM SET
 shared_buffers = '512MB';
ALTER SYSTEM SET
 effective_cache_size = '1536MB';
ALTER SYSTEM SET
 maintenance_work_mem = '128MB';
ALTER SYSTEM SET
 checkpoint_completion_target = '0.9';
ALTER SYSTEM SET
 wal_buffers = '16MB';
ALTER SYSTEM SET
 default_statistics_target = '100';
ALTER SYSTEM SET
 random_page_cost = '4';
ALTER SYSTEM SET
 effective_io_concurrency = '2';
ALTER SYSTEM SET
 work_mem = '524kB';
ALTER SYSTEM SET
 min_wal_size = '2GB';
ALTER SYSTEM SET
 max_wal_size = '8GB';
ALTER SYSTEM SET
 max_worker_processes = '2';
ALTER SYSTEM SET
 max_parallel_workers_per_gather = '1';
ALTER SYSTEM SET
 max_parallel_workers = '2';
ALTER SYSTEM SET
 max_parallel_maintenance_workers = '1';
EOF

• Restart PostgreSQL service again to apply the change or take affect

sudo systemctl restart postgresql-13

Testing

• Test create a database, user and its password

sudo -u postgres psql -p $PGPORT <<'EOF'
CREATE DATABASE testdb;
CREATE USER testuser WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
EOF

• Allow connection permission in “$PGDATA/pg_hba.conf” file

cat <<'EOF' | sudo tee -a $PGDATA/pg_hba.conf
# Allow connection permission for testing
host    testdb     testuser    192.168.100.1/24         md5
EOF

sudo -u postgres psql -c "select pg_reload_conf();"

• Allow local firewall connection rule

sudo firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=192.168.100.1 port port=5432 protocol=tcp accept'
sudo firewall-cmd --reload

• Remote connection from any support tool like DBeaver

You have done the great job, Thanks!