Thread: Copying data files to new hardware?

Copying data files to new hardware?

From
EDH
Date:
I have a large Postgres DB (1100 GB) that I'd like to move to a new
physical machine.  In the past I've done this via pg_dump & restore,
but the DB was much smaller then, and I'm concerned about how long
that would take.  The version of pg currently in use is:

PostgreSQL 8.2.5 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.1 20070105 (Red Hat 4.1.1-52)

What I'd like to know is: if I install the latest 8.2.x release - I
see 8.2.18 RPMs are available - can I do a straight copy of the
contents of /var/lib/pgsql/data/ to the new server and start it up?
Or is dump & restore the only real way to do this?

If I have to do a dump & restore I figure I may as well take the
opportunity to migrate to 8.4 or 9.0, but I'd rather just get
everything done as quickly as possible.

Thanks,
Evan

Re: Copying data files to new hardware?

From
Jaime Casanova
Date:
On Wed, Oct 13, 2010 at 11:03 AM, EDH <evandhoffman@gmail.com> wrote:
[...]
>
> What I'd like to know is: if I install the latest 8.2.x release - I
> see 8.2.18 RPMs are available - can I do a straight copy of the
> contents of /var/lib/pgsql/data/ to the new server and start it up?
> Or is dump & restore the only real way to do this?
>

it's safe to just install any version of 8.2.x, copy the whole data
directory (plus any tablespace's directories you could have) and start
again... probably you want to REINDEX your indexes (read the release
notes to see what specific kind of indexes you need to reindex)

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

Re: Copying data files to new hardware?

From
Brian Hirt
Date:
Evan,

Others can probably answer your question better about copying, but in general we never do that but I think if you use
thesame arch and major release of postgresql you will be okay. 

We have used Slony successfully for all of our database upgrades, server maintenance and database moves over the last
severalyears.  Slony 1.2 still supports postgres 8.2.   You can set up the new database on the new machine, set it up
asa slave and the current machine as a master, replicate the entire database, wait for replication to catch up do a
switchoverand shut down the old master and uninstall slony.  The plus is that you can accomplish what you need with
prettymuch 0 downtime. 

--brian

On Oct 13, 2010, at 10:03 AM, EDH wrote:

> I have a large Postgres DB (1100 GB) that I'd like to move to a new
> physical machine.  In the past I've done this via pg_dump & restore,
> but the DB was much smaller then, and I'm concerned about how long
> that would take.  The version of pg currently in use is:
>
> PostgreSQL 8.2.5 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
> 4.1.1 20070105 (Red Hat 4.1.1-52)
>
> What I'd like to know is: if I install the latest 8.2.x release - I
> see 8.2.18 RPMs are available - can I do a straight copy of the
> contents of /var/lib/pgsql/data/ to the new server and start it up?
> Or is dump & restore the only real way to do this?
>
> If I have to do a dump & restore I figure I may as well take the
> opportunity to migrate to 8.4 or 9.0, but I'd rather just get
> everything done as quickly as possible.
>
> Thanks,
> Evan
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Re: [ADMIN] Copying data files to new hardware?

From
Tom Lane
Date:
Jaime Casanova <jaime@2ndquadrant.com> writes:
> On Wed, Oct 13, 2010 at 11:03 AM, EDH <evandhoffman@gmail.com> wrote:
>> What I'd like to know is: if I install the latest 8.2.x release - I
>> see 8.2.18 RPMs are available - can I do a straight copy of the
>> contents of /var/lib/pgsql/data/ to the new server and start it up?
>> Or is dump & restore the only real way to do this?

> it's safe to just install any version of 8.2.x, copy the whole data
> directory (plus any tablespace's directories you could have) and start
> again...

Not just "any" version --- it has to be the same build options.
(integer-datetimes is definitely a critical option, and I forget what
else.)  But if you're using an RPM from the same RPM supplier as before,
they probably got this right.

            regards, tom lane

Re: Copying data files to new hardware?

From
Brian Hirt
Date:
Yes, we've used Slony for migrating 8.2 -> 8.3 -> 8.4 and plan an using it to migrate to 9.0 in the near future.  You
shouldbe able to skip releases as well like you say 8.2 -> 8.4. 

You'll probably want to test out both slony and 8.4 on your development machines first and make sure everything works
okay.  It takes a little bit to get familiar with slony, it's not a simple program that you install and click a button
toset up replication and have everything happen for you.  We spent a fair amount of time writing scripts to work with
slonyto help support our processes. 

On Oct 13, 2010, at 10:59 AM, Evan D. Hoffman wrote:

> Thanks, Brian & Jaime.  Regarding Slony, would that allow for
> migration to a new version as well - i.e. moving from 8.2 on the old
> machine to 8.4 on the new machine via Slony with minimal downtime?
>
> The Slony method is one I hadn't considered.  Since our database is so
> large, even a direct file copy would require some downtime (since we'd
> need to stop the DB before beginning the copy).  Slony would probably
> let us cut the downtime from hours to minutes (dump & restore for us
> has historically taken days).
>
> Thanks again,
> Evan


Re: Copying data files to new hardware?

From
"Evan D. Hoffman"
Date:
Thanks, Brian & Jaime.  Regarding Slony, would that allow for
migration to a new version as well - i.e. moving from 8.2 on the old
machine to 8.4 on the new machine via Slony with minimal downtime?

The Slony method is one I hadn't considered.  Since our database is so
large, even a direct file copy would require some downtime (since we'd
need to stop the DB before beginning the copy).  Slony would probably
let us cut the downtime from hours to minutes (dump & restore for us
has historically taken days).

Thanks again,
Evan

Re: Copying data files to new hardware?

From
Craig Ringer
Date:
On 14/10/10 00:59, Evan D. Hoffman wrote:
> Thanks, Brian & Jaime.  Regarding Slony, would that allow for
> migration to a new version as well - i.e. moving from 8.2 on the old
> machine to 8.4 on the new machine via Slony with minimal downtime?
>
> The Slony method is one I hadn't considered.  Since our database is so
> large, even a direct file copy would require some downtime (since we'd
> need to stop the DB before beginning the copy).

You don't have to stop the DB before doing a file-level database copy,
though. If you set up WAL archiving to the destination server, you can
then use pg_start_backup(), copy the data directory while Pg is writing
to it, disconnect clients, pg_stop_backup(), and only then shut the old
Pg down.

You will face some WAL replay time bringing the new machine up, though
if you set it up as a warm spare with continuous replay that'll minimize
the replay time. Unless your master is under heavy continuous write load
it won't be too bad anyway.

The handy thing about this approach is that you can *test* it by doing
the whole migration as if you meant it for real, just not disconnecting
clients from the original server or shutting it down at the end. That
gives you a chance to verify that the DB that's been replicated to the
new server has come up cleanly and is working well before you do the
whole thing again for real. Even better, if you've done such a test you
can use rsync to update your base backup after pg_start_backup() instead
of copying the whole thing again, which can be a *LOT* faster.

If you take this approach, be VERY sure to read the documentation on
PITR and warm standby, and test your migration before doing it for real.



--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

Re: Copying data files to new hardware?

From
Vick Khera
Date:
On Wed, Oct 13, 2010 at 12:59 PM, Evan D. Hoffman
<evandhoffman@gmail.com> wrote:
> The Slony method is one I hadn't considered.  Since our database is so
> large, even a direct file copy would require some downtime (since we'd
>

If you do go the slony route, you may want to do the replication
incrementally.  That is, instead of configuring all of your tables all
in one slony "set", group your tables (possibly even having one table
per set if they are really big) and adding one set at a time.  The
initial copy slony does requires it all to be done in one transaction,
so if it takes days to copy and populate the database you will have an
open transaction for that long on your master.  This will impact your
vacuums at the least.

It will let you skip versions.  Two years ago we went from 8.1 to 8.3
using slony, and this week I'm moving from 8.3 to 9.0.  We run slony
continuously anyway, just to keep a live streaming copy of our data,
so using it is natural for us.

There is a mailing list dedicated to slony if you're looking for more
assistance.