Thread: Log archiving (like db2, Oracle)

Log archiving (like db2, Oracle)

From
Holger Marzen
Date:
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


Re: Log archiving (like db2, Oracle)

From
David Griffiths
Date:
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

Re: Log archiving (like db2, Oracle)

From
Holger Marzen
Date:
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.


Re: Log archiving (like db2, Oracle)

From
David Griffiths
Date:
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.