0

We are moving from one server with postgres 8.1 on it to another with 9.0 on it. We were looking to use slony to replicate between the two and already have slony-I 2 on that new server. However, it doesn't work with postgres 8.1, though slony 1.2 does.

What would be the best approach to migrating our databases over to the new server? Slony 1.2 on both, slony 2.0 on both, dump and import, or something else?

Eric Snow
  • 145
  • 1
  • 5
  • Be sure you've tested your database on 9.0 before you make the jump. Read the Release Notes for 8.2, 8.3, 8.4 and 9.0 and make sure they didn't change anything you were using, like the automatic typecast to TEXT they disabled as of 8.3 – DerfK Feb 16 '11 at 19:53
  • FYI: I ended up using londiste (skytools) to do the replication. It was super easy and super fast. I did find a few postgres features (and defaults) that had changed, but my schema was not terribly complex, so the migration went well. – Eric Snow May 26 '11 at 16:09

2 Answers2

2

The best and really the only reliable way to make such a large change would be:

  1. Test PostgreSQL 9 works with your application - do your ODBC connectors need updated? Do your SQL queries still work right?
  2. Dump the database and reload it into the new database - this will involve downtime.
Avleen
  • 1,042
  • 7
  • 4
  • thanks! So using replication here is not safe? We are talking maybe 25 million rows between 20 tables and not much complexity it our schema. If replication is not a reliable method then we definitely will avoid that. – Eric Snow Feb 16 '11 at 20:20
  • Across such a large version gap, I believe there is no guarantee that it would work correctly or as expected. In fact I believe that if you use Londiste, you need to use very specific versions to achieve it - and with any replication method there could be various untested and unexpected bugs. – Avleen Feb 21 '11 at 11:02
1

I'd say it would depend on whether you can accept the downtime. If it isn't 24x7 critical, I'd time a dump and restore and see how long it would take before doing it for real.

Otherwise, if you aren't going to continue replicating after switching to the new server, you're going to spend a lot of time installing the old version of slony, setting up the replication, waiting for everything to be copied over, switching over to the new master server, then shutting down all the slony stuff.

If you are going to upgrade the old server and continue replicating, you'll probably end up doing all of the above with the 1.2 slony, then upgrading to the 2.0 slony. Might as well dump and restore, then skip straight to configuring slony 2.0.

If you're going to continue replicating between 9.0 and 8.1, then go ahead and use slony to copy everything over and leave it running in slony 1.2 after the switchover (the switchover instructions linked above make the former master a slave of the new master, so replication automatically "reverses").

DerfK
  • 19,493
  • 2
  • 38
  • 54
  • 1
    I wouldn't advise trying to replicate across a major version change -- It *should* work, but this is a case where taking the downtime and doing it according to the book (Postgres manual) is definitely worth it. – voretaq7 Feb 16 '11 at 21:18
  • You've gotten the gist of my problem. Basically, I am going to copy over to a development server running 9.0 to make sure I have my configuration right. Then I am pushing to our new production box. I wanted to give it a couple weeks before we killed the old box (with 8.1) so I was hoping to reverse the replication during that time. The replication to the dev box would only be temporary. Later on I'm going to replicate the new server over to data warehouse. – Eric Snow Feb 16 '11 at 21:28
  • And, unfortunately, I don't have a large enough maintenance window to cover taking down the database for long enough to dump, copy, and import. – Eric Snow Feb 16 '11 at 21:28