Database upgrade - PostgreSQL

 

Upgradation :-

Auto upgrade:

1) Stop the server
$] /user/local/pgsql_11/bin/pg_ctl -D /var/lib/pgsql/11/ stop

2) Compatibiilty check:-
$] /usr/local/pgsql_11/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/local/pgsql_11/bin -d /var/lib/pgsql/10/ -D /var/lib/pgsql/11/ - p 5432 -P 5433 -c 
output --> *Clusters are comptible* -- "ok"

3) Stop the older custer i.e. 10 - PostgreSQL
$] /user/local/pgsql/bin/pg_ctl -D /var/lib/pgsql/10/ stop

4) Run the below command for Actual upgradation
 /usr/local/pgsql_11/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/local/pgsql_11/bin -d /var/lib/pgsql/10/ -D /var/lib/pgsql/11/ - p 5432 -P 5433
output --> Performing Consistency Checks
           Peformaing Upgrade
           Upgrade Completed
Optimizer statistics are not transfered by pg_uprade 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

 

Actual upgradation command :-

 

$ pg_upgrade -d /data/db12/ \

       -D /data/db13 \

       -b /path/pg12/bin/ \

       -B /usr/local/Cellar/postgresql/13.0/bin/

Compatibility check :-

Note:- We have to remove -C for doing actual upgrade: -