postgres-streaming-replication

Configure Primary Node

app1: vi /etc/postgresql/12/main/postgresql.conf

line 59: uncomment and change

listen_addresses = '*'

line 192: uncomment

wal_level = replica

line 197: uncomment

synchronous_commit = on

line 285: uncomment (max number of concurrent connections from streaming clients)

max_wal_senders = 10

line 287: uncomment and change (minimum number of past log file segments)

wal_keep_segments = 10

line 300: uncomment and change

synchronous_standby_names = '*'
app1: vi /etc/postgresql/12/main/pg_hba.conf

add to the end

host    replication     [replication user]     [allowed network]  [authentication method]

host    replication     replication_user        10.200.2.31/32            md5

create a user for replication

app1: su - postgres
postgres@app1:~$ createuser --replication -P replication_user
app1: systemctl restart postgresql

Configure Replica Node

stop PostgreSQL and remove existing data

app2: systemctl stop postgresql
app2: rm -rf /var/lib/postgresql/12/main/*

get backup from Primary Node

su - postgres
postgres@app2:~$ pg_basebackup -R -h 10.200.2.31 -U replication_user -D /var/lib/postgresql/12/main -P --checkpoint=fast
app2: vi /etc/postgresql/12/main/postgresql.conf

line 59: uncomment and change

listen_addresses = '*'

line 315: uncomment

hot_standby = on
app2: vi /var/lib/postgresql/12/main/postgresql.auto.conf

add [application_name] to auto generated auth file (any name you like, like hostname and so on)

primary_conninfo = 'user=replication_user password=12345 host=10.200.2.31 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any application_name=app2'

app2: systemctl start postgresql