Incremental backup in postgresql

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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).