Обсуждение: 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
. 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.
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?
Is there any risk to make it in this way?
What will happen if during the wal copy operation the database writes something in too?
Will the wal file copied usefull in those situations?
What other alternatives do we have?
Is there any risk to make it in this way?
What will happen if during the wal copy operation the database writes something in too?
Will the wal file copied usefull in those situations?
What other alternatives do we have?
Thanks in advance!
Streit
Yahoo! Acesso Grátis
Internet rápida e grátis. Instale o discador agora!
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.
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 <phil.f@worldonline.fr> 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
activate
the "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 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.
Yahoo! Search
Dê uma espiadinha e saiba tudo sobre o Big Brother Brasil.
> 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