Upgrading Postgresql from/to 9.1/9.3 along with Ubuntu 14.04


This is about getting Postgresql from versions 9.1 to 9.3 initiated by the upgrade of Ubuntu to 14.04 (Lts). Actually, successfully upgrading Ubuntu to trusty finds you starting with the old 9.1 still running as usual and the new 9.3 already installed and initialized (initdb). Since file locations for Postgresql on Debianlikes always feature the version with the pathnames, this not much of a problem. Notably at least, the old 9.1 keeps to the known default port of 5432 whereas the new 9.3 has been configured to the also known increment of 5433. To anticipate the results below, the databases (clusters) upgrade finished without any problems, a simple hurdle being the correct callout of pg_upgrade against pg_ctl, which is not officially installed on Debianlikes. Beware to install the postgres-xc-package as recommended when trying to tentatively lift off pg_ctl on the command line – you’re not going to need a bit of it. All you’ll need is to pass some dedicated pg_ctl-options within the option list of pg_upgrade, just an exception for Debianlikes, deviating from the otherwise still excellent Postgresql documentation (hats off). Docs that helped along the way read as follows:

The remaing text is very much scriptlike but still well indented such that you should get the ideas and command examples easily.

  • necessary options
    • pg_upgrade –old-bindir=, –new-bindir=, –old-datadir=, –new-datadir=, –old-options , –new-options , –check
  • current postrgresql dir’s
    • old: bin=/usr/lib/postgresql/9.1/bin, data=/var/lib/postgresql/9.1/main, cfg=/etc/postgresql/9.1/main
    • new: bin=/usr/lib/postgresql/9.3/bin, data=/var/lib/postgresql/9.3/main, cfg=/etc/postgresql/9.3/main
  • check the existence of …/9.3/bin/pg_upgrade binary and …/9.3/lib/pg_upgrade_support library
  • check the current psql connect behaviour
    • /usr/bin/psql _calls_ /usr/share/postgresql-common/pg_wrapper, which always executes the latest version for backward compatibility (see the comments within the file)
    • the old cluster runs on default port 5432, the new one on port 5433 ; see /var/run/postgresql/*
    • omitting the port when connecting through psql therefore connects with the new psql to the old cluster ; execute sudo -i -u postgres psql and some part of the output will be “… psql (9.3.5, server 9.1.13) …” ; the output for the new cluster, executing sudo -i -u postgres psql -p 5433 only reads “… psql (9.3.5) …”
    • another test uses -l, the lists databases option of psql to verify where a connect goes
      execute sudo -i -u postgres psql -l and see the known users and databases
      execute sudo -i -u postgres psql -p 5433 -l and see nothing new yet
  • check initdb and extensions
    • initdb already executed on ubuntu (cluster has been created and is working)
    • again, using the port diffs to connect to the old and the new cluster view the extensions by selecting select * from pg_extension;
    • me, i need to install “adminpack” on the new cluster as well ; CREATE EXTENSION adminpack; ; “plpgsql” was already installed
  • check custom shared object files and any authentication adjustments
    • does not apply for me
  • (optionally get the status and) stop both servers
    • using “service”
      execute sudo service postgresql status, prints “9.1/main (port 5432): online” and “9.3/main (port 5433): online”
      execute sudo service postgresql stop, prints “* Stopping PostgreSQL 9.1 database server [ OK ]” and “* Stopping PostgreSQL 9.3 database server [ OK ]”
      or sudo service postgresql (start|stop) (9.1|9.3)
    • using “pg_ctl” is not recommended or even marked as installed on debian/ubuntu (although in fact it is installed and even used by “pg_upgrade”), iff deviating from “service” for any reason use “pg_ctlcluster”, e.g. sudo pg_ctlcluster 9.3 main stop
  • upgrade check (again always using the newer binary)
    • execute sudo -i -u postgres /usr/lib/postgresql/9.3/bin/pg_upgrade --old-bindir=/usr/lib/postgresql/9.1/bin --new-bindir=/usr/lib/postgresql/9.3/bin --old-datadir=/var/lib/postgresql/9.1/main --new-datadir=/var/lib/postgresql/9.3/main --old-port 5432 --new-port 5433 --old-options ' -c config_file=/etc/postgresql/9.1/main/postgresql.conf' --new-options ' -c config_file=/etc/postgresql/9.3/main/postgresql.conf' --check
    • prints
      Performing Consistency Checks
      —————————–
      Checking cluster versions ok
      Checking database user is a superuser ok
      Checking for prepared transactions ok
      Checking for reg* system OID user data types ok
      Checking for contrib/isn with bigint-passing mismatch ok
      Checking for presence of required libraries ok
      Checking database user is a superuser ok
      Checking for prepared transactions ok
      *Clusters are compatible*
    • see what log files have been written sudo -i -u postgres ls /var/lib/postgresql/*.log
      /var/lib/postgresql/pg_upgrade_internal.log
      /var/lib/postgresql/pg_upgrade_utility.log
      /var/lib/postgresql/pg_upgrade_server.log
    • inspect the main log sudo -i -u postgres less /var/lib/postgresql/pg_upgrade_server.log
  • upgrade – like the check above omitting the –check option
    • execute sudo -i -u postgres /usr/lib/postgresql/9.3/bin/pg_upgrade --old-bindir=/usr/lib/postgresql/9.1/bin --new-bindir=/usr/lib/postgresql/9.3/bin --old-datadir=/var/lib/postgresql/9.1/main --new-datadir=/var/lib/postgresql/9.3/main --old-port 5432 --new-port 5433 --old-options ' -c config_file=/etc/postgresql/9.1/main/postgresql.conf' --new-options ' -c config_file=/etc/postgresql/9.3/main/postgresql.conf'
    • prints

      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
  • reverse the ports for old and new (i.e., 9.1 and 9.3), such that new will use the default, in
    /etc/postgresql/(9.1|9.3)/main/postgresql.conf
  • restart both servers (and optionally get the status and have a peek inside)
    • using “service”
      execute sudo service postgresql start, prints “* Starting PostgreSQL 9.1 database server [ OK ]” and “* Starting PostgreSQL 9.3 database server [ OK ]”
      execute sudo service postgresql status, prints “9.1/main (port 5433): online” and “9.3/main (port 5432): online”
      execute sudo -i -u postgres psql -l and see the known users and databases within *new*
  • create initial statistics using the “pg_upgrade” provided script
    • execute sudo -i -u postgres /var/lib/postgresql/analyze_new_cluster.sh
  • cleanup the old cluster
    • includes the old data as in /var/lib/postgresql/delete_old_cluster.sh
    • however, misses out: /usr/lib/postgresql/9.1/bin, /usr/share/doc/postgresql-client-9.1
      /usr/share/postgresql/9.1/man, for whatever reason
      execute sudo apt-get --purge remove postgresql-9.1
      execute sudo rm -R /usr/lib/postgresql/9.1
      execute sudo rm -R /usr/share/doc/postgresql-client-9.1
      execute sudo rm -R /usr/share/postgresql/9.1

Uhhm, jep, this is why --old-options and --new-options with the pointer to postgresql.conf are necessary, otherwise pg_upgrade will fail as follows in /var/lib/postgresql/pg_upgrade_server.log because on Debianlikes we have the config below /etc/postgresql/9.1/main not /var/lib/postgresql/9.1/main.

-----------------------------------------------------------------
pg_upgrade run on Fri Aug 22 13:45:11 2014
-----------------------------------------------------------------
command: "/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.1/main" -o "-p 5432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/postgresql'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....postgres cannot access the server configuration file "/var/lib/postgresql/9.1/main/postgresql.conf": No such file or directory
.... stopped waiting
pg_ctl: could not start server

Have fun!

Advertisements

4 comments

  1. This is a great tutoria, best I’ve found. One question: You say to switch the ports. I’ll go look that up, but if you included that bit of information your post would have all the details needed to make this upgrade work.

    Like

    1. hi, it is deep within the script above, lookup or just use this snippet which names the relevant files:
      * reverse the ports for old and new (i.e., 9.1 and 9.3), such that new will use the default, in
      /etc/postgresql/(9.1|9.3)/main/postgresql.conf .
      within the files itself do look for the “port = ” key below the connection settings.
      regards p

      Like

  2. These instructions are excellent. I just followed them all the way through with no major issues. Two observations you might want to incorporate:

    1. I had to tweak the settings in /etc/postgresql/({9.3,9.1}/main/pg_hba.conf to set the login as trusted.
    2. It’s a bit unclear in the very last step if you’re proposing the apt-get purge as an alternative or as an addition to the delete_old_cluster.sh script.

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s