0

I have one primary and one standby Postgres database nodes set up for streaming replication. Last month I promoted my standby, and swapped places with the primary. That is, I edited my postgresql.conf and pg_hba.conf to switch hostnames of which was designated as primary and which was designated as standby, and restarted postgres. Everything seemed to be working after the swap.

Now, though, I see that WALs have been piling up on the (new) primary since last month. When I look at the logs, I see this message:

< 2023-09-12 20:29:27.285 UTC  >LOG:  archive command failed with exit code 1
< 2023-09-12 20:29:27.285 UTC  >DETAIL:  The failed archive command was: (ssh [email protected] "test ! -f /var/lib/pgsql/data/pg_wal/00000002.history") && rsync --ignore-existing -a /var/lib/pgsql/data/pg_wal/00000002.history [email protected]:/var/lib/pgsql/data/pg_wal/00000002.history
< 2023-09-12 20:29:27.285 UTC  >WARNING:  archiving write-ahead log file "00000002.history" failed too many times, will try again later

According to documentation, Postgres creates a new timeline whenever a standby is promoted, which explains why 00000002.history appeared and all subsequent WALs have the "2" prefix.

Comparing the WAL directories on the standby to what's on the primary, both have 00000002.history, and they're identical. The primary has WALs going back to the time of when it was promoted. The standby has the history file as mentioned, but other than that it just has WALs going back to yesterday, as expected, so it does seem to be getting WALs from the primary. It looks like the primary is stuck on this history file and can't move on until that's resolved.

Knowing that I shouldn't directly muck about in pg_wal, what should I do to resolve this?

postgresql.conf:

listen_addresses = '*'
max_connections = 1024
unix_socket_directories = '/var/run/postgresql, /tmp'
shared_buffers = 9586MB
work_mem = 958MB
maintenance_work_mem = 1278MB
dynamic_shared_memory_type = posix  
password_encryption = 'scram-sha-256'  
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1024
archive_mode = on
archive_command = '(ssh [email protected] "test ! -f /var/lib/pgsql/data/pg_wal/%f") && rsync --ignore-existing -a /var/lib/pgsql/data/pg_wal/%f [email protected]:/var/lib/pgsql/data/pg_wal/%f'
2
  • You probably shouldn't be archiving directly into another server's pg_wal in the first place. So you are already mucking about in pg_wal due to that! Indeed, why are you archiving at all if you are using streaming? Maybe there is a reason for it, but you haven't described what it is.
    – jjanes
    Sep 13, 2023 at 14:09
  • @jjanes I did some more reading, and yes, it turns out that archiving is not necessary. I thought archiving was needed, but I guess I had a faulty understanding of streaming replication. I have removed archiving from my postgresql.conf and now everything is back to normal with no errors in the logs, and the WALs are back to normal.
    – cscal
    Sep 13, 2023 at 20:42

1 Answer 1

1

Your archive command is failing. Since the instance only starts producing WAL after promotion, it is not surprising that you only noticed the problem after you promoted the instance.

You will have to debug your archive_command manually. Perhaps it is something as simple as that you forgot to configure ssh, but it could be anything.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.