backups

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C67449.2C25D7B0
Content-Type: text/plain;
charset="iso-8859-1"

Greetings,

Does the pg_dump command 'backup' the pg_xlog files?

Thank you,
Tim

------_=_NextPart_001_01C67449.2C25D7B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">

<meta name=3DProgId content=3DWord.Document>
<meta name=3DGenerator content=3D"Microsoft Word 9">
<meta name=3DOriginator content=3D"Microsoft Word 9">
<link rel=3DFile-List href=3D"cid:filelist.xml [at] 01C67428.8709D440">
<title>dbsize & pg_dump</title>

<style>

</style>
</head>

<body lang=3DEN-US style=3D'tab-interval:.5in'>

<div class=3DSection1>

Greetings,

<=
![if =
!supportEmptyParas]> <![endif]><o:p></o:p>=

D=
oes the
pg_dump command ‘backup’ the pg_xlog =
files?<o:p></o:p>

<=
![if =
!supportEmptyParas]> <![endif]><o:p></o:p>=

T=
hank you,<o:p></o:p>

T=
im<o:p></o:p>

<=
![if =
!supportEmptyParas]> <![endif]><o:p></o:p>=

</div>

</body>

</html>

------_=_NextPart_001_01C67449.2C25D7B0--

tim.mcelroy [ Mi, 10 Mai 2006 17:49 ] [ ID #1309302 ]

Re: backups

On Wed, 2006-05-10 at 10:49, mcelroy, tim wrote:
> Greetings,
>
>
>
> Does the pg_dump command =E2=80=98backup=E2=80=99 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?

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Scott Marlowe [ Mi, 10 Mai 2006 17:57 ] [ ID #1309303 ]

Re: backups

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C6744A.478576AE
Content-Type: text/plain;
charset="UTF-8"

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 [at] g2switchworks.com]
Sent: Wednesday, May 10, 2006 11:57 AM
To: mcelroy, tim
Cc: 'pgsql-admin [at] 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?

------_=_NextPart_001_01C6744A.478576AE
Content-Type: text/html;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3DUTF-8">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2657.73">
<TITLE>RE: [ADMIN] backups</TITLE>
</HEAD>
<BODY>

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 [<A =
HREF=3D"mailto:smarlowe [at] g2switchworks.com">mailto:smarlowe [at] g 2switchworks=
..com</A>]
 Sent: Wednesday, May 10, 2006 11:57 AM
 To: mcelroy, tim
 Cc: 'pgsql-admin [at] 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?


</BODY>
</HTML>
------_=_NextPart_001_01C6744A.478576AE--

tim.mcelroy [ Mi, 10 Mai 2006 17:56 ] [ ID #1309304 ]

Re: 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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Scott Marlowe [ Mi, 10 Mai 2006 18:11 ] [ ID #1309305 ]

Re: backups

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C67452.65F8F996
Content-Type: text/plain;
charset="iso-8859-1"

Great, exactly the answer I was looking for. Thank you again Scott.

Tim

-----Original Message-----
From: Scott Marlowe [mailto:smarlowe [at] g2switchworks.com]
Sent: Wednesday, May 10, 2006 12:12 PM
To: mcelroy, tim
Cc: 'pgsql-admin [at] 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.

------_=_NextPart_001_01C67452.65F8F996
Content-Type: text/html;
charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2657.73">
<TITLE>RE: [ADMIN] backups</TITLE>
</HEAD>
<BODY>

Great, exactly the answer I was looking for. Thank you again Scott.


Tim

 

 -----Original Message-----
 From: Scott Marlowe [<A HREF="mailto:smarlowe [at] g2switchworks.com">mailto:smarlowe [at] g2switchworks.com</A>] 
 Sent: Wednesday, May 10, 2006 12:12 PM
 To: mcelroy, tim
 Cc: 'pgsql-admin [at] 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.


</BODY>
</HTML>
------_=_NextPart_001_01C67452.65F8F996--

tim.mcelroy [ Mi, 10 Mai 2006 18:54 ] [ ID #1309307 ]

Re: backups

On Wed, May 10, 2006 at 11:56:20 -0400,
"mcelroy, tim" <tim.mcelroy [at] 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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo [at] postgresql.org so that your
message can get through to the mailing list cleanly

Bruno Wolff III [ Mi, 10 Mai 2006 21:11 ] [ ID #1309309 ]

Re: backups

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C67465.83111DB6
Content-Type: text/plain;
charset="iso-8859-1"

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 [at] wolff.to]
Sent: Wednesday, May 10, 2006 3:12 PM
To: mcelroy, tim
Cc: 'Scott Marlowe'; 'pgsql-admin [at] postgresql.org'
Subject: Re: backups

On Wed, May 10, 2006 at 11:56:20 -0400,
"mcelroy, tim" <tim.mcelroy [at] 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.

------_=_NextPart_001_01C67465.83111DB6
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2657.73">
<TITLE>RE: backups</TITLE>
</HEAD>
<BODY>

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 [<A =
HREF=3D"mailto:bruno [at] wolff.to">mailto:bruno [at] wolff.to</A>] 
 Sent: Wednesday, May 10, 2006 3:12 =
PM
 To: mcelroy, tim
 Cc: 'Scott Marlowe'; =
'pgsql-admin [at] postgresql.org'
 Subject: =
Re: backups


On Wed, May 10, 2006 at 11:56:20 -0400,
 "mcelroy, tim" =
<tim.mcelroy [at] 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.


</BODY>
</HTML>
------_=_NextPart_001_01C67465.83111DB6--

tim.mcelroy [ Mi, 10 Mai 2006 21:11 ] [ ID #1309310 ]

Re: 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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Scott Marlowe [ Mi, 10 Mai 2006 21:35 ] [ ID #1309311 ]

Re: backups

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C67469.D976D1CE
Content-Type: text/plain;
charset="iso-8859-1"

Thanks Scott, great idea. Does one use pg_restore to restore the files that
pg_dumpall creates?

-----Original Message-----
From: Scott Marlowe [mailto:smarlowe [at] g2switchworks.com]
Sent: Wednesday, May 10, 2006 3:35 PM
To: mcelroy, tim
Cc: 'Bruno Wolff III'; 'pgsql-admin [at] 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.

------_=_NextPart_001_01C67469.D976D1CE
Content-Type: text/html;
charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2657.73">
<TITLE>RE: [ADMIN] backups</TITLE>
</HEAD>
<BODY>

Thanks Scott, great idea. Does one use pg_restore to restore the files that pg_dumpall creates?

 

 -----Original Message-----
 From: Scott Marlowe [<A HREF="mailto:smarlowe [at] g2switchworks.com">mailto:smarlowe [at] g2switchworks.com</A>] 
 Sent: Wednesday, May 10, 2006 3:35 PM
 To: mcelroy, tim
 Cc: 'Bruno Wolff III'; 'pgsql-admin [at] 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.


</BODY>
</HTML>
------_=_NextPart_001_01C67469.D976D1CE--

tim.mcelroy [ Mi, 10 Mai 2006 21:43 ] [ ID #1309314 ]

Re: backups

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 [at] g2switchworks.com]
> Sent: Wednesday, May 10, 2006 3:35 PM
> To: mcelroy, tim
> Cc: 'Bruno Wolff III'; 'pgsql-admin [at] 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/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Joshua Drake [ Mi, 10 Mai 2006 22:14 ] [ ID #1309316 ]

Re: Backups

Nilesh Govindarajan wrote:

> I want a script to backup all databases table by table in different
> .sql files.

My first question would be whether they all need to be from the same
point in time? Is it a requirement that if they were all restored
they would provide a database with referential integrity, etc.? Or
is it OK if each table represents a different moment in time?

Either way, you probably want pg_dump. If you don't care about
having these separate files to collectively provide a consistent
image of a moment in time, or you can prevent any modifications while
you are running a set of backups, you could use pg_dump with the -t
option. Otherwise you probably need to use pg_dump (or pg_dumpall)
and parse the output to split it into the required files. Or you
might be able to do pg_dump to the custom format and extract each
table into a separate file.

> Also since it is a script, there has to be some role having read
> access to all tables on all databases. How to grant that ?

If you don't want to use the database superuser and don't already
have some other role with the appropriate rights, you will have to
grant them. It's hard to provide details without more information
about what's already there.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Kevin Grittner [ Sa, 20 Februar 2010 18:44 ] [ ID #2032948 ]

Re: Backups

On 02/20/2010 11:14 PM, Kevin Grittner wrote:
> Nilesh Govindarajan wrote:
>
>> I want a script to backup all databases table by table in different
>> .sql files.
>
> My first question would be whether they all need to be from the same
> point in time? Is it a requirement that if they were all restored
> they would provide a database with referential integrity, etc.? Or
> is it OK if each table represents a different moment in time?
>
> Either way, you probably want pg_dump. If you don't care about
> having these separate files to collectively provide a consistent
> image of a moment in time, or you can prevent any modifications while
> you are running a set of backups, you could use pg_dump with the -t
> option. Otherwise you probably need to use pg_dump (or pg_dumpall)
> and parse the output to split it into the required files. Or you
> might be able to do pg_dump to the custom format and extract each
> table into a separate file.
>
>> Also since it is a script, there has to be some role having read
>> access to all tables on all databases. How to grant that ?
>
> If you don't want to use the database superuser and don't already
> have some other role with the appropriate rights, you will have to
> grant them. It's hard to provide details without more information
> about what's already there.
>
> -Kevin

That is what exactly. I can prevent the changes to the database when the
backup is going on, but how do I grant the permissions ? I don't want to
key in the password for the superuser in the script.

--
Nilesh Govindarajan
Site & Server Adminstrator
www.itech7.com

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Nilesh Govindrajan [ So, 21 Februar 2010 02:59 ] [ ID #2032955 ]

Re: Backups

Nilesh Govindarajan wrote:

> I can prevent the changes to the database when the
> backup is going on, but how do I grant the permissions ? I don't
> want to key in the password for the superuser in the script.

There are so many ways to deal with that that it's hard to make a
suggestion without knowing more. You could just use CREATE USER and
GRANT to set things up, you could use trust authentication in the
pg_hba.conf to grant a database superuser permission from a secure
machine, or you could use a pgpass file. (Check the documentation
for details.) If I got creative I could probably come up with more,
but I'm shooting in the dark here.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Kevin Grittner [ So, 21 Februar 2010 03:06 ] [ ID #2032957 ]

Datenbanken » gmane.comp.db.postgresql.admin » backups

Vorheriges Thema: Backups
Nächstes Thema: upgrading, but learned about pg_dumpall too late