Re: [GENERAL] wal copies for high availability
Hi, Our IT budget is not so much and even so I´m trying to set up a Postgresql high availability solution for our business. My managers gave me the following statements that I must follow: . the system could be out of service no more than 2 hours . last 5 minutes of work could be lost The first choice was to use slony-i and a master/slave configuration, but I could not use it because we have many tables without primary keys and the software house will charge us a lot to make this kind of modification. I believe you can get slony to transparently add in a primary key for each of those tables. This is probably your best option. Another cheap alternative is diarly (00:00) stop the master database and make a copy of the $PGDATA directory to the slave and during the day make regularly (5 x 5 minutes) copies of the current wal file to the slave too. Check out the instructions for incremental backup. It's not necessary to stop the database. This should work fine but you will need to make sure you are copying the latest WAL file at least every 5 minutes. To recover the system we need only to apply all the logs created during the day to the slave and sta rt it up. Again, see the directions for incremental backup. http://www.postgresql.org/docs/8.1/interactive/backup-online.html Rick ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] wal copies for high availability
Slony is a good choice... but the ERP owner will charge us a lot to include primary keys or unique not null columns all over the system. Philippe Ferreira <[EMAIL PROTECTED]> escreveu: Hi,> Another cheap alternative is diarly (00:00) stop the master database > and make a copy of the $PGDATA directory to the slave and during the > day make regularly (5 x 5 minutes) copies of the current wal file to > the slave too.> > To recover the system we need only to apply all the logs created > during the day to the slave and sta rt it up.> > Will it work? It will work, but :You must *not* stop any database or service diarly (00:00), but instead activatethe "hot backup mode" to be able to copy your $PGDATA directory "on the fly" tothe slave. If you do this way, you will get a recoverable copy of your server.To activate the slave, you will only have to start it in "recovery mode", and all yourWAL files of the day will be replayed in a second !> Is there any risk to make it in this way?If you take care of your WAL files, everything should be ok !>> What will happen if during the wal copy operation the database writes > something in too?This is a good question ! I don't know the exact consequences...Maybe the best solution is to put your pg_xlog directory on a "snapshot capable" file system,and send a snapshot of the current WAL every 5 minutes...> Will the wal file copied usefull in those situations?Of course, yes !The filled WAL files (WAL archives) are automatically copied by the postmaster process,but you need to copy yourself the current one to be able to recover the last stuff !> ; What other alternatives do we have?Slony ?Philippe Ferreira. Yahoo! Search Dê uma espiadinha e saiba tudo sobre o Big Brother Brasil.
Re: [GENERAL] wal copies for high availability
Hi, Another cheap alternative is diarly (00:00) stop the master database and make a copy of the $PGDATA directory to the slave and during the day make regularly (5 x 5 minutes) copies of the current wal file to the slave too. To recover the system we need only to apply all the logs created during the day to the slave and sta rt it up. Will it work? It will work, but : You must *not* stop any database or service diarly (00:00), but instead activate the "hot backup mode" to be able to copy your $PGDATA directory "on the fly" to the slave. If you do this way, you will get a recoverable copy of your server. To activate the slave, you will only have to start it in "recovery mode", and all your WAL files of the day will be replayed in a second ! Is there any risk to make it in this way? If you take care of your WAL files, everything should be ok ! What will happen if during the wal copy operation the database writes something in too? This is a good question ! I don't know the exact consequences... Maybe the best solution is to put your pg_xlog directory on a "snapshot capable" file system, and send a snapshot of the current WAL every 5 minutes... Will the wal file copied usefull in those situations? Of course, yes ! The filled WAL files (WAL archives) are automatically copied by the postmaster process, but you need to copy yourself the current one to be able to recover the last stuff ! What other alternatives do we have? Slony ? Philippe Ferreira. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
