copying a database without dumping it

This link explains lot of useful techniques for backup and restore

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

On 12/15/05, Marc Brünink <mbruen@smartsoft.de> wrote:

Hi list,

I know this was asked a lot of times on this mailing list. But actually
no reply satisfied me :-)
However: I've a running postgres database. It's about 6 GB big. Now I
want to copy this database to another host. Clearly pg_dumpall comes to
the mind. But there're 2 problems:

1. The new host hasn't got an internet connection. So I've to copy the
data via DVD. Doesn't sound like a problem, eh? But it is! I've to copy
the database to 4 different hosts. So let's calculate: 4 hosts, about
30 GB of sql statements (plain), therefore about 8 DVDs = lot's of
annoying DVD changes. Which brings me to the next problem. Time.

2. All these 4 hosts have to be updated within 2-3 hours. Which is
simply impossible with a plain text export. If I remember correctly the
initial import of the data took about 3-4 hours. On a Sun 240. So I'll
took much longer on a (much slower) i386 system.

Because of these problems I tought about just cp the data folder. This
didn't work. Is there some sort of evil trick to do so? Is postgres
binary compatible? (Wouldn't be a huge problem if it's not)

Now I'l try a custom dump. Perhaps this will suffice. But I guess it's
impossible for a dump to be as fast as a cp. So if a cp would be
possible I would favour it. Oh, and did I tell: Everything have to be
done tommorow..... *sigh*

Many thanks
Marc

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

http://archives.postgresql.org

--
Regards
Pandu

Marc Br�nink schrieb:

Hi list,

...

Now I'l try a custom dump. Perhaps this will suffice. But I guess it's
impossible for a dump to be as fast as a cp. So if a cp would be
possible I would favour it. Oh, and did I tell: Everything have to be
done tommorow..... *sigh*

Actually its faster. Custom dump is the way to go
because its much more flexible then dumping plaintext.

I'd not use pg_dumpall but pg_dump for each DB in turn.
See also the various compression options or if short
on free space try uncompressed custom dump and
rar.

Since the dump only dumps DDL and Data, its much less
data then your pg_data directory currently has.

HTH
Tino Wildenhain

On Donnerstag, Dez 15, 2005, at 12:11 Europe/Berlin, Tino Wildenhain
wrote:

Marc Brünink schrieb:

Hi list,

...

Now I'l try a custom dump. Perhaps this will suffice. But I guess
it's impossible for a dump to be as fast as a cp. So if a cp would
be possible I would favour it. Oh, and did I tell: Everything have to
be done tommorow..... *sigh*

Actually its faster. Custom dump is the way to go
because its much more flexible then dumping plaintext.

[..]

Since the dump only dumps DDL and Data, its much less
data then your pg_data directory currently has.

Oh yes! It's damn fast! All hail to the postgres crew! Gosh. My dump
file is just 1.3 GB big. Impressive.
I'm looking forward for the import. Guess I'll get another surprise...

I used:
pg_dump -f shape.postgresql -F c -o -U postgres -Z 9 -v shape

One last thing:
Will I have to re-cluster my tables?

Thanks
Marc