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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Federico Simonetti (Etheye)
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
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
"Federico Simonetti (Etheye)" <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

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

Federico Simonetti (Etheye)
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: [hidden email]
> [mailto:[hidden email]] Per conto
> di Tom Lane
> Inviato: giovedì 23 giugno 2005 0.29
> A: Federico Simonetti (Etheye)
> Cc: [hidden email]
> Oggetto: Re: [pgsql-hackers-win32] How to move a DB from one
> server to another...
>
> "Federico Simonetti (Etheye)" <[hidden email]> 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



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: R: How to move a DB from one server to

John Arbash Meinel
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
=:->


signature.asc (261 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Harald Armin Massa[legacy]
In reply to this post by Federico Simonetti (Etheye)
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
Reply | Threaded
Open this post in threaded view
|

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

Federico Simonetti (Etheye)
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:[hidden email]]
Inviato: giovedì 23 giugno 2005 12.14
A: Federico Simonetti (Etheye)
Cc: [hidden email]
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
Reply | Threaded
Open this post in threaded view
|

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

Andrew Dunstan

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:[hidden email]]
> Inviato: giovedì 23 giugno 2005 12.14
> A: Federico Simonetti (Etheye)
> Cc: [hidden email]
> 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




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: R: R: How to move a DB from one server

Jochem van Dieten-2
In reply to this post by Federico Simonetti (Etheye)
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: R: R: How to move a DB from one server

Obe, Regina
Just a thought.  When you are restoring, are you restoring using the same
encoding as the database you dumped.  I recall having similar problems and
it was because my dumped database was in SQL ASCII and I had mistakenly
created my new database using UNICODE.

Also I think I had an issue if I was using a pg_restore older than the
version that came with the pg version I used dump.


-----Original Message-----
From: Jochem van Dieten [mailto:[hidden email]]
Sent: Thursday, June 23, 2005 8:25 AM
To: Federico Simonetti (Etheye)
Cc: [hidden email]
Subject: Re: R: R: [pgsql-hackers-win32] How to move a DB from one server


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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by Federico Simonetti (Etheye)
"Federico Simonetti (Etheye)" <[hidden email]> 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

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

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by Federico Simonetti (Etheye)
"Federico Simonetti (Etheye)" <[hidden email]> 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

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