Re: Dump a database excluding one table DATA?

1. I need to shorten pg_dump results (for backup purposes), so pg_restore is
too late for that...

2. If I use "pg_dump -s" separately, the data may not load (or load to slow)
after that, because all indices/foreign keys are already there. Is there a
way to split "pg_dump -s" into 2 parts: the first part dumps everything
excluding indices, checks and foreign keys, and the second part - only them?
Not sure it is possible at all, because I think pg_dump may dump data not
between these two blocks of DDLs...

On Fri, Aug 19, 2011 at 12:04 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:

> On Thursday, August 18, 2011 12:49:45 pm Dmitry Koterov wrote:
> > Hello.
> >
> > Is there any way (or hack) to dump the whole database, but to exclude the
> > DATA from a table within this dump? (DDL of the table should not be
> > excluded: after restoring the data the excluded table should look
> "empty".)
> >
> > I see -T switch of pg_dump, but seems -T excludes the data AND the DDL of
> a
> > table (and possibly all objects which depend on this table?), so after
> > restoration the database structure becomes broken sometimes.
>
> One way I know you can do it, is exclude the data from restoring. This
> requires
> you use the pg_dump custom format. For full details see here:
>
> http://www.postgresql.org/docs/9.0/interactive/app-pgrestore.html
>
> Short version use pg_restore -l to generate a listing from the dump file.
> Comment out the line that copys the data into that table.
> Use pg_restore ... -L to have pg_restore those items not commented out.
>
> Another way is do it using the -T switch for the 'complete' db dump. Then
> do a
> separate dump using -s (schema only) and -t some_table and then restore it
> on
> its own.
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>