Re: Something else about Redo Logs disappearing - Mailing list pgsql-general

On 6/8/20 7:33 PM, Peter wrote:
> 
> Actually, the affair had some good side: as usual I was checking
> my own designs first and looking for flaws, and indeed I found one:
>                                                                                  
> If you do copy out the archive logs not directly to tape, but to
> some disk area for further processing, then there is an issue with
> possible loss. If you do it like the docs say, with a command like
> this:
>                                                                                  
> archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p
> +/mnt/server/archivedir/%f'  # Unix
>                                                                                  
> That "cp" is usually not synchronous. So there is the possibility
> that this command terminates successfully, and reports exitcode zero
> back to the Postgres, and then the Postgres will consider that log
> being safely away.

Which is why just following the above command in the docs is:

"(This is an example, not a recommendation, and might not work on all 
platforms.) "

Generally for peace of mind folks use third party tools like:

pg_backrest(https://pgbackrest.org/),
pg_probackup(https://postgrespro.com/products/extensions/pg_probackup) 
or Barman(https://www.pgbarman.org/).

as they offer safety checks for your backups.

I use pg_backrest, but it does not look promising for running on BSD:
https://fluca1978.github.io/2019/03/04/pgbackrest_FreeBSD.html

Not sure about pg_probackup.

Barman is Python package:
http://docs.pgbarman.org/release/2.10/#installation-from-sources

>                                                                                  
> But the target of the copy may not yet been written to disk. If
> at that point a power loss happens, the log may become missing/damaged/
> incomplete, while the database may or may not consider it done
> when restarting.
>                                                                                  
> Therefore, mounting such a target filesystem in all-synchronous mode
> might be a good idea. (UFS: "-o sync", ZFS: "set sync=always")
>                                                                                  
> cheerio,
> PMc
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com