kxdbxq
Last Updated: February 25, 2016
·
1.587K
· yreinhart

Upgrade PostgreSQL version without losing data and downing service

I have issue when installing Replication Manager in Postgresql 9.3 with Repmgr. Due to 9.3 is obsolete, I need to upgrade to 9.4 without losing and downing live version.

check what version is running

> pg_lsclusters

Ver Cluster Port Status Owner     Data directory               Log file

9.3 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log

install new postgresql 9.4

apt-get install postgresql-9.4 postgresql-client-9.4 libpq-dev

check how many PostgreSQLs are running now:

> pg_lsclusters

Ver Cluster Port Status Owner     Data directory               Log file

9.3 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log

9.4 main    5433 online postgres  /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

So we have 2 active PostgreSQLs. We only need to do 3 simple steps:

  • Drop Newest PostgreSQL cluster for a moment
pg_dropcluster 9.4 main --stop
  • Upgrade old version 9.3 to newer cluster
pg_upgradecluster 9.3 main
  • Drop older version 9.3
pg_dropcluster 9.3 main

After you ensure the every thing works fine that you can uninstall old PostgreSQL and remove the version folders, it will be your choice to use remove or purge.

apt-get remove --purge postgresql-9.3 postgresql-client-9.3
rm -rf /var/lib/postgresql/9.3
rm -rf /usr/lib/postgresql/9.3
rm -rf /etc/postgresql/9.3

1 Response
Add your response

18977

This is the standard behaviour for upgrading PG clusters but it DOES INTRODUCE a downtime when running pg_upgradecluster (it will restart it with minimal access rights for DB migration).

You can only upgrade when using a PG connection pooling service and your upgradecluster is fast enough. But normally you will always have a small downtime. Or you have more than one master server .

over 1 year ago ·