[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    Re: [ADMIN] pg_restore >10million large objects
From:       bricklen <bricklen () gmail ! com>
Date:       2013-12-23 15:54:48
Message-ID: CAGrpgQ_oWuPdGgM1GjW42eW7AM10RUzpAWf6kqZj0xc4QZoU4w () mail ! gmail ! com
[Download RAW message or body]

On Mon, Dec 23, 2013 at 7:19 AM, Mike Williams <mike.williams@comodo.com>wrote:

>
> How can restoring a database with a lot of large objects run faster?
>
> It seems that each "SELECT pg_catalog.lo_create('xxxxx');" is run
> independently and sequentially, despite having --jobs=8 specified.
>
>
I don't have an answer for why the restore seems to be serialized, but have
you considered creating your pg_dump (-Fc) but exclude all the lobs, then
dump or COPY the large objects out separately which you can them import
with a manually-specified number of processes? By "manually specified", I
mean execute a number of COPY FROM commands using separate threads.

[Attachment #3 (text/html)]

<div dir="ltr"><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Dec 23, \
2013 at 7:19 AM, Mike Williams <span dir="ltr">&lt;<a \
href="mailto:mike.williams@comodo.com" \
target="_blank">mike.williams@comodo.com</a>&gt;</span> wrote:<br> <blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><br> How can restoring a database with a lot of large objects \
run faster?<br> <br>
It seems that each &quot;SELECT pg_catalog.lo_create(&#39;xxxxx&#39;);&quot; is \
run<br> independently and sequentially, despite having --jobs=8 specified.<br>
<br></blockquote></div><br></div><div class="gmail_extra">I don&#39;t have an answer \
for why the restore seems to be serialized, but have you considered creating your \
pg_dump (-Fc) but exclude all the lobs, then dump or COPY the large objects out \
separately which you can them import with a manually-specified number of processes? \
By &quot;manually specified&quot;, I mean execute a number of COPY FROM commands \
using separate threads.<br> </div></div>



[prev in list] [next in list] [prev in thread] [next in thread]