By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,811 Members | 1,978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,811 IT Pros & Developers. It's quick & easy.

Backup?

P: n/a
Should one use pg_dumpall to backup the database or is it more practical
to just copy the data directory?
Regards,

BTJ
--
-----------------------------------------------------------------------------------------------
Bjørn T Johansen (BSc,MNIF)
Executive Manager
bt*@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."
-----------------------------------------------------------------------------------------------

Nov 11 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
>>>>> "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: kh***@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #2

P: n/a
Bjørn T Johansen <bt*@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #3

P: n/a
On Thu, 2003-09-04 at 15:40, Doug McNaught wrote:
Bjørn T Johansen <bt*@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. ro***********@cox.net
Jefferson, LA USA

"The UN couldn't break up a cookie fight in a Brownie meeting."
Larry Miller
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #4

P: n/a
>>>>> "RJ" == Ron Johnson <ro***********@cox.net> writes:

RJ> On Thu, 2003-09-04 at 15:40, Doug McNaught wrote:
Bjørn T Johansen <bt*@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: kh***@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

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

Nov 11 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.