Thread: Fastest DB restore options

Fastest DB restore options

From
ogjunk-pgjedan@yahoo.com
Date:
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
datafrom 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



Re: Fastest DB restore options

From
"Joshua D. Drake"
Date:
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
datafrom 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/


Re: Fastest DB restore options

From
Naomi Walker
Date:
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
datafrom 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
theindividual or entity to which it is addressed, and may contain information that is privileged, confidential or
exemptfrom disclosure under applicable law. If you are not the intended recipient or it appears that this e-mail has
beenforwarded to you without proper authority, you are notified that any use or dissemination of this information in
anymanner is strictly prohibited. In such cases, please notify us immediately at mailmaster@mphasis.com and delete this
mailfrom your records. 

Re: Fastest DB restore options

From
Tom Lane
Date:
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
followingshould 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

Re: Fastest DB restore options

From
ogjunk-pgjedan@yahoo.com
Date:
----- 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
followingshould 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
newlyinstalled 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




Re: Fastest DB restore options

From
Alvaro Herrera
Date:
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
newlyinstalled 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.

Re: Fastest DB restore options

From
"Marco Bizzarri"
Date:
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
datafrom 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/

Re: Fastest DB restore options

From
ogjunk-pgjedan@yahoo.com
Date:
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
datafrom 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




Re: Fastest DB restore options

From
"Ben Trewern"
Date:
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
>