Re: Experience and feedback on pg_restore --data-only

On Fri, Mar 21, 2025 at 2:36 PM Dimitrios Apostolou <jimis(at)gmx(dot)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.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!