Any reason to use pg_dumpall on an idle database

Bruce Momjian wrote:

Seems a typical file system backup is fine on an idle database, right?

A file system backup will only work on a set of databases, won't it?

Cheers,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

On Thu, 25 May 2000, Bruce Momjian wrote:

Seems a typical file system backup is fine on an idle database, right?

how do you know its idle, and/or will remain so?

On Thu, 25 May 2000, Bruce Momjian wrote:

Seems a typical file system backup is fine on an idle database, right?

how do you know its idle, and/or will remain so?

pg_ctl stop of modification of pg_hba.conf.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Thu, 25 May 2000, Bruce Momjian wrote:

On Thu, 25 May 2000, Bruce Momjian wrote:

Seems a typical file system backup is fine on an idle database, right?

how do you know its idle, and/or will remain so?

pg_ctl stop of modification of pg_hba.conf.

ack ... why would you want to? *raised eyebrow*

On Thu, 25 May 2000, Bruce Momjian wrote:

On Thu, 25 May 2000, Bruce Momjian wrote:

Seems a typical file system backup is fine on an idle database, right?

how do you know its idle, and/or will remain so?

pg_ctl stop of modification of pg_hba.conf.

ack ... why would you want to? *raised eyebrow*

Well, I am not sure. In the book, I say you can use a normal file
system backup if the database is idle, or use pg_dumpall and backup the
file it creates. In fact, once you run pg_dumpall, there is no need to
backup the /data directory except for the few configuration files like
pg_hba.conf. Does this make sense?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Thu, 25 May 2000, Bruce Momjian wrote:

On Thu, 25 May 2000, Bruce Momjian wrote:

On Thu, 25 May 2000, Bruce Momjian wrote:

Seems a typical file system backup is fine on an idle database, right?

how do you know its idle, and/or will remain so?

pg_ctl stop of modification of pg_hba.conf.

ack ... why would you want to? *raised eyebrow*

Well, I am not sure. In the book, I say you can use a normal file
system backup if the database is idle, or use pg_dumpall and backup the
file it creates. In fact, once you run pg_dumpall, there is no need to
backup the /data directory except for the few configuration files like
pg_hba.conf. Does this make sense?

when you mean 'idle', do you mean 'read-only'? else the files in
/data/base/* would be changing, no?

Well, I am not sure. In the book, I say you can use a normal file
system backup if the database is idle, or use pg_dumpall and backup the
file it creates. In fact, once you run pg_dumpall, there is no need to
backup the /data directory except for the few configuration files like
pg_hba.conf. Does this make sense?

when you mean 'idle', do you mean 'read-only'? else the files in
/data/base/* would be changing, no?

No, like everyone has gone home and nothing is happening.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Thu, 25 May 2000, Bruce Momjian wrote:

Well, I am not sure. In the book, I say you can use a normal file
system backup if the database is idle, or use pg_dumpall and backup the
file it creates. In fact, once you run pg_dumpall, there is no need to
backup the /data directory except for the few configuration files like
pg_hba.conf. Does this make sense?

when you mean 'idle', do you mean 'read-only'? else the files in
/data/base/* would be changing, no?

No, like everyone has gone home and nothing is happening.

okay, so this is used on an IntraNet where 'schedualed downtime' is an
option ... then doing a shutdown, tar, and startup makes sense ...

On Thu, 25 May 2000, Bruce Momjian wrote:

Well, I am not sure. In the book, I say you can use a normal file
system backup if the database is idle, or use pg_dumpall and backup the
file it creates. In fact, once you run pg_dumpall, there is no need to
backup the /data directory except for the few configuration files like
pg_hba.conf. Does this make sense?

when you mean 'idle', do you mean 'read-only'? else the files in
/data/base/* would be changing, no?

No, like everyone has gone home and nothing is happening.

okay, so this is used on an IntraNet where 'schedualed downtime' is an
option ... then doing a shutdown, tar, and startup makes sense ...

Yes, I was just trying to make it clear to them how a more simple backup
can happen.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Fri, 26 May 2000, Bruce Momjian wrote:

Seems a typical file system backup is fine on an idle database, right?

I think it is a good idea to backup pg_log first, then the rest.
Then you should imho be safe even if load is heavy.
No vacuum until finished of course.

Andreas

On Fri, 26 May 2000, Bruce Momjian wrote:

Seems a typical file system backup is fine on an idle database, right?

I think it is a good idea to backup pg_log first, then the rest.
Then you should imho be safe even if load is heavy.
No vacuum until finished of course.

You know, that was always my assumption too, that doing pg_log first
made things safer. I am not sure if it is 100% safe, though.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I think it is a good idea to backup pg_log first, then the rest.
Then you should imho be safe even if load is heavy.
No vacuum until finished of course.

You know, that was always my assumption too, that doing pg_log first
made things safer. I am not sure if it is 100% safe, though.

I think there is a problem with our "big" pagesize of 8k.
If we used the system page size (usually 2 or 4k) a write with a
concurrent read should imho not be possible. But since we need to write
2-4 system pages I am not so sure that that is atomic, thus we risc
backing up an incompletely written pg page.

sounds like a nogo :-(
Andreas