Re: Something else about Redo Logs disappearing

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(dot)klaver(at)aklaver(dot)com