PostgreSQL Streaming Replication - LOG: archive command failed with exit code 1

Question

When I bring up the replica Node I am get the following error in the log

2020年04月27日 00:12:26.278 EDT [3084] LOG: archive command failed with exit code 1
2020年04月27日 00:12:26.278 EDT [3084] DETAIL: The failed archive command was: test ! -f /var/lib/postgresql/pg_log_archive/main/00000001000000000000000C && cp pg_wal/00000001000000000000000C /var/lib/postgresql/pg_log_archive/replica/00000001000000000000000C

The achive command is set to below:

archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/main/%f && cp %p /var/lib/postgresql/pg_log_archive/replica/%f'

The configuration:

Primary Server postgresql.conf:

wal_level = replica
wal_log_hints = on
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/main/%f && cp %p /var/lib/postgresql/pg_log_archive/replica/%f'
max_wal_senders = 10
wal_keep_segments = 64

Standby server postgres.conf:

wal_level = replica
wal_log_hints = on
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/replica/%f && cp %p /var/lib/postgresql/pg_log_archive/main/%f'
max_wal_senders = 10
wal_keep_segments = 64
hot_standby = on

recovery.conf:

standby_mode = on
primary_conninfo = 'host=192.168.56.103 port=5432 user=postgres password=****'
restore_command ='cp var/lib/postgresql/pg_log_archive/replica/%f %p'
recovery_target_timeline ='latest'

• jjanes: The stderr of the cp command should probably be captured in the log file (perhaps depending on how you have logging set up). What reason does cp give for failing?
• jjanes: Your test and your cp are referencing different directories. Did you edit one place but not the other?
• Mark Shay: archive command failed with exit code 1 DETAIL: The failed archive command was: test ! -f /var/lib/postgresql/pg_log_archive/replica/000000010000000000000006 && cp pg_wal/000000010000000000000006 /var/lib/postgresql/pg_log_archive/main/000000010000000000000006

Answer 1

There are two possibilities:

1. You have archive_mode = always on the standby.

   Then the standby server will try to archive WAL segments (redundantly).

2. The standby server is not really a standby server, but a standalone database. That means that you have made a mistake setting up the standby server.

   The way to determine is that is the case, run

   SELECT pg_is_in_recovery();
   

   If that returns FALSE, the server is not a standby.

• Mark Shay: Thanks for the comment. I do have archive_mode = on
• Laurenz Albe: Then it is not a standby server. Try SELECT pg_is_in_recovery();, it should return FALSE. You must have made a mistake creating the standby. See the PostgreSQL log for details.
• Mark Shay: Replica return "False"
• Laurenz Albe: This cannot be. Either it is a primary, or archive_mode = always, otherwise PostgreSQL won't try to archive WAL segments. Perhaps you are confused and the errors you show are not from the standby?
• Laurenz Albe: Oh, I am dumb. You said it returns FALSE. So that proves it is not a standby server, and you made a mistake setting up replication. See my updated answer. Consult the log on the "standby" to see what happened.

Answer 2

This turned out be a very simple solution. I had recovery.conf in the wrong directory. Doh!

I had the file in /etc/postgresql/10/main/recovery.conf

when it should have been in the data directory: /var/lib/postgresql/10/main