Обсуждение: Backup?
Should one use pg_dumpall to backup the database or is it more practical to just copy the data directory?
Regards,
BTJ
Regards,
BTJ
-- ----------------------------------------------------------------------------------------------- Bjørn T Johansen (BSc,MNIF) Executive Manager btj@havleik.no Havleik Consulting Phone : +47 67 54 15 17 Conradisvei 4 Fax : +47 67 54 13 91 N-1338 Sandvika Cellular : +47 926 93 298 http://www.havleik.no ----------------------------------------------------------------------------------------------- "The stickers on the side of the box said "Supported Platforms: Windows 98, Windows NT 4.0, Windows 2000 or better", so clearly Linux was a supported platform." ----------------------------------------------------------------------------------------------- |
>>>>> "BTJ" == Bjørn T Johansen <Bj> writes: BTJ> Should one use pg_dumpall to backup the database or is it more yes, or pg_dump specific databases. BTJ> practical to just copy the data directory? this would do you no good, since the files are not necessarily in a consistent state when you copy many of them. ie, the copy is non-atomic, and there is no guarantee that all data is flushed to disk. i don't even do a file system dump of my PG data partition because of this. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
Bjørn T Johansen <btj@havleik.no> writes: > Should one use pg_dumpall to backup the database or is it more practical > to just copy the data directory? The data directory will not be consistent unless the server is stopped. pg_dumpall works well, produces a consistent backup and is easily portable to a different machine architecture if need be. -Doug
On Thu, 2003-09-04 at 15:40, Doug McNaught wrote: > Bjørn T Johansen <btj@havleik.no> writes: > > > Should one use pg_dumpall to backup the database or is it more practical > > to just copy the data directory? > > The data directory will not be consistent unless the server is > stopped. pg_dumpall works well, produces a consistent backup and is > easily portable to a different machine architecture if need be. And it's smaller than the data/ directory, especially when "-F c" option is used. -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA "The UN couldn't break up a cookie fight in a Brownie meeting." Larry Miller
>>>>> "RJ" == Ron Johnson <ron.l.johnson@cox.net> writes: RJ> On Thu, 2003-09-04 at 15:40, Doug McNaught wrote: >> Bjørn T Johansen <btj@havleik.no> writes: >> >> > Should one use pg_dumpall to backup the database or is it more practical >> > to just copy the data directory? >> >> The data directory will not be consistent unless the server is >> stopped. pg_dumpall works well, produces a consistent backup and is >> easily portable to a different machine architecture if need be. RJ> And it's smaller than the data/ directory, especially when "-F c" RJ> option is used. That and indexes are represented as a single "CREATE INDEX" statement, rather than possibly gigabytes of data ;-) I had one index which I just realized was redundant to another and deleting it saved me ~1Gb on disk. For the curious, the indexes were like this: PRIMARY KEY (a,b); INDEX a ON mytable (a); The query planner shows slightly longer plans with just the PK, but the cost savings on mass inserts which happen often offset this immensely, not to mention 1Gb of disk which never needs to be read into the buffers ;-) -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/