On Fri, Mar 21, 2025 at 2:36 PM Dimitrios Apostolou <jimis@gmx.net> wrote:
On Thu, 20 Mar 2025, Dimitrios Apostolou wrote:
> Rationale: > > When restoring a backup in an emergency situation, it's fine to run > pg_restore as superuser and get an exact replica of the dumped db.
How often do you have emergencies requiring database restore???? In my seven years managing PG systems, I've had TWO.
> AFAICT pg_restore (without --data-only) is optimised for such case.
[snip]
> Any feedback for improving my process?
Yes: don't use a logical backup tool like pg_dump to backup production databases.
PgBackRest (the tool I have experience with; there are others, though) has mandatory features like PITR, incremental and differential backups, delta restores and encryption. Use that instead.
To get rid of the cruft in your database, go through the schema and manually drop unused tables.
