Thread: backups
Greetings,
Does the pg_dump command ‘backup’ the pg_xlog files?
Thank you,
Tim
On Wed, 2006-05-10 at 10:49, mcelroy, tim wrote: > Greetings, > > > > Does the pg_dump command ‘backup’ the pg_xlog files? No. Those are internal files used by the database to maintain transactional integrity. The pg_dump command backs up the database as snapshotted at a specific point of time. What are you trying to accomplish?
Thanks for the quick answer Scott. I thought so but wanted to double-check. I was asked by the person who performs our system backups if just backing up the directory where I put the nightly backups (created with pg_dump) was enough. I also have them backing up /var/lib/pgsql and everything under that and they're complaining that it's taking too long. I would think we would also want to back up the pg_xlog directory though. Or is that overkill? Is there a standard in the PG community I should follow? Is just performing the pg_dump and saving that to tape enough? I just don't want to get bit in the butt if I tell him to not back something up that should be backed up.
Tim
-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Wednesday, May 10, 2006 11:57 AM
To: mcelroy, tim
Cc: 'pgsql-admin@postgresql.org'
Subject: Re: [ADMIN] backups
On Wed, 2006-05-10 at 10:49, mcelroy, tim wrote:
> Greetings,
>
>
>
> Does the pg_dump command 'backup' the pg_xlog files?
No. Those are internal files used by the database to maintain
transactional integrity. The pg_dump command backs up the database as
snapshotted at a specific point of time.
What are you trying to accomplish?
On Wed, 2006-05-10 at 10:56, mcelroy, tim wrote: > Thanks for the quick answer Scott. I thought so but wanted to > double-check. I was asked by the person who performs our system > backups if just backing up the directory where I put the nightly > backups (created with pg_dump) was enough. I also have them backing > up /var/lib/pgsql and everything under that and they're complaining > that it's taking too long. I would think we would also want to back > up the pg_xlog directory though. Or is that overkill? Is there a > standard in the PG community I should follow? Is just performing the > pg_dump and saving that to tape enough? I just don't want to get bit > in the butt if I tell him to not back something up that should be > backed up. Generally speaking, backing up with pg_dump is plenty. The /var/lib/pgsql directory backups aren't necessarily useful since the files may not be backed up in the same "state" i.e. some transaction might be in two places at once, partially written, etc... pg_dump doesn't not have this problem. If you want something more up to the second, look for point in time replication in the postgresql docs.
Great, exactly the answer I was looking for. Thank you again Scott.
Tim
-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Wednesday, May 10, 2006 12:12 PM
To: mcelroy, tim
Cc: 'pgsql-admin@postgresql.org'
Subject: RE: [ADMIN] backups
On Wed, 2006-05-10 at 10:56, mcelroy, tim wrote:
> Thanks for the quick answer Scott. I thought so but wanted to
> double-check. I was asked by the person who performs our system
> backups if just backing up the directory where I put the nightly
> backups (created with pg_dump) was enough. I also have them backing
> up /var/lib/pgsql and everything under that and they're complaining
> that it's taking too long. I would think we would also want to back
> up the pg_xlog directory though. Or is that overkill? Is there a
> standard in the PG community I should follow? Is just performing the
> pg_dump and saving that to tape enough? I just don't want to get bit
> in the butt if I tell him to not back something up that should be
> backed up.
Generally speaking, backing up with pg_dump is plenty.
The /var/lib/pgsql directory backups aren't necessarily useful since the
files may not be backed up in the same "state" i.e. some transaction
might be in two places at once, partially written, etc... pg_dump
doesn't not have this problem.
If you want something more up to the second, look for point in time
replication in the postgresql docs.
On Wed, May 10, 2006 at 11:56:20 -0400, "mcelroy, tim" <tim.mcelroy@bostonstock.com> wrote: > Thanks for the quick answer Scott. I thought so but wanted to double-check. > I was asked by the person who performs our system backups if just backing up > the directory where I put the nightly backups (created with pg_dump) was > enough. I also have them backing up /var/lib/pgsql and everything under > that and they're complaining that it's taking too long. I would think we > would also want to back up the pg_xlog directory though. Or is that > overkill? Is there a standard in the PG community I should follow? Is just > performing the pg_dump and saving that to tape enough? I just don't want to > get bit in the butt if I tell him to not back something up that should be > backed up. You might want to make sure you have the configuration files backed up as well.
Excellent point Bruno, thanks. I must admit that I did think of that and have already copied all my .conf files to the backup directory. I should have noted that earlier.
Thanks,
Tim
-----Original Message-----
From: Bruno Wolff III [mailto:bruno@wolff.to]
Sent: Wednesday, May 10, 2006 3:12 PM
To: mcelroy, tim
Cc: 'Scott Marlowe'; 'pgsql-admin@postgresql.org'
Subject: Re: backups
On Wed, May 10, 2006 at 11:56:20 -0400,
"mcelroy, tim" <tim.mcelroy@bostonstock.com> wrote:
> Thanks for the quick answer Scott. I thought so but wanted to double-check.
> I was asked by the person who performs our system backups if just backing up
> the directory where I put the nightly backups (created with pg_dump) was
> enough. I also have them backing up /var/lib/pgsql and everything under
> that and they're complaining that it's taking too long. I would think we
> would also want to back up the pg_xlog directory though. Or is that
> overkill? Is there a standard in the PG community I should follow? Is just
> performing the pg_dump and saving that to tape enough? I just don't want to
> get bit in the butt if I tell him to not back something up that should be
> backed up.
You might want to make sure you have the configuration files backed up as well.
On Wed, 2006-05-10 at 14:11, mcelroy, tim wrote: > Excellent point Bruno, thanks. I must admit that I did think of that > and have already copied all my .conf files to the backup directory. I > should have noted that earlier. > > Thanks, > Tim Another thing to check is that your global data (i.e. user accounts) are being backed up. pg_dumpall does this, but individual pg_dumps do not. In our prod environment we run a backup of those accounts with 'pg_dumpall -g' every night as well.
Thanks Scott, great idea. Does one use pg_restore to restore the files that pg_dumpall creates?
-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Wednesday, May 10, 2006 3:35 PM
To: mcelroy, tim
Cc: 'Bruno Wolff III'; 'pgsql-admin@postgresql.org'
Subject: Re: [ADMIN] backups
On Wed, 2006-05-10 at 14:11, mcelroy, tim wrote:
> Excellent point Bruno, thanks. I must admit that I did think of that
> and have already copied all my .conf files to the backup directory. I
> should have noted that earlier.
>
> Thanks,
> Tim
Another thing to check is that your global data (i.e. user accounts) are
being backed up. pg_dumpall does this, but individual pg_dumps do not.
In our prod environment we run a backup of those accounts with
'pg_dumpall -g' every night as well.
mcelroy, tim wrote:
> Thanks Scott, great idea. Does one use pg_restore to restore the files
> that pg_dumpall creates?
No, oddly you use psql.
Sincerely,
Joshua D. Drake
>
>
> -----Original Message-----
> From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
> Sent: Wednesday, May 10, 2006 3:35 PM
> To: mcelroy, tim
> Cc: 'Bruno Wolff III'; 'pgsql-admin@postgresql.org'
> Subject: Re: [ADMIN] backups
>
> On Wed, 2006-05-10 at 14:11, mcelroy, tim wrote:
> > Excellent point Bruno, thanks. I must admit that I did think of that
> > and have already copied all my .conf files to the backup directory. I
> > should have noted that earlier.
> >
> > Thanks,
> > Tim
>
> Another thing to check is that your global data (i.e. user accounts) are
> being backed up. pg_dumpall does this, but individual pg_dumps do not.
>
> In our prod environment we run a backup of those accounts with
> 'pg_dumpall -g' every night as well.
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/