I've read all the posts in thread, and as I understood in version 9.2 some
patches were applied to improve pg_dump speed. I've just installed
PostgreSQL 9.2.1 and I still have the same problem. I have a database with
2600 schemas in it. I try to dump each schema individually, but it takes too
much time for every schema (about 30-40 seconds per schema, no matter what
the data size is). Also for each schema dump I have a slow query log entry,
here is an example:
>2012-11-06 13:15:32 GMTLOG: duration: 12029.334 ms statement: SELECT
c.tableoid, c.oid, c.relname, c.relacl, c.relkind, c.relnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = c.relowner) AS rolname,
c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules, c.relhasoids,
c.relfrozenxid, tc.oid AS toid, tc.relfrozenxid AS tfrozenxid,
c.relpersistence, CASE WHEN c.reloftype <> 0 THEN
c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, d.refobjid AS
owning_tab, d.refobjsubid AS owning_col, (SELECT spcname FROM pg_tablespace
t WHERE t.oid = c.reltablespace) AS reltablespace,
array_to_string(c.reloptions, ', ') AS reloptions,
array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x), ',
') AS toast_reloptions FROM pg_class c LEFT JOIN pg_depend d ON (c.relkind =
'S' AND d.classid = c.tableoid AND d.objid = c.oid AND d.objsubid = 0 AND
d.refclassid = c.tableoid AND d.deptype = 'a') LEFT JOIN pg_class tc ON
(c.reltoastrelid = tc.oid) WHERE c.relkind in ('r', 'S', 'v', 'c', 'f')
ORDER BY c.oid
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/pg-dump-and-thousands-of-schemas-tp5709766p5730864.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.