Enabling TimescaleDB Streaming Replication for PostgreSQL 13 with Docker

Edoardo 🇮🇹 Oct 22, 2021 5 min read
Table of Contents
This post illustrates the process of setting up streaming replication for a PostgreSQL 13 database with the TimescaleDB extension using Docker, in order to create a real-time 1:1 replica of the primary database.

Introduction #

Part of my work at Magenta was to investigate the possibility of having a 1:1 replica of AirQino’s production database (which had more than 100M rows, running PostgreSQL 13 and Timescale), possibly on a different server, and having them in sync all the time for performance tests, offloading heavy queries and availability (also check out my other post on implementing continuous aggregates to make database queries run faster).

As it turns out, TimescaleDB does not support for logical replication (using libraries like pglogical), but it can handle replication using PostgreSQL’s built-in streaming replication, which is what i ended up using. PostgreSQL achieves streaming replication by having replicas continuously stream the WAL (Write-Ahead Logging) from the primary database. For more information see PostgreSQL’s WAL Documentation1.

Preparing the primary database #

  1. Create a PostgreSQL user with a role that allows it to initialize streaming replication:
1SET password_encryption = 'scram-sha-256';
2CREATE ROLE repuser WITH REPLICATION PASSWORD 'SOME_SECURE_PASSWORD' LOGIN;
  1. Add the following replication settings to postgresql.conf (which can be usually found in $PGDATA folder, i.e. /var/lib/postgresql/data):
1listen_addresses = '*'
2wal_level = replica
3max_wal_senders = 2
4max_replication_slots = 2
5synchronous_commit = off
  1. Add the following at the end of $PGDATA/pg_hba.conf to configure host-based authentication to accept connections from the replication user on the host of the replica:
host     replication     repuser   <REPLICA_IP>/32       scram-sha-256
  1. Restart the primary database to apply changes
  2. Finally, create a replication slot and give it a name:
SELECT * FROM pg_create_physical_replication_slot('replica_1_slot');

Configuring the replica #

  1. Stop Postgres instance:

    pg_ctl -D $PGDATA -m fast -w stop

  2. Clear PGDATA folder:

    NOTE: if you do this step while Postgres is running, you will get into trouble.
    rm -rf $PGDATA/*

  3. Create a base backup on the replica:

    NOTE: This may take a while, depending on the database size (less than 5 minutes for me). You will also be asked to input the password interactively, the one you set up in step 1 of preparing the primary database section.
    pg_basebackup -h <PRIMARY_HOST> -p <PRIMARY_PORT> -D $PGDATA -U repuser -vP -R -W

  4. Finally, restart Postgres instance:

    pg_ctl -D $PGDATA -w start

Automating it all using Docker Compose #

Luckily, all these steps can be automated using Docker for a smooth setup. Timescale offers sample Docker configuration and run scripts with their streaming-replication-docker GitHub repository, which was very useful even though it is quite outdated (supports only up to PostgreSQL 10), so a few changes are needed.

Primary setup #

Create the following PrimaryDockerfile:

1FROM timescale/timescaledb:latest-pg13
2ADD primary.sh /docker-entrypoint-initdb.d/

And then create the entrypoint script primary.sh:

 1# Create replica user
 2psql -U postgres -c "SET password_encryption = 'scram-sha-256';"
 3psql -U postgres -c "CREATE ROLE $REPLICA_USER WITH REPLICATION PASSWORD '$REPLICA_PASSWORD' LOGIN;"
 4 
 5# Add replication settings to primary postgres.conf
 6cat >> ${PGDATA}/postgresql.conf <<EOF
 7listen_addresses= '*'
 8wal_level = replica
 9max_wal_senders = 2
10max_replication_slots = 2
11synchronous_commit = off
12EOF
13 
14# Add replication settings to primary pg_hba.conf
15cat >> ${PGDATA}/pg_hba.conf <<EOF
16host     replication     ${REPLICA_USER}   ${REPLICA_IP}/32       scram-sha-256
17EOF
18 
19# Restart Postgres
20pg_ctl -D ${PGDATA} -m fast -w restart
21 
22# Add replication slot
23psql -U postgres -c "SELECT * FROM pg_create_physical_replication_slot('${REPLICA_NAME}_slot');"

This script will perform all the necessary steps to allow replication every time the database starts. If you plan on doing this without an entrypoint script (i.e. by running the commands directly with docker exec), make sure to run these as the postgres user by running su - postgres before issuing commands. By default, TimescaleDB image logs in as root user, which may break things.

Replica setup #

Please note that the replica setup involves shutting down the postgres instance and deleting contents of the PGDATA folder. As such, it must be executed from an entrypoint script and not while the container is running.

Create the following ReplicaDockerfile:

1FROM timescale/timescaledb:latest-pg13
2ADD replica.sh /docker-entrypoint-initdb.d/

And then create the entrypoint script replica.sh:

 1echo "Stopping Postgres instance..."
 2pg_ctl -D ${PGDATA} -m fast -w stop
 3 
 4echo "Clearing PGDATA folder..."
 5rm -rf ${PGDATA}
 6 
 7echo "Creating base backup..."
 8PGPASSWORD=${REPLICATION_PASSWORD} pg_basebackup -h ${REPLICATION_HOST} -p ${REPLICATION_PORT} -D ${PGDATA} -U ${REPLICATION_USER} -vP -R -w
 9 
10echo "Restarting Postgres instance..."
11pg_ctl -D ${PGDATA} -w start
NOTE: pg_basebackup’s -W option will ask for replication password interactively, which does not work well for automated setups. Instead, we can use the lowercase -w option and specify the password using the PGPASSWORD environment variable (or even using a .pgpass file). See the docs for more info.

Composing services #

Finally, create a docker-compose.yml file:

NOTE: for some reason, using the default /var/lib/postgresql/data as PGDATA for the replica didn’t work for me: no matter how many times I stopped the Postgres instance, the rm -rf ${PGDATA} command always ended up throwing the error rm: can't remove '/var/lib/postgresql/data': Resource busy. Using a subfolder instead, such as /var/lib/postgresql/data/pgdata, ended up working fine.
 1version: "3"
 2 
 3services:
 4  # Primary DB
 5  primary:
 6    build:
 7      context: .
 8      dockerfile: PrimaryDockerfile
 9    environment:
10      POSTGRES_USER: postgres
11      POSTGRES_PASSWORD: postgres
12 
13      # Replication parameters
14      REPLICA_NAME: replica_1
15      REPLICA_USER: repuser
16      REPLICA_PASSWORD: SOME_SECURE_PASSWORD
17      REPLICA_IP: x.x.x.x # ip of the replica db
18    ports:
19      - 5432:5432
20    volumes:
21      - /var/primary-pg13-timescale/:/var/lib/postgresql/data
22 
23  # Replica DB
24  replica:
25    build:
26      context: .
27      dockerfile: ReplicaDockerfile
28    environment:
29      # Note: a replica is 1:1 of the production db, you cannot add new users/tables/views to it.
30      # So these credentials are never really added (they're here to prevent initialization errors)
31      POSTGRES_USER: dummy_user
32      POSTGRES_PASSWORD: dummy_pw
33 
34      # Custom PDATA folder (for some reason the default `/var/lib/postgresql/data` doesn't work)
35      PGDATA: /var/lib/postgresql/data/pgdata
36 
37      # Replication parameters
38      REPLICA_USER: repuser
39      REPLICATION_HOST: x.x.x.x # ip of the primary db
40      REPLICATION_PORT: 5432
41      REPLICATION_PASSWORD: SOME_SECURE_PASSWORD
42    ports:
43      - 5433:5432
44    volumes:
45      - /var/replica-pg13-timescale/:/var/lib/postgresql/data

And then run it with docker-compose up, you should see an output like this:

Example output of the docker-compose up command

Conclusion #

You should now have a fully working replica database always in sync with the primary one. Congrats! 🎉

Keep in mind that it is read-only and there are a few limitations:

  • Users and access roles are mirrored from the primary instance, which means you cannot use different credentials or add additional user to the replica only
  • Tables and views cannot be created on the replica instance; you’re basically only allowed to execute SELECT queries from it.

  1. TimescaleDB docs: Replication and HA. ↩︎