Search Postgresql Archives

Re: database migration question between different ubuntus and different postgresql server versions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 04/16/2015 05:52 PM, Octavi Fors wrote:
Hi Adrian,

I didn't received any answer from Andrews.

Yes, sorry I didn't describe completely my migration plan.
Right now the database 'db' is in NAS1 mounted via nfs with computer 1
(running ubuntu 12.04 postgresql 9.2).
I want to migrate 'db' to a faster NAS2 mounted via nfs with computer 2
(running ubuntu 14.04 postgresql 9.3).

Any commands/help to migrate 'db' from comp1+NAS1 to comp2+NAS2 would be
most than welcome.

So to be clear you only want to move the one database 'db', not the whole Postgres database cluster?

Is so and it where me I would, with proper user, host, etc parameters:

<NOTE> This is an outline only, consider it untested </NOTE>

1) pg_dumpall --globals-only -f pg_globals.sql
This dumps the cluster roles and tablespaces
Open file and modify tablespace location.

Load the globals into new cluster
psql -d postgres -f pg_globals.sql

2) pg_dump -Fc db -f db.out
This gets you a compressed form of the dump.

3)
If you want to test first without the data do:

pg_restore -d postgres -C -s  db.out

This connects to the system database postgres and the CREATES the 'db' database with only the schema(tables,functions,etc) defintions. You can verify that the schema layout is how you want it. You can add --clean to the above to DROP objects(including the database) each time you run the command.

If it is then you could do:

pg_restore -d db -a  db.out

This dumps the data only.

FYI, you can also add the -s and -a switches to the pg_dump command to make separate schema and data files from the start, if you want.



Cheers,

Octavi.



--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux