Fw: Redundant databases/real-time backup

Lockhurst,

I looked at your startup line and it looks like you are missing an '>' in
the middle of it. I have sent you my original message because I see that
you responded to what Ragnar said later in the thread.

Ragnar had also suggested that I could use fifo, named pipe instead of
processing the transactions in batch, but I believe that is incorrect. It
is crucial to know whether or not the statement was executed properly by the
redundant database server, thus my perl script sends one sql statement at a
time, gets a response, decides whether there was an error, and continues.

Good luck. I realize that there are many shortcomings to the approach I
have taken, including the inability of the script to test a connection to
the redundant database before processing, high server overhead, etc. Maybe
somebody could do it better in C. I don't know C but I'm sure that a binary
would work a heck of a lot better than this little perl script.

_______________________________

Hi,

Tried by modifying /etc/rc.d/init.d/postgresql with following changes:
su -l postgres -c '/usr/bin/postmaster -i -D /var/lib/pgsql
/var/lib/pgsql/query_log 2>&1 &'

The file query_log is created. But it is remaining empty even after restart
and
all sorts of transactions including insert statements.

Early thanks for any help

Wish you all a vey happy new 2001.

Lockhurst

----- Original Message -----
From: "root" <root(at)dennis(dot)veritime(dot)com>
To: <pgsql-admin(at)postgreSQL(dot)org>
Sent: Thursday, November 16, 2000 8:14 AM
Subject: [ADMIN] 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
>