Timescaledb: Unable to upgrade timescaledb from 11 to 12 using tianon/docker-postgres-upgrade

[jflambert]

I'm trying to use tianon/docker-postgres-upgrade to automate timescaledb upgrades from any version to any version. The idea is pretty simple (but ingenious): fetch binaries from both versions of postgresql and run pg_upgrade --link with any other optional flags (such as "-c timescaledb.restoring='on'")

Using this very simple example, I can upgrade vanilla postgresql from 11 to 12 without issue

mkdir -p postgres-upgrade-testing
cd postgres-upgrade-testing
OLDPG='11'
NEWPG='12'

docker run -dit \
  --name postgres-upgrade-testing \
  -e POSTGRES_PASSWORD=password \
  -v "$PWD/$OLDPG/data":/var/lib/postgresql/data \
  "postgres:$OLDPG"

docker exec -it \
  -u postgres \
  postgres-upgrade-testing \
  pgbench -i -s 10

docker stop postgres-upgrade-testing
docker rm postgres-upgrade-testing

docker run --rm \
  -v "$PWD":/var/lib/postgresql \
  "tianon/postgres-upgrade:$OLDPG-to-$NEWPG" \
  --link -v

docker run -dit \
  --name postgres-upgrade-testing \
  -e POSTGRES_PASSWORD=password \
  -v "$PWD/$NEWPG/data":/var/lib/postgresql/data \
  "postgres:$NEWPG"

docker logs --tail 100 postgres-upgrade-testing
sudo rm -rf "$OLDPG"

docker stop postgres-upgrade-testing
docker rm postgres-upgrade-testing

sudo rm -rf *

Now I'm trying to apply this simple example to timescaledb's image and I get an error at the pg_upgrade step.

docker run -dit \
  --name postgres-upgrade-testing \
  -e POSTGRES_PASSWORD=password \
  -v "$PWD/$OLDPG/data":/var/lib/postgresql/data \
  "timescale/timescaledb:latest-pg$OLDPG"

docker exec -it \
  -u postgres \
  postgres-upgrade-testing \
  pgbench -i -s 10

docker stop postgres-upgrade-testing
docker rm postgres-upgrade-testing

docker run --rm \
  -v "$PWD":/var/lib/postgresql \
  "tianon/postgres-upgrade:$OLDPG-to-$NEWPG" \
  --link -v -O "-c timescaledb.restoring='on'"

I get the following error:

There were problems executing ""/usr/lib/postgresql/11/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/11/data" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start >> "pg_upgrade_server.log" 2>&1"
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/lib/postgresql/.s.PGSQL.50432"?

could not connect to source postmaster started with the command:
"/usr/lib/postgresql/11/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/11/data" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

I'm going to guess that timescaledb's image is slightly different from vanilla postgres. Any pointers?

[k-rus]

@jflambert You can see the Dockerfile, which is used for building TimescaleDB image, at https://github.com/timescale/timescaledb-docker/blob/master/Dockerfile

[netrounds-fredrik]

I have successfully used tianon/postgres-upgrade as a base for upgrading from PG11+TS1.7.0 to PG12+TS1.7.0. What you need to do is to install the TimescaleDB extension for both versions of PG in the tianon/postgres-upgrade image and tell postgresql to load the extension. This is the Dockerfile that I used to do the migration:

FROM tianon/postgres-upgrade:11-to-12

RUN apt-get update
RUN apt-get install -y wget
RUN echo "deb https://packagecloud.io/timescale/timescaledb/debian/ stretch main" > /etc/apt/sources.list.d/timescaledb.list
RUN wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | apt-key add -
RUN apt-get update
RUN apt-get install -y timescaledb-1.7.0-postgresql-11
RUN apt-get install -y timescaledb-1.7.0-postgresql-12

Then the migration was then performed using the following command:

docker build -t timescaledb-upgrade .
docker run --rm -v /path/to/postgresql:/var/lib/postgresql timescaledb-upgrade -O "-c timescaledb.restoring='on'" -O "-c shared_preload_libraries=timescaledb"

Note that if you are also going to update the version of TimescaleDB you have to first do that on the old version of PostgreSQL using the normal ALTER EXTENSION timescaledb UPDATE method documented in https://docs.timescale.com/latest/using-timescaledb/update-db.

[jflambert]

Wow @netrounds-fredrik amazing response! Thanks a lot, I'll try this next week, at which point I'll close this issue since it doesn't seem to be Timescale's "fault".

However I do wish that their upgrade page included a few more options, such as this one!

Note that if you are also going to update the version of TimescaleDB you have to first do that on the old version of PostgreSQL

Yeah I kind of learned that the hard way and actually raised an issue about this, which seems to have been picked up recently as "documentation".

[cljk]

Damn! Saw response from @netrounds-fredrik too late. Just finished my own Dockerfile - but his is way better I think since it´s based on a slick prebuild image made for postgres upgrade.

Mine is based on ubuntu but seems to work too - so I´ll leave mine here as well.
I´ll test upgrading my prod db next month (not without a backup and a snapshot).

FROM ubuntu:20.04

RUN apt-get update
RUN apt-get install -y wget gnupg2 software-properties-common

RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main" | tee /etc/apt/sources.list.d/pgdg.list && \
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - &&\
    add-apt-repository ppa:timescale/timescaledb-ppa &&\
    apt-get update

RUN apt-get install -y timescaledb-1.7.0-postgresql-11
RUN apt-get install -y timescaledb-1.7.0-postgresql-12
RUN timescaledb-tune -pg-version 11 -yes
RUN timescaledb-tune -pg-version 12 -yes

docker build . -t timescale-upgrade:1.7.0-pg11-to-12

[jflambert]

hey thanks @cljk for your contribution!

But I didn't think you needed to run timescaledb-tune, I thought that was automatically done on initial startup of timescaledb. Then again I only ever used their docker images.

[cljk]

hey thanks @cljk for your contribution!

But I don't think you needed to run timescaledb-tune since it runs as part of the docker image (to my understanding at least)

To be honest, I don´t know if it´s needed.

Since my base image is a "vanilla" ubuntu there is no magic in there. My script is derived from the timescale installation doc for Ubuntu - just combined version 11 and 12.

https://docs.timescale.com/latest/getting-started/installation/ubuntu/installation-apt-ubuntu

The easiest way to get started is to run timescaledb-tune, which is installed by default when using apt:

[svenklemm]

Closing this since it seems this was resolved. Feel free to reopen if the issue still persists.

[netrounds-fredrik]

Since people are having problems figuring out how to upgrade TimescaleDB and PostgreSQL, I think that the official documentation should be improved.