pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Denis <socsam@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: [HACKERS] pg_dump and thousands of schemas
Date: Tue, 6 Nov 2012 06:16:14 -0800 (PST)
Message-ID: <1352211374706-5730864.post@n5.nabble.com> (raw )
In-Reply-To: <CA+TgmobHDYs2RoL0P+Fx0-W-f00ziy8M6pE7tSCuDAt8OTyRNg@mail.gmail.com >
References: <20120529.185149.1127748531209576379.t-ishii@sraoss.co.jp >
<12147.1338306742@sss.pgh.pa.us >
<20120530.180620.600165924826262795.t-ishii@sraoss.co.jp >
<20120531.092043.1953698679173443322.t-ishii@sraoss.co.jp >
<20120830204433.GW8753@momjian.us >
<1086.1346359916@sss.pgh.pa.us >
<CA+TgmobHksoQmbLyAMB7D5FwqDGA8+u5rooviEuP8rg_zcMzGg@mail.gmail.com >
<27683.1346383075@sss.pgh.pa.us >
<CAMkU=1xAUY_aCiWeVL1J5Xrub4vMObWG9UnOQrTR==6z4w1dUw@mail.gmail.com >
<CA+TgmobHDYs2RoL0P+Fx0-W-f00ziy8M6pE7tSCuDAt8OTyRNg@mail.gmail.com >
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.
view thread (66+ messages) latest in thread
Message-ID: <1352211374706-5730864.post@n5.nabble.com>
Permalink: ../1352211374706-5730864.post@n5.nabble.com/
Also on: postgresql.org/message-id/1352211374706-5730864.post@n5.nabble.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: socsam@gmail.com
Subject: Re: [HACKERS] pg_dump and thousands of schemas
In-Reply-To: <1352211374706-5730864.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox