Thread: Backup method
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. Does anyone have any thoughts on this matter. Is one way better than the other? Thanks in advance. Bob Powell Database Administrator
"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.