---
title: PostgreSQL Streamin replication
url: https://devopstales.github.io/linux/postgresql_replication/
date: 2020-02-03
keywords: postgresql 12, postgresql 11, streaming replication
---


In this pos I will show you how to configure Postgresql Streamin replication.

<!--more-->

### WAL segments
At all times, PostgreSQL maintains a write ahead log (WAL) in the `pg_wal/` subdirectory of the cluster's data directory. The log describes every change made to the database's data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by “replaying" the log entries made since the last checkpoint. This wal segments san be used to repliyate the transactions to a second read database server.

### requirements

```
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
yum install postgresql12 postgresql12-server postgresql12-contrib -y
```


```
/usr/pgsql-12/bin/postgresql-12-setup initdb
```

Set up the streaming replication related parameters on the two servers

```
$ nano /var/lib/pgsql/12/data/postgresql.conf
--------------------------------------------------------
listen_addresses = '*'

wal_level = hot_standby

max_wal_senders = 6

#16 Mb * 32 = 512 Mb
wal_keep_segments = 32

max_wal_size = 2GB

# for pg dump on slave
hot_standby_feedback = on

# for pg_rewind
full_page_writes = on
wal_log_hints = on
```

### master config
```
$ nano /var/lib/pgsql/12/data/postgresql.conf
--------------------------------------------------------
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/archive/%f'
```

```
systemctl enable postgresql-12
systemctl start postgresql-12
```

```
su - postgres
pgsql
ALTER SYSTEM SET listen_addresses TO '*';
CREATE ROLE replica_user WITH REPLICATION LOGIN;
\du
\q
exit
```


```
echo "host    replication     replica_user    192.168.0.111/32      trust">>/var/lib/pgsql/12/data/pg_hba.conf
systemctl restart postgresql-12
```

### slave config

```
rm -rf /var/lib/pgsql/12/data/*

su - postgres
/usr/pgsql-12/bin/pg_basebackup --host=192.168.0.110 --pgdata=/var/lib/pgsql/12/data/ --username=replica_user --verbose --progress --wal-method=stream --write-recovery-conf --checkpoint=fast

ll /var/lib/pgsql/12/data/standby.signal
cat /var/lib/pgsql/12/data/postgresql.auto.conf

echo "restore_command = 'cp /var/lib/pgsql/archive/%f %p'" >> /var/lib/pgsql/12/data/postgresql.auto.conf

systemctl start postgresql-12
```

### slave  test
```
SELECT * FROM pg_stat_wal_receiver;
sudo -u postgres psql -x -c "select * from pg_stat_wal_receiver"
```

### master test
```
SELECT * FROM pg_stat_replication;
sudo -u postgres psql -x -c "select * from pg_stat_replication"
```