How to do PostgreSQL replication on CentOS 7?

Question

Followed this guide to do the PostgreSQL replication:

https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps

I didn't do this:

psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.1/main/ slave_IP_address:/var/lib/postgresql/9.1/main/
psql -c "select pg_stop_backup();"

After I configed both master and slave nodes, test to create a table on master, didn't find new data been synced to slave.

From this guide I saw need to add a archive_command config in the recovery.conf file:

https://www.server-world.info/en/note?os=CentOS_7&p=postgresql&f=3

archive_command = 'cp %p /var/lib/pgsql/archive/%f'

But PostgreSQL 9.6 maybe not support that feature. So how can it been synced automatically?

---

Addition

It's helpful:

https://dalibo.github.io/PAF/Quick_Start-CentOS-7.html

Answer 1

The archive_command setting doesn't fit the recovery.conf but the postgresql.conf file.

The documentation you're following is deprecated.

Please switch to the official documentation.

Here are the steps you need to perform:

• Primary node settings (postgresql.conf, pg_hba.conf)
• Primary node backup (pg_basebackup, pgbarman, pgbackrest... Whatever)
• Restoring on secondary node (manually, pgbarman, pgbackrest... Whatever)
• Secondary node settings (recovery.conf, postgresql.conf, eventually pg_hba.conf)

The part you didn't do is simply the initial backup/restore. You can't do replication without it.

• cloud_cloud: Thank you. How to send the database data to slave on PostgreSQL 9.6? Maybe use archive path, but which is the archive path of 9.6? The sample is rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.1/main/ slave_IP_address:/var/lib/postgresql/9.1/main/, but there is no path like /var/lib/pgsql/9.6/main with 9.6 version.
• cloud_cloud: Maybe run this: pg_basebackup -h master-ip -D ~postgres/9.6/data/ -X stream -P, but run it on slave, then got this error: pg_basebackup: directory "/var/lib/pgsql/9.6/data" exists but is not empty.
• cloud_cloud: I run rsync -cva --inplace --exclude=*pg_xlog* ~postgres/9.6/data/ slave_ip:~postgres/9.6/data/ on master, then change to right info(IP) under data folder on slave, it works!
• Arkhena: If you didn't use psql -c "select pg_start_backup('initial_backup');" before your rsync and psql -c "select pg_stop_backup();" after, you're just lucky it works...
• cloud_cloud: I used that two psql commands.