How do you address backups in 24x7 environment

[HenryMonster]

Hi All,

I am an Oracle guy and quite new to PostgreSQL.
I have do develop a huge but very low budget project,
so the imperative is to 'cut corners'.

I am trying to make a trade-off between PostgreSQL & mySQL.
mySQL scares me because it does not seem to have transactional mechanism (when either all your changes
apply or none); PostgreSql seems to be capable of
doing that (transaction support is very important
because we will be dealing with creadit cards and
financial records and will have situations where
updates of several tables as an atomic operation will be
required).

However I found a point of concern with PostgreSQL
as well:
suppose you performed a full open database backup
at 6:00am in the morning and at 4pm in the afternoon
the database crashed. We have this 6:00am backup
and we have PostgreSQL log files. But PostgreSQL
does not seem to be capable to restore to it's pre-crash
status based on this. How do you hadle such
recoveries in mission critical applications where
a loss of several hour of work is NOT ACCEPTABLE ?


Thank you in advance.

 

[rycamor]

You are definitely right to stay away from MySQL.

Here are a few links with info on High Availability and replication for PostgreSQL:

http://pgreplicator.sourceforge.net/

http://www.taygeta.com/ha-postgresql.html

http://www.erserver.com/

http://usogres.good-day.net/

http://dbbalancer.sourceforge.net/

http://gborg.postgresql.org/project/pgreplication/projdisplay.php

And in general, browse through the links at

http://techdocs.postgresql.org/

-- there are some gold mines in there ;-). -------------------------------------------

"Calculus is just the meaningless manipulation of higher symbols"
                          -unknown F student

 

[rycamor]

Also, do some reading about WAL (Write-Ahead Logging), in the PostgreSQL documentation. -------------------------------------------

"Calculus is just the meaningless manipulation of higher symbols"
                          -unknown F student