Greetings,
* Mladen Gogala (gogala.mladen@gmail.com) wrote:
> On 10/23/21 23:12, Lucas wrote:
> >This has proven to work very well for me. I had to restore a few backups
> >already and it always worked. The bad part is that I need to stop the
> >database before performing the Snapshot, for data integrity, so that means
> >that I have a hot-standby server only for these snapshots.
> >Lucas
>
> Actually, you don't need to stop the database. You need to execute
> pg_start_backup() before taking a snapshot and then pg_stop_backup() when
> the snapshot is done. You will need to recover the database when you finish
> the restore but you will not lose any data. I know that pg_begin_backup()
> and pg_stop_backup() are deprecated but since PostgreSQL doesn't have any
> API for storage or file system snapshots, that's the only thing that can
> help you use storage snapshots as backups. To my knowledge,the only database
> that does have API for storage snapshots is DB2. The API is called "Advanced
> Copy Services" or ACS. It's documented here:
>
> https://www.ibm.com/docs/en/db2/11.1?topic=recovery-db2-advanced-copy-services-acs
>
> For Postgres, the old begin/stop backup functions should be sufficient.
No, it's not- you must also be sure to archive any WAL that's generated
between the pg_start_backup and pg_stop_backup and then to be sure and
add into the snapshot the appropriate signal files or recovery.conf,
depending on PG version, to indicate that you're restoring from a backup
and make sure that the WAL is made available via restore_command.
Just doing stat/stop backup is *not* enough and you run the risk of
having an invalid backup or corruption when you restore.
If the entire system is on a single volume then you could possibly just
take a snapshot of it (without any start/stop backup stuff) but it's
very risky to do that and then try to do PITR with it because we don't
know where consistency is reached in such a case (we *must* play all the
way through to the end of the WAL which existed at the time of the
snapshot in order to reach consistency).
In the end though, really, it's much, much, much better to use a proper
backup and archiving tool that's written specifically for PG than to try
and roll your own, using snapshots or not.
Thanks,
Stephen