Thread: BAcking up a Postgres Database

BAcking up a Postgres Database

From
D Johnson
Date:
Will the postgres community ever consider creating a decent backup
capability. Currently the way to create backups is through a Cron job.
For Postgres to ever be considered a true production systen then some
sort of transactional tracing has to be done. Otherwise you risk the
potential of losing quite a bit of data.

Dave Johnson


Re: BAcking up a Postgres Database

From
"Dr. Hans-Christian Waldmann"
Date:
>
> Will the postgres community ever consider creating a decent backup
> capability. Currently the way to create backups is through a Cron job.
> For Postgres to ever be considered a true production systen then some
> sort of transactional tracing has to be done. Otherwise you risk the
> potential of losing quite a bit of data.
>
> Dave Johnson
>

Who says that fancy user interfaces to backup services and schedule managers,
do any other thing than setting up a big big crontab file ?? This is just
what ADABAS or SAPDB do (and we should consider R/3-enabled dbs production
systems !?).

Hans



---------------------------------------------------------------------
PD Dr. Hans C Waldmann
Methodology & Applied Statistics in Psychology & the Health Sciences

ZFRF / University of Bremen / Grazer Str 6 / 28359 Bremen / Germany
waldmann@samson.fire.uni-bremen.de / http://samson.fire.uni-bremen.de

friend of: AIX PERL POSTGRES ADABAS SAS TEX
---------------------------------------------------------------------


Re: BAcking up a Postgres Database

From
Ragnar Kjørstad
Date:
On Wed, Jan 10, 2001 at 05:57:21AM -0600, D Johnson wrote:
> Will the postgres community ever consider creating a decent backup
> capability. Currently the way to create backups is through a Cron job.
> For Postgres to ever be considered a true production systen then some
> sort of transactional tracing has to be done. Otherwise you risk the
> potential of losing quite a bit of data.


You can take a snapshot of the database-device (while the database is
down), and backup from the snapshot to avoid this problem.

You need a volum-manager that support snapshot, but AFAIK most do.



--
Ragnar Kjørstad
BigStorage

Re: BAcking up a Postgres Database

From
Tim White
Date:
Does this provide true "point of failure" recovery?  This sounds like no
more than a cold backup,
which does not provide "point of failure" recovery.  I think the original
question is very valid.  Postgres
does not, to my knowledge, support transaction logging, which is necessary
for this style of recovery.
In Oracle, you restore the data files from a previous backup and then
re-apply the transaction (archive)
logs, a process called "rolling forward", then you can open the database
for use, and it is in the state
just prior to the failure.  I've seen some creative dialogue on this list
about writing to multiple database
instances to have a live backup, and some regarding logging each SQL
statement, but the introduction
of a transaction archiver into the engine itself would make this process
much easier and make Postgres
more attractive to sites currently using the major commercial database
packages, IMHO.

Let me know if any of this is blatantly incorrect.

Tim White

Ragnar Kjørstad wrote:

> On Wed, Jan 10, 2001 at 05:57:21AM -0600, D Johnson wrote:
> > Will the postgres community ever consider creating a decent backup
> > capability. Currently the way to create backups is through a Cron job.
> > For Postgres to ever be considered a true production systen then some
> > sort of transactional tracing has to be done. Otherwise you risk the
> > potential of losing quite a bit of data.
>
> You can take a snapshot of the database-device (while the database is
> down), and backup from the snapshot to avoid this problem.
>
> You need a volum-manager that support snapshot, but AFAIK most do.
>
> --
> Ragnar Kjørstad
> BigStorage


Re: BAcking up a Postgres Database

From
D Johnson
Date:
Yes, the first method will not be valid because you cannot just take down your
system anytime you need to do a backup, plus it's risky anytime you stop and
start a large server application that manages data. The pg_dump method would
be better.

I have worked extensively with Oracle database apps. that collect data 24/7.
This test data is highly critical and you need to have a transactional logging
system, recovery and roll forward capability as Tim indicated. We have saved
our rear ends a number times thanks to Oracles archiving.

 I was wondering about pg_dump and transactional recovery and making it
similar to Oracles "Rman" recovery app and recovery database. This would
probably make Postgres robust in that it would provide good integrity and
allow you to develop drivers for various backup devices i.e tape drives.

Dave Johnson

Tim White wrote:

> Does this provide true "point of failure" recovery?  This sounds like no
> more than a cold backup,
> which does not provide "point of failure" recovery.  I think the original
> question is very valid.  Postgres
> does not, to my knowledge, support transaction logging, which is necessary
> for this style of recovery.
> In Oracle, you restore the data files from a previous backup and then
> re-apply the transaction (archive)
> logs, a process called "rolling forward", then you can open the database
> for use, and it is in the state
> just prior to the failure.  I've seen some creative dialogue on this list
> about writing to multiple database
> instances to have a live backup, and some regarding logging each SQL
> statement, but the introduction
> of a transaction archiver into the engine itself would make this process
> much easier and make Postgres
> more attractive to sites currently using the major commercial database
> packages, IMHO.
>
> Let me know if any of this is blatantly incorrect.
>
> Tim White
>
> Ragnar Kjørstad wrote:
>
> > On Wed, Jan 10, 2001 at 05:57:21AM -0600, D Johnson wrote:
> > > Will the postgres community ever consider creating a decent backup
> > > capability. Currently the way to create backups is through a Cron job.
> > > For Postgres to ever be considered a true production systen then some
> > > sort of transactional tracing has to be done. Otherwise you risk the
> > > potential of losing quite a bit of data.
> >
> > You can take a snapshot of the database-device (while the database is
> > down), and backup from the snapshot to avoid this problem.
> >
> > You need a volum-manager that support snapshot, but AFAIK most do.
> >
> > --
> > Ragnar Kjørstad
> > BigStorage


Re: BAcking up a Postgres Database

From
Mike Castle
Date:
On Thu, Jan 11, 2001 at 08:57:26AM -0600, Tim White wrote:
> In Oracle, you restore the data files from a previous backup and then
> re-apply the transaction (archive)
> logs, a process called "rolling forward", then you can open the database
> for use, and it is in the state
> just prior to the failure.  I've seen some creative dialogue on this list

And where do you get this transaction log?

hmmmm
let me guess:

From a backup?

mrc
--
       Mike Castle       Life is like a clock:  You can work constantly
  dalgoda@ix.netcom.com  and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day.  -- mrc
    We are all of us living in the shadow of Manhattan.  -- Watchmen

Re: BAcking up a Postgres Database

From
Ragnar Kjørstad
Date:
On Thu, Jan 11, 2001 at 08:57:26AM -0600, Tim White wrote:
> Does this provide true "point of failure" recovery?  This sounds like no
> more than a cold backup,
> which does not provide "point of failure" recovery.
Yes, this is only for regular backup (but it doesn't require a long
downtime for you DB-server)



--
Ranar Kjørstad
Big Storage

Re: BAcking up a Postgres Database

From
Mike Castle
Date:
On Mon, Jan 15, 2001 at 07:06:41AM -0600, Tim White wrote:
> Yes, you do restore the archive logs from some backup medium.  But I don't
> understand you point, the discussion relates to transaction logging, meaning
> that as dml statements are executed and committed they are logged, thus making
> it possible to re-apply them.  To my knowledge Postgres does not do
> transaction logging, this makes it nearly impossible to do point of failure
> recovery without doing a pg_dump after every transaction, which is rather
> impractical.

Then perhaps it's a lack of understanding on my point.

Define "point of failure recovery."

I'm imagining something like a system crash of some sort (maybe an internal
database error and the database is corrupted somehow).

So you reinstall the latest back up.

Then you apply the transaction log.

Is that what you're saying?

Well, if the system crashed, how can you guarantee the integrity of the
transaction log in order the forward apply it?

If the database system crashed due to an internal error, and corrupted the
database files, how do you know the transaction log wasn't corrupted?

If the OS crashed, and corrupted the database, how do you know the
transaction log wasn't corrupted?

The only time I could see how you could guarantee that a transaction log
was not corrupt was if it's on a separate physical media and you had a
media issue.  Wish can largely be overcome with using something like RAID
anyway.

Can you give me a scenario where you a transaction log is actually useful
and you can guarantee it is not corrupt?

mrc
--
       Mike Castle       Life is like a clock:  You can work constantly
  dalgoda@ix.netcom.com  and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day.  -- mrc
    We are all of us living in the shadow of Manhattan.  -- Watchmen

Re: BAcking up a Postgres Database

From
Tim White
Date:
Yes, you do restore the archive logs from some backup medium.  But I don't
understand you point, the discussion relates to transaction logging, meaning
that as dml statements are executed and committed they are logged, thus making
it possible to re-apply them.  To my knowledge Postgres does not do
transaction logging, this makes it nearly impossible to do point of failure
recovery without doing a pg_dump after every transaction, which is rather
impractical.

Tim White

Mike Castle wrote:

> On Thu, Jan 11, 2001 at 08:57:26AM -0600, Tim White wrote:
> > In Oracle, you restore the data files from a previous backup and then
> > re-apply the transaction (archive)
> > logs, a process called "rolling forward", then you can open the database
> > for use, and it is in the state
> > just prior to the failure.  I've seen some creative dialogue on this list
>
> And where do you get this transaction log?
>
> hmmmm
> let me guess:
>
> >From a backup?
>
> mrc
> --
>        Mike Castle       Life is like a clock:  You can work constantly
>   dalgoda@ix.netcom.com  and be right all the time, or not work at all
> www.netcom.com/~dalgoda/ and be right at least twice a day.  -- mrc
>     We are all of us living in the shadow of Manhattan.  -- Watchmen