Migration from postgresql 9.3 to 9.4

migrate_postgres_9.3_9.4_ubuntu_14.10.md

Upgrade from PostgreSQL 9.3 to 9.4 on Ubuntu 14.10

Install postgresql-9.4

$ sudo apt-get install postgresql-9.4

Then psql version should be 9.4.x

$ psql --version
psql (PostgreSQL) 9.4.2

We now have two clusters running on ports 5432 and 5433. A default cluster has been created for version 9.4 during the installation.

$ 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

Drop the 9.4 cluster to allow a new one to be created

$ sudo pg_dropcluster 9.4 main --stop

Upgrade the 9.3 cluster

$ sudo pg_upgradecluster 9.3 main

GOTCHA:

When all the connections are not closed, this happens:

$ sudo pg_upgradecluster 9.3 main
Stopping old cluster...
pg_ctl: server does not shut down
HINT: The "-m fast" option immediately disconnects sessions rather than
waiting for session-initiated disconnection.
Error: Could not stop old cluster

$ sudo pg_upgradecluster 9.3 main
psql: FATAL:  the database system is shutting down
psql: FATAL:  the database system is shutting down
Use of uninitialized value $out in pattern match (m//) at /usr/share/perl5/PgCommon.pm line 915.
psql: FATAL:  the database system is shutting down
Use of uninitialized value $out in pattern match (m//) at /usr/share/perl5/PgCommon.pm line 921.
Use of uninitialized value $ctype in scalar chomp at /usr/share/perl5/PgCommon.pm line 924.
Use of uninitialized value $collate in scalar chomp at /usr/share/perl5/PgCommon.pm line 925.
Error: could not get cluster locales

So, we need to stop all the connections to the db before the upgrade, then it went well:

$ sudo pg_upgradecluster 9.3 main
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Creating new cluster 9.4/main ...
  config /etc/postgresql/9.4/main
  data   /var/lib/postgresql/9.4/main
  locale en_US.UTF-8
  port   5433
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs...
Fixing hardcoded library paths for stored procedures...
Upgrading database myapp_development...
Analyzing database myapp_development...
Re-enabling connections to the old cluster...
Re-enabling connections to the new cluster...
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Stopping target cluster...
Stopping old cluster...
Disabling automatic startup of old cluster...
Configuring old cluster to use a different port (5433)...
Starting target cluster on the original port...
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with

  pg_dropcluster 9.3 main

The new cluster is now running on port 5432:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5433 down   postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

Check everything is fine and drop the old cluster:

$ sudo pg_dropcluster 9.3 main

Sources: Credits go to https://gist.github.com/dideler/60c9ce184198666e5ab4 I only followed the steps and documented the minor troubles I went in.

Nice!
I had the same problem from postgresql 9.5 to 9.6, I restarted postgres server and stopped it again in order to bypass the "GOTCHA". Steps follow:

pg_dropcluster 9.6 main --stop
pg_upgradecluster 9.5 main # GOTCHA
service postgresql start # wait, slow
service postgresql stop
pg_upgradecluster 9.5 main # worked ok
pg_dropcluster 9.5 main

I was getting the same error, turns out there was one open connection. Thanks.

How do you stop all connections?

Why is this error, given that I updated all packages using a command /sudo apt update && sudo apt -y full-upgrade