PostgreSQL Intstallation on RHEL8 Or 9

Postgres Best Practice
	These practices only guides you at the beginning of installation. You must monitor the system, ,get security, SLA and other requirements, and also the parameters may change according to your environments.


1-Recommended hardware specifications for a medium database to begin with;

    RAM = 32GB
    CPU = 8 core
    DISK = must be allocated based on data size. LVM must be configured.


If different physical disks can be defined for the cluster directory and the wal directory, they must be on separate disks.  Even if there is more than one database on an instance, separate tablespaces and separate disks should be defined for each database.

In the installation, an additional contrib package must be installed in the server package.


2-postgresql.conf  parameters that need to be changed after installation

For 32GB server

listen_addresses = '*'
max_connections = 150
shared_buffer: 8GB   # (must be maximum one in four of total RAM )
shared_preload_libraries = 'pg_stat_statements'
maintenance_work_mem=1GB 
bgwriter_delay = 10ms                 
bgwriter_lru_maxpages = 1000           
bgwriter_lru_multiplier = 4.0     
wal_level = replica 
wal_log_hints = on    # (if checksum not  enabled)
max_wal_size = 12GB
min_wal_size = 6GB
checkpoint_completion_target = 0.7
checkpoint_timeout =10
archive_mode = on 
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
wal_keep_segments = 1000
log_filename = 'postgresql-%a.log'
log_min_duration_statement = 1000 
log_line_prefix = '%m [%p] app=%a  user=%u db=%d host=%h pid=%p '
log_lock_waits = on
log_temp_files = 0
track_io_timing = on 
autovacuum = on         #(It is not recommended to turn off)
log_autovacuum_min_duration = 0
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.01 
effective_cache_size= 24GB


In addition to the master server, there must be at least one sync replica server. If slots are to be used in replication, their losses should be considered.

3-Security Best Practices

1-Postgres user must be disabled for remote access.
2-Superuser authority should not be defined for users coming from applications.
3-Detailed access permissions must be defined from pg_hba.conf
4-Port must be used other than the postgresql default port.
5-The database version should be kept up to date with the minor and major updates.
6-Separate users and authorizations should be defined for  replication backup users.
7. encrypt sensitive data with pgcrypto extension
8-Application user should not have super user account
9-do not give any privileges to public
10- revoke connect role from public
11- do not create objects in public schema
12-If it is possible for your application drop public schema
13- Give least privileges to application user that the application only can work.
14- use pg_audit extension if default auditing is not adequate for clients requirements


4-Recommended extensions to use

Default extensions to be installed:

1-pg_stat_statements
2-pg_repack
 

Plugins to be installed as needed:

            a- foreign data wrappers
            b- pg_audit
            c- chkpass: determines password quality of user postgres
      

5-PostgreSQL system log analysis;

pgbadger



6-Connection Management:

If there is no pooling mechanism, pgbouncer can be installed.


7-  Monitoring Tools

 	a-pg_top
 	b-pg_activity 
	c-pgadmin
	d-PMM (Percona Monitoring Tool) or Zabbix 

8-Backup and Recovery Strategies
We recommend one server for monitoring database and backup server.
We recommend archive mode on, and put archive log to network attached servers,
where your standby servers and backup server could read these archive logs.

It is recommended to use pg_probackup or pgbackrest as a backup tool.
They provide parallelism and incremental backup mechanisms. 
Schedule backups using cron utility’s of linux


9-HA
Patroni , BFM vb.

10- Development Tools
dbeaver

pgadmin

 
11. Linux Kernel Settings

SET Below parameters in sysctl.conf

kernel.shmall (SHMALL) 2 * <size of RAM in the default system page size>
kernel.sem (SEMMNI)  256 * <size of RAM in GB>

For big memorys Use hugepages for linux
script to quantify Huge Pages
#!/bin/bash
pid=`head -1 $PGDATA/postmaster.pid`
echo "Pid:            $pid"
peak=`grep ^VmPeak /proc/$pid/status | awk '{ print $2 }'`
echo "VmPeak:            $peak kB"
hps=`grep ^Hugepagesize /proc/meminfo | awk '{ print $2 }'`
echo "Hugepagesize:   $hps kB"
hp=$((peak/hps))
echo Set Huge Pages:     $hp

set vm.nr_hugepages in /etc/sysctl.conf
set huge_pages=on in postgresql.conf

set below parameter in /etc/sysctl.conf
vm.swappiness default 60 set the value to 1 if you want to minimize swapping
vm.overcommit_memory  A value of 2 for vm.overcommit_memory yields better performance for PostgreSQL
vm.dirty_background_ratio  default value 10  if system is write intensive lower the value 

 
vm.swappines=1
vm.overcommit_memory=2

kernel.shmmax=34359738368
kernel.shmall=8388608
vm.nr_hugepages= 4500