Thread: PITR and modification of the backup database

PITR and modification of the backup database

From
Sören Kress
Date:
Hi,

we are running a PostgreSQL server (8.2.4) which is used for our internet
service and use log file shipping to get a near-real-time backup.

As the hardware for the backup database server is quite expensive we would
like to use it for internal reporting purposes. That's why we have done the
following:

Server A is the production server, Server B is the backup server. The
database that should be backed up (from A) is called prod_db. We created
another (empty) database reporting_db on the _production_ server.

Once a day we create a base backup and copy it to the backup server. Then we
startup the server and replay all WAL files that are available. Then we
create the records in the reporting_db on the backup server that we need for
our reporting purposes.

In case of a fatal hardware failure of the production server we would like
to switch as fast as possible to the backup server. All new WAL files should
be replayed and then the backup server should be used as production server.
The usage for reporting purposes would have to be stopped until the other
server is repaired.

My question is: do we get in trouble when we change the contents of a
database on the backup server and then try to replay the latest log files
from the production server? The database on the backup server which we
change is not used on the production server and it's contents don't have to
be preserved. We are only interested in the contents of the production
database prod_db (which provides the basis for the reporting database).

Best regards
Soeren