Skip to content

📚 PostgreSQL Install

🔍 Prepare：

## OS and Software environment：

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）

## Download below packages（Example PG 16.8）：

Package name Download address
postgresql16-libs-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package
postgresql16-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package
postgresql16-server-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package
postgresql16-contrib-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package
postgresql16-devel-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package
postgresql16-plpython3-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package
instantclient-basic-linux.x64-21.19.0.0.0dbru.zip Click Download RPM Package
instantclient-sqlplus-linux.x64-21.19.0.0.0dbru.zip Click Download RPM Package
instantclient-sdk-linux.x64-21.19.0.0.0dbru.zip Click Download RPM Package
oracle_fdw-master.zip Click Download RPM Package

Create source folder and upload package：

mkdir -p /pgdata/source
ll /pgdata/source

1. PostgreSQL Server Install

  • 1.1 Create user Account and Folder（root）
groupadd -g 800 postgres
useradd -u 800 -g 800 -c "PostgreSQL Software Only" postgres
passwd postgres
mkdir /pgsql /pgarch /pgdata
chown -R postgres. /pgarch /pgsql /pgdata

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

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

alt text

  • 1.3 Modify environment parameter（postgres）

choice one of （1.3.1） or （1.3.2 + 1.3.3）

  • 1.3.1 Edit .bash_profile 

    su - postgres
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:/pgsql/pgsql-16/bin:
export PATH

LD_LIBRARY_PATH=/pgsql/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

  • 1.3.2 Edit SID_ENV.sh（Must replace file&content SID）

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

#User specific environment and startup programs
PATH=\$PATH:/usr/pgsql-16/bin/:
LD_LIBRARY_PATH=/usr/pgsql-16/lib
export PATH

####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

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

#If you want to customize your settings,
#Use the file below. This is not overridden
#by the RPMS.

[ -f /home/postgres/.pgsql_profile ] && source /home/postgres/.pgsql_profile
EOF

  • 1.3.3 Edit .bash_profile（Must replace content SID and add options）

    su - postgres
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/:
LD_LIBRARY_PATH=/usr/pgsql-16/lib
export PATH

####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

echo "Please select a number from following for the PostgreSQL_HOME "
echo " ( 1 )--------> SID "

read -t 5 HOME_SELECT
case \$HOME_SELECT in
  1) . ./SID_ENV.sh ;;
  *) echo "Timeout or invalid selection" ;;
esac
EOF

  • 1.4 Initial Cluster（postgres）

su - postgres
initdb -D /pgdata/SID --encoding="UTF8" --locale="en_US.UTF-8"

alt text

  • 1.5 Grant trust for Postgres DB server（postgres）
cp -p /pgdata/SID/pg_hba.conf /pgdata/SID/pg_hba.conf.backup
echo "# Add one line to IPV4" >> /pgdata/SID/pg_hba.conf
echo "host    all             all             0.0.0.0/0               scram-sha-256" >> /pgdata/SID/pg_hba.conf

alt text

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

  • 1.6 Config instance parameter（postgres）
cp -p /pgdata/SID/postgresql.conf /pgdata/SID/postgresql.conf.backup

## For QAS Config： 

cat << EOF >> /pgdata/SID/postgresql.conf
####For All base Config####
listen_addresses = '*'   # suggest * or PRD_IP
port = 5435              # suggest * or PRD_IP
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/SID/postgresql.conf
####For All base Config####
listen_addresses = '*'   # suggest * or PRD_IP
port = 5435
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 = on
archive_timeout = 1800
archive_command = 'cp -i %p /pg_arch/SID_archive/%f'
restore_command = 'cp -i /pg_arch/SID_archive/%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

alt text

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;

2. Install extension（root）

  • 2.1 PGagent extension
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

yum clean all
yum makecache
yum list |grep pgagent
yum install -y pgagent_16.x86_64
chown -R postgres. /usr/pgsql-16

alt text

  • 2.2 Pldebugger extension
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

yum clean all
yum makecache
yum list |grep pldebugger
yum install -y pldebugger_16.x86_64
chown -R postgres. /usr/pgsql-16

alt text

  • 2.3 pgvector extension
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

yum clean all
yum makecache
yum list |grep pgvector
yum install -y pgvector_16.x86_64
chown -R postgres. /usr/pgsql-16

alt text

  • 2.4 PG_Profile extension 
    tar -zxvf /pgdata/source/pg_profile--4.3.tar.gz -C /usr/pgsql-16/share/extension/
chown -R postgres. /usr/pgsql-16/

alt text

  • 2.5 Oracle_fdw extension
mkdir /opt/oracle/
unzip /pgdata/source/instantclient-basic-linux.x64-21.19.0.0.0dbru.zip -d /opt/oracle/
unzip /pgdata/source/instantclient-sqlplus-linux.x64-21.19.0.0.0dbru.zip -d /opt/oracle/
unzip /pgdata/source/instantclient-sdk-linux.x64-21.19.0.0.0dbru.zip -d /opt/oracle/
mv /opt/oracle/instantclient_21_6 /opt/oracle/instantclient
unzip /pgdata/source/oracle_fdw-master.zip -d /pgdata/source/
chown -R postgres:postgres /pgdata/source/oracle_fdw-master

su - postgres
cd /pgdata/source/oracle_fdw-master/
make install
chown -R postgres:postgres /usr/pgsql-16/

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

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

alt text alt text alt text

3. Postgres_dba script（postgress）

su - postgres
cp -r /pgdata/source/postgres_dba /pgdata/postgres_dba
cd ~
echo "\\set dba '\\\\i `pwd`/postgres_dba/start.psql'" >> ~/.psqlrc

alt text

4. Start & Stop（postgress）

  • 4.1 Start service
su - postgres
pg_ctl start -D /pgdata/SID/ -l logfile
pg_ctl status -D /pgdata/SID/ -l logfile

alt text

  • 4.2 Stop service
su - postgres
pg_ctl stop -D /pgdata/SID/ -l logfile
pg_ctl status -D /pgdata/SID/ -l logfile

alt text

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

5. Change Cluster password and revoke public on postgres database（postgress）

  • 5.1 Change passwd
su - postgres
psql -p 5436

\password

alt text

  • 5.2 Revoke public permission
su - postgres
psql -p 5436

revoke all on schema public from public;

alt text