Copying data files to new hardware?

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

Evan,

Others can probably answer your question better about copying, but in general we never do that but I think if you use the same 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 several years. Slony 1.2 still supports postgres 8.2. You can set up the new database on the new machine, set it up as a slave and the current machine as a master, replicate the entire database, wait for replication to catch up do a switchover and shut down the old master and uninstall slony. The plus is that you can accomplish what you need with pretty much 0 downtime.

--brian

On Oct 13, 2010, at 10:03 AM, EDH 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

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

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 should be 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 to set up replication and have everything happen for you. We spent a fair amount of time writing scripts to work with slony to help support our processes.

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

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/

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.