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 "s@^Environment=PGDATA=.*@Environment=PGDATA=$PGDATA@" /usr/lib/systemd/system/postgresql*.service
sudo sed -i "s@^PGDATA=.*@PGDATA=$PGDATA@" /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!
Just desire to say your article is as surprising. The clearness in your post is simply great and i could assume you are an expert on this subject. Fine with your permission allow me to grab your feed to keep updated with forthcoming post. Thanks a million and please keep up the rewarding work. Dorella Darn Tremaine
You have observed very interesting details! ps decent website. Cleo Dalis Minnnie
A wonderful serenity has taken possession of my entire soul, like these sweet mornings of spring which I enjoy with my whole heart. I am alone, and feel the charm of existence in this spot, which was created for the bliss of souls like mine. I am so happy, my dear friend, so absorbed in the exquisite sense of mere tranquil. Eyde Vinnie Tilney
Hey there. I found your site by way of Google at the same time as looking for a similar topic, your website came up. It seems to be great. I have bookmarked it in my google bookmarks to come back then. Julieta Fremont Erastatus