PostgreSQL backup idea

Dear all,

I have been thinking about PostgreSQL backup for some time now.

I can't implement PITR right now on our live systems, for commercial reasons.

I have been backing up the server with PG_DUMP every two days, reason it takes some times more than 24 Hours to backup the full database.

I have an idea not sure how workable it is:
1 - Backup live server pipe the content of pg_dump to psql and restore it to the second database server.
I have tested this with a small database on my test model and it works, not sure how long it will take though.

2 - I also thought, once I have backed up the full database, I am going to see if it is possible to check which tables have changed and only backup those to the remote server.
Not sure if it is possible to figure out which tables have changed, is there log or some command which can tell me which tables have changed?

3 - Another idea would be, to backup the full DB and then check when was the last update and from there do a backup and restore remotely.
For example: Last update was at 13:00, so from 13:00 onwards I would copy all the records and restore on the remote server.
Is it possible to find out what was the last minute which we had an update, and then backup only the records which were updated to the current time?
If so, how would I go about to do that?

4 - Backup instead of time use transactionID
Do a full backup, mark what was the last transactionID to the minute of backup finish and then onwards to backups only for the updated transactionIDs.
For example: Full backup finishes at 13:00, the last transactionID at 13:00 would be 00013, then from 13:01 onwards backup the updates, so on.

I am not sure if some of these things are possible, these are only ideas and I would appreciate any input and help, in either build it or destroying it.

If anyone has a backup script which handles failure and emails out and would like to share, for me to study it, I would very much appreciate it.

If you need more details why, reasons etc, please email me and I will clarify.

I am trying to work around the problems I am facing currently.

Thank you very much.

Really appreciate any help and input

Best regards

Renato

Renato Oliveira

e-mail: renato(dot)oliveira(at)grant(dot)co(dot)uk

Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
http://www.grant.co.uk/

Grant Instruments (Cambridge) Ltd

Company registered in England, registration number 658133

Registered office address:
29 Station Road,
Shepreth,
CAMBS SG8 6GB
UK

P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is confidential. It is intended only for the named recipients(s). If you are not the named recipient please notify the sender immediately and do not disclose the contents to another person or take copies.

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept liability for any damage which you sustain as a result of software viruses. You should therefore carry out your own virus checks before opening the attachment(s).

OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our http://www.grant.co.uk/Support/openxml.html