Skip to content
Snippets Groups Projects

Provide tool for upgrading PostgreSQL

Once we have Slony bundled (https://gitlab.com/gitlab-org/omnibus-gitlab/merge_requests/1009) we can start working on a tool that can be used to upgrade a PostgreSQL 9.2 database to PostgreSQL 9.5. This process will be semi automatic and does have some requirements. These requirements are that:

  • The user has at least 2 database servers, one for running the old version and one for running the new version
  • The new server should be a clean installation without any database data, this prevents PostgreSQL from refusing to start because the cluster version is older
  • Both servers can connect to each other using an authenticated PostgreSQL super user
  • The user can afford to have at least 2x the storage space during the upgrade procedure (spread over 2 servers)

User Setup

The first step is to set up a replication user on every database. This user must have super user permissions and must have a password set so it can be used to connect to external databases. There are two options here:

  1. Have the user create this user manually, allowing them to choose a password themselves.
  2. Create a user using a special command with a unique password for every server. This requires the user to note down the password to use for the source/target database

In both cases the user should be created as follows:

CREATE ROLE slony WITH SUPERUSER LOGIN REPLICATION ENCRYPTED PASSWORD 'password string here';
ALTER ROLE slony SET statement_timeout TO 0;

Here "slony" is the role/user name. This role must have super user permissions.

Database Preparation

Once the user has been set up on every database we can start preparing the databases. To do so I envision one running the following command:

sudo gitlab-postgresql replication-config --from-host 10.0.1.0 --from-user slony --from-password XXX --from-port 5432 --to-host 10.0.1.1 --to-user slony --to-password XXX --to-port 5432

This would generate a configuration file for Slony containing all the replication settings needed. This would also prepare the databases. Next we need to make sure that this configuration file somehow exists on all servers. One way of doing this is to run the above command on every server and make sure it skips steps that have already been completed. Alternatively we can stuff the configuration file in a database table and pull it onto a server when starting replication.

Once the configuration file is present on all servers one can start replication:

sudo gitlab-postgresql replicate

This wouldn't need any extra options as everything needed is in the Slony configuration file.

At some point replication is done (I haven't found a way of tracking progress accurately other than comparing database sizes, which may not be identical). We now need to make the new server the master:

sudo gitlab-postgresql promote 10.0.1.1 # this would promote the host at 10.0.1.1 to the master

Before doing so the user should make sure their application is not writing any data. This will mean downtime, but only for a few minutes.

Once the application is down and the new server is the master we can stop replication and remove any configuration settings, triggers, etc:

sudo gitlab-postgresql stop-replication # run this on every server
sudo gitlab-postgresql remove-replication # this only needs to be executed on a single server since it would just run a bunch of DB queries

So all in all this would require some extra steps, but it's much easier than using Slony directly and doesn't require hours of downtime. In case of GitLab.com it took around 6 hours for the new PostgreSQL 9.5.4 database to be in sync with the 9.4 database.

cc @marin