Redundant databases/real-time backup

Several people have expressed an interest in having the capability to have real
time redundancy. I am releasing my kludge solution to the mailing list in hope
that others might expand upon it.

First, you should dump your database, drop it, and recreate it on the computer
to be mirrored. You should also create a fresh copy on the mirroring computer.
Most likely, your OIDs are still not going to be in synch. For those of you
that use OIDs as a poor man's primary key, it will be necessary for you to
write a script that can sync up the oids on both computers (probably best run
as a cron script too). I have thought about ways to do this, but I'll leave
that to someone else to complete. (PLEASE POST YOUR RESULTS THOUGH!)

It is necessary to create/alter the postgresql startup script. I have included
a copy of mine. The database to be mirrored must start up with logging
enabled:

su -l postgres -c '/usr/bin/postmaster -i -D/home/postgres/data >/home/postgres/data/query_log 2>&1 &'

In this case I have specified a file called query_log that will maintain a copy
of all of the queries that have been executed. I have included a complete
copy of my startup script called (unimaginatively) postgresql. For linux users,
it should be in /etc/rc.d/init.d

I have also attached my pg_options file. For me, this resides in
/home/postgres/data. I have found that this file does not seem to affect my
query_log, but I incude it for reference for others to use.

Next, you should create a line in your /etc/crontab or /var/spool/cron/root file
to execute the redundancy script with root level permissions:

0-59/5 * * * * /root/redundancy.pl

You should install the redundancy.pl and rederhandler.pl scripts in the same
directory.

You will, of course, need to modify these scripts to work. Several of my dead
ends are still in the scripts commented out.

rederhandler.pl is set up to work with qmail instead of sendmail. You should
be able to substitute the path to your sendmail program and it should work
fine, i.e. /usr/sbin/sendmail instead of /var/qmail/bin/qmail-inject

Other points:
The query_log can get large rather quickly. You cannot simply issue a rm -rf
query_log, touch query_log and chmod. Even with the appropriate permissions
the daemon will not write to a new file, for some reason you must restart
postgres using the startup script. Perhaps one of the developers has an answer
to this problem.....

Also, my script does not check for network problems....

The script takes a lot of overhead. For high volume inserts and deletes,
depending on how often you run redundancy.pl, sometimes it just cannot keep up.
BEWARE....it can bog down and crash your server if the backlog becomes too
large.

Also, I chose to allow it to pass SELECT statements and get back the results
from the remote database......I beleive that if you do not use sub-selects, you
may grep -v 'SELECT' and improve your performance significantly.

Please email comments and suggestions/modifications to me at
rhampton(at)veritime(dot)com or root(at)veritime(dot)com