Обсуждение: 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@etheye.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@postgresql.org > [mailto:pgsql-hackers-win32-owner@postgresql.org] Per conto > di Tom Lane > Inviato: giovedì 23 giugno 2005 0.29 > A: Federico Simonetti (Etheye) > Cc: pgsql-hackers-win32@postgresql.org > Oggetto: Re: [pgsql-hackers-win32] How to move a DB from one > server to another... > > "Federico Simonetti (Etheye)" <flame@etheye.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
"Federico Simonetti (Etheye)" <flame@etheye.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