Re: Dump a database excluding one table DATA?

Mmm, --disable-triggers is not surely enough - we also have RULEs and (much
worse) INDEXes.

If we create all indices and then restore all data, it is MUCH SLOWER than
restore the data first and then - create all indices.
So I think that there is no work-around really...

I propose to include an option to pg_dump to skip several tables data
restoration. :-)

On Fri, Aug 19, 2011 at 12:44 AM, Dmitry Koterov <dmitry(at)koterov(dot)ru> wrote:

> Thanks, "pg_dump --data-only --disable-triggers" is the king.
>
> (Unfortunately it is not supported by pg_dumpall, but it is entirely
> another story. :-)
>
>
> On Fri, Aug 19, 2011 at 12:36 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:
>
>> On Thursday, August 18, 2011 1:23:25 pm Dmitry Koterov wrote:
>> > 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...
>> >
>>
>> I am not sure I follow. Are you saying you eventually restore the data for
>> that
>> table as a separate step? If so, from the previous link, this might help:
>>
>> "
>> --disable-triggers
>>
>> This option is only relevant when performing a data-only restore. It
>> instructs pg_restore to execute commands to temporarily disable triggers
>> on the
>> target tables while the data is reloaded. Use this if you have referential
>> integrity checks or other triggers on the tables that you do not want to
>> invoke
>> during data reload.
>>
>> Presently, the commands emitted for --disable-triggers must be done as
>> superuser. So, you should also specify a superuser name with -S, or
>> preferably
>> run pg_restore as a PostgreSQL superuser.
>> "
>>
>>
>>
>> --
>> 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
>>
>
>