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.
Best Answer
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.