Skip to content

Resolve "Upgrade docker-compose postgres to version 12"

Ghost User requested to merge 568-upgrade-docker-compose-postgres-to-version-12 into develop

Merge Request Checklist

Closes #568 (closed)

Background

Since we are going to use PostgreSQL 12 specific features in the next release of Baserow, we need to make sure to provide a migration guide for users that self-host Baserow using our provided docker-compose.yml file. We also need to update the postgres-client in the Docker image for the backend, as to keep pg utilities in sync with the PostgreSQL version (required by Baserows backup/restore commands).

The installation guide for "Install on Ubuntu" also needs to be updated. Once in the "Install Postgres" section, to make sure that at least version 12 gets installed and once in the "Update" section. Here I opt for simply showing the user a warning (in the guide) that links to the official PG guide on how to upgrade.

Upgrade possibilities

According to the official PostgreSQL documentation there are three ways to upgrade between major versions: https://www.postgresql.org/docs/12/upgrading.html

  1. Dump from the old DB and restore in the new DB
  2. Use PGs upgrade utility: pg_upgrade
  3. Replication

The idea behind 2.) and 3.) is to have two separate PG installations/servers running per version. So if you want to migrate from version 11 to version 12, you would install both PG servers and migrate the data either by replication between two servers (replication between two different major versions is supported by PG) or use the utility "pg_upgrade". Both will copy the data between the servers. PG_upgrade can be used in "link"-mode which means data will not be copied, but if the upgrade fails, then the old datadir can't be used anymore.

Both these options would require to start two containers with different versions of PG and for example manually exec into the container running PG12 and start the pg_upgrade utility. Another option would be to provide a "Upgrade Docker image" like in this project: https://github.com/tianon/docker-postgres-upgrade/blob/master/11-to-12/Dockerfile -> The provided images by this project could be used as is, but they currently have a bug in them, which requires manually execing into the container to correct the pg_hba.conf.

Suggested Approach -> 1)

Approach 2) or 3) make the update definitely more complex and involves more manual intervention. I suggest therefore using the easier solution for the enduser which is dumping the data and restoring from backup. Baserow already supports dumping and restoring out of the box, hence this approach is the one with the least friction.

Approach 1) would require the end user to shut down baserow (i.e. docker-compose down), create a backup from the DB as described in our docks. Pull the latest changes, rebuild the images and restore from backup.

Postgres recommends running the pg_dumpall utility from the newer PG version. There are two downsides here. Firstly the Baserow backup command runs "pg_dump" instead of "pg_dumpall". Which should be fine, since people using the "docker-compose.yml" file will only run Baserow with the DB. Secondly with the outlined steps above, the "pg_dump" utility of the old PG version will be used. Using the newer "pg_dump" version with the old DB container would require an additional step of running a one-off container from a custom Dockerfile for example which mounts the old data volume, does the pg_dump from the newer version. By providing a custom Dockerfile for such a function, we might as well go with "pg_upgrade". Running "pg_dump" from the newer server version is only a recommendation from PG, not a must. Running the outlined steps from above works fine.

Things to further consider

We might want to add a check before starting the baserow backend in order to find out whether the PG version a user might be trying to run the backend with is a supported version.

I already played around with a custom Django System Check which could be used. It could connect to the DB, do a query like "Select version();" or "show server_version;" and parse the version number to make a decision whether this is a supported version or not.

Useful links

Testing locally

  • Check out the develop branch
  • If applicable remove pgdata volume
  • Start baserow via "docker-compose up -d", add some test data
  • Shut baserow down and follow the guide "updating-baserow-from-1.5-to-a-later-version.md" from this branch.
  • At the point "git checkout 1.6" use "git checkout 568-upgrade-docker-compose-postgres-to-version-12" instead.
Edited by Ghost User

Merge request reports