Re: Dump a database excluding one table DATA?

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org, dmitry(at)koterov(dot)ru
Subject: Re: Dump a database excluding one table DATA?
Date: 2011-08-18 20:04:22
Message-ID: 201108181304.22882.adrian.klaver@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Erwin Brandstetter 2011-08-18 20:14:22 Retrieve number of rows from COPY command inside a plpgsql function
Previous Message John Cheng 2011-08-18 19:54:51 Re: call initdb as regular user