Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Unable to upgrade timescaledb from 11 to 12 using tianon/docker-postgres-upgrade #2260

Closed
jflambert opened this issue Aug 26, 2020 · 9 comments

Comments

@jflambert
Copy link

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
Copy link
Contributor

k-rus commented Aug 26, 2020

@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
Copy link

netrounds-fredrik commented Sep 3, 2020

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
Copy link
Author

jflambert commented Sep 4, 2020

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
Copy link

cljk commented Sep 19, 2020

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
Copy link
Author

jflambert commented Sep 21, 2020

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
Copy link

cljk commented Sep 21, 2020

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
Copy link
Member

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

@netrounds-fredrik
Copy link

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

@zoodirector
Copy link

Thanks so much for this information collected here. I am (still) in the process of upgrading pg11/timescale1.7.0 to pg12/timescale2.4.0. I decided on this upgrade path:

  1. pg11 -> pg12
  2. timescale1.7.0 -> timescale2.4.0

Some troubles I am having seem worth sharing. For doing step 1 (pg11 -> pg12) I wanted to use the Dockerfile given by @netrounds-fredrik . This lead me to the following error message when doing docker run ...

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
SQL command failed
SELECT pg_catalog.set_config('search_path', '', false);
ERROR:  could not access file "$libdir/timescaledb-1.5.1": No such file or directory
Failure, exiting

It took me a while to figure out why postgres wanted to load timescaledb-1.5.1. Both databases postgres and data were showing 1.7.0 when executing \dx timescaledb. The database template1 was still referring to version 1.5.1! So do not forget to do the ALTER EXTENSION step on all databases.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

6 participants