Thread: Log archiving (like db2, Oracle)
I am working and playin with PostgreSQL for about a year now, and I still find out new, great features. It is really great, stable and very easy to admininistrate compared to db2 or Oracle. But there is one important feature that ist missing compared to the big players: Recovery of a database with the last full dump (e.g. nightly) and all the archived logs. There are no archived logs in PostgreSQL by now. PostgreSQL only has th WAL that does a crash recovery. Are there plans to include archiving of "used" logs? Regards Holger -- PGP/GPG Key-ID: http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1
I am new to Postgres (we are looking at swaping out our Oracle databases to save on the repressive licencing fees). Archive logging is not just for database recovery - it can be used to implement standby databases. Archived redo logs is one of the bigger gaps, as we use standby databases to help maintain 24x7 (or our best attempt at). Of course, adding this feature is one more step away from the features you like (a fast, reliable, easy-to-administer database) and one step closer to an Oracle database (slower, less reliable, and harder to administer). David ----- Original Message ----- From: "Holger Marzen" <holger@marzen.de> To: <pgsql-general@postgresql.org> Sent: Tuesday, February 12, 2002 11:04 PM Subject: [GENERAL] Log archiving (like db2, Oracle) > I am working and playin with PostgreSQL for about a year now, and I > still find out new, great features. It is really great, stable and very > easy to admininistrate compared to db2 or Oracle. > > But there is one important feature that ist missing compared to the big > players: Recovery of a database with the last full dump (e.g. nightly) > and all the archived logs. There are no archived logs in PostgreSQL by > now. PostgreSQL only has th WAL that does a crash recovery. Are there > plans to include archiving of "used" logs? > > Regards > Holger > > -- > PGP/GPG Key-ID: > http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1 > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Tue, 12 Feb 2002, David Griffiths wrote: > I am new to Postgres (we are looking at swaping out our Oracle databases to > save on the repressive licencing fees). > > Archive logging is not just for database recovery - it can be used to > implement standby databases. Archived redo logs is one of the bigger gaps, > as we use standby databases to help maintain 24x7 (or our best attempt at). You can implement standby databases by dumping the production db and recovering the standby db. But, of course, it depends of the amounts of data if this can be done frequently. > Of course, adding this feature is one more step away from the features you > like (a fast, reliable, easy-to-administer database) and one step closer to > an Oracle database (slower, less reliable, and harder to administer). I cannot see any reason why a select should be slower when archive logs are written. Even when updating a db, archive logs can IMHO be done by switching the WAL and keeping it instead of writing to the WAL *and* an additional log that is to be archived.
Our database is in the 20 gigabyte range, so we won't be copying files to get standby databases working. Archiving can slow the database down by generating alot of disk-traffic writing the logs (read below). A lot of questions need to be answered: 1) How will logs get to the standby database? Will the database do it? Or will the OS/developer do it? 2) Will archived logs be applied automatically to the standby, or queued up. How will you tracking missing WAL log files (due to network problem, etc)? 3) Do all tables get archived? Or do you have a NOLOGGING option like Oracle? If all tables get archived, then even heavy reads and creating lots of connections (that alter the internal tables) will generate alot of archived Write Ahead Logs. 4) Switching the WAL has issues as well. Under Oracle, when the database fills one Redo Log file, it jumps to the next, and the Archiver archives the previous. A problem will arise if the Archiver falls behind, and is still archiving a Redo Log File when the database (Log Writer) suddenly needs it. You can alleviate this by spanning redo log files across your disks, and putting the archive log files on a seperate disk as well. Will only happen if your site is busy. David. ----- Original Message ----- From: "Holger Marzen" <holger@marzen.de> To: "David Griffiths" <dgriffiths@boats.com> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, February 13, 2002 12:00 AM Subject: Re: [GENERAL] Log archiving (like db2, Oracle) > On Tue, 12 Feb 2002, David Griffiths wrote: > > > I am new to Postgres (we are looking at swaping out our Oracle databases to > > save on the repressive licencing fees). > > > > Archive logging is not just for database recovery - it can be used to > > implement standby databases. Archived redo logs is one of the bigger gaps, > > as we use standby databases to help maintain 24x7 (or our best attempt at). > > You can implement standby databases by dumping the production db and > recovering the standby db. But, of course, it depends of the amounts of > data if this can be done frequently. > > > Of course, adding this feature is one more step away from the features you > > like (a fast, reliable, easy-to-administer database) and one step closer to > > an Oracle database (slower, less reliable, and harder to administer). > > I cannot see any reason why a select should be slower when archive logs > are written. Even when updating a db, archive logs can IMHO be done by > switching the WAL and keeping it instead of writing to the WAL *and* an > additional log that is to be archived.