Thread: 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
Attachment
On Thu, Nov 16, 2000 at 11:14:15AM -0500, root wrote: > 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 &' But why do you do 2>&1 in your startup-script? This will redirect data on stderr to stdout - I don't think that is a good idea. > 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..... This is becuase the postgres deamon keeps the file open, and will keep on writing to the old file even after you delete it. Most deamons will reopen their log-files if you send them a HUP signal, so a possible solution is: mv logfile logfile.1 kill -HUP <pid> process logfile.1 rm logfile.1 The order is important to make sure you don't have a race-condition. A different alternative is to use a FIFO instead of a normal file, and process it continually istead of in batch-jobs. A totally different approach would be to have a "sql-proxy" relay all requests to two (or more) different servers to always keep them in sync. -- Ragnar Kjørstad
On Thu, Nov 30, 2000 at 12:08:20AM -0800, hamptone wrote: > > [ about processing the postgresql log ] > > Thanks for the tips Ragnar...although I wish you would explain more about > FIFO. The only thing that rings a bell on this is First In First Out from > the inventory chaper of my accounting textbook..... I guess you're familiar with pipes? like: cat logfile | program a FIFO is pretty much the same thing, only it has a filename. This mean a process can write to the FIFO thinking it's writing to a regular file, and another process can read from the FIFO thinking it's reading from a regular file. This makes it possible to process the file in realtime instead of in batch, if you choose to. If you use a FIFO, your postgresql process will block if there is nothing actually reading from the FIFO. This means two things: 1. If the program processing the logfile dies, postgresql die too. 2. The postgresql server can't work faster than your logfile processer. But the good news is your backupserver will always be up-to-date. -- Ragnar Kjørstad
Thanks for the tips Ragnar...although I wish you would explain more about FIFO. The only thing that rings a bell on this is First In First Out from the inventory chaper of my accounting textbook..... ----- Original Message ----- From: "Ragnar Kjørstad" <postgres@ragnark.vestdata.no> To: "root" <root@dennis.veritime.com> Cc: <pgsql-admin@postgresql.org> Sent: Thursday, November 23, 2000 8:28 AM Subject: Re: [ADMIN] Redundant databases/real-time backup > On Thu, Nov 16, 2000 at 11:14:15AM -0500, root wrote: > > 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 &' > > But why do you do 2>&1 in your startup-script? This will redirect data > on stderr to stdout - I don't think that is a good idea. > > > 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..... > > This is becuase the postgres deamon keeps the file open, and will keep > on writing to the old file even after you delete it. Most deamons will > reopen their log-files if you send them a HUP signal, so a possible > solution is: > > mv logfile logfile.1 > kill -HUP <pid> > process logfile.1 > rm logfile.1 > > The order is important to make sure you don't have a race-condition. > > A different alternative is to use a FIFO instead of a normal file, and > process it continually istead of in batch-jobs. > > > > > A totally different approach would be to have a "sql-proxy" relay all > requests to two (or more) different servers to always keep them in sync. > > > -- > Ragnar Kjørstad >
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: "Ragnar Kjørstad" <postgres@ragnark.vestdata.no> > To: "root" <root@dennis.veritime.com> > Cc: <pgsql-admin@postgresql.org> > Sent: Thursday, November 23, 2000 8:28 AM > Subject: Re: [ADMIN] Redundant databases/real-time backup > > > On Thu, Nov 16, 2000 at 11:14:15AM -0500, root wrote: > > > 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 &' > > > > But why do you do 2>&1 in your startup-script? This will redirect data > > on stderr to stdout - I don't think that is a good idea. > > > > > 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..... > > > > This is becuase the postgres deamon keeps the file open, and will keep > > on writing to the old file even after you delete it. Most deamons will > > reopen their log-files if you send them a HUP signal, so a possible > > solution is: > > > > mv logfile logfile.1 > > kill -HUP <pid> > > process logfile.1 > > rm logfile.1 > > > > The order is important to make sure you don't have a race-condition. > > > > A different alternative is to use a FIFO instead of a normal file, and > > process it continually istead of in batch-jobs. > > > > > > > > > > A totally different approach would be to have a "sql-proxy" relay all > > requests to two (or more) different servers to always keep them in sync. > > > > > > -- > > Ragnar Kjørstad > >