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

Question

From what I have read, I need to make changes to the configuration file postgres.conf and pg_hba.conf and after that we need to create a "replication" user.

1. How do I make changes to these configuration files and make sure that the changes to these configuration files persist through the restarts?

2. Since my postgres is running inside a kubernetes pod, I reckon that the user must be created inside the container. But when I do createuser -replication -P replica, it wont allow me to create one since the current user after I did docker exec -it <container_id> bash is root and not postgres.

Answer 1

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 IP
wal_level = hot_standby  
max_wal_sender = 5       #  Specifies the maximum number of concurrent connections from standby servers
ssl = off
archive_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