Re: Log archiving (like db2, Oracle)

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.