Hello, I'm interested in using WAL shipping / replication for backup purposes but have no interest in failover. Currently my situation is:
I have two servers, live and backup, which are in different cities. The backup server is also a test/development machine. Backups of my most important database are made hourly with pg_dump, excluding some larger tables with non-critical logging data. Even so, as the database grows, backups are taking longer and it looks as though they may start to impact performance. A full backup is made nightly and transferred to the backup machine, along with all of the day's hourly backups. I'm looking into using replication by WAL shipping - after all, there's no use to backing up data which hasn't changed since last time - only a small percentage of records are created/updated. However, I need a) to be able to restore to a point in time easily, which I can do to within an hour at the moment by restoring the correct dump. Sometimes users ask for a restore having accidentally updated/deleted records. b) to carry on running a test server database, that means one that's read and writeable. I obviously can't use a replication slave as a read/write test server at the same time. At the moment I've thought of a couple of options, I don't know if either are possible - I have a bit of a hazy idea of WAL replication. 1) Continuously ship the WAL records to somewhere on the test server unknown to Postgres but run the test machine as a normal database completely separately. If a backup is needed, delete the test database, restore to the last full backup (a filesystem backup?) and copy all WAL records into Postgres' directory so it can see them. Start it up configured to replay them, up to a certain time. 2) Run two instances of Postgres on the test/backup server on different ports, one configured as a replication slave, one normal. I'm not sure if this is possible with the RPM builds I'm using. Are either of those two likely? Any other suggestions? Another question is will the replication coming in v9.0 change things and would it be worth holding off until then? In particular Command Prompt's PITR tools look useful for restoring to a particular point in time, will these still work or will there be equivalents? Regards Oliver Kohll oli...@agilebase.co.uk / +44(0)7814 828608 / skype:okohll -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
