Postgresql Streaming Replication

So this is pretty simple. Make sure both or all postgresql versions match. make sure that each machine can reach the other. Once that’s good you can move on to configuration.

I’m going to call the original server the master, and the server to get the data the replicant

Step one is on the original machine. you need to edit two files. The postgresql.conf and pg_hba.conf

nano /etc/postgresql/13/main/postgresql.conf

# line 59 : uncomment and change
listen_addresses = '*'
# line 196 : uncomment
wal_level = replica
# line 201 : uncomment
synchronous_commit = on
# line 290 : uncomment (max number of concurrent connections from streaming clients)
max_wal_senders = 10
# line 305 : uncomment and change
synchronous_standby_names = '*'


nano /etc/postgresql/13/main/pg_hba.conf

# add to the end
# host replication [replication user] [allowed network] [authentication method]
host    replication     rep_user        10.0.0.30/32            md5
host    replication     rep_user        10.0.0.51/32            md5

# create a user for replication
su - postgres
postgres@www:~$ createuser --replication -P rep_user
Enter password for new role:   # set any password
Enter it again:
postgres@www:~$ exit
systemctl restart postgresql

Now for the Replicant

# stop PostgreSQL and remove existing data
systemctl stop postgresql
rm -rf /var/lib/postgresql/13/main/*
# get backup from Primary Node
su - postgres
pg_basebackup -R -h www.srv.world -U rep_user -D /var/lib/postgresql/13/main -P
Password:   # password of replication user
exit
nano /etc/postgresql/13/main/postgresql.conf

# line 59 : uncomment and change
listen_addresses = '*'
# line 318 : uncomment
hot_standby = on

nano /var/lib/postgresql/13/main/postgresql.auto.conf

# add [application_name] to auto generated auth file (any name you like, like hostname and so on)
primary_conninfo = 'user=rep_user password=password host=www.srv.world port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any application_name=node01'

systemctl start postgresql