pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Fastest DB restore options
9+ messages / 7 participants
[nested] [flat]

* Fastest DB restore options
@ 2007-02-22 15:43  ogjunk-pgjedan@yahoo.com
  0 siblings, 5 replies; 9+ messages in thread

From: ogjunk-pgjedan@yahoo.com @ 2007-02-22 15:43 UTC (permalink / raw)
  To: pgsql-admin

Hello,

I have a fairly large DB to dump and restore as fast as possible.  I'm moving from 8.0.3 to 8.2.3! :)

I normally dump with these options:

  -d MyDB --clean --inserts --column-inserts --format=P

But the last time I tried that, the restore took foreeeeeeeeeeeeeever.  So I'm looking for the fastest way to import data from the old DB to the new one.  Judging from pg_dump man page the following should be the fastest dump & restore:

  -d MyDB --format=c --ignore-version
 
Is there anything else I can do to make the restore as fast as possible?

Thanks,
Otis

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Simpy -- http://www.simpy.com/  -  Tag  -  Search  -  Share





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Fastest DB restore options
@ 2007-02-22 15:58  Naomi Walker <nwalke@eldocomp.com>
  parent: ogjunk-pgjedan@yahoo.com
  4 siblings, 0 replies; 9+ messages in thread

From: Naomi Walker @ 2007-02-22 15:58 UTC (permalink / raw)
  To: ogjunk-pgjedan@yahoo.com <ogjunk-pgjedan@yahoo.com>; +Cc: pgsql-admin

Consider leaving all indicies off until the load finishes...

Naomi

ogjunk-pgjedan@yahoo.com wrote:
> Hello,
>
> I have a fairly large DB to dump and restore as fast as possible.  I'm moving from 8.0.3 to 8.2.3! :)
>
> I normally dump with these options:
>
>   -d MyDB --clean --inserts --column-inserts --format=P
>
> But the last time I tried that, the restore took foreeeeeeeeeeeeeever.  So I'm looking for the fastest way to import data from the old DB to the new one.  Judging from pg_dump man page the following should be the fastest dump & restore:
>
>   -d MyDB --format=c --ignore-version
>  
> Is there anything else I can do to make the restore as fast as possible?
>
> Thanks,
> Otis
>
> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
> Simpy -- http://www.simpy.com/  -  Tag  -  Search  -  Share
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>   


-- 
---------------------------------------------------------------------------- 
Naomi Walker                          Chief Information Officer 
Mphasis Healthcare Solutions          nwalker@mhs.mphasis.com 
  ---An EDS Company                   602-604-3100 
---------------------------------------------------------------------------- 
A positive attitude may not solve all your problems, but it will annoy 
enough people to make it worth the effort. --Herm Albright (1876 - 1944)
----------------------------------------------------------------------------

-- CONFIDENTIALITY NOTICE --

Information transmitted by thisĀ e-mail is proprietary to MphasiS and/or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that isĀ privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this e-mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at mailmaster@mphasis.com and delete this mail from your records.



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Fastest DB restore options
@ 2007-02-22 16:05  Joshua D. Drake <jd@commandprompt.com>
  parent: ogjunk-pgjedan@yahoo.com
  4 siblings, 0 replies; 9+ messages in thread

From: Joshua D. Drake @ 2007-02-22 16:05 UTC (permalink / raw)
  To: ogjunk-pgjedan@yahoo.com; +Cc: pgsql-admin

ogjunk-pgjedan@yahoo.com wrote:
> Hello,
> 
> I have a fairly large DB to dump and restore as fast as possible.  I'm moving from 8.0.3 to 8.2.3! :)
> 
> I normally dump with these options:
> 
>   -d MyDB --clean --inserts --column-inserts --format=P
> 
> But the last time I tried that, the restore took foreeeeeeeeeeeeeever.  So I'm looking for the fastest way to import data from the old DB to the new one.  Judging from pg_dump man page the following should be the fastest dump & restore:
> 
>   -d MyDB --format=c --ignore-version
>  
> Is there anything else I can do to make the restore as fast as possible?

Don't use -d, it means dump as inserts.

Joshua D. Drake


> 
> Thanks,
> Otis
> 
> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
> Simpy -- http://www.simpy.com/  -  Tag  -  Search  -  Share
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
> 


-- 

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Fastest DB restore options
@ 2007-02-22 16:25  Tom Lane <tgl@sss.pgh.pa.us>
  parent: ogjunk-pgjedan@yahoo.com
  4 siblings, 0 replies; 9+ messages in thread

From: Tom Lane @ 2007-02-22 16:25 UTC (permalink / raw)
  To: ogjunk-pgjedan@yahoo.com; +Cc: pgsql-admin

ogjunk-pgjedan@yahoo.com writes:
> I normally dump with these options:
>   -d MyDB --clean --inserts --column-inserts --format=P
> But the last time I tried that, the restore took foreeeeeeeeeeeeeever.

--inserts is pretty expensive.

>  So I'm looking for the fastest way to import data from the old DB to the new one.  Judging from pg_dump man page the following should be the fastest dump & restore:

>   -d MyDB --format=c --ignore-version
 
Don't use --ignore-version; it's a good way to shoot yourself in the foot.

pg_dump's default behavior is about as good as you can get; there are no
optional switches that will make it faster.  What you *can* do is make
sure that the receiving system is properly configured before you start
the restore --- increase maintenance_work_mem and checkpoint_segments
in particular.  See this page, especially the last section:
http://www.postgresql.org/docs/8.2/static/populate.html

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Fastest DB restore options
@ 2007-02-22 17:21  ogjunk-pgjedan@yahoo.com
  0 siblings, 1 reply; 9+ messages in thread

From: ogjunk-pgjedan@yahoo.com @ 2007-02-22 17:21 UTC (permalink / raw)
  To: pgsql-admin

----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>

ogjunk-pgjedan@yahoo.com writes:
> I normally dump with these options:
>   -d MyDB --clean --inserts --column-inserts --format=P
> But the last time I tried that, the restore took foreeeeeeeeeeeeeever.

--inserts is pretty expensive.

OG: right.  I won't use -d then.

>  So I'm looking for the fastest way to import data from the old DB to the new one.  Judging from pg_dump man page the following should be the fastest dump & restore:

>   -d MyDB --format=c --ignore-version
 
Don't use --ignore-version; it's a good way to shoot yourself in the foot.

OG: even when upgrading (8.0.3 -> 8.2.3)?  I'll dump with pg_dump from 8.0.3 and them import with pg_restore from the newly installed 8.2.3.  Wouldn't I *have to* use --ignore-version for that to work?

pg_dump's default behavior is about as good as you can get; there are no
optional switches that will make it faster.  What you *can* do is make
sure that the receiving system is properly configured before you start
the restore --- increase maintenance_work_mem and checkpoint_segments
in particular.  See this page, especially the last section:
http://www.postgresql.org/docs/8.2/static/populate.html

OG: Thanks for the pointer!

Otis






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Fastest DB restore options
@ 2007-02-22 18:13  Alvaro Herrera <alvherre@commandprompt.com>
  parent: ogjunk-pgjedan@yahoo.com
  0 siblings, 0 replies; 9+ messages in thread

From: Alvaro Herrera @ 2007-02-22 18:13 UTC (permalink / raw)
  To: ogjunk-pgjedan@yahoo.com; +Cc: pgsql-admin

ogjunk-pgjedan@yahoo.com wrote:

> >   -d MyDB --format=c --ignore-version
>  
> Don't use --ignore-version; it's a good way to shoot yourself in the foot.
> 
> OG: even when upgrading (8.0.3 -> 8.2.3)?  I'll dump with pg_dump from 8.0.3 and them import with pg_restore from the newly installed 8.2.3.  Wouldn't I *have to* use --ignore-version for that to work?

The recommended procedure is to use 8.2.3's pg_dump, not 8.0's, to
connect to the old database.  And no, you don't need --ignore-version
for that, because pg_dump knows how to talk to previous server versions.
That switch is there only for connecting to a database of a _newer_
version that pg_dump's, and it's generally problematic because sometimes
an older pg_dump doesn't know how to read the newer system catalogs.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Fastest DB restore options
@ 2007-02-22 20:47  Marco Bizzarri <marco.bizzarri@gmail.com>
  parent: ogjunk-pgjedan@yahoo.com
  4 siblings, 0 replies; 9+ messages in thread

From: Marco Bizzarri @ 2007-02-22 20:47 UTC (permalink / raw)
  To: ogjunk-pgjedan@yahoo.com <ogjunk-pgjedan@yahoo.com>; +Cc: pgsql-admin

On 2/22/07, ogjunk-pgjedan@yahoo.com <ogjunk-pgjedan@yahoo.com> wrote:
> Hello,
>
> I have a fairly large DB to dump and restore as fast as possible.  I'm moving from 8.0.3 to 8.2.3! :)
>
> I normally dump with these options:
>
>   -d MyDB --clean --inserts --column-inserts --format=P
>
> But the last time I tried that, the restore took foreeeeeeeeeeeeeever.  So I'm looking for the fastest way to import data from the old DB to the new one.  Judging from pg_dump man page the following should be the fastest dump & restore:
>
>   -d MyDB --format=c --ignore-version
>
> Is there anything else I can do to make the restore as fast as possible?
>
> Thanks,
> Otis


I'm not sure it is advisable, or it is even faster in current
implementation. In older ones, if you configure postgresql not to sync
after each write, you could end in a faster restore. Since this is a
restore, after all, if lights goes out, you can always throw all away
and start from scratch...

Regards
Marco


-- 
Marco Bizzarri
http://iliveinpisa.blogspot.com/



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Fastest DB restore options
@ 2007-02-23 06:42  ogjunk-pgjedan@yahoo.com
  0 siblings, 0 replies; 9+ messages in thread

From: ogjunk-pgjedan@yahoo.com @ 2007-02-23 06:42 UTC (permalink / raw)
  To: ; +Cc: pgsql-admin

Hi,

Yes, In remember discussions about (f)sync config.  Can anyone comment on whether turning fsync off for a restore into 8.2.3:
1) is advisable
2) will make the restore faster

If the OS and FS matter, this is on a Fedora Core3 Linux with kernel 2.6.9 and the ext3 journaling FS.

Thanks,
Otis

----- Original Message ----
From: Marco Bizzarri <marco.bizzarri@gmail.com>
Cc: pgsql-admin@postgresql.org
Sent: Thursday, February 22, 2007 3:47:37 PM
Subject: Re: [ADMIN] Fastest DB restore options

On 2/22/07, ogjunk-pgjedan@yahoo.com <ogjunk-pgjedan@yahoo.com> wrote:
> Hello,
>
> I have a fairly large DB to dump and restore as fast as possible.  I'm moving from 8.0.3 to 8.2.3! :)
>
> I normally dump with these options:
>
>   -d MyDB --clean --inserts --column-inserts --format=P
>
> But the last time I tried that, the restore took foreeeeeeeeeeeeeever.  So I'm looking for the fastest way to import data from the old DB to the new one.  Judging from pg_dump man page the following should be the fastest dump & restore:
>
>   -d MyDB --format=c --ignore-version
>
> Is there anything else I can do to make the restore as fast as possible?
>
> Thanks,
> Otis


I'm not sure it is advisable, or it is even faster in current
implementation. In older ones, if you configure postgresql not to sync
after each write, you could end in a faster restore. Since this is a
restore, after all, if lights goes out, you can always throw all away
and start from scratch...

Regards
Marco






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Fastest DB restore options
@ 2007-02-25 22:58  Ben Trewern <ben.trewern@mowlem.com>
  parent: ogjunk-pgjedan@yahoo.com
  4 siblings, 0 replies; 9+ messages in thread

From: Ben Trewern @ 2007-02-25 22:58 UTC (permalink / raw)
  To: pgsql-admin

If you are just looking for the least down time between stopping one server 
and starting the new one you could try Slony see 
http://gborg.postgresql.org/project/slony1/projdisplay.php

Regards,

Ben

<ogjunk-pgjedan@yahoo.com> wrote in message 
news:821429.69798.qm@web50304.mail.yahoo.com...
> Hello,
>
> I have a fairly large DB to dump and restore as fast as possible.  I'm 
> moving from 8.0.3 to 8.2.3! :)
>
> I normally dump with these options:
>
>  -d MyDB --clean --inserts --column-inserts --format=P
>
> But the last time I tried that, the restore took foreeeeeeeeeeeeeever.  So 
> I'm looking for the fastest way to import data from the old DB to the new 
> one.  Judging from pg_dump man page the following should be the fastest 
> dump & restore:
>
>  -d MyDB --format=c --ignore-version
>
> Is there anything else I can do to make the restore as fast as possible?
>
> Thanks,
> Otis
>
> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
> Simpy -- http://www.simpy.com/  -  Tag  -  Search  -  Share
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
> 





^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2007-02-25 22:58 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-22 15:43 Fastest DB restore options ogjunk-pgjedan@yahoo.com
2007-02-22 15:58 ` Naomi Walker <nwalke@eldocomp.com>
2007-02-22 16:05 ` Joshua D. Drake <jd@commandprompt.com>
2007-02-22 16:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-22 20:47 ` Marco Bizzarri <marco.bizzarri@gmail.com>
2007-02-25 22:58 ` Ben Trewern <ben.trewern@mowlem.com>
2007-02-22 17:21 Re: Fastest DB restore options ogjunk-pgjedan@yahoo.com
2007-02-22 18:13 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-02-23 06:42 Re: Fastest DB restore options ogjunk-pgjedan@yahoo.com

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