Обсуждение: File system level backup

pg_dump is fine, in fact better.  For a file system backup, you have to
stop the postmaster, backup, then restart it.

---------------------------------------------------------------------------

Ganesan Kanavathy wrote:
> Please help me with question. So far no replies.
>
> Regards,
> ganesh
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Ganesan Kanavathy
> Sent: Friday, October 17, 2003 5:14 PM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] File system level backup
>
> Dear all,
>
> All the while I was doing pg_dump as backup method for my pgsql db.
> Today I came across File system level backup on Postgresql
> Documentation.
>
> After reading it, I am quite unsure whether File system level backup is
> better than pg_dump.
>
> Furthermore, do we really need to do File system level backup or pg_dump
> is good enough?
>
> Any suggestion is much appreciated.
>
> Regards,
> ganesh

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Wed, 22 Oct 2003 09:12:40 +0800
"Ganesan Kanavathy" <ganesh@magnusquest.com> wrote:

> All the while I was doing pg_dump as backup method for my pgsql db.
> Today I came across File system level backup on Postgresql
> Documentation.
>

If you do a filesystem level backup the only "safe" way to do this is to
either stop postgres and then backup, or if you are using a logical
volume manager take a snapshot.    You cannot just "tar" a "live" pgdata
directory as the contents will change as tar goes through each file and
you'll be left with a corrupt database.


--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

On Wed, 22 Oct 2003, Ganesan Kanavathy wrote:

> All the while I was doing pg_dump as backup method for my pgsql db.
> Today I came across File system level backup on Postgresql
> Documentation.
>
> After reading it, I am quite unsure whether File system level backup is
> better than pg_dump.
>
> Furthermore, do we really need to do File system level backup or pg_dump
> is good enough?

The two are more complementary than equivalent.  Here's a short list of
the pros and cons of each:

Advantages of pg_dump:
* transportable between versions of postgresql / other RDBMSs
* can be run while the database is up and get a consistent snapshot
* can be run from a remote machine easily.

Disadvantages of pg_dump:
* slower than a file system backup
* may require hand editing of the dump before restoring (rare, but has
happened due to some obscure bugs.)

Advantages of file level backup:
* Very fast
* Can be used to clone a database quickly
* Is a great way to backup a database before a minor revision (i.e. 7.3.3
to 7.3.4) in case things go horribly wrong.

Disadvantages of file level backup:
* Server needs to be shut down to get a consistent backup
* file level database backups are not very portable.  I.e. if you want to
restore a file level backup, the target database must be running the same
base version of postgresql, must be on the same architecture, and must
have many of the compile time options be the same, or it won't work.

Generally, pg_dump is preferred, but file level backups have their place.

> Dear all,
>
> All the while I was doing pg_dump as backup method for my pgsql db.
> Today I came across File system level backup on Postgresql
> Documentation.
>
> After reading it, I am quite unsure whether File system level backup is
> better than pg_dump.

You have to look at your needs.

> Furthermore, do we really need to do File system level backup or pg_dump
> is good enough?

File system level backup is typically complicated to design, and is
specific to your needs.

pg_dump is nearly universally usable, but might not meet your needs, if
you are a 24x7 shop. Online backup is almost always specific to your
site, usually a big deal to set up, and rarely impossible.

> Any suggestion is much appreciated.

Without more information about what you're doing, I can't say anything
more. But most likely, pg_dump will work for you. It covers most cases.

-j

--
Jamie Lawrence                                        jal@jal.org
Helpful Hint: Unbreakable toys can be used to break other toys.