Upgrade from PostgreSQL 10 to PostgreSQL 11

 

Scenario:

 

Cluster is running on RHEL 7 OS and current PostgreSQL version is 10, we will upgrade from 10 to 11.

 

Step1) Shutdown the both PostgreSQL instances

 

Step2) Check the pg_hba.conf file and add the below entries

 

# "local" is for Unix domain socket connections only

local   all             all                                     trust

 

 

Step3) Run the below command for upgradation but -c can be used to check the upgradation status before starting actual upgrade.

 

 

-bash-4.2$ /u02/PostgreSQL/11/11/bin/pg_upgrade -d /u02/PostgreSQL/10/data1_10ver -D /u02/PostgreSQL/11/data/ -b /u02/PostgreSQL/10/bin/ -B /u02/PostgreSQL/11/11/bin/ -c

 

Some information regarding the flags used above:
 
-b (--old-bindir=bindir) is the old cluster executable directory
 
-B (--new-bindir=bindir) is the new cluster executable directory
 
-d (--old-datadir=configdir) is the old cluster data directory
 
-D (--new-datadir=configdir) is the new cluster data directory
 
-k (--link)links instead of copying files to the new cluster NOTE: remove this option if you need to keep a local copy of the old cluster's data files. This will increase the time it takes the upgrade to run as well as the size of the cluster on disk. You can NOT go back after using -k. Please be sure that you have enough disk space for 2 whole copies of the cluster if you do NOT use the -k flag.
 
-c (--check) does a "check" only, and doesn't change any data. It will run through numerous consistency checks. Always run your pg_upgrade command with this first.
 
Additional information regarding pg_upgrade can be found in the documentation.

 

 

 

/u02/PostgreSQL/11/11/bin/pg_upgrade -d /u02/PostgreSQL/10/data1_10ver -D /u02/PostgreSQL/11/data/ -b /u02/PostgreSQL/10/bin/ -B /u02/PostgreSQL/11/11/bin/

 

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok

Checking for prepared transactions                          ok

 

If pg_upgrade fails after this point, you must re-initdb the

new cluster before continuing.

 

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok
 
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,

once you start the new server, consider running:

  

 ./analyze_new_cluster.sh----------------------------------------------------------------------------------->

Running this script will delete the old cluster's data files:

 ./delete_old_cluster.sh

 

 

 

-bash-4.2$

-bash-4.2$

 

Note: Once upgradation is completed successfully, it will create two scripts analyze_new_cluster.sh and delete_old_cluster.sh

 

 

Step4) Start the PostgreSQL instance

 

-bash-4.2$ /u02/PostgreSQL/11/11/bin/pg_ctl -D /u02/PostgreSQL/11/data start

waiting for server to start....2020-03-30 20:20:17.242 IST [62864] LOG:  listening on IPv4 address "127.0.0.1", port 5555

2020-03-30 20:20:17.248 IST [62864] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5555"

2020-03-30 20:20:17.263 IST [62865] LOG:  database system was shut down at 2020-03-30 20:17:29 IST

2020-03-30 20:20:17.268 IST [62864] LOG:  database system is ready to accept connections

 done

server started

 

 

Step5) Login to psql prompt and check the version

 

-bash-4.2$ psql -p 5555

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

Type "help" for help.

 

postgres=#

postgres=#

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 ganga     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sandeep   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
           |          |          |             |             | =c/postgres
(5 rows)
 
postgres=# select version();
                                   version
-----------------------------------------------------------------------------
 PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 5.5.0, 64-bit
(1 row)
 

postgres=#

 

 

-bash-4.2$ pwd

/u02/PostgreSQL/10

 

-bash-4.2$ ls -lrt
total 604
drwxr-xr-x.  6 postgres postgres   4096 Aug  9  2019 include
drwxr-xr-x. 11 postgres postgres   4096 Aug  9  2019 share
drwxr-xr-x.  2 postgres postgres   4096 Aug  9  2019 bin
drwxr-xr-x.  7 postgres postgres  12288 Aug  9  2019 lib
-rw-r--r--.  1 postgres postgres   6441 Aug  9  2019 README-linux-x64.txt
-rw-------.  1 postgres postgres 567738 Aug  9  2019 install.log
drwx------  20 postgres postgres   4096 Mar 30 19:25 data
drwx------  19 postgres postgres   4096 Mar 30 20:17 data1_10ver
-rwx------   1 postgres postgres     51 Mar 30 20:17 delete_old_cluster.sh
-rwx------   1 postgres postgres    769 Mar 30 20:17 analyze_new_analyze_new_clustercluster.sh
 
 
 
 
-bash-4.2$ ./analyze_new_cluster.sh -p 5555
 
 
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy.  When it is done, your system will
have the default level of optimizer statistics.
 
If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after

running this script because they will delay fast statistics generation.

 

If you would like default statistics as quickly as possible, cancel

this script and run:

    "/u02/PostgreSQL/11/11/bin/vacuumdb" --all --analyze-only

 

vacuumdb: could not connect to database template1: could not connect to server: No such file or directory

        Is the server running locally and accepting

        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

 

 
export PATH=/u02/PostgreSQL/11/11/bin:$PATH
export PGDATA=/u02/PostgreSQL/11/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5555
 
 
-bash-4.2$ cat .bash_profile1
export PATH=/u02/PostgreSQL/11/11/bin:$PATH
export PGDATA=/u02/PostgreSQL/11/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5555
-bash-4.2$
-bash-4.2$
-bash-4.2$

 

analyze_new_cluster.sh ==> script in order to generate stats so the system is usable.

 

 

-bash-4.2$ ./analyze_new_cluster.sh

This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy.  When it is done, your system will
have the default level of optimizer statistics.
 
If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.
 
If you would like default statistics as quickly as possible, cancel
this script and run:
    "/u02/PostgreSQL/11/11/bin/vacuumdb" --all --analyze-only
 
vacuumdb: processing database "ganga": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "sandeep": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "ganga": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "sandeep": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "ganga": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "sandeep": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
 
Done
 
 
 
-bash-4.2$ ls -lrt
total 604
drwxr-xr-x.  6 postgres postgres   4096 Aug  9  2019 include
drwxr-xr-x. 11 postgres postgres   4096 Aug  9  2019 share
drwxr-xr-x.  2 postgres postgres   4096 Aug  9  2019 bin
drwxr-xr-x.  7 postgres postgres  12288 Aug  9  2019 lib
-rw-r--r--.  1 postgres postgres   6441 Aug  9  2019 README-linux-x64.txt
-rw-------.  1 postgres postgres 567738 Aug  9  2019 install.log
drwx------  20 postgres postgres   4096 Mar 30 19:25 data
drwx------  19 postgres postgres   4096 Mar 30 20:17 data1_10ver----------------------------This directory will be removed once below command is executed
-rwx------   1 postgres postgres     51 Mar 30 20:17 delete_old_cluster.sh
-rwx------   1 postgres postgres    769 Mar 30 20:17 analyze_new_cluster.sh
-bash-4.2$ pwd
/u02/PostgreSQL/10
-bash-4.2$
-bash-4.2$ cat delete_old_cluster.sh
#!/bin/sh
 
rm -rf '/u02/PostgreSQL/10/data1_10ver'
-bash-4.2$
-bash-4.2$
-bash-4.2$ ./delete_old_cluster.sh
-bash-4.2$
-bash-4.2$ cd /u02/PostgreSQL/10/
-bash-4.2$ ls -lrt
total 600
drwxr-xr-x.  6 postgres postgres   4096 Aug  9  2019 include
drwxr-xr-x. 11 postgres postgres   4096 Aug  9  2019 share
drwxr-xr-x.  2 postgres postgres   4096 Aug  9  2019 bin
drwxr-xr-x.  7 postgres postgres  12288 Aug  9  2019 lib
-rw-r--r--.  1 postgres postgres   6441 Aug  9  2019 README-linux-x64.txt
-rw-------.  1 postgres postgres 567738 Aug  9  2019 install.log
drwx------  20 postgres postgres   4096 Mar 30 19:25 data
-rwx------   1 postgres postgres     51 Mar 30 20:17 delete_old_cluster.sh
-rwx------   1 postgres postgres    769 Mar 30 20:17 analyze_new_cluster.sh
-bash-4.2$
 
-bash-4.2$ psql
psql.bin (11.7)
Type "help" for help.
 
postgres=#
postgres=#
 
-bash-4.2$ pwd
/u02/PostgreSQL/11
 
-bash-4.2$ ls -lrt
total 6952
drwx------  3 postgres postgres    4096 Mar 30 18:49 rollbackBackupDirectory
drwx------  3 postgres postgres    4096 Mar 30 18:51 OmniDB
drwx------  4 postgres postgres    4096 Mar 30 18:51 pl-languages
-rwx------  1 postgres postgres 6946528 Mar 30 18:52 Uninstall11
-rwx------  1 postgres postgres  138094 Mar 30 18:52 Uninstall11.dat
drwx------  6 postgres postgres    4096 Mar 30 18:54 11
-rw-------  1 postgres postgres     358 Mar 30 20:04 pg_upgrade_utility.log
-rw-------  1 postgres postgres    1315 Mar 30 20:04 pg_upgrade_internal.log
-rw-------  1 postgres postgres    3459 Mar 30 20:04 pg_upgrade_server.log
drwx------ 19 postgres postgres    4096 Mar 30 20:20 data
 
 
-bash-4.2$ pwd
/u02/PostgreSQL/11/data/pg_hba.conf
 
# TYPE  DATABASE        USER            ADDRESS                 METHOD
 
# "local" is for Unix domain socket connections only
local   all             all                                     trust
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5