Обсуждение: Any reason to use pg_dumpall on an idle database
Seems a typical file system backup is fine on an idle database, right? -- 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, Pennsylvania19026
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?
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? 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, Pennsylvania19026
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, Pennsylvania19026
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, Pennsylvania19026
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, Pennsylvania19026
> 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, Pennsylvania19026
> > 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
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