The trap when using pg_dumpall

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Years ago, I started backing up my table data in individual groups (typically SCHEMAs).  More recently, I have also been backing up the entire cluster using pg_dumpall.

Today I thought:

Why not stop the dumps of individual groups?  Why not use continue to dump with pg_dumpall, & then use pg_restore to restore all or just portions of the database, as needed?

That sounded good until I did a bit of research.  Despite this site https://www.postgresqltutorial.com/postgresql-restore-database/ saying that you could use pg_restore with pg_dumpall, the authoritative documentation says that you can't.

So, assuming that's true, what is the best way to accomplish what I want?  I see no effective way of filtering out individual SCHEMAs when restoring from the output of a pg_dumpall.  That means:

  1. Using pg_restore, which has the capability of filtering the restore.
  2. Using pg_dump with archive output.

Unfortunately, as far as I know, pg_dump doesn't dump roles & possibly other data that I need.  I presently have script files that I keep updated (hopefully) in parallel, for the compute manual recreation of the database, but there's nothing like backing up up everything.

So, here's my ultimate question(s):

  1. Can I do a pg_dumpall to backup just the parts that pg_dump omits, & then be able to do a complete restore by restoring the non-data with psql, & then restoring the data with pg_restore?
  2. If so, what are the appropriate options to pg_dumpall?

I'm thinking the following will work, but an authoritative answer would be nice:

pg_dumpall  -rs  (I don't use tablespaces)






[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux