R: How to move a DB from one server to another...

Hello,

this seems strangely to be an extremely complex task. I've tried all kinds
of backup/restore (compressed, tared, plain) with all possible options and
combination of options. I cannot seem to be able to backup a PostgreSQL
satabase on my server and restore it on another server. I always get errors
during the restore procedure.

I've even tried to zip the entire database folder, but no luck. I'm stuck
with this problem. Why is it so difficult to move a database from one server
to another? Many other RDBMS just let you copy the database file (or dir) to
the new machine and import it.

Please help, this feature is extremely important for my development.

Thank you in advance,
bye.

Federico Simonetti
Etheye

"Federico Simonetti (Etheye)" <flame(at)etheye(dot)com> writes:
> I've even tried to zip the entire database folder, but no luck. I'm stuck
> with this problem. Why is it so difficult to move a database from one server
> to another?

You can move the entire $PGDATA tree --- not parts, all of it --- to
another machine of the same architecture running the same major Postgres
release. Anything else is not going to work. It's a good idea to be
sure the postmaster is shut down while you copy the directory tree.

If you thought you were doing that, then let's see the exact error
messages you get.

regards, tom lane

Thank you Tom.

I see. But what if the need is different? Say I have four databases on my
central server and I need to copy *only one* of them to another server.

Same server version and platform: 8.0.3 on Windows.

I can't believe there's no easy way to do this. PostgreSQL appears to be an
extremely good RDBMS but if it lacks this feature it can't fit our needs
and, I guess, there are many other people in the same situation.

I've found the same question in many forums, m-lists and web sites. Never
found a concrete answer up till now. Is this in the development plans? I
really do believe if PostgreSQL wants to be an alternative to SQLServer,
Oracle or even mySQL, an easy copy/move/replicate feature is a must. You
agree?

Of course we could use SQL (CREATE DATABASE, CREATE TABLE, INSERT INTO....)
but this would take years on large databases, while a rough "copy this
folder to the new server" would take just a few seconds.

Has anybody faced/solved this issue?

Thank you in advance,

Federico Simonetti

> -----Messaggio originale-----
> Da: pgsql-hackers-win32-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-win32-owner(at)postgresql(dot)org] Per conto
> di Tom Lane
> Inviato: giovedì 23 giugno 2005 0.29
> A: Federico Simonetti (Etheye)
> Cc: pgsql-hackers-win32(at)postgresql(dot)org
> Oggetto: Re: [pgsql-hackers-win32] How to move a DB from one
> server to another...
>
> "Federico Simonetti (Etheye)" <flame(at)etheye(dot)com> writes:
> > I've even tried to zip the entire database folder, but no luck. I'm
> > stuck with this problem. Why is it so difficult to move a database
> > from one server to another?
>
> You can move the entire $PGDATA tree --- not parts, all of it
> --- to another machine of the same architecture running the
> same major Postgres release. Anything else is not going to
> work. It's a good idea to be sure the postmaster is shut
> down while you copy the directory tree.
>
> If you thought you were doing that, then let's see the exact
> error messages you get.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Federico Simonetti (Etheye) wrote:

>Thank you Tom.
>
>I see. But what if the need is different? Say I have four databases on my
>central server and I need to copy *only one* of them to another server.
>
>Same server version and platform: 8.0.3 on Windows.
>
>I can't believe there's no easy way to do this. PostgreSQL appears to be an
>extremely good RDBMS but if it lacks this feature it can't fit our needs
>and, I guess, there are many other people in the same situation.
>
>I've found the same question in many forums, m-lists and web sites. Never
>found a concrete answer up till now. Is this in the development plans? I
>really do believe if PostgreSQL wants to be an alternative to SQLServer,
>Oracle or even mySQL, an easy copy/move/replicate feature is a must. You
>agree?
>
>Of course we could use SQL (CREATE DATABASE, CREATE TABLE, INSERT INTO....)
>but this would take years on large databases, while a rough "copy this
>folder to the new server" would take just a few seconds.
>
>Has anybody faced/solved this issue?
>
>Thank you in advance,
>
>Federico Simonetti
>
>

pg_dumpall doesn't do the right thing for you?
Copy the entire directory, and then issue the commands "DROP DATABASE
blah" which you don't want on the new machine?

Use Slony (http://slony.info) to get live replication/load balancing
instead.

Or pgcluster (don't have a link offhand) to allow multi-master load
balancing.

John
=:->

Federico,

> Of course we could use SQL (CREATE DATABASE, CREATE TABLE, INSERT
> INTO....)
> but this would take years on large databases, while a rough "copy this
> folder to the new server" would take just a few seconds.
>

I do copys of databases every other day. pg_dump and pg_restore work like a
breeze on databases with around 1 gig of stuff, and I do not even use the
binary format and use intermediate files.

Where ist the performance bottleneck you are speaking of?

Harald

--
GHUM Harald Massa
persuasion python postgresql
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607

First, your mailer's habit of putting "R:" instead of the standard "Re:" at
the front of replies is annoying. You need to fix it.

Second, of course pg_restore works on Windows. The most common mistakes in
using pg_dump/pg_restore (on all platforms) are:
. not using pg_dump from the target version, and
. trying to use pg_restore rather than psql to restore a text format dump.

I suggest you try using pg_dump -F c on your Windows machine to dump the
remote db and then try pg_restore on the result to load your copy. If that
gives you trouble then tell us *precisely* what the trouble is.

cheers

andrew

Etheye said:
> Hello Harald,
>
> uhm, you may have lost my first message... I said that I am unable to
> run pg_restore on a different computer other than the one pg_dump was
> used on. I always get a return code of 1 (error). I can easily and
> quickly dump a database with pg_dump but I can't restore it on a PC
> different from the one used to back it up. And I really can't
> understand why, I have tried all possible options...
>
> I'm running PostgreSQL 8.0.3 on Win32. I've found some solutions for
> Linux but nobody was able to help me regarding the Windows platform.
>
> Thanks,
>
> Federico
>
>
>
> _____
>
> Da: Harald Armin Massa [mailto:haraldarminmassa(at)gmail(dot)com]
> Inviato: giovedì 23 giugno 2005 12.14
> A: Federico Simonetti (Etheye)
> Cc: pgsql-hackers-win32(at)postgresql(dot)org
> Oggetto: Re: R: [pgsql-hackers-win32] How to move a DB from one server
> to another...
>
>
> Federico,
>
>
> Of course we could use SQL (CREATE DATABASE, CREATE TABLE, INSERT
> INTO....) but this would take years on large databases, while a rough
> "copy this folder to the new server" would take just a few seconds.
>
>
>
> I do copys of databases every other day. pg_dump and pg_restore work
> like a breeze on databases with around 1 gig of stuff, and I do not
> even use the binary format and use intermediate files.
>
> Where ist the performance bottleneck you are speaking of?
>
> Harald
>
> --
> GHUM Harald Massa
> persuasion python postgresql
> Harald Armin Massa
> Reinsburgstraße 202b
> 70197 Stuttgart
> 0173/9409607

Hello Harald,

uhm, you may have lost my first message... I said that I am unable to run
pg_restore on a different computer other than the one pg_dump was used on. I
always get a return code of 1 (error). I can easily and quickly dump a
database with pg_dump but I can't restore it on a PC different from the one
used to back it up. And I really can't understand why, I have tried all
possible options...

I'm running PostgreSQL 8.0.3 on Win32. I've found some solutions for Linux
but nobody was able to help me regarding the Windows platform.

Thanks,

Federico

_____

Da: Harald Armin Massa [mailto:haraldarminmassa(at)gmail(dot)com]
Inviato: giovedì 23 giugno 2005 12.14
A: Federico Simonetti (Etheye)
Cc: pgsql-hackers-win32(at)postgresql(dot)org
Oggetto: Re: R: [pgsql-hackers-win32] How to move a DB from one server to
another...

Federico,

Of course we could use SQL (CREATE DATABASE, CREATE TABLE, INSERT INTO....)
but this would take years on large databases, while a rough "copy this
folder to the new server" would take just a few seconds.

I do copys of databases every other day. pg_dump and pg_restore work like a
breeze on databases with around 1 gig of stuff, and I do not even use the
binary format and use intermediate files.

Where ist the performance bottleneck you are speaking of?

Harald

--
GHUM Harald Massa
persuasion python postgresql
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607

Federico Simonetti (Etheye) wrote:
>
> uhm, you may have lost my first message... I said that I am unable to
> run pg_restore on a different computer other than the one pg_dump was
> used on. I always get a return code of 1 (error). I can easily and
> quickly dump a database with pg_dump but I can't restore it on a PC
> different from the one used to back it up. And I really can't understand
> why, I have tried all possible options...

If you can not restore a dump, you are either doing something
wrong or you have discovered a bug:
http://www.postgresql.org/docs/8.0/interactive/bug-reporting.html

Jochem

"Federico Simonetti (Etheye)" <flame(at)etheye(dot)com> writes:
> I see. But what if the need is different? Say I have four databases on my
> central server and I need to copy *only one* of them to another server.

> I can't believe there's no easy way to do this.

For that you use pg_dump.

regards, tom lane

"Federico Simonetti (Etheye)" <flame(at)etheye(dot)com> writes:
> uhm, you may have lost my first message... I said that I am unable to =
> run
> pg_restore on a different computer other than the one pg_dump was used =
> on. I
> always get a return code of 1 (error).

What is the error message, exactly?

You do realize that the default output format of pg_dump is a plain text
file that you just feed into psql? pg_restore is only used with -Fc or -Ft
output formats.

regards, tom lane