Fw: Redundant databases/real-time backup
От | rhampton |
---|---|
Тема | Fw: Redundant databases/real-time backup |
Дата | |
Msg-id | 002d01c07426$96c13e80$cf211e18@mw.mediaone.net обсуждение исходный текст |
Список | pgsql-admin |
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 >
Вложения
В списке pgsql-admin по дате отправления:
Следующее
От: jmoschet@netscape.netДата:
Сообщение: problem with mkMakefile.tcldefs.sh if USE_TCL=false