Re: [GENERAL] database migration question between different ubuntus and different postgresql server versions
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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] database migration question between different ubuntus and different postgresql server versions
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 a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] database migration question between different ubuntus and different postgresql server versions
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 a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] database migration question between different ubuntus and different postgresql server versions
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. Cheers, Octavi. On Thu, Apr 16, 2015 at 8:35 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: 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.kla...@aklaver.com
Re: [GENERAL] database migration question between different ubuntus and different postgresql server versions
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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] database migration question between different ubuntus and different postgresql server versions
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.kla...@aklaver.com 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.kla...@aklaver.com
