Hi Stephen, > On 18. Jun, 2020, at 21:26, Stephen Frost <sfrost@xxxxxxxxxxx> 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 what a bad idea that was, I reorganized them all away. So we don't use tablespaces anymore. Still, they used to be on the same 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 to go 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 whole database 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, I never saw a use for this with Oracle too. Cheers, Paul
