Re: Challenges with huge postgresql deployments in 300+ TBs - Mailing list pgsql-general

You going to want to do DISK snapshots with such a large database.  You will want to use SAN/NAS that can export its snapshots to create offsite backups.  PGBackrest is a great tool and I use it, however recovery time or creating a Read replica with PgBackRest on such a large database is painfully slow vs DISK SNAPSHOTS.     

Todo disksnapshot backups  you will need to turn on:
Full Page Writes (PG turns this on during a backup if it is off)
CheckSums
WAL Archiving somewhere on a different SAN/NAS and offsite somewhere. 

Without  WAL archiving you can not create online backup.  PgBackRest, PgBasebackup  and others tools all require capturing the WAL during the backup.

PG recently added incremental backups in 17
https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-INCREMENTAL-BACKUP

I also suggest creating Logical Backup using pg_dump every so often.  This way there is backup that can be restored to any version of PG within reason.  Pg_dumps will take some time on a 300 TB database so going to want to use the -f=directory and  -j=number of workers arguements
https://www.postgresql.org/docs/current/app-pgdump.html

 Restore a Disksnapsots put PG replica mode with the signal file and  use the restore command to fetch the WAL files from the archive, and the connection details to the primary. The replica will bring itself online once the WAL is caught up.  This requires keeping all the WAL files from the restored backup.  

Have to figure out why the standby is lagging first.  Key setting that stops alot of lagging is hot_standby_feedback =on   This stops most conflicting WAL replay updates coming from the primary as the replica communicates back to the primary its current tuple locks.  This prevents vacuum removing previous tuples versions.   

If you have a good backup and Full Pages writes on you have PITR.  The length of time to recover is the time to restore a backup and replay all the WAL files to the desired time code.  

You have to define what you mean by maintenance?  PG major maintenance event vacuum is automatic; it is pretty well tuned for most database loads. There are conditions where a table needs specific settings to trigger vacuum/analyze more/less often.