postgresql 12 streaming replication not working

Question

I want to set up streaming replication on the Slave.

On the Primary Server (Server A. Ubuntu 18.04; Postgres 12) :

1. sudo nano /etc/postgresql/12/main/postgresql.conf
2. Set listen_addresses = '*'
3. The postgresql.conf settings are :

wal_level = "logical"

archive_mode = "ON"

archive_timeout = 3600

checkpoint_timeout = 600

hot_standby = "ON"

max_connections = 700

max_wal_sender = 5

max_wal_size = 16GB

min_wal_size = 2GB

max_worker_processes = 30

synchronous_commit = "OFF"

wal_log_hints = "OFF"

wal_recycle = "ON"

wal_keep_segments = 4000

wal_sync_method = "fdatasync"

This Primary Server has already hosted logical replication to another server (Server B. Ubuntu 18.04; Postgres 12)

On the Slave Server (Server C. Ubuntu 22.04; Postgres 12) :

• change the data directory
• set this up as Slave for streaming replication from Server A.

Here is the detail : After installing postgres (fresh install) I make sure it's running, then I change the data directory :

1. sudo -u postgres psql
2. SHOW data_directory;. --Output /var/lib/postgresql/12/main
3. Quit psql : \q
4. sudo systemctl stop postgresql
5. sudo systemctl status postgresql
6. sudo rsync -av /var/lib/postgresql /data/postgresql2
7. rename the folder : sudo mv /var/lib/postgresql/12/main /var/lib/postgresql/12/main.bak
8. Edit the postgresql.conf : sudo nano /etc/postgresql/12/main/postgresql.conf
9. Set to new location : data_directory = '/data/postgresql2/postgresql/12/main'
10. Start the db again : sudo systemctl start postgresql

The result is good. Postgres is running well on Server C. When I do SHOW data_directory; it gives out : /data/postgresql2/postgresql/12/main

Then I setup streaming replication on Server C :

1. stop the service on the slave : systemctl stop [email protected]

2. su - postgres then cp -R /data/postgresql2/postgresql/12/main /data/postgresql2/postgresql/12/data_original

3. Remove the old directory :

   rm -rf /data/postgresql2/postgresql/12/main/*

4. Then do base backup :

   pg_basebackup -h 10.100.9.40 -p 5432 -U rep_user -D /data/postgresql2/postgresql/12/main -Fp -Xs -R -P -v

5. Restart : systemctl start [email protected]

--gives out error :

*`Job for [email protected] failed because the service did not take the steps required by its unit configuration.
See "systemctl status [email protected]" and "journalctl -xeu [email protected]" for details`*

6. Then I start using another way : sudo -i -u postgres /usr/lib/postgresql/12/bin/pg_ctl start -D /data/postgresql2/postgresql/12/main

It gives out error :

waiting for server to start....postgres: could not access the server configuration file "/data/postgresql2/postgresql/12/main/postgresql.conf": No such file or directory

Please help. What might cause this?

Answer 1

You forgot to set up replication on the standby. At the very minimum, you have to set restore_command in postgresql.conf and create standby.signal.

The whole dance with renaming directories and running rsync is unnecessary, all that is needed is pg_basebackup.