CODEWITHSUNDEEP
postgresqldockerdocker-composetimescaledb
Escher
Escher

Reputation: 5786

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

I'm trying to use the timescaledb extension, and so I'm running their official docker image.

In the last line of my docker entrypoint file, I run:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

I verify that it's available for use with \dx in psql. As soon as I try to make use of the extension, I get:

No function matches the given name and argument types. You might need to add explicit type casts.

I find I have to add it manually by execing into psql and running CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

What's wrong with my entrypoint? Why do I have to manually create the extension after the container is built and running?

Edit: here's the full entrypoint script:

#!/bin/bash
set -e

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;
    CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
EOSQL

Upvotes: 2

Views: 1038

Answers (2)

suntruth
suntruth

Reputation: 351

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.

Upvotes: 3

user330315
user330315

Reputation:

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.

Upvotes: 4

Related Questions