Thread: Copying data files to new hardware?
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
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 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
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 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
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
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.