How to setup a Postgres replication with docker and docker-compose

README.md

How to setup a Postgres replication with docker and docker-compose

How to use

To run you will need docker and docker-compose installed, and run the command docker-compose up on a folder within this file named docker-compose.yml(attached in this Gist)

How the PGAudit is enable, you will see the queries log and in which database is running which query, this is the main purpose of this Gist, along with have a database with replication working out of the box

To add user with just reading rights to access the database on slave replica

I suppose that you are inside the directory with the docker-compose file run the commands:

1 - Access the master container(you read right the user added to master will be replicated to slave)

docker-compose exec postgresql-master bash

2 - Access the psql client

psql -U postgres

3 - Run the SQL commands, creating the user 'reading_user' and giving him permissions

CREATE USER reading_user WITH PASSWORD 'reading_pass';
GRANT CONNECT ON DATABASE my_database TO reading_user;
\connect my_database
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reading_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO reading_user;
GRANT USAGE ON SCHEMA public TO reading_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reading_user;

I suggest restart all containers and possible connections

The docke-compose.yml was heavily based on https://github.com/bitnami/bitnami-docker-postgresql/blob/72183d5623eb92a9781637f6f395f13ee93c5836/docker-compose-replication.yml thanks bitnami for this great work

Questions about the SQL script https://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql

docker-compose postgres with replication enabled

version: '2'

services:
  postgresql-master:
    image: 'docker.io/bitnami/postgresql:11-debian-10'
    ports:
      - '5432:5432'
    volumes:
      - 'postgresql_master_data:/bitnami/postgresql'
    environment:
      - POSTGRESQL_PGAUDIT_LOG=READ,WRITE
      - POSTGRESQL_LOG_HOSTNAME=true
      - POSTGRESQL_REPLICATION_MODE=master
      - POSTGRESQL_REPLICATION_USER=repl_user
      - POSTGRESQL_REPLICATION_PASSWORD=repl_password
      - POSTGRESQL_USERNAME=postgres
      - POSTGRESQL_DATABASE=my_database
      - ALLOW_EMPTY_PASSWORD=yes
  postgresql-slave:
    image: 'docker.io/bitnami/postgresql:11-debian-10'
    ports:
      - '5433:5432'
    depends_on:
      - postgresql-master
    environment:
      - POSTGRESQL_USERNAME=postgres
      - POSTGRESQL_PASSWORD=my_password
      - POSTGRESQL_MASTER_HOST=postgresql-master
      - POSTGRESQL_PGAUDIT_LOG=READ,WRITE
      - POSTGRESQL_LOG_HOSTNAME=true
      - POSTGRESQL_REPLICATION_MODE=slave
      - POSTGRESQL_REPLICATION_USER=repl_user
      - POSTGRESQL_REPLICATION_PASSWORD=repl_password
      - POSTGRESQL_MASTER_PORT_NUMBER=5432

volumes:
  postgresql_master_data:
    driver: local

I am getting below warning on slave

docker-compose-postgresql-slave-1  | 2023-05-25 14:14:23.214 GMT [286] LOG:  invalid record length at 0/35E4230: wanted 24, got 0
docker-compose-postgresql-slave-1  | 2023-05-25 14:14:23.352 GMT [384] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1

Also how to connect to slave postgres instance. psql -U postgres seems to be not working with my_password

How can I increase the number of slave dbs ?

Could I use this way also to safely backup a postgres database volume, without downtime? I could stop postgresql-slave service and backup it via https://offen.github.io/docker-volume-backup. When the postgresql-slave gets restart again, will it be synced automatically with the master? And I when want use the backup, can I restore it to postgresql_master_data volume or how would then restoration of a backup work?

Hi, thanks for sharing! links in the readme file are not working so for those of you interested here's the new repo link: https://github.com/bitnami/containers/tree/main/bitnami/postgresql-repmgr

Best regards!

How do you offload the read queries to the replica in this case?