As said in the topic I need some help restoring a database to a new server.

There is a production database on one server - I do a daily automated backup of that database to a remote location: pgdump, binaries, and bzipped logs.

Now I want to create a test environment on the other server running a copy of that database.

Could you please tell step by step me how to recover it?

I read http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html but I have some problems understanding it since I've been using Postgres for a short period of time.


So you take a nightly 'consistent' pg_dump backup, which you can pg_restore into your test instance; that's step one, and if you just want to restore the database as it appeared during your nightly backup, that's all you need to do.

Step two, optional, is to grab your committed WAL files from the production instance and 'replay' them into your test instance; that gets you all the way forward from the time of your backup to the time the last WAL file was committed. The doc you reference is mostly concerned with the idea of setting up an infrastructure around those production WAL files so that they're reliably committed at regular intervals, and so that hooks into your backup system allow the WAL files to be backed up as soon as they are committed; we built a system around this that allows us to perform a database restore to within five minutes of the beginning of any outage period.

It sounds, however, like what you're trying to do is considerably less involved than this; pg_restore should be entirely sufficient for your requirements.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.