Re: Fw: Redundant databases/real-time backup - Mailing list pgsql-admin

Yes, if you look at the redundancy.pl script, you will see that it does a
grep on the query_log.  Simply add grep-v 'SELECT' to remove the select
statements.  I left in the ability for the script to handle selects because
of the possibility that someone might use a subselect with an insert,
update, or delete.

----- Original Message -----
From: "Lockhurst" <lochurst@cal2.vsnl.net.in>
To: "rhampton" <rhampton@veritime.com>
Cc: <pgsql-admin@postgresql.org>; "kevin harvey" <harvey_k_j@yahoo.com>
Sent: Monday, January 01, 2001 9:57 AM
Subject: Re: Fw: [ADMIN] 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@dennis.veritime.com>
> > To: <pgsql-admin@postgreSQL.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@veritime.com or root@veritime.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
>
>