Обсуждение: Fastest DB restore options
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
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/
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.
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
----- 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
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.
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/
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
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 >