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

[Octavi Fors <octavi@xxxxxxxxxxxx>]

Hi Adrian,

at first glance, option 1) seems to me simpler. But does it guarantee server version upgrade compatibility?

Could you/someone please provide an example of commands which I could use?

Thanks a lot,

Octavi.

On Thu, Apr 16, 2015 at 5:38 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 04/16/2015 02:01 PM, Octavi Fors wrote:

Dear all,

I have one newbie question which I hope one kind soul of this list can
help me.

The situation is that I have two postgresql servers:

   -9.2 running on Ubuntu 12.04 with a database 'db' already created and
populated with data,

   -9.3 running on Ubuntu 14.04.02 with no database created (no tables,
no data),

I want to migrate the database 'db' (tablespace+tables+data) from
computer with 9.2 server to computer with 9.3 server.

Note: database 'db' is living on a NAS mounted via NFS, with a
tablespace modification I was given in this list last week:

CREATE TABLESPACE onNAS LOCATION '/NAS-box/pgsql-data';
CREATE DATABASE db TABLESPACE onNAS;


I would appreciate if you could provide commands I can type to make me
start with the process.

Well it is an upgrade from one major version to another, so you have the following options using Postgres core utilities:

1) Do a dump and restore:
http://www.postgresql.org/docs/9.3/interactive/app-pgdump.html
http://www.postgresql.org/docs/9.3/interactive/app-pgrestore.html

2) Use pg_upgrade:
http://www.postgresql.org/docs/9.3/interactive/pgupgrade.html

Thanks in advance,

Octavi Fors.

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx