The past few days I’ve been trying to update my version of TimescaleDB from v1.6.0 to v2.3.0 (see yesterday’s post on updating the TimescaleDB extension), while also upgrading PostgreSQL from v11 to v13, since TimescaleDB v2.3.0 doesn’t support Postgres 11 anymore.
This second post is about how I managed to upgrade PostreSQL to v13 from v11 while preserving all my data (which is saved in a Docker volume).
Here are the official instructions for upgrading PostgreSQL. All they tell you is to use pg_upgrade. However, that’s got its own complications, and I didn’t want the database to be down for any length of time, so instead I backed up our existing database, then created a new PostgreSQL 13 database with a new Docker volume, and restored the backup to the new database. Following are my steps.
Here’s a link to my previous post on how to automatically backup a TimescaleDB database each night to an AWS S3 bucket, using a custom Docker image I created. Follow those steps to create a backup in an AWS S3 bucket.
Next I created a brand new “docker-compose.prod13.yml” file for my PostgreSQL v13 database.
Before I executed it, I also created a separate volume for PostgreSQL 13:
$ docker volume create timescale-db-pg13
Here’s the yaml file:
version: '3.7' services: timescale13: # Name of the image and tag the Dockerfile creates image: timescale/timescaledb:2.3.0-pg13 volumes: - type: volume # source: timescale-db # the volume name source: timescale-db-pg13 target: /var/lib/postgresql/data # the location in the container where the data are stored read_only: false # Custom postgresql.conf file will be mounted (see command: as well) - type: bind source: ./postgresql_custom.conf target: /postgresql_custom.conf read_only: false env_file: .env command: ["-c", "config_file=/postgresql_custom.conf"] ports: - 0.0.0.0:5432:5432 networks: traefik-public: deploy: # Either global (exactly one container per physical node) or # replicated (a specified number of containers). The default is replicated mode: replicated # For stateless applications using "replicated" mode, # the total number of replicas to create replicas: 1 placement: constraints: # Since this is for the stateful database, # only run it on the swarm manager, not on workers - "node.role==manager" restart_policy: condition: on-failure # Custom backup container that automatically sends backups to AWS S3 each night backup13: image: mccarthysean/timescaledb_backup_s3:13-1.0.8 depends_on: - timescale13 env_file: .env environment: # This takes precedence over the env_file POSTGRES_HOST: timescale13 # Schedule this backup job to backup and upload to AWS S3 every so often # * * * * * command(s) # - - - - - # | | | | | # | | | | ----- Day of week (0 - 7) (Sunday=0 or 7) # | | | ------- Month (1 - 12) # | | --------- Day of month (1 - 31) # | ----------- Hour (0 - 23) # ------------- Minute (0 - 59) SCHEDULE: '0 7 * * *' # The AWS S3 bucket to which the backup file should be uploaded S3_BUCKET: ijack-backup-timescaledb # S3_PREFIX creates a sub-folder in the above AWS S3 bucket S3_PREFIX: myijack_timescaledb_backup networks: traefik-public: healthcheck: # Periodically check if PostgreSQL is ready, for Docker status reporting test: ["ping", "-c", "1", "timescale"] interval: 60s timeout: 5s retries: 5 deploy: # Either global (exactly one container per physical node) or # replicated (a specified number of containers). The default is replicated mode: replicated # For stateless applications using "replicated" mode, # the total number of replicas to create replicas: 1 placement: constraints: # Since this is for the stateful database, # only run it on the swarm manager, not on workers - "node.role==manager" restart_policy: condition: on-failure # Use a named external volume to persist our data volumes: timescale-db-pg13: external: true networks: # Use the previously created public network "traefik-public", shared with other # services that need to be publicly available via this Traefik traefik-public: external: true
I deployed the above stack to my
traefik-public network with:
$ docker stack deploy --with-registry-auth -c docker-compose.prod13.yml timescale13
Then I had two TimescaleDB databases running–the second of which (PG13) was empty and fresh.
First I created the database in the new PG13 container using psql:
$ docker exec -it timescale13_timescale13 psql -X -U postgres
postgres=# create database mydb; postgres=# \list postgres=# exit
After creating the database to which I wanted to restore everything, I created a fresh backup of my PostgreSQL v11 database by going into the new
backup13 container I just created and running the following:
$ docker exec -it timescale13_backup13 $ export POSTGRES_HOST=timescale11 $ bash /backup.sh
Running the above backup and uploading its 2.4 GB to AWS S3 took about ~20 minutes, as it does each night when it runs automatically. After it finished, I ran the following to download and restore the backup to my new PostgreSQL v13 database, into its new Docker volume:
$ docker exec -it timescale13_backup13 $ export POSTGRES_HOST=timescale13 $ bash /download_backup_from_AWS_S3.sh $ bash /restore.sh
The above restore operation took about twice as long (~40 minutes for a 2.4 GB-sized backup file) as the backup operation, but it was worth it because my original database was running the whole time so there was no disruption in the web app service that depends on the database.