Thread: pg_dumpall --exclude
I've inherited an application that creates a temp schema and stores a table for each user there. The data is temporary and does not need to be backed up. I run pg_dumpall each night. I can't just delete the tables because users may be working during the backup. Any suggestion on how to exclude the temp schema? Richard Ray
Richard Ray wrote:
> I've inherited an application that creates a temp schema and stores
> a table for each user there.
> The data is temporary and does not need to be backed up.
> I run pg_dumpall each night.
> I can't just delete the tables because users may be working during the
> backup.
> Any suggestion on how to exclude the temp schema?
You don't say what version of Postgres you are using but 8.2 added this
feature:
* Allow complex selection of objects to be included or excluded by
pg_dump (Greg Sabino Mullane) pg_dump now supports multiple -n
(schema) and -t (table) options, and adds -N and -T options to
exclude objects. Also, the arguments of these switches can now be
wild-card expressions rather than single object names, for example
-t 'foo*', and a schema can be part of a -t or -T switch, for
example -t schema1.table1.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Wed, 9 Jan 2008, Bruce Momjian wrote: > Richard Ray wrote: >> I've inherited an application that creates a temp schema and stores >> a table for each user there. >> The data is temporary and does not need to be backed up. >> I run pg_dumpall each night. >> I can't just delete the tables because users may be working during the >> backup. >> Any suggestion on how to exclude the temp schema? > > You don't say what version of Postgres you are using but 8.2 added this > feature: sorry bout that, 8.2.5 > > * Allow complex selection of objects to be included or excluded by > pg_dump (Greg Sabino Mullane) pg_dump now supports multiple -n > (schema) and -t (table) options, and adds -N and -T options to > exclude objects. Also, the arguments of these switches can now be > wild-card expressions rather than single object names, for example > -t 'foo*', and a schema can be part of a -t or -T switch, for > example -t schema1.table1. I use pg_dumpall to get all changes to the server including users, groups, etc. If I use pg_dump what might the command look like that would accomplish the same as pg_dumpall > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://postgres.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + >
Richard Ray wrote: > On Wed, 9 Jan 2008, Bruce Momjian wrote: > > > Richard Ray wrote: > >> I've inherited an application that creates a temp schema and stores > >> a table for each user there. > >> The data is temporary and does not need to be backed up. > >> I run pg_dumpall each night. > >> I can't just delete the tables because users may be working during the > >> backup. > >> Any suggestion on how to exclude the temp schema? > > > > You don't say what version of Postgres you are using but 8.2 added this > > feature: > > sorry bout that, 8.2.5 > > > > > * Allow complex selection of objects to be included or excluded by > > pg_dump (Greg Sabino Mullane) pg_dump now supports multiple -n > > (schema) and -t (table) options, and adds -N and -T options to > > exclude objects. Also, the arguments of these switches can now be > > wild-card expressions rather than single object names, for example > > -t 'foo*', and a schema can be part of a -t or -T switch, for > > example -t schema1.table1. > > I use pg_dumpall to get all changes to the server including users, groups, > etc. > If I use pg_dump what might the command look like that would accomplish > the same as pg_dumpall I am afraid you are going to have to use pg_dumpall --globals-only and then loop each database running pg_dump with the flags you need. The -t/-n flags were not added to pg_dumpall on the assumption that you wouldn't be including/exclusing the same schemas/tables on every database, so you have to do it per-database. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +