Backup method

"Bob Powell" <Bob@hotchkiss.org> writes:

Hello everyone:

I have a systems admin that is backing up our Linux computers
(postgres) by backing up the directory structure. This of course
includes all the files that pertain to my postgres databases. I
maintain that using pgdump and creating a file of SQL commands for
restore is a better method by allowing the restore on any linux box that
is running postgress as opposed to having to reconstruct the directory
on another server.

You're not guaranteed to get a recoverable database from a filesystem
backup unless you (a) take Postgres down during the backup, or (b) use
point-in-time recovery (which is documented in the manual). So what
you're doing now isn't actually saving your data for you.

I would definitely go the pg_dump route unless you want to set up
PITR...

-Doug

Bob Powell wrote:

I have a systems admin that is backing up our Linux computers
(postgres) by backing up the directory structure. This of course
includes all the files that pertain to my postgres databases. I
maintain that using pgdump and creating a file of SQL commands for
restore is a better method

Your current admin is doing it wrong. You are completely correct on this
point.

On Wed, 2006-04-05 at 15:42 -0400, Bob Powell wrote:

I have a systems admin that is backing up our Linux computers
(postgres) by backing up the directory structure. This of course
includes all the files that pertain to my postgres databases. I
maintain that using pgdump and creating a file of SQL commands for
restore is a better method by allowing the restore on any linux box that
is running postgress as opposed to having to reconstruct the directory
on another server.

Does anyone have any thoughts on this matter. Is one way better than
the other? Thanks in advance.

If you want to do this quickly then you should use PITR. The base backup
is faster, plus you're covered if you crash between backups.

Archivelogmode is standard for Oracle/DB2 etc installations; PITR should
be your standard if you run PostgreSQL too. Here's why:

pg_dump produces portable backups, but that won't help you if you took
the backup at 04:00 and your server crashes at 14:15 - you'll still lose
*all* the transactions your business performed in the last 10+ hours.
You'll also have to explain that away to your boss and remember she/he's
the one handing out the raises at the end of the year...

PITR takes more thought, but then is the purpose of a backup to make
your life easier or to recover the data for the person paying you?

Best Regards, Simon Riggs

On Wed, 2006-05-04 at 22:29 +0100, Simon Riggs wrote:

On Wed, 2006-04-05 at 15:42 -0400, Bob Powell wrote:

I have a systems admin that is backing up our Linux computers
(postgres) by backing up the directory structure. This of course
includes all the files that pertain to my postgres databases. I
maintain that using pgdump and creating a file of SQL commands for
restore is a better method by allowing the restore on any linux box that
is running postgress as opposed to having to reconstruct the directory
on another server.

Does anyone have any thoughts on this matter. Is one way better than
the other? Thanks in advance.

If you want to do this quickly then you should use PITR. The base backup
is faster, plus you're covered if you crash between backups.

Archivelogmode is standard for Oracle/DB2 etc installations; PITR should
be your standard if you run PostgreSQL too. Here's why:

pg_dump produces portable backups, but that won't help you if you took
the backup at 04:00 and your server crashes at 14:15 - you'll still lose
*all* the transactions your business performed in the last 10+ hours.
You'll also have to explain that away to your boss and remember she/he's
the one handing out the raises at the end of the year...

PITR takes more thought, but then is the purpose of a backup to make
your life easier or to recover the data for the person paying you?

Best Regards, Simon Riggs

How do you suggest one does PITR ?

It has been a while since I read the Docs, but do not recall
any tools that allow one to do such a thing.

Guy Fraser <guy@incentre.net> writes:

How do you suggest one does PITR ?

It has been a while since I read the Docs, but do not recall
any tools that allow one to do such a thing.

PITR went in to 8.0 (IIRC); the docs for that version will cover it.

-Doug

On Thu, 2006-06-04 at 15:21 -0400, Douglas McNaught wrote:

Guy Fraser <guy@incentre.net> writes:

How do you suggest one does PITR ?

It has been a while since I read the Docs, but do not recall
any tools that allow one to do such a thing.

PITR went in to 8.0 (IIRC); the docs for that version will cover it.

Excellent.

I checked out the docs, and will definitely be planning on using
PITR.

Thanks a lot for bringing this to my attention.