Fast backup/restore

Gandald,

have a look at

http://momjian.us/main/writings/pgsql/administration.pdf

page 44ff

There are descriptions how to do database-backups at the speed of raw file
system operations.

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

On Mon, 2006-10-16 at 16:29 +0530, Gandalf wrote:

I am looking for a *fast* backup/restore tools for Postgres. I've
found the current used tools pg_dump and pg_restore to be very slow on
large databases (~30-40GB). Restore takes time in the tune of 6 hrs on
a Linux, 4 proc, 32 G RAM machine which is not acceptable.

I am using "pg_dump -Fc" to take backup. I understand binary
compression adds to the time, but there are other databases (like DB2)
which take much less time on similar data sizes.

Are there faster tools available?

http://www.postgresql.org/docs/8.1/static/backup-online.html

With that backup system, you can backup with normal filesystem-level
tools (e.g. tar) while the database is online.

Make sure to backup the remaining active WAL segments. Those are
necessary for the backup to be complete. This step will be done
automatically in 8.2.

If your filesystem has snapshot capability, you have nothing to worry
about. Just snapshot the fs and backup the data directory plus any WAL
segments and tablespaces.

Regards,
Jeff Davis

We just tar/gzip the entire data directory. It takes all of 20 sec. We've
successfully restored from that also. The machine you are restoring to
*must* be running the save version of postgresql you backed up from.

Matthew Engel

Jeff Davis <pgsql@j-davis.com>
Sent by: pgsql-general-owner@postgresql.org
10/16/2006 02:35 PM

To
Gandalf <gandalf.me@gmail.com>
cc
pgsql-general@postgresql.org
Subject
Re: [GENERAL] Fast backup/restore

On Mon, 2006-10-16 at 16:29 +0530, Gandalf wrote:

I am looking for a *fast* backup/restore tools for Postgres. I've
found the current used tools pg_dump and pg_restore to be very slow on
large databases (~30-40GB). Restore takes time in the tune of 6 hrs on
a Linux, 4 proc, 32 G RAM machine which is not acceptable.

I am using "pg_dump -Fc" to take backup. I understand binary
compression adds to the time, but there are other databases (like DB2)
which take much less time on similar data sizes.

Are there faster tools available?

http://www.postgresql.org/docs/8.1/static/backup-online.html

With that backup system, you can backup with normal filesystem-level
tools (e.g. tar) while the database is online.

Make sure to backup the remaining active WAL segments. Those are
necessary for the backup to be complete. This step will be done
automatically in 8.2.

If your filesystem has snapshot capability, you have nothing to worry
about. Just snapshot the fs and backup the data directory plus any WAL
segments and tablespaces.

Regards,
Jeff Davis

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

mengel@notes.cc.sunysb.edu writes:

We just tar/gzip the entire data directory. It takes all of 20 sec. We've
successfully restored from that also.

You've been very lucky ... unless you stopped the postmaster while
taking the backup. Without that, this method WILL screw you someday.

(But as long as you're willing to stop the postmaster, it's a perfectly
reasonable option.)

regards, tom lane

On Oct 17, 2006, at 10:43 AM, mengel@notes.cc.sunysb.edu wrote:

We just tar/gzip the entire data directory. It takes all of 20
sec. We've successfully restored from that also. The machine you
are restoring to *must* be running the save version of postgresql
you backed up from.

If you successfully backed up in 20 seconds, then you have a tiny
DB. Also, if you successfully restored from that style backup, your
DB must not be written to much, or you were extremely lucky to get a
consistent state.

Vivek,

What methods of backup do you recommend for medium to large databases? In
our example, we have a 20GB database and it takes 2 hrs to load from a
pg_dump file.

Thanks.

Steve Poe

On Oct 17, 2006, at 2:35 PM, Steve Poe wrote:

Vivek,

What methods of backup do you recommend for medium to large
databases? In our example, we have a 20GB database and it takes 2
hrs to load from a pg_dump file.

my largest db is about 60Gb with indexes. reloading the data (about
30Gb) takes 1 hour from compressed format pg_dump, and another two to
reindex.

for increasing reload size, bump your checkpoint_segments to
something big, like 128 or 256 depending on how much disk space you
can spare for it.

On Tue, Oct 17, 2006 at 02:43:28PM -0400, Vivek Khera wrote:

On Oct 17, 2006, at 2:35 PM, Steve Poe wrote:

Vivek,

What methods of backup do you recommend for medium to large
databases? In our example, we have a 20GB database and it takes 2
hrs to load from a pg_dump file.

my largest db is about 60Gb with indexes. reloading the data (about
30Gb) takes 1 hour from compressed format pg_dump, and another two to
reindex.

for increasing reload size, bump your checkpoint_segments to
something big, like 128 or 256 depending on how much disk space you
can spare for it.

Other hints for restoring from pg_dump:

also increase wal_buffers
fsync=off
set maintenance_work_mem as high as you can (note that values over 1G
generally don't work).
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)