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@koterov.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.klaver@gmail.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.
"
--