Moving Database between machines

Classic Servoy
1

Hi

I am looking for the easiest, i. e. most elegant way to move a Postgres database from one (Mac OS X [Server]) machine to another one.
I know the following 2 forms:

pg_dump[all]:

sudo su - postgres -c '/usr/local/pgsql-8.4/bin/pg_dumpall -o' > /Volumes/skyphos/backups/postgresql/pgbackup84.sql
sudo su - postgres -c '/usr/local/pgsql-9.0/bin/psql -d template1 -f /Volumes/skyphos/backups/postgresql/pgbackup84.sql'

I also tried to just copy the postgres data folder from one machine to the other, and, as long as they have the same processor architecture it seems to work as well, but I am not sure if it really is a trustworthy option (it’s easy though).

What is your experience? Are there any other options?

I am also interested about the experience of when using pg_dump, pg_dumpall and using them with exporting data only.

Regards,

2

Hi

I have used Navicat for MySQL which have a feature for doing exactly this, have not tried on PostgreSQL but would suspect its the same. They have a 30 day fully working trial which runs on most platforms including the Mac (http://www.navicat.com/)

HTH

Gordon

3

Hi Gordon

Thanks for the tip, will have a look a Navicat :-)
Beside this optionality I am still interested about the various possibilities doing that with the PostgreSQL toolset.

Best regards,

Gordon:
Hi

I have used Navicat for MySQL which have a feature for doing exactly this, have not tried on PostgreSQL but would suspect its the same. They have a 30 day fully working trial which runs on most platforms including the Mac (http://www.navicat.com/)

HTH

Gordon

4

Hi Robert

Robert Huber:
Thanks for the tip, will have a look a Navicat :-)
Beside this optionality I am still interested about the various possibilities doing that with the PostgreSQL toolset.

Without doubt it would be a better approach to do this within the db. We have also been using Master/Master replication on MySQL which is great if you want office and cloud access with the best performance. I found that to set it up the quickest approach was to use Navicat to create the duplicate in the first instance and then work from there with MySQL doing the replication.

I will be interested to see how you get on with the PostgreSQL toolkit approach, certainly nice to have it all in one process

Cheers
Gordon

5

Hi Gordon

I agree with you, let’s see what idea will pop-up :-)

Regards,

6

Hi Robert,

You could use the pg_dumpall/pg_dump and psql and pipe the dump straight into the psql so you don’t end up with a dumpfile.

Hope this helps.