Upgrade PostgreSQL from 9.5 to 11.3

In this blog we are going to upgrade the postgresql server from 9.5 to 11.3 .

We are upgraded the postgres server by using pg_upgrade utility as well as logical backup method ,

you can follow anyone  of the method .

 

 

Collect the server details before proceed  upgrade activity.

s/no.	PostgreSQL VERSION 9.5	PostgreSQL VERSION 11.3
DATA DIRECTORY	/data_9.5	/data_11.3
BIN path	/opt/postgresql/9.5	/opt/postgresql/11.3
PORT	50000	5432

 

PREREQUEST :
===========

Step 1. Check the application backend connection if any application connected , Disconnect the application from DB server.

Below command will be helpful to checking backend connection :

template1=# select datname, count(*) from pg_stat_activity group by datname;
  datname  | count 
-----------+-------
 testdb    |     1
 template1 |     0
(2 rows)
 
template1=# select datname, numbackends from pg_stat_database;
  datname  | numbackends 
-----------+-------------
 testdb    |           1
 template1 |           0
 template0 |           0
(3 rows)

Comment the application details in pg_hba.conf and restart the old cluster before taking backup,

# host    all             all             54.19.98.56/32          trust
# host    all             all             55.19.97.86/32          trust

Step 2. Take the full (physical/logical) backup of existing postgres (9.5) server.

/opt/postgresql/9.5.6/bin/pg_dumpall" -p 50000 -U pgstain -h localhost -w > "/backup/db_bkp_`date +%d%b%y%H%M%S`.dmp"

Step 3. Below query helpful to find the changed parameter (no default) value of postgresql.conf file .

SELECT name,setting,source FROM pg_settings
WHERE source NOT IN
('default','client','override','environment variable'); 

            name            |                          setting                          |       source
----------------------------+-----------------------------------------------------------+--------------------
 archive_command            | gzip < %p > /Archive/%f | configuration file
 archive_mode               | on                                                        | configuration file
 DateStyle                  | ISO, MDY                                                  | configuration file
 default_text_search_config | pg_catalog.english                                        | configuration file
 dynamic_shared_memory_type | posix                                                     | configuration file
 lc_messages                | en_US.UTF-8                                               | configuration file
 lc_monetary                | en_US.UTF-8                                               | configuration file
 lc_numeric                 | en_US.UTF-8                                               | configuration file
 lc_time                    | en_US.UTF-8                                               | configuration file
 listen_addresses           | *                                                         | configuration file
 log_directory              | log                                                       | configuration file
 log_filename               | postgresql-%a.log                                         | configuration file
 log_rotation_age           | 1440                                                      | configuration file
 log_timezone               | PRC                                                       | configuration file
 log_truncate_on_rotation   | on                                                        | configuration file
 logging_collector          | on                                                        | configuration file
 max_connections            | 203                                                       | configuration file
 max_wal_size               | 1024                                                      | configuration file
 min_wal_size               | 80                                                        | configuration file
 port                       | 64001                                                     | configuration file
 shared_buffers             | 16384                                                     | configuration file
 TimeZone                   | PRC                                                       | configuration file
(22 rows)

Step 4. Backup the application details (pg_hba.conf) of old postgres server.

For example
i have taken sample application details they are.

host    all             all             54.19.98.56/32          trust
host    all             all             55.19.97.86/32          trust

Step 5. Issue the ” \l+ ” & Check the database sizes .

 

IMPLEMENTATION :
===============

Postgres database installation 3 types, they are

1. GRAPHICAL Installation  (./ postgresql.run) .
2. BINAY INSTALLATION  (RPM) .
3. SOURCE CODE INSTALLATION  (configure —) .

Follow the below steps to install postgres database , we have given steps for rpm installation & source code installation types for study purposes, you can install anyone type as per your comfortable.

Binary Installation :
=============
Step 1. As root user install the below packages .

rpm -ivh postgresql-infrastructure-1-6.noarch.rpm
rpm -ivh postgresql113-libs-11.3-1.x86_64.rpm
rpm -ivh postgresql113-server-11.3-1.x86_64.rpm 
rpm -ivh postgresql113-contrib-11.3-1.x86_64.rp
rpm -ivh postgresql113-devel-11.3-1.x86_64.rpm 
rpm -ivh postgresql113-docs-11.3-1.x86_64.rpm
rpm -ivh postgresql113-plperl-11.3-1.x86_64.rpm 
rpm -ivh postgresql113-plpython-11.3-1.x86_64.rpm 
rpm -ivh postgresql113-pltcl-11.3-1.x86_64.rpm
rpm -ivh postgresql113-server-11.3-1.x86_64.rpm
rpm -ivh postgresql113-test-11.3-1.x86_64.rpm

Step 2. Now create a postgres user and directory to be used as data directory for initializing database cluster. Owner of this data directory should be postgres user and permissions should be 700 and also set path for postgresql binaries for our ease.

# useradd pgstain
# passwd pgstain
# mkdir -p /data_11.3/
# chown -R pgstain. /data_11.3/

Step 3. Initialize the postgres cluster as user pgstain .

$ /opt/postgresql/11.3/bin/initdb -D /data_11.3/ -U pgstain

->>> Where -D is location for this database cluster or we can say it is data directory where we want to initialize database cluster, -U for database superuser name.

Step 4. Start the new cluster as user pgstain .

/opt/postgresql/11.3/bin/pg_ctl -D /data_11.3/ restart

 

Source code installation :
==================

1. First install required prerequisites such as gcc, readline-devel and zlib-devel using package manager as shown.

# yum install gcc zlib-devel readline-devel     [On RHEL/CentOS]
# apt install gcc zlib1g-dev libreadline6-dev   [On Debian/Ubuntu]
# Zypper in gcc zlib1g-dev libreadline6-dev     [On SUSE Linux   ]

2. Download the source code tar file from the official postgres website using the following wget command directly on system.

# wget https://ftp.postgresql.org/pub/source/v11.3/postgresql-11.3.tar.gz

3. Use tar command to extract the downloaded tarball file. New directory named postgresql-11.3 will be created.

# tar -xvzf postgresql-11.3.tar.gz

# ll
Sample Output
total 26328
drwxr-xr-x 2 pgstain pgstain     4096 Sep 21  2014 bin
-rwxr-xr-x 1 pgstain pgstain      347 Nov  7  2017 init-user-db.sh
drwxr-xr-x 6 pgstain pgstain     4096 May  7 04:59 postgresql-11.3
-rw-r--r-- 1 pgstain pgstain 25868246 Jul  8 12:04 postgresql-11.3.tar.gz
drwxr-xr-x 2 pgstain pgstain     4096 May 17  2017 public_html

4. Next step for installation procedure is to configure the downloaded source code by choosing the options according to your needs.

# cd postgresql-11.3
# ls -l
total 764
-rw-r--r--  1 pgstain pgstain    730 May  7 04:46 .dir-locals.el
-rw-r--r--  1 pgstain pgstain   1622 May  7 04:46 .gitattributes
-rw-r--r--  1 pgstain pgstain    504 May  7 04:46 .gitignore
-rw-r--r--  1 pgstain pgstain   1192 May  7 04:46 COPYRIGHT
-rw-r--r--  1 pgstain pgstain   3848 May  7 04:46 GNUmakefile.in
-rw-r--r--  1 pgstain pgstain    284 May  7 04:46 HISTORY
-rw-r--r--  1 pgstain pgstain  74257 May  7 04:59 INSTALL
-rw-r--r--  1 pgstain pgstain   1682 May  7 04:46 Makefile
-rw-r--r--  1 pgstain pgstain   1212 May  7 04:46 README
-rw-r--r--  1 pgstain pgstain    522 May  7 04:46 aclocal.m4
drwxr-xr-x  2 pgstain pgstain   4096 May  7 04:58 config
-rwxr-xr-x  1 pgstain pgstain 561752 May  7 04:46 configure
-rw-r--r--  1 pgstain pgstain  84451 May  7 04:46 configure.in
drwxr-xr-x 56 pgstain pgstain   4096 May  7 04:58 contrib
drwxr-xr-x  3 pgstain pgstain   4096 May  7 04:58 doc
drwxr-xr-x 16 pgstain pgstain   4096 May  7 04:59 src

Note : use ./configure –help to get help about various options.

5. Now create a directory where you want to install postgres files and use prefix option with configure.

# mkdir /opt/postgresql/11.3/
# ./configure --prefix=/opt/postgresql/11.3/   
(or)  
#./configure --prefix=/opt/postgresql/11.3/  --without-readline

:- To Know more the out put click below link

Sample OutPuts

6. After configuring, next we will start to build postgreSQL using following make command.

# make          (or) # make world  (additional modules (contrib), type instead PostgreSQL, contrib, and documentation
# make install   (or) # make install-world (if you want contribution extension)

7. Postgresql 11 has been installed in “/opt/postgresql/11.3/” directory. now create a postgres user and directory to be used as data directory for initializing database cluster. Owner of this data directory should be postgres user and permissions should be 700 and also set path for postgresql binaries for our ease.

# useradd pgstain
# passwd pgstain
# mkdir -p /data_11.3/
# chown -R pgstain.pgstain /data_11.3/

8. Now initialize database using the following command as postgres user before using any postgres commands.

# su pgstain
$ /opt/postgresql/11.3/bin/initdb -D /data_11.3/ -U pgstain

Where -D is location for this database cluster or we can say it is data directory where we want to initialize database cluster, -U for database superuser name.

For more info and options we can refer initdb –help.

9. After initializing database, start the database cluster or if you need to change port or listen address for server, edit the postgresql.conf file in data directory of database server.

change the port & listen_address as per the application and restart the postgres database.

$  /opt/postgresql/11.3/bin/pg_ctl -D /data_11.3/ -l logfile start

10. After starting database, verify the status of postgres server process by using following commands.

$ ps -ef |grep -i postgres
$ netstat -apn |grep -i 5432

We can see that database cluster is running fine, and startup logs can be found at location specified with -l option while starting database cluster.

11. Now connect to database cluster and create database by using following commands.

$ psql -p 5432
postgres=# \l   --- to list all databases in cluster
postgres=# \q   --- to quit form postgres console

12. Create the environment variable file then only you can able to access the postgres utility directly on home path without going to bin path.

$ cat /home/pgstain/.profile
export PATH=/opt/postgresql/11.3/bin:$PATH
export PGHOME=/opt/postgresql/11.3
export PGDATA=/data_11.3
export LD_LIBRARY_PATH=/opt/postgresql/11.3/lib
export PGDATABASE=pgstain
export PGUSER=pgstain
export PGPORT=5432
export PGLOCALEDIR=/opt/postgresql/11.3/share/locale
export MANPATH=$MANPATH:/opt/postgresql/11.3/share/man

POSTGRES UPGRADATION :
=====================

Step 1. Stop the old Postgres Server & start the new postgres server.

$ /opt/postgresql/9.5.5/bin/pg_ctl stop -D /data_9.5

Step 2. Restore the backed up file into new cluster server.

$ /opt/postgresql/11.3/bin/psql -U pgstain -p 5432 -d postgres -f /backup/db_bkp_11Jul19112808.dmp
                                                 
   (OR)

{  
1. Stop the both server 9.5 as well as 11.3
2. Below command will check the postgres upgrade compatibility.
 /opt/postgresql/11.3/bin/pg_upgrade -d /data_9.5 -D /data_11.3 -b /opt/postgresql/9.5.5/bin -B /opt/postgresql/11.3/bin -o 64000 -O 5432 -c
3. Below command will upgrade the postgres server.
/opt/postgresql/11.3/bin/pg_upgrade -d /data_9.5 -D /data_11.3 -b /opt/postgresql/9.5.5/bin -B /opt/postgresql/11.3/bin -o 64000 -O 5432

}

Step 7. Change the postgresql.conf file as per “prerequest” step 2.

$ cat postgresql.conf
listen_addresses = '*'         
port = 50000  

## To keep 7 days of logs, one log file per day named postgresql-Mon.log, postgresql-Tue.log, etc, and automatically overwrite last week's log .

logging_collector=on
log_filename = 'postgresql-%a.log'
log_directory = 'log'
log_rotation_age = 1d 
log_truncate_on_rotation=on  

## To enabling archive logs

archive_mode=on
archive_command = 'gzip < %p > /Archive/%f'
.....
...
.etc

Step 8. put the Web/Apps details on pg_ha.conf files as per pre request step 3 .

$ cat pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32           trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     pgstain                                trust
#host    replication     pgstain        127.0.0.1/32            trust
#host    replication     pgstain        ::1/128                 trust
host    all             all             127.0.0.1/32            trust
host    all             all             54.19.98.56/32          trust
host    all             all             55.19.97.86/32          trust

Step 9. Restart the new server to reflect all parameters.

$ /opt/postgresql/11.3/bin/pg_ctl -D /data_11.3  restart

Step 10. Change the backup script as per new server path

"/opt/postgresql/11.3/bin/pg_dumpall" -p 50000 -U pgstain -w > "/backup/db_bkp_`date +%d%b%y%H%M%S`.dmp"
find /backup/ -mtime +3 -exec rm -rf {} \;
find /Archive/ -type f -mtime +3  -exec rm -rf {} \;

 

POSTREQUEST :
============

1. Issue the below query again. to get parameter is updated/changed details and compare it with old paramer ( Pre request step 2) .

SELECT name,setting,source FROM pg_settings
WHERE source NOT IN
('default','client','override','environment variable');

2. Check the database size again ” \l+ ” .

3. Wait for web/Apps data validation, Once you get confirmation uninstall the old postgres version & delete the old data directories.

Connection checking after restored:

pgstain@SCDCB0000181:/Archive> ps -ef|grep postgres
pgstain  40583     1  0 22:16 ?        00:00:00 /opt/postgresql/11.3/bin/postgres -D /data_11.3
pgstain  40585 40583  0 22:16 ?        00:00:01 postgres: checkpointer
pgstain  40586 40583  0 22:16 ?        00:00:00 postgres: background writer
pgstain  40587 40583  0 22:16 ?        00:00:03 postgres: walwriter
pgstain  40588 40583  0 22:16 ?        00:00:00 postgres: autovacuum launcher
pgstain  40589 40583  0 22:16 ?        00:00:00 postgres: archiver   last was 00000001000000020000002D
pgstain  40590 40583  0 22:16 ?        00:00:00 postgres: stats collector
pgstain  40591 40583  0 22:16 ?        00:00:00 postgres: logical replication launcher
root     42056  2324  6 22:28 ?        00:00:10 /usr/sbin/save -LL -s scdca0000044.adcchina.corpintra.net -g Backup53/P0978_D_B1_LIN_FI_02/backup/P0978_D_B1_LIN_FI_02 -a *policy action jobid=174165 -a *policy name=Backup53 -a *policy workflow name=P0978_D_B1_LIN_FI_02 -a *policy action name=backup -y Tue Jul 30 23:59:59 GMT+0800 2019 -w Tue Jul 30 23:59:59 GMT+0800 2019 -m SCDCB0000181-bn1.cn.bg.corpintra.net -b ADCC1 B1 DDBOOST POOL -t 1563200881 -o MODIFIED_ASOF_TIME:timeval=1563200880;RENAMED_DIRECTORIES:index_lookup=on;BACKUPTIME:lookup_range=1563114637:1563200881;REQUESTED_LEVEL:level=incr; -l incr -q -W 78 -N /srv/postgresql/var/inst1/log /srv/postgresql/var/inst1/log
root     42098  2324  0 22:30 ?        00:00:00 /usr/sbin/save -LL -s scdca0000044.adcchina.corpintra.net -g Backup53/P0978_D_B1_LIN_FI_02/backup/P0978_D_B1_LIN_FI_02 -a *policy action jobid=174165 -a *policy name=Backup53 -a *policy workflow name=P0978_D_B1_LIN_FI_02 -a *policy action name=backup -y Tue Jul 30 23:59:59 GMT+0800 2019 -w Tue Jul 30 23:59:59 GMT+0800 2019 -m SCDCB0000181-bn1.cn.bg.corpintra.net -b ADCC1 B1 DDBOOST POOL -t 1563200994 -o RENAMED_DIRECTORIES:index_lookup=on;BACKUPTIME:lookup_range=1563114642:1563200994;REQUESTED_LEVEL:level=incr; -l incr -q -W 78 -N /srv/postgresql/data /srv/postgresql/data
pgstain  42173 41182  0 22:31 pts/1    00:00:00 grep --color=auto postgres

 

4. Check the application backend connection if application connected disconnect the application from DB server 

Below command will be helpful to checking backend connection :
 template1=# select datname, count(*) from pg_stat_activity group by datname;
  datname  | count 
-----------+-------
 testdb    |     8
 template1 |     1
(2 rows)
 
template1=# select datname, numbackends from pg_stat_database;
  datname  | numbackends 
-----------+-------------
 testdb    |           8
 template1 |           1
 template0 |           0
(3 rows)