How to enable streaming replication in PostgreSQL running in kubernetes pods? How to enable streaming replication in PostgreSQL running in kubernetes pods? kubernetes kubernetes

How to enable streaming replication in PostgreSQL running in kubernetes pods?


This gist has a detailed explanation on how to setup a master-slave replication with Postgres using docker.

Master configuration

Create replication_user

CREATE USER replication_user NOSUPERUSER;ALTER USER replication_user WITH REPLICATION;ALTER USER replication_user WITH PASSWORD 'CHANGEME';

postgresql.conf

Edit or add these lines:

listen_addresses = '*'   # It can be more specific with the listen IPwal_level = hot_standby  max_wal_sender = 5       #  Specifies the maximum number of concurrent connections from standby serversssl = offarchive_mode = on   archive_command = "cp %p /path/to/archive/%f"

pg_hba.conf

Let's suppose that 192.168.0.2/32 is the IP of the slave and replication_user is the replication user.

case no authentication is required

 host  replication   replication_user   192.168.0.2/32      trust

case authentication using password

 host  replication   replication_user   192.168.0.2/32      scram-sha-256 # or MD5

Slave configuration

Let's suppose that the Master IP is: 192.168.0.1 and the port is the default (5432).

listen_addresses = '*'primary_conninfo = 'user=replication_user password=CHANGEME host=192.168.0.1 port=5432'hot_standby = on

Making the configuration persistent

You can use docker volumes (or for kubernetes).Start by creating the configuration files in the local machine that start the containers and then:

docker run --rm --name IMAGE_NAME -v /local/path/to/postgresql.conf:/etc/postgresql/postgresql.conf -v /local/path/to/pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf CONTAINER_NAME

The same for the slaves.

sources:

https://wiki.postgresql.org/wiki/Streaming_Replication

https://gist.github.com/avshabanov/eb8e03a050c79f8e77420b06f9b4abe5