TimescaleDB Upgrade in Docker

TimescaleDB and PostgreSQL Installation, Configuration, and Errors
,
1

Current Version:
OS: Ubuntu 22.04.1
PostgreSQL Version: 13.8
TimescaleDB Version: 2.7.1
Installation Method: Docker(timescale/timescaledb-ha:pg13.7-ts-2.7.1-latest)
Setup: 3 Node of Patroni-HA - ETCD and PgPool using Docker Swarm.

I am Planning to Uprade with docker image timescale/timescaledb-ha:pg14.7-ts-2.10.1-latest.

What I Tried:
I thought this is the simple step: I stopped the container with the current version, replaced the version of image in the docker-compose yml file to reference the new version, and then started the container. When I tried running psql inside the container, I got the error:
psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: No such file or directory
Is the server running locally and accepting connections on that socket?

I checked the log of the container and I found: database file incompatible with server.(screenshot attached)

WhatsApp Image 2023-04-05 at 18.17.26
WhatsApp Image 2023-04-05 at 18.17.261280Ă—67 18.6 KB

I also tried taking the dump of the database and mapping it to an upgraded version of the container. But when I am taking a dump, this error occurs:
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump: hypertable
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.

Also, this method is not feasible as if data grows larger then it will take a lot of time to complete this process.

Is there any other way of upgrading?

2

Hi @aviral.singh21, you need to upgrade PG and Timescale independently.

You can reuse the data without using pg_dump by directly mounting the data directory from the old container to the new one. However, this method only works when the PostgreSQL versions are compatible with each other. I’m not sure if that’s your case. If there are significant differences between the two versions, data migration using pg_dump or pg_upgrade is recommended.

If your database is not that big, try to use pg_dump that seems the most straightforward way to upgrade.

3

I am trying to take pg_dump but due to hypertable present in database, it is not able to take complete backup.
For pg_upgrade, we need an old and new directory. Since old directory is located in another container so I am finding difficulty in applying pg_uprade.

Am I missing any step in any of the solution?

4

Probably you’ll need to create a custom container which contains both folders if you want to run via docker, or expose your docker folder to your host machine.

5

Thanks, that worked. I mounted the old data directory(pg-13.7-ts2…7.1) to path /var/lib/postgresql13 in the container. And new data directory(pg14.7-ts2.10.1) to path /var/lib/postgresql. Then stopped the PostgreSQL in fast mode(coz I was getting error of still in recovery mode), pause the patroni service. Then ran the pg_upgrade command. It successfully upgraded.

1 Like
6

Amazing! thanks for sharing your steps to make it work!