Upgrading from very old installation (>5 versions) and multiple databases

[b-dreissigacker]

Relevant system information:

• OS: Ubuntu 18.04
• PostgreSQL version 10.11
• TimescaleDB version 1.6.0
• Installation method: Docker, image timescale/timescaledb:1.6.0-pg10-oss

Describe the bug when starting the docker container I get:

[28] ERROR: could not access file "$libdir/timescaledb-1.2.1": No such file or directory [1] LOG: worker process: TimescaleDB Background Worker Scheduler (PID 28) exited with exit code 1

but everything else seems to run fine after that.

To Reproduce Steps to reproduce the behavior:

1. Start a docker container with the image timescale/timescaledb:1.2.1-pg10-oss and volumes:
   • database:/var/lib/postgresql/data
   • pg_sock:/var/run/postgresql/
2. Follow the upgrade guide https://docs.timescale.com/latest/using-timescaledb/update-db#update and update the image to timescale/timescaledb:1.6.0-pg10-oss

Expected behavior The database should start up without any errors.

Actual behavior [28] ERROR: could not access file "$libdir/timescaledb-1.2.1": No such file or directory

[bboule]

hey @b-dreissigacker thanks for the report... just so I have all of the correct information you were running v1.2.1 in docker and the upgraded to 1.6.0 and saw the error message? But after it got past that all seems to be running OK? As you use it are you seeing any other side effects (i.e. are jobs not running or any generally odd behavior) or is the database running as normal?

[b-dreissigacker]

hi @bboule, yes you understand it correct. But to be honest I just tried a few selects from comandline client by now. If I notice anything else during development I will make sure to let you know.

[LloydAlbin]

I have run into this issue also. After you UPDATE the extension everything works fine.

The reason for the error is that the Dockerfile only copies the last 5 versions of the of the extension plus compiles the current version.

In the case for version 1.6.0, the docker image would only contain the following versions of the extension. 1.4.0 1.4.1 1.4.2 1.5.0 1.5.1 1.6.0 (current)

############################
# Grab old versions from previous version
############################
ARG PG_VERSION
FROM timescale/timescaledb:${PREV_TS_VERSION}-pg${PG_VERSION}${PREV_EXTRA} AS oldversions
# Remove update files, mock files, and all but the last 5 .so/.sql files
RUN rm -f $(pg_config --sharedir)/extension/timescaledb--*--*.sql \
    && rm -f $(pg_config --sharedir)/extension/timescaledb*mock*.sql \
    && rm -f $(ls -1 $(pg_config --pkglibdir)/timescaledb-tsl-*.so | head -n -5) \
    && rm -f $(ls -1 $(pg_config --pkglibdir)/timescaledb-1*.so | head -n -5) \
    && rm -f $(ls -1 $(pg_config --sharedir)/extension/timescaledb-*.sql | head -n -5)

[b-dreissigacker]

@LloydAlbin With UPDATE the extension you mean ALTER EXTENSION timescaledb UPDATE;? I have done that but the error on startup is still there.

                                      List of installed extensions
    Name     | Version |   Schema   |                            Description                            
-------------+---------+------------+-------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 1.6.0   | public     | Enables scalable inserts and complex queries for time-series data

[LloydAlbin]

@b-dreissigacker Once I did the ALTER EXTENSION timescaledb UPDATE; for all databases that contain the extension. Doing so will get the messages to go away. This included the postgres and template1 databases, in my case.

Note: I ran into this same issue when upgrading from 1.2.2 to 1.5.1.

Pre-Fix:

psql -h kw-beta-e01 -d template1 -U postgres -c "SELECT * FROM pg_available_extension_versions WHERE name = 'timescaledb';"
ERROR:  could not access file "$libdir/timescaledb-1.2.2": No such file or directory

The fix:

/psql -h kw-beta-e01 -d template1 -U postgres -c "ALTER EXTENSION timescaledb UPDATE;"
ALTER EXTENSION

Post-Fix:

psql -h kw-beta-e01 -d template1 -U postgres -c "SELECT * FROM pg_available_extension_versions WHERE name = 'timescaledb';"
    name     | version | installed | superuser | relocatable | schema | requires |                              comment
-------------+---------+-----------+-----------+-------------+--------+----------+-------------------------------------------------------------------
 timescaledb | 1.4.1   | f         | t         | f           |        |          | Enables scalable inserts and complex queries for time-series data
 timescaledb | 1.5.1   | t         | t         | f           |        |          | Enables scalable inserts and complex queries for time-series data
 timescaledb | 1.4.2   | f         | t         | f           |        |          | Enables scalable inserts and complex queries for time-series data
 timescaledb | 1.5.0   | f         | t         | f           |        |          | Enables scalable inserts and complex queries for time-series data
 timescaledb | 1.3.2   | f         | t         | f           |        |          | Enables scalable inserts and complex queries for time-series data
 timescaledb | 1.4.0   | f         | t         | f           |        |          | Enables scalable inserts and complex queries for time-series data

Note that the list of available extensions does not include the 1.2.2 that I was upgrading from because the docker image only contains 6 versions, the current version and the 5 previous versions.

[b-dreissigacker]

@LloydAlbin I can confirm that this fixes the issue, thank you. Maybe someone should ad a sentence to the update documentation that ALTER EXTENTION must be called for all databases?

[bboule]

@b-dreissigacker and @LloydAlbin thank you both for highlighting this I will be leaving the issue open as we look to make the doc change suggested above!! thanks again for reporting the issue!

[mfreed]

Sorry, @LloydAlbin @b-dreissigacker looking back at the discussion, I'm not sure I fully understand the issue?

Was the problem that you upgraded the underlying software (from a very old version) but didn't run ALTER yet to complete the upgrade process? So that if you had upgraded from more than 5 versions back, you were getting and error, while if you had upgraded from less than 5 versions, you weren't getting an error?

Or that you had executed it on one database, but didn't know you had to run ALTER EXTENSION on every database to which the extension is installed?

(The former feels a bit more like a knowledge base "gotcha"; not fully clear where it would go in the docs. I'll otherwise look if there's an appropriate place to add a :NOTE: about the "all databases in which installed" issue.)

[LloydAlbin]

@mfreed

Upgrading more than 5 version, which in my case IS NOT VERY OLD, because 1.2.2 was released on 3/13/2019 and 1.5.1 was released on 11/11/2019. This is less than 8 months between releases and upgrades. You can't expect production environments to upgrade on less than a yearly basis. We only upgrade PostgreSQL servers 2-3 years due to the federal re-validation requirements which we must perform in-house as PostgreSQL itself is not a validated software.

If you are upgrading less than 5 version you will not run into the "could not access file" error message, because the docker contains the last 5 versions of the TimescaleDB dll's. Once you run the ALTER EXTENSION on your custom database, that database will be running the new version of the software while the postgres and template1 databases will still be running the old version of the software.

If you upgrade more than 5 versions, then you will run into "could not access file" error message even after running ALTER EXTENSION on your custom database because the docker has the extension loaded into postgres and template1 databases and the error message is coming from these databases not your custom database.

You will also run into this issue if you do two upgrade of 3 versions each and only run the ALTER EXTENSION on your custom database. Due to the fact that again that the tempalte1 and postgres databases will not have been upgraded with the ALTER EXTENSION command.

The documentation only talks about upgrading your custom databases, which is all that you technically need to do. Except if you are like @b-dreissigacker and I who don't like to see any error messages in our log files. Then we want to track down and fix the error messages. To make it so that this error does not occur, all databases except for template0 need to have the ALTER EXTENSION run on them.

Notes on databases within the PostgreSQL cluster: template0 - Internal template for creating new databases. template1 - A user customized template based off of template0. TimescaleDB customized this template with the TimescaleDB Extension so that all new databases created on the cluster will automatically contain the TimescaleDB extension. postgres - The generic database that GUI tools talk to first to be able to read a list of databases, etc. mydb - Your custom database name.

Notes on 00_install_timescaledb.sh script inside the docker image: When running the docker for the first time, the 00_install_timescaledb.sh installs the TimescaleDB extension into the postgres and template1 databases, so that when the users database is created from template1 is will have the TimescaleDB extension automatically added.

# create extension timescaledb in initial databases
psql -U "${POSTGRES_USER}" postgres -c "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"
psql -U "${POSTGRES_USER}" template1 -c "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"

if [ "${POSTGRES_DB:-postgres}" != 'postgres' ]; then
  psql -U "${POSTGRES_USER}" "${POSTGRES_DB}" -c "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"
fi

[kandyrise]

Instruction for those running docker images.

1. Ensure to start the docker container. Find the 'CONTAINER ID' of the docker images
2. Login to the image, using docker exec -it ${CONTAINER-ID} /bin/sh
3. In the shell, run the following command psql -U "postgres" -d postgres -c "ALTER EXTENSION timescaledb UPDATE;" psql -U "postgres" -d my_db -c "ALTER EXTENSION timescaledb UPDATE;"

[LloydAlbin]

Kandy, I have added one ling that you were missing in your instructions. Lloyd

Instruction for those running docker images.

1. Ensure to start the docker container. Find the 'CONTAINER ID' of the docker images
2. Login to the image, using docker exec -it ${CONTAINER-ID} /bin/sh
3. In the shell, run the following command psql -U "postgres" -d postgres -c "ALTER EXTENSION timescaledb UPDATE;" psql -U "postgres" -d tempalte1 -c "ALTER EXTENSION timescaledb UPDATE;" psql -U "postgres" -d my_db -c "ALTER EXTENSION timescaledb UPDATE;"

From: Kandy Nachimuthu notifications@github.com Sent: Tuesday, March 24, 2020 12:06 PM To: timescale/docs.timescale.com-content docs.timescale.com-content@noreply.github.com Cc: Albin, Lloyd P lalbin@scharp.org; Mention mention@noreply.github.com Subject: Re: [timescale/docs.timescale.com-content] Upgrading from very old installation (>5 versions) and multiple databases (#300)

Instruction for those running docker images.

1. Ensure to start the docker container. Find the 'CONTAINER ID' of the docker images
2. Login to the image, using docker exec -it ${CONTAINER-ID} /bin/sh
3. In the shell, run the following command psql -U "postgres" -d postgres -c "ALTER EXTENSION timescaledb UPDATE;" psql -U "postgres" -d my_db -c "ALTER EXTENSION timescaledb UPDATE;"

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_timescale_docs.timescale.com-2Dcontent_issues_300-23issuecomment-2D603449594&d=DwMCaQ&c=eRAMFD45gAfqt84VtBcfhQ&r=Xh-FbvkmuY2wtq98eUcUtlVLZcPEI5HL7TuqKWjcsAI&m=HdIV1CBJprg-_i9FcMwNrY3Gm5oX2Kxy8rn1IowLkEw&s=4GLF-iGJyuDsbNF_Rnbc7BYXDCbPDvU0uW5h3MkyVMA&e=, or unsubscribehttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_notifications_unsubscribe-2Dauth_ACB4JQYCJWBEY6OZGJJXDVLRJEACPANCNFSM4KXNDPCA&d=DwMCaQ&c=eRAMFD45gAfqt84VtBcfhQ&r=Xh-FbvkmuY2wtq98eUcUtlVLZcPEI5HL7TuqKWjcsAI&m=HdIV1CBJprg-_i9FcMwNrY3Gm5oX2Kxy8rn1IowLkEw&s=U2MIa0yDBMX2aqS27MxTX8HyXdkb68NMN8pRn-0ZEuI&e=.

[solugebefola]

If I understand this correctly, the issue is that for databases created within the instance, but not necessarily using TimescaleDB (postgres, and template1 are on this list by default), the extension is still installed, but not obviously.
So that when upgrading to a version newer than 5 versions, users would naturally update TimescaleDB on their created DBs, but not necessarily know to update on postgres, template1 (and any others that aren't necessarily using the extension). This leads to an error on those DBs, but it's not obvious that it's coming from those DBs?

So a warning is needed to update ALL DBs on the instance, and specifically call out postgres and template1?

[LloydAlbin]

Solar,

You are basically correct except there is one more database but it will not contain the TimescaleDB extension.

The Docker image installs the TimescaleDB into the postgres database and template1 database. This means that when you have the docker create your database, it uses template1 which has the TimescaleDB extension already installed, so your new database will automatically have the TimescaleDB extension installed. The one other database on the cluster will be template0 which should never have anything installed.

So to make sure there are no error messages in your log file about missing the old versions of the TimescaleDB extension, you must update the extension on your database plus the postgres and template1 databases. But you would never do it on the template0 database. Most docker images would only have the one database you created, but if you have created more than one, then yes, you need to update all of your databases plus the postgres and template1 databases.

So I agree that a warning is needed to update ALL DBs on the instance, and specifically call out postgres and template1 while excluding template0.

Lloyd

From: Solar Olugebefola notifications@github.com Sent: Wednesday, March 25, 2020 7:57 AM To: timescale/docs.timescale.com-content docs.timescale.com-content@noreply.github.com Cc: Albin, Lloyd P lalbin@scharp.org; Mention mention@noreply.github.com Subject: Re: [timescale/docs.timescale.com-content] Upgrading from very old installation (>5 versions) and multiple databases (#300)

If I understand this correctly, the issue is that for databases created within the instance, but not necessarily using TimescaleDB (postgres, and template1 are on this list by default), the extension is still installed, but not obviously. So that when upgrading to a version newer than 5 versions, users would naturally update TimescaleDB on their created DBs, but not necessarily know to update on postgres, template1 (and any others that aren't necessarily using the extension). This leads to an error on those DBs, but it's not obvious that it's coming from those DBs?

So a warning is needed to update ALL DBs on the instance, and specifically call out postgres and template1?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_timescale_docs.timescale.com-2Dcontent_issues_300-23issuecomment-2D603887228&d=DwMCaQ&c=eRAMFD45gAfqt84VtBcfhQ&r=Xh-FbvkmuY2wtq98eUcUtlVLZcPEI5HL7TuqKWjcsAI&m=6oNzB7slwahWCgVFK6OPbPY7ONM8-frw6twiraqb-X8&s=9FlwQ6P6iqDzQczNOjES4CUYGrqVkpAEbyonlhhseDk&e=, or unsubscribehttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_notifications_unsubscribe-2Dauth_ACB4JQ6AXNQGGHMSFSGKP5LRJILR5ANCNFSM4KXNDPCA&d=DwMCaQ&c=eRAMFD45gAfqt84VtBcfhQ&r=Xh-FbvkmuY2wtq98eUcUtlVLZcPEI5HL7TuqKWjcsAI&m=6oNzB7slwahWCgVFK6OPbPY7ONM8-frw6twiraqb-X8&s=rWRs49hPTkIpEbH-48RyzWZ-CI4veTzJFiNRArUcRM8&e=.

[akashagarwal7]

Thanks @LloydAlbin! This line is definitely necessary to avoid falling into same issue when (re)creating a database:

psql -U "postgres" -d template1 -c "ALTER EXTENSION timescaledb UPDATE;"

I would also suggest dropping the extension from template1 to prevent the extension from getting added to any new databases automatically.