TimescaleDB in Container

Question

I need help to write Dockerfile to create TimescaleDB in a container. I found this instruction for creating a container:

docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb:latest-pg12

But I want to be able to specify the login and data base name and run creation with pipeline.

Thanks.

Hello, I would suggest you to use specific timescaledb version instead of latest (timescale/timescaledb:latest-pg12). You might run in problem on later deployments as it would install new version. — Onkar Janwa, Mar 22 '21 at 06:55

k_rus · Answer 1 · 2020-06-24T13:09:19.893

I guess you use a docker image from timescale/timescaledb. The Dockerfile used to build these images is public in GH repo timescale/timescaledb-docker. TimescaleDB docker image is based from postgres-alpine and thus inherits entrypoint from there. The description of postgres image presents available environment variables, which can be used during new container start. It includes POSTGRES_DB, which can be used to specify the database to create on start. If the available options is not enough, the entrypoint can be redefined by using option --entrypoint for docker run command.

Summary:

use postgres image environment variables to provide your options, since timescaledb image inherits entrypoint from Postgres-alpine.
provide custom entrypoint with docker run --entrypoint, see the doc.
the dockerfile of TimescaleDB is available public.

Note that Timescale also provides docker image of TimescaleDB with HA for k8s. This one doesn't inherit from postgres and contains own entrypoint.

score 0 · Answer 2 · answered Aug 17 '23 at 05:10

DockerFile

 FROM timescale/timescaledb:latest-pg12
    
    # Set environment variables for PostgreSQL
    ENV POSTGRES_USER=myuser
    ENV POSTGRES_PASSWORD=mypassword
    ENV POSTGRES_DB=mydb
    
    # Copy initialization script into the container
    COPY init.sql /docker-entrypoint-initdb.d/
    
    # Expose the PostgreSQL port
    EXPOSE 5432

you will need to create an init.sql script that contains the necessary SQL commands to create the desired login role, database, and any other customizations you might need. Here's an example init.sql script

init script

-- Create the user with the specified username and password
CREATE USER myuser WITH PASSWORD 'mypassword';

-- Create the database and grant privileges to the user
CREATE DATABASE mydb;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

After creating the Dockerfile and the init.sql script, you can build the Docker image using the following command:

docker build -t my-timescaledb

Then, you can run the container and initialize the TimescaleDB by piping the init.sql script content to the psql command within the container:

cat init.sql | docker run -i --rm --name my-timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=mypassword my-timescaledb:latest-pg12

This approach creates a Docker image that automatically runs the SQL script during the container initialization, creating the specified user, database, and other customizations you might include in the init.sql script.

TimescaleDB in Container

2 Answers2