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 trustStep 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 trustStep 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.rpmStep 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_html4. 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 srcNote : 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
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 pgstainWhere -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 start10. After starting database, verify the status of postgres server process by using following commands.
$ ps -ef |grep -i postgres
$ netstat -apn |grep -i 5432We 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 console12. 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/manPOSTGRES 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.5Step 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'
.....
...
.etcStep 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 trustStep 9. Restart the new server to reflect all parameters.
$ /opt/postgresql/11.3/bin/pg_ctl -D /data_11.3 restartStep 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)
