Thread: pg_restore

pg_restore

From
"D Kavan"
Date:
Hi all,

We are running postgres 8.03.

Getting a dump is no problem on our 6 GB database.  However, the restore is
a killer, it takes 10 minutes for the dump pg_dump, but over 5 hours for a
restore.  Is there a way to 'turn off transactions' on the restore to speed
things up?   I've tried different restore commands, but I don't see anything
on transactions logging.  What else could speed it up?   oracle and sybase
can restore the same db in just 90 minutes.    Does postgres just not know
how to take advantage of the resources unless different users are involved?
In other words to speed it up, should I script different users to each
restore some of the tables to a database and then after all the tables have
restored, restore keys and then indexes?   Thanks for any help.

~DjK

psql dbname < dump_file
pg_restore -d test_restore_2 -Fc niehs_dump.backup



Re: pg_restore

From
Bruno Wolff III
Date:
On Thu, Aug 11, 2005 at 15:29:22 -0400,
  D Kavan <bitsandbytes88@hotmail.com> wrote:
> Hi all,
>
> We are running postgres 8.03.
>
> Getting a dump is no problem on our 6 GB database.  However, the restore is
> a killer, it takes 10 minutes for the dump pg_dump, but over 5 hours for a
> restore.  Is there a way to 'turn off transactions' on the restore to speed
> things up?   I've tried different restore commands, but I don't see
> anything on transactions logging.  What else could speed it up?   oracle
> and sybase can restore the same db in just 90 minutes.    Does postgres
> just not know how to take advantage of the resources unless different users
> are involved?  In other words to speed it up, should I script different
> users to each restore some of the tables to a database and then after all
> the tables have restored, restore keys and then indexes?   Thanks for any
> help.
>
> ~DjK
>
> psql dbname < dump_file
> pg_restore -d test_restore_2 -Fc niehs_dump.backup

Some things you can do are to turn off fsync for the restore and not
restore foreign key contsraints and indexes until after the database
has been loaded.

This topic has been discussed before and you might find some more specific
information in the archives.

Re: pg_restore

From
Tom Lane
Date:
Bruno Wolff III <bruno@wolff.to> writes:
>> psql dbname < dump_file
>> pg_restore -d test_restore_2 -Fc niehs_dump.backup

> Some things you can do are to turn off fsync for the restore and not
> restore foreign key contsraints and indexes until after the database
> has been loaded.

pg_dump/pg_restore should get the ordering considerations right already.
fsync off might help some but I'm not convinced of it.  I'd look at the
value of maintenance_work_mem --- increasing that to maybe a few hundred
Mb would help the speed of index builds and foreign key checks.

But really what you ought to do first is find out where it's spending
its time.  Can you run the restore with query logging and duration
logging enabled?

            regards, tom lane

Re: pg_restore

From
"D Kavan"
Date:
Thanks!

We increased work memory from 16 MB to 32 MB and maintenance work memory
from 256 MB to 768 MB.  That made a huge difference.   We did set true the
log.statement and duration, but where is the file those are logging too?

That did the trick.  30 minutes only for a restrore instead of 5 + hours.

I did notice that the size is 4 GB instead of 5.6 GB.  Is something else
going on?

We tried a pg_restore with -Fc once and only got 4 GB, but the next time
with a standard pg_dump command and psql newdb < dumpfile command, we also
had 4 GB.

~DjK



Re: pg_restore

From
Tom Lane
Date:
"D Kavan" <bitsandbytes88@hotmail.com> writes:
> That did the trick.  30 minutes only for a restrore instead of 5 + hours.

Good.

> I did notice that the size is 4 GB instead of 5.6 GB.  Is something else
> going on?

Kinda sounds like you weren't vacuuming often enough in the old
installation, leading to table or index bloat ...

            regards, tom lane

Log stdout in PG 8?

From
Steve Lane
Date:
Hello all:

I am working with the new log redirection and rotation possibilities in
postgres 8.0.3. Here is my question.

I'm using redirect_stderr=true in my conf file. This works as I expect --
but I still seem to see some log messages trickle through to the console. I
assume these are appearing on stdout?

Formerly, I would always redirect stdout and stderr to the same logfile. I
tried that here, starting postmaster with 1>&2 (instead of 2>&1 as I used
to) but it doesn't seem to help.

Am I barking up the wrong tree?

-- sgl


--------
Steve Lane
Vice President
Soliant Consulting, Inc.
(610) 788-2124 (V)
(847) 890-6029 (F)
slane@soliantconsulting.com


Re: Log stdout in PG 8?

From
Alvaro Herrera
Date:
On Fri, Aug 12, 2005 at 04:23:01PM -0400, Steve Lane wrote:
> Hello all:
>
> I am working with the new log redirection and rotation possibilities in
> postgres 8.0.3. Here is my question.
>
> I'm using redirect_stderr=true in my conf file. This works as I expect --
> but I still seem to see some log messages trickle through to the console. I
> assume these are appearing on stdout?

What messages?

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
Oh, oh, las chicas galacianas, lo harán por las perlas,
¡Y las de Arrakis por el agua! Pero si buscas damas
Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)

Re: pg_restore

From
"D Kavan"
Date:
I do a full vacuum and analyze every night, but I'm getting this error.

chived transaction log file "000000010000001200000091"
< % %2005-08-12 06:13:49 EDT>LOG:  archived transaction log file
"000000010000001200000092"
< % %2005-08-12 06:13:57 EDT>LOG:  archived transaction log file
"000000010000001200000093"
< % %2005-08-12 06:17:51 EDT>LOG:  checkpoints are occurring too frequently
(16 seconds apart)
< % %2005-08-12 06:17:51 EDT>HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
<postgres %niehs_dev %2005-08-12 06:17:52 EDT>LOG:  duration: 2426771.793 ms
  statement: VACUUM FULL;



Re: pg_restore

From
"Joshua D. Drake"
Date:
D Kavan wrote:
> I do a full vacuum and analyze every night, but I'm getting this error.

Those aren't errors. They are letting you know that you need to increase
your  checkpoint_segments.

>
> chived transaction log file "000000010000001200000091"
> < % %2005-08-12 06:13:49 EDT>LOG:  archived transaction log file
> "000000010000001200000092"
> < % %2005-08-12 06:13:57 EDT>LOG:  archived transaction log file
> "000000010000001200000093"
> < % %2005-08-12 06:17:51 EDT>LOG:  checkpoints are occurring too
> frequently (16 seconds apart)
> < % %2005-08-12 06:17:51 EDT>HINT:  Consider increasing the
> configuration parameter "checkpoint_segments".
> <postgres %niehs_dev %2005-08-12 06:17:52 EDT>LOG:  duration:
> 2426771.793 ms  statement: VACUUM FULL;
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: Log stdout in PG 8?

From
Steve Lane
Date:
Here's an example:

2005-08-12 15:12:43 CDT 12465 LOG:  XX000: could not create IPv6 socket:
Address family not supported by protocol
2005-08-12 15:12:43 CDT 12465 LOCATION:  StreamServerPort, pqcomm.c:337

These semm to be appearing in the written logs as well.

-- steve


> From: Alvaro Herrera <alvherre@alvh.no-ip.org>
> Date: Fri, 12 Aug 2005 16:39:35 -0400
> To: Steve Lane <slane@soliantconsulting.com>
> Cc: <pgsql-admin@postgresql.org>
> Subject: Re: [ADMIN] Log stdout in PG 8?
>
> On Fri, Aug 12, 2005 at 04:23:01PM -0400, Steve Lane wrote:
>> Hello all:
>>
>> I am working with the new log redirection and rotation possibilities in
>> postgres 8.0.3. Here is my question.
>>
>> I'm using redirect_stderr=true in my conf file. This works as I expect --
>> but I still seem to see some log messages trickle through to the console. I
>> assume these are appearing on stdout?
>
> What messages?
>
> --
> Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
> Oh, oh, las chicas galacianas, lo harán por las perlas,
> ¡Y las de Arrakis por el agua! Pero si buscas damas
> Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)


Re: Log stdout in PG 8?

From
Tom Lane
Date:
Steve Lane <slane@soliantconsulting.com> writes:
> Here's an example:
> 2005-08-12 15:12:43 CDT 12465 LOG:  XX000: could not create IPv6 socket:
> Address family not supported by protocol
> 2005-08-12 15:12:43 CDT 12465 LOCATION:  StreamServerPort, pqcomm.c:337

> These semm to be appearing in the written logs as well.

There is not anything in the backend that would send that to stdout as
opposed to stderr.

What do you mean by "written logs" exactly?
What have you got log_destination set to?

            regards, tom lane