Skip to content

PostgreSQL 16 Installation

1. Prepare

1.1 Environment prepare

Environment name Version Download address
OS Rocky Linux 9.x Rocky Download
DB PostgreSQL 16.X PostgreSQL Download（YUM Repository）
Oracle Client PostgreSQL 16.X Oracle Client Download（ZIP）
Oracle FDW PostgreSQL 16.X Oracle FDW Download（ZIP）

1.2 Download packages

Example PG 16.11

Package name Download address
postgresql16-16.11-1PGDG.rhel9.x86_64.rpm Click Download RPM Package
postgresql16-contrib-16.11-1PGDG.rhel9.x86_64.rpm Click Download RPM Package
postgresql16-devel-16.11-1PGDG.rhel9.x86_64.rpm (for FDW only) Click Download RPM Package
postgresql16-libs-16.11-1PGDG.rhel9.x86_64.rpm Click Download RPM Package
postgresql16-plpython3-16.11-1PGDG.rhel9.x86_64.rpm Click Download RPM Package
postgresql16-server-16.11-1PGDG.rhel9.x86_64.rpm Click Download RPM Package
instantclient-basic-linux.x64-23.26.0.0.0.zip Click Download RPM Package
instantclient-sqlplus-linux.x64-23.26.0.0.0.zip Click Download RPM Package
instantclient-sdk-linux.x64-23.26.0.0.0.zip Click Download RPM Package
oracle_fdw-master.zip Click Download RPM Package

1.3 Package prepare

  • Create source folder and upload package
mkdir -p /pgdata/source
ll /pgdata/source

image-20260202102023684

2. PostgreSQL Server Install

2.1 Create user (root)

groupadd -g 800 postgres
useradd -u 800 -g 800 -c "PostgreSQL Software Only" postgres
passwd postgres
mkdir /pgarch
chown -R postgres. /pgarch /pgdata

Introduction:
/pgsql ---Storage software/script /pgdata ---Storage data /pgarch ---Storage archive wal

image-20260202102346568

2.2 Install postgres package

  • Run package installation by sequence (if the downloaded file in /pgdata/source) 
dnf install -y libicu.x86_64 libxslt.x86_64 perl-libs.x86_64 lz4 llvm   perl clang-devel libicu-devel llvm-devel
cd /pgdata/source
rpm -ivh postgresql16-libs-16.11-1PGDG.rhel9.x86_64.rpm
rpm -ivh postgresql16-16.11-1PGDG.rhel9.x86_64.rpm
rpm -ivh postgresql16-server-16.11-1PGDG.rhel9.x86_64.rpm
rpm -ivh postgresql16-contrib-16.11-1PGDG.rhel9.x86_64.rpm
dnf --enablerepo=devel install perl-IPC-Run -y
rpm -ivh postgresql16-devel-16.11-1PGDG.rhel9.x86_64.rpm
rpm -ivh postgresql16-plpython3-16.11-1PGDG.rhel9.x86_64.rpm
rpm -qa|grep postgresql16
chown -R postgres. /usr/pgsql-16

image-20260202103154312

  • Modify environment parameter

  • Edit .bash_profile

    switch to postgres user

    su - postgres

    edit .bash_profile

    cat << EOF > /home/postgres/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=\$PATH:/usr/pgsql-16/bin:
export PATH

LD_LIBRARY_PATH=/usr/pgsql-16/lib
export LD_LIBRARY_PATH

[ -f /etc/profile ] && source /etc/profile
PGDATA=/pgdata/
export PGDATA

# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /pgdata/.pgsql_profile ] && source /pgdata/.pgsql_profile

####ORACLE Client (Next four line For FDW extension)
export ORACLE_HOME=/opt/oracle/instantclient
export OCI_LIB_DIR=\$ORACLE_HOME
export OCI_INC_DIR=\$ORACLE_HOME/sdk/include
export LD_LIBRARY_PATH=\$ORACLE_HOME:\$LD_LIBRARY_PATH
EOF
source .bash_profile

    image-20260202103415687

3. PG Creation

3.1 Initial instance

initdb -D /pgdata/SEQAS --encoding="UTF8" --locale="en_US.UTF-8"

📌Note: SEQAS is the name of the PG instance, please change it to the actual name.

image-20260202103605680

3.2 Config instance

  • Grant trust for postgres DB server
cp -p /pgdata/SEQAS/pg_hba.conf /pgdata/SEQAS/pg_hba.conf.bk
echo "# Add to IPv4" >> /pgdata/SEQAS/pg_hba.conf
echo "host    all             all             0.0.0.0/0               scram-sha-256" >> /pgdata/SEQAS/pg_hba.conf
cat /pgdata/SEQAS/pg_hba.conf | tail

image-20260202104423681

Allow all clients (IP address range 0.0.0.0/0) to authenticate using scram-sha-256 encryption and access all databases and users Prioritize meeting the top rule

  • Config instance parameter
cp -p /pgdata/SEQAS/postgresql.conf /pgdata/SEQAS/postgresql.conf.bk

For QAS Config：

cat << EOF >> /pgdata/SEQAS/postgresql.conf
####For All base Config####
listen_addresses = '*'   # suggest * or PRD_IP
port = 5432              
max_connections = 500
max_worker_processes = 100
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4
huge_pages = try
shared_buffers = 4GB            # suggest OS 1/4 memory
max_wal_size = 6GB              # suggest 6GB
min_wal_size= 1GB                  # suggest 1GB
wal_level = minimal
max_wal_senders = 0

####ENMOTECH Suggestion For All Config####
wal_keep_size = 10GB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
temp_file_limit = 20GB
idle_in_transaction_session_timeout = 1800000
idle_session_timeout = 1800000
work_mem = 10MB
maintenance_work_mem = 2GB
effective_cache_size = 12GB       # suggest OS 3/4 memory

####For Use PG_Profile/PG_stat_statements/Auto Explain Config####
shared_preload_libraries = 'pg_stat_statements,auto_explain,plugin_debugger'
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all
#auto_explain.log_min_duration = 0    #Suggest observing performance to obtain execution plans when necessary
#auto_explain.log_analyze = true       #Suggest observing performance to obtain execution plans when necessary

#### For All Log Function Config####
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a%H.log'
log_truncate_on_rotation = on
log_rotation_age = 60
log_rotation_size = 1000000
log_min_messages = info
log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_hostname = on
log_line_prefix = '%t [%p] : db = %d , user = %u , app = %a , Tag = %i , client = %h , session_id = %c , session_start  = %s'
log_lock_waits = on
log_statement = 'mod'
EOF

For PRD Config：

cat << EOF >> /pgdata/SEQAS/postgresql.conf
####For All base Config####
listen_addresses = '*'   # suggest * or PRD_IP
port = 5432
max_connections = 500
max_worker_processes = 100
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4
huge_pages = try
shared_buffers = 4GB            # suggest OS 1/4 memory
max_wal_size = 6GB              # suggest 6GB
min_wal_size= 1GB                  # suggest 1GB
wal_level = replica
max_wal_senders = 30
archive_mode = always
archive_timeout = 1800
archive_command = 'cp -i %p /pgarch/SEQAS_arch/%f'
restore_command = 'cp -i /pgarch/SEQAS_arch/%f %p' # Set on PRD and Standby, no effect on PRD

####ENMOTECH Suggestion For All Config####
wal_keep_size = 10GB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
temp_file_limit = 20GB
idle_in_transaction_session_timeout = 1800000
idle_session_timeout = 1800000
work_mem = 10MB
maintenance_work_mem = 2GB
effective_cache_size = 12GB       # suggest OS 3/4 memory

####For Use PG_Profile/PG_stat_statements/Auto Explain Config####
shared_preload_libraries = 'pg_stat_statements,auto_explain,plugin_debugger'
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all
#auto_explain.log_min_duration = 0    #Suggest observing performance to obtain execution plans when necessary
#auto_explain.log_analyze = true       #Suggest observing performance to obtain execution plans when necessary

#### For All Log Function Config####
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a%H.log'
log_truncate_on_rotation = on
log_rotation_age = 60
log_rotation_size = 1000000
log_min_messages = info
log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_hostname = on
log_line_prefix = '%t [%p] : db = %d , user = %u , app = %a , Tag = %i , client = %h , session_id = %c , session_start   = %s'
log_lock_waits = on
log_statement = 'mod'

#### PRD/STB DB Setting ####
hot_standby = on # Set on PRD and Standby, no effect on PRD
EOF

Notice： 1、Add to last line, Multiple repeated parameters, with the last one as the final one 2、The same parameter takes effect by taking the value of the lowest line parameter 3、postgresql.conf < postgresql.auto.conf（Configuration file level） 4、Configuration file level < database level < user level < session level < transaction level

Example: Database Level： postgres=# alter database postgres set log_duration = 'on';

User Level： postgres=# ALTER ROLE postgres IN DATABASE "XXX" SET log_duration TO 'on';

Session Level： postgres=# set work_mem = '5MB';

Transaction Level： postgres=# begin; postgres=# set work_mem = '5MB'; postgres=# end;

3.3 Install extension

switch root user

su - root

3.3.1 PGagent extension

  • Check postgres repository
ll /etc/yum.repos.d/*pg*

if not exist pleas create new repository

cat << EOF > /etc/yum.repos.d/pgdg_rocky-16.repo
[pgdg16]
name=PostgreSQL 16 for RHEL/CentOS
baseurl=https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-\$releasever-\$basearch
enabled=1
gpgcheck=0
EOF
  • Install pgagent
dnf clean all && dnf makecache
dnf list | grep pgagent
dnf install -y pgagent_16.x86_64

image-20260202112535696

3.3.2 Pldebugger extension

  • Install pgdebugger
dnf list | grep pldebugger
dnf install -y pldebugger_16.x86_64

image-20260202112647596

3.3.3 PGvector extension

  • Install pgvector
dnf list | grep pgvector
dnf install -y pgvector_16.x86_64

image-20260202112753350

3.3.4 PG_Profile extension

  • Upload pg_profile package and install

download pg_profile package: https://github.com/zubkov-andrei/pg_profile/releases/download/4.8/pg_profile--4.8.tar.gz

tar -zxvf /pgdata/source/pg_profile--4.8.tar.gz -C /usr/pgsql-16/share/extension/
chown -R postgres. /usr/pgsql-16/

image-20260202113140270

3.3.5 Oracle_fdw extension

  • Prepare package
mkdir /opt/oracle
unzip /pgdata/source/instantclient-basic-linux.x64-23.26.0.0.0.zip -d /opt/oracle/
unzip /pgdata/source/instantclient-sqlplus-linux.x64-23.26.0.0.0.zip -d /opt/oracle/
unzip /pgdata/source/instantclient-sdk-linux.x64-23.26.0.0.0.zip -d /opt/oracle/
mv /opt/oracle/instantclient_23_26 /opt/oracle/instantclient
unzip /pgdata/source/oracle_fdw-master.zip -d /home/postgres/
chown -R postgres. /home/postgres/oracle_fdw-master/
  • Install oracle_fdw
su - postgres
cd /home/postgres/oracle_fdw-master/
make install
chown -R postgres. /usr/pgsql-16/

image-20260202114614168

  • Environment prepare
su - root
ldd /usr/pgsql-16/lib/oracle_fdw.so

cp /opt/oracle/instantclient/libclntsh.so.23.1 /lib64/
cp /opt/oracle/instantclient/libnnz.so /lib64/
cp /opt/oracle/instantclient/libclntshcore.so.23.1 /lib64/
ldconfig
ldd /usr/pgsql-16/lib/oracle_fdw.so

image-20260202114910202

3.4 DBA script install

  • Download postgresql DBA into /pgdata/source
su - postgres
cd /pgdata/source && git clone https://github.com/NikolayS/postgres_dba.git
  • Install postgresql DBA script
cp -r /pgdata/source/postgres_dba /home/postgres/
cd ~
echo "\\set dba '\\\\i `pwd`/postgres_dba/start.psql'" >> ~/.psqlrc

4. Instance Start & Stop

4.1 Start service

su - postgres
pg_ctl start -D /pgdata/SEQAS/ -l logfile
pg_ctl status -D /pgdata/SEQAS/ -l logfile

image-20260202123327881

4.2 Stop service

su - postgres
pg_ctl stop -D /pgdata/SEQAS/ -l logfile

image-20260202123415101

PS. pg_ctl -m fast stop = pg_ctl stop (default is fast)

4.3 Permission maintain

  • Change postgres password
psql -p 5432
\password
  • Revoke public permission on postgres database
revoke all on schema public from public;

image-20260202123611502