On Thu, Oct 8, 2026 at 4:16 AM kavya chandren <[email protected]> wrote:
> > Few challenges which caught my attention include, > > 1. Backup, restore of such a large database > > 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 > 1. Recovery of a broken database (A lost replica) > > 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. > > 1. Fixing a standby that lags a lot > > 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. > > 1. Triggering a PITR with any backup tool > > 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. > > 1. Maintenance at times > > 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. > > 1. Finding equivalents of oracle items (which is common during any > oracle to postgres migration) > > I dont see any limits in database/ objects as specified in > https://www.postgresql.org/docs/current/limits.html > > Thanks and Regards, > Kavya >
