Обсуждение: database migration question between different ubuntus and different postgresql server versions

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@aklaver.com

On Thu, Apr 16, 2015 at 02:38:56PM -0700, Adrian Klaver wrote:
> Well it is an upgrade from one major version to another, so you have the
> following options using Postgres core utilities:

And, if you don't want to use core utilities, you can use one of the
trigger-based replication systems to move all the data from one to the
other.  Depending on your uptime requirements and the size of the
database, this approach can either be a life saver or a total waste of
time and will to live.  More often the latter, please be aware.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On Thu, Apr 16, 2015 at 06:14:20PM -0400, Octavi Fors wrote:
> at first glance, option 1) seems to me simpler. But does it guarantee
> server version upgrade compatibility?

Yes.  Use the pg_dump from the later postgres, which can read old
versions and generate any output needed for the new version.  It's
just like any other pg_dump otherwise.

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

Usually pg_dump [connection options] databasename | psql [connection
options] databasename

For instance, if you wanted from the new machine to dump egdb from the
old machine and restore locally, you could do

pg_dump -U postgres -h 192.0.2.1 -C egdb | psql -U postgres

I recommend reading the pg_dump (and if you like, pg_dumpall) manuals
before proceeding.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On 04/16/2015 03:14 PM, Octavi Fors wrote:
> 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?

See Andrews answer.

There is the matter of the tablespace.

It was not clear in your first post what you plan to do with the tablespace?

Are you going to keep it on the NAS or put it somewhere else?

>
> Thanks a lot,
>
> Octavi.
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com

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@aklaver.com