I have a general question/discussion item regarding archiving.
The setup (Standard HA System):
I have 2 nodes, a Primary and a Synchronous Secondary.
I enabled archive logs to a local disk of my primary.
I have a job that copies these logs off of the node, and cleans the archive directory (for example this runs hourly).
The worst case scenario:
My archive copy job has not run in 55 minutes (it is due to run), and in this 5 minute window, both my nodes are destroyed and are unrecoverable. I can at best restore to 55 minutes ago.
My Question:
Should NFS/NAS be used for archive logs? The theory being as long as there is no NAS/NFS issue you could lose all nodes on your database, and recover right up to the moment the database went down and the last archive log was written. But, this assumes the NAS is always healthy, and that archiving here adds no overhead. Does anyone have any experience with this or any recommendations?
I’d suggest you alter the script used to log ship your WALs. Is there any reason why you can’t replace your batch job, which I am assuming is time based, with a user-defined script which is executed by the runtime parameter “archive_command”? If you look at the documentation you can see that if a non zero status is returned then the log shipping is signaled as failed. You can further configure your user-defined script to send out an alert, Pager Duty(?), informing you of the failure to copy giving you the ability to react faster.
To clarify, I run standard archive to local disk, a separate job then copies these regularly to safeguard the loss of the local disk.
Other than paging is there any other reason to use a custom script, rather than simply have the archive_command directly write to the NAS by pointing to a mountpoint?
I have yet to encounter a scenario where my archiving fails, how does Postgres handle a failure of the archive thread as you say with a non-zero return from the archive_command? Does it auto restart? Running 14.6
I have yet to encounter a scenario where my archiving fails.
Yup, I agree. I don’t see this happening very often. Usually it’s something silly like a firewall rule.
How does Postgres handle a failure of the archive thread as you say with a non-zero return from the archive_command?
In the case that archive_command returns a non-zero status, which is what happens when either a C program or a bash script returns when it fails, PostgreSQL continues to operate BUT a message is recorded in the log stating that the WAL failed to ship and the WAL remains. In that case PostgreSQL will attempt to resend the WAL over and over and over … until it either gets confirmation i.e. status 0, or you’ve taken the time to correct the problem causing the issue.
For example if for some reason your script was not completely correct and that you noticed it sometime after it failed. Well, you’d have quite the collection of WALS in your wal directory. The good news is that as soon as the problem is fixed postgres will proceed to push the backlog of WALS as fast as possible and will very quickly remove them without further issue.
Yes that clears that up, my main concern with network archiving was how it would handle failures if there was maintenance on network or NAS, but given it will retry that should not be a problem.
One last little tidbit of information: postgres typically retries at 10 second intervals after it’s detected a failure to complete the archive_command
Good to know @Robert_Bernier one last part, more a general one. Say for example in my system, my primary dies, followed an hour later by my secondary. I currently only save archive logs on my primary.
So until the primary goes down, the archive logs come from the primary, then they are written by the secondary.
Can I restore using a combination of Full backup, archive of initial primary, and the latest logs of the secondary? Or is a full backup of the new secondary needed first?