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 по дате отправления: