pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFastest 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