replication error

Question

I’m trying to use a command on shell in Postgresql(Windows) with the following syntax:

select pg_start_backup('nome do meu banco de dados', true);

It responds to a record. Now, when I type the following command nothing happens:

pg_basebackup -U postgres -D /var/lib/pgsql/9.4/data/db/secundario -P -h Ip do Slave -Ft

When I give that command:

pg_stop_backup();

The following message appears:

ERROR: syntax error at or near "ph_basebackup"
ROW 1: pg_basebackup -U postgres -D /var/li/psql/9.4/data/db/secu...

Does anyone know what’s going on?

Thanks in advance, thank you!

Answer 1

From what you can see by your question, you are trying to create a SLAVE server from your master.

1 - Install RSYNC on your master and your slave server. If it’s Centos/Fedora/Red hat:

yum install -y rsync

2 - Create a user REPLICATOR on your Master server (if not already):

psql -c "CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD 'suasenhaaqui';"

3 - On your master server, change the following parameters in postgresql.conf:

listen_addresses = ‘*’
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

4 - In the archive pg_hba.conf, amend the following:

host    replication     replicator     endereco_ip_do_slave/32   md5

5 - Restart your Postgres master server:

service postgresql-9.6 restart

6 - To start the RSYNC, so that we have a copy of your database on the slave server, perform the following steps on your master server: (Don’t forget you need the STEP NUMBER 3 here)

psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace /var/lib/pgsql/9.6/data/ ip_servidor_slave:/var/lib/pgsql/9.6/data/
psql -c "select pg_stop_backup();"

After the above step, remember to have Recovery.conf [1] in your slave before starting Postgres.

You will also need to change the pg_hba.conf in his slave to:

host    replication     replicator     endereco_ip_do_master/32   md5