How to upgrade postgreSQL with zero & without Zero DownTime ?

  • Posted by admin
  • Date January 25, 2019

You can upgrade the postgreSQL  Server with following anyone methods

Method 1: Required More DownTime
pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g. from 9.5.8 to 9.6.4 or from 10.7 to 11.2. It is not required for minor version upgrades, e.g. from 9.6.2 to 9.6.3 or from 10.1 to 10.2.

Method 2: Required Less DownTime than Mehtod 1

  • Stop any running postgres instance;
  • Install the new version and start it; Check if you can connect to the new version as well;
  • Change old version’s postgresql.conf -> port from 5432 to 5433;
  • Start the old version postgres instance;
  • Open a terminal and CD to the new version bin folder;
  • Run pg_dumpall -p 5433 -U | psql -p 5432 -U
  • Stop old postgres running instance;

Method 3: Less DownTime Than Method1 & Method2
It is also possible to use logical replication methods to create a standby server with the updated version of PostgreSQL. This is possible because logical replication supports replication between different major versions of PostgreSQL. The standby can be on the same computer or a different computer. Once it has synced up with the master server (running the older version of PostgreSQL), you can switch masters and make the standby the master and shut down the older database instance. Such a switch-over results in only several seconds of downtime for an upgrade.

This method of upgrading can be performed using the built-in logical replication facilities as well as using external logical replication systems such as pglogical, Slony, Londiste, and Bucardo.

Contact Us

Fields with (*) are compulsory.

admin