Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] pg_dump and thousands of schemas

From: Denis <socsam(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: [HACKERS] pg_dump and thousands of schemas
Date: 2012-11-06 14:16:14
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-performance
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:
Sent from the PostgreSQL - performance mailing list archive at

In response to


pgsql-performance by date

Next:From: Willem LeenenDate: 2012-11-06 14:20:13
Subject: Re: help with too slow query
Previous:From: Виктор ЕгоровDate: 2012-11-06 12:17:07
Subject: Re: help with too slow query

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-11-06 15:07:34
Subject: Re: [HACKERS] pg_dump and thousands of schemas
Previous:From: John LumbyDate: 2012-11-06 13:53:03
Subject: Re: [PATCH] Prefetch index pages for B-Tree index scans

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group