From: Mike Williams <mike.williams@comodo.com>
To: pgsql-admin@postgresql.org
Subject: pg_restore >10million large objects
Date: Mon, 23 Dec 2013 15:19:03 +0000
Message-ID: <8431385.HKVqUUWTfq@mahdell>
User-Agent: KMail/4.10.5 (Linux/3.10.7-gentoo; KDE/4.10.5; x86_64; ; )
MIME-Version: 1.0
Content-Transfer-Encoding: 7Bit
Content-Type: text/plain; charset="us-ascii"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Hi all,

There have been some questions about pg_dump and huge numbers of large objects 
recently. I have a query about the opposite.
How can restoring a database with a lot of large objects run faster?

My database has a relatively piddling 13 million large objects, so dumping it 
isn't a problem.
Restoring it is a problem though.
This is for a migration from 8.4 to 9.3. The dump is taken using pg_dump from 
9.3.


I've run a test on a significantly smaller test system.
~4GB overall, and 1.1 million large objects. It took 2 hours, give or take.
The server it's on isn't especially fast though.
It seems that each "SELECT pg_catalog.lo_create('xxxxx');" is run 
independently and sequentially, despite having --jobs=8 specified.


Is there any magic incantation, or animal sacrifice, I can make to get those 
lo_create() calls run in parallel?
Our 9.3 production servers have 12 cores (plus HT) and SSDs, so can do many 
queries at the same time.


Thanks

-- 
Mike Williams


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin