Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to migrate a production database from one linux server to other. I am not concern about upgrading my database version, just I want to move the database from one server to other with minimum down time.

The version of my database is 8.3. Its of size 160 GB.

It takes 12 Hours to take the dump and 8 hours to restore it.

Over here the downtime is of 20 Hours, which I cannot afford. I can afford a down time of 4 hours at max.

So how will I refresh the delta changes that happened in 20 Hours into the production database with the database over the other server.

Please help.

share|improve this question
    
This isn't "incremental" backup so much as a one-time "delta," right? –  Matt Ball Apr 8 '13 at 13:52
    
What's the transfer rate between your two servers? –  Daniel Vérité Apr 8 '13 at 14:00
    
Yes its one time delta. –  ArunK Padule Apr 8 '13 at 14:10
    
Transfer rate is 3 MB per sec. –  ArunK Padule Apr 8 '13 at 14:28
    
At 3MB/s, it would take ~15 hours to transfer the 160GB to the target system, which eliminates the cold backup option. –  Daniel Vérité Apr 9 '13 at 11:31
add comment

migrated from stackoverflow.com Apr 8 '13 at 17:58

This question came from our site for professional and enthusiast programmers.

3 Answers

OK - if you can't dump + restore, and don't want to understand your system well enough to set up replication then you've got only one option left.

You will need to run identical setups at each end (version of PostgreSQL, 32/64-bit, compilation options, file paths etc).

Then, scp the entire data directory - including all the WAL files (pg_xlog) and all other sub-directories.

During a quiet period, halt your primary server and use rsync to bring your copy up-to-date with respect to the original. Then, start the copied server and off you go.

Whether you can do this in less than 4 hours, I don't know. It will depend on how efficient rsync really is, the latency between both systems, how many updates you have had etc.

It might be that running rsync before halting the primary server will make the real run go faster. Difficult to say.

share|improve this answer
    
Good answer, but use rsync with --checksum option, as file modification times, that rsync is depending on by default, are not reliable in database data directories. –  Tometzky Apr 9 '13 at 18:55
add comment

You may as well duplicate your file system in a lot of different ways: your file system may permit to create snapshot, or you might have your file system on a LVM that permits snapshot, or it may reside on an external appliance able to duplicate the entire volume.

In all of this scenarios, just stop your database, run your snapshot, and restart your database again. This will stop your database only a few seconds, even if it may slow it down for a few hours while copying your data from the snapshot.

Then, create a new postgresql cluster and assign it as data directory your snapshotted data. If you do not plan to modify a lot of data in your new copy, you may just use the snapshot itself, otherwise you need to completely copy your snapshot to a new file system.

share|improve this answer
add comment

You'll need to use an out-of-core replication solution like Slony-I, Bucardo or Londiste from Skytools.

Londiste is probably the simplest option if you need only asynchronous one-way replication.

Alternately, you could do a two-stage copy to reduce (but not eliminate) the migration window. rsync the data directory to the new server while the old server is running. Once this is done, stop the old server and rsync the data directory again to copy the differential changes only. It may be useful to pg_start_backup() on the old server before starting the first copy and pg_stop_backup() just before you shut it down for the final rsync. This will cause table bloat but will tend to cause tables to be appended to rather than rewritten, allowing rsync to potentially copy faster. This does require downtime, possibly significant downtime, but if you don't want to use replication then you don't have many other application-level options.

You could use various lower-level options - switch to a shared network block device like DRBD, use an md asynchronous raid1 volume, etc. Most such options tend to risk performance problems on on the source database system, though.

Personally I strongly recommend that you look into a replication based approach. If you aren't confident setting one up then you should consider seeking professional support. (Disclosure: I work for one of the listed companies, as shown in my profile).

share|improve this answer
    
Hey I am new to postgresql. I am not confident on setting up replication. Is there any easy approach like copying the changes to a file and then scp the file and restore. –  ArunK Padule Apr 8 '13 at 14:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.