Re: Fw: Redundant databases/real-time backup

Поиск
Список
Период
Сортировка
От Lockhurst
Тема Re: Fw: Redundant databases/real-time backup
Дата
Msg-id 3A50C508.19D81102@cal2.vsnl.net.in
обсуждение исходный текст
Список pgsql-admin
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


В списке pgsql-admin по дате отправления:

Предыдущее
От: Lockhurst
Дата:
Сообщение: Re: Redundant databases/real-time backup-Revisited
Следующее
От: Michael Davis
Дата:
Сообщение: Removing a constraint?