Re: Fw: Redundant databases/real-time backup

Hi,

Thank you very much for your valuable tips. Our application is quite mission
critical and we are working toward a C / JAVA program to tackle the problem of
accumulation, making .jar file, autodialing and transfer of information over a
large set of dial-up lines. We will share the schema once it is ready along with
the C code.

Now about the other details:

The data logging to pgsql_log file has successfully started today after putting
out the -S before the -D, which was there by default.
Now the line reads:
su -l postgres -c '/usr/bin/postmaster -i
-D/home/postgres/data>/home/postgres/data/query_log 2>&1 &'

Well: it is logging all operations including select. Is there any way of logging
only the SQL statements or a selected set of it : like INSERT, UPDATE and
DELETE. This will reduce a lot of coding.

Early thanks for any help.

Wish you all a happy 2001.

- Lockhurst

rhampton wrote:

> 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
> >
>
> ------------------------------------------------------------------------
> Name: postgresql.dat
> postgresql.dat Type: unspecified type (application/octet-stream)
> Encoding: quoted-printable
>
> Name: pg_options.txt
> pg_options.txt Type: Plain Text (text/plain)
> Encoding: quoted-printable
>
> Name: rederhandler.pl
> rederhandler.pl Type: Perl Program (application/x-perl)
> Encoding: quoted-printable
>
> Name: redundancy.pl
> redundancy.pl Type: Perl Program (application/x-perl)
> Encoding: quoted-printable