Can't access postgres extention created in docker entrypoint file, until I manually created it in psql Can't access postgres extention created in docker entrypoint file, until I manually created it in psql docker docker

Can't access postgres extention created in docker entrypoint file, until I manually created it in psql


Disclaimer: I don't know docker, but this seems only related to Postgres, not Docker itself


create extension will create the extension in database psql is currently connected to. Seeing the script, this is most probably the default database postgres that you connect to.

So the extension will be created in the postgres database, not in the testdb database.

You have two options on how to change that:

1. Use the template1 database

Anything created in the template1 database will automatically be created in every database created afterwards. So if you connect to the template database and run the create extension before creating the test database, the extension will automatically be available:

psql -v ON_ERROR_STOP=1 --dbname=template1 --username "$POSTGRES_USER" <<-EOSQL    CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;    CREATE USER test_user PASSWORD 'password123';    ALTER USER test_user WITH SUPERUSER; --needed to create timescaledb extension    CREATE DATABASE testdb OWNER test_user;    GRANT ALL PRIVILEGES ON DATABASE testdb TO test_user;    CREATE DATABASE tsdb OWNER test_user;    GRANT ALL PRIVILEGES ON DATABASE tsdb TO test_user;    ALTER USER test_user CREATEDB;EOSQL

Note that the extension is created before anything else. The actual order isn't that important, the only thing that is important is that it's done before creating a new database.

2. Connect to the newly created database

Switch to the newly created database from within psql before you create the extension using the \connect command in psql

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL    CREATE USER test_user PASSWORD 'password123';    ALTER USER test_user WITH SUPERUSER; --needed to create timescaledb extension    CREATE DATABASE testdb OWNER test_user;    GRANT ALL PRIVILEGES ON DATABASE testdb TO test_user;    CREATE DATABASE tsdb OWNER test_user;    GRANT ALL PRIVILEGES ON DATABASE tsdb TO test_user;    ALTER USER test_user CREATEDB;    \connect testdb    CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;EOSQL

The main difference between the two methods is that with the first, the extension will be available automatically in all databases that are created in the future. Whereas with the second method it is only available in the testdb


Unrelated, but: the newly created user does not need the superuser privilege as the extension is created using the postgres user, not the newly created one.


To chime in with the prior answer, the TimescaleDB extension is applied per database, so if you run CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; without first connecting to your desired database with \c yourdatabase it will apply the extension to the default database. See http://docs.timescale.com/v0.9/getting-started/setup for the step-by-step instructions to apply after install.