Re: large database: problems with pg_dump and pg_restore

"Jehan-Guillaume (ioguix) de Rorthais" <ioguix(at)free(dot)fr> writes:
> If you can spend some more time using -Fc, then you'll be able to play
> with -l and -L switches of pg_restore to optionally decide what should
> be restored or not (with more control than -n, -t, etc, think about
> excluding slony stuffs as instance)

See also pg_staging that implements some commands for pg_restore
filtering by schema. You can use that feature on its own.

http://github.com/dimitri/pg_staging
http://tapoueh.org/articles/blog/_pg_staging's_bird_view.html
http://tapoueh.org/pgstaging.html

Ok, for now check the docs, later I'll try to blog about some features
in pg_staging that you can use on their own, and maybe some more about
getting up to speed with pg_staging, after all.

From the commands docs :

catalog::

Show the filtered out catalog we'll give to +pg_restore -L+.

From the configuration (INI) file docs :

schemas::

List of schema (comma separated) to restore. If present, any schema
not listed here nor in +schemas_nodata+ will get filtered out from
the +pg_restore+ catalog.

schemas_nodata::

List of schema (comma separated) to restore without content. The
+pg_restore+ catalog +TABLE DATA+ sections will get filtered out.

From the code comments:

http://github.com/dimitri/pg_staging/blob/master/pgstaging/restore.py

##
# In the catalog, we have such TRIGGER lines:
#
# 6236; 2620 15995620 TRIGGER jdb www_to_reporting_logger webadmin
#
# The TRIGGER code could depend on a procedure hosted in a schema that
# we filter out. In this case, we want to also filter out the TRIGGER
# itself.
#
#CREATE TRIGGER www_to_reporting_logger
#AFTER INSERT OR DELETE OR UPDATE ON daily_journal
#FOR EACH ROW
#EXECUTE PROCEDURE pgq.logtriga('www_to_reporting', 'kkvvvvvvvvv', 'jdb.daily_journal');
#
# get_trigger_funcs will return a dict of
# {'trigger_name': ['procedure']}

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support