Re: Netapp SnapCenter - Mailing list pgsql-general

Hi Stephen,

> On 18. Jun, 2020, at 21:26, Stephen Frost <sfrost@snowman.net> wrote:
> If the entire database, all tablespaces, and pg_wal, are on the same
> volume and the snapshot of the volume is atomic, then you don't actually
> need to go through the start/stop backup- a snapshot being restored will
> look just like a system crash and PG will just go back to the last
> checkpoint and replay the WAL that's in pg_wal and it should reach
> consistency and come up.

we DID use tablespaces as an old habit (coming from Oracle :-( when everything PostgreSQL was new). When we realized
whata bad idea that was, I reorganized them all away. So we don't use tablespaces anymore. Still, they used to be on
thesame volume, just a different directory. So even that would have been no problem in this particular case. :-) 

Only Flyway doesn't like a DBA to reorg a tablespace if developers add a tablespace clause to their create table/index
statements.But we made the developers remove those clauses. 

> The one issue here is that if you're using the deprecated exxclusive
> backup API, then PG will create a backup_label file in the data
> directory.  If the system reboots while that file exists, there's a good
> chance that PG won't start up cleanly since, due to the file existing,
> it thinks that it's restoring from a backup when it isn't.

we don't. We use the non-exclusive backup mode according to:

25.3.3.1. Making A Non-Exclusive Low-Level Backup
https://www.postgresql.org/docs/current/continuous-archiving.html

But I must say that I'm really not a friend of being forced to keep the session open until the pg_stop_backup() call
occurs.This really gave me a huge headache when writing the backup script. It's solved now but I consider this very
ugly.

When I wrote our backup mode script I read the deprecation note about the exclusive mode backup. This is why I decided
togo with non-exclusive to be ready when exclusive backup mode is finally removed some day. Yet, I don't see the
reason.Everything has to be consistent. So a non-exclusive backup mode makes absolutely no sense to me. Either the
wholedatabase cluster is in backup mode or it is not. There's nothing in between. 

This is unlike Oracle where you can set single tablespaces to backup mode and then just backup them separately. Still,
Inever saw a use for this with Oracle too. 

Cheers,
Paul