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

Search Discussions

  • Joshua D. Drake at Feb 22, 2007 at 4:05 pm

    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/
  • Naomi Walker at Feb 22, 2007 at 4:14 pm
    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.
  • Tom Lane at Feb 22, 2007 at 4:25 pm

    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
  • Ogjunk-pgjedan at Feb 22, 2007 at 5:21 pm
    ----- 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
  • Alvaro Herrera at Feb 22, 2007 at 6:14 pm

    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.
  • Marco Bizzarri at Feb 22, 2007 at 8:47 pm

    On 2/22/07, 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
  • Ogjunk-pgjedan at Feb 23, 2007 at 6:42 am
    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 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
  • Ben Trewern at Feb 25, 2007 at 11:15 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-admin @
categoriespostgresql
postedFeb 22, '07 at 3:43p
activeFeb 25, '07 at 11:15p
posts9
users7
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase