postgreSQL vs mysql: backup & recovery
 

i'll be maintaining a database server and have a choice btwn mysql and postgresql. We need transactional support, this means in mysql we'll be using their InnoDB engine. this is for an ecommerce site. i don't want to loose any orders or registrations. Very recent backups of the data is important.

I haven't dug into either very deeply in the backup & recovery area, hoping those who have walked the path can give me some shortcuts. mysql has mysqldump and binary log files. seems InnoDB needs the server stopped to do backups other than mysqldump. postgres has pg_dump that does hot backups.

one thing i know for sure, there are plenty more books on mysql than postgresql. however, i think 1 quality book on either one is all anyone needs.

I agree that one good book is a good thing to have; my personal preference in
the "battle of the RDBMS" is clearly PostgreSQL, popularity aside ;}

I'm not sure whether you've stumbled across these pages in your quest for
information:
http://www.postgresql.org/docs/curre...up-online.html
and
http://www.postgresql.org/docs/curre...iguration.html

They discuss WAL archiving (which is similar to Oracles archive-log
mechanism - in the functionality, anyway). Basically what you do is
to start with a, for example, week-old snapshot for the restore, and
then just add the (always current) WAL chunks back to it on restore.
After all, on a heavily loaded production database system you can't
run an export every 30 minutes. ;}



Cheers,
Tink