Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Willy-Bas Loos
On Fri, Jul 15, 2016 at 5:07 PM, Tom Lane wrote: > Willy-Bas Loos writes: > > (the manual says:"It is recommended that you use the pg_dump and > pg_dumpall > > programs from the newer version of PostgreSQL") > > The reason for the manual's recommendation

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Tom Lane
Willy-Bas Loos writes: > (the manual says:"It is recommended that you use the pg_dump and pg_dumpall > programs from the newer version of PostgreSQL") The reason for the manual's recommendation is that the newer version might contain bug fixes not present in the older one.

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Willy-Bas Loos
On Fri, Jul 15, 2016 at 4:40 PM, Melvin Davidson wrote: > Look, if you dump in plain SQL, it is standard to both old and new > versions of PostgreSQL. That is the recommended method to upgrade. > Yes, errors like "row_security" can/will happen, but you can edit and >

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Melvin Davidson
On Fri, Jul 15, 2016 at 10:34 AM, Willy-Bas Loos wrote: > > > On Fri, Jul 15, 2016 at 4:31 PM, Adrian Klaver > wrote: > >> Remember you can reconstitute a plain format dump from a custom format >> dump by doing: >> >> pg_restore ... -f

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Willy-Bas Loos
On Fri, Jul 15, 2016 at 4:31 PM, Adrian Klaver wrote: > Remember you can reconstitute a plain format dump from a custom format > dump by doing: > > pg_restore ... -f plain_sql.txt custom_dump.out > > Not sure what errors you are talking about? > > Nice one! errors in

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Adrian Klaver
On 07/15/2016 07:26 AM, Melvin Davidson wrote: On Fri, Jul 15, 2016 at 10:24 AM, Willy-Bas Loos > wrote: On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver > wrote: pg_dump

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Willy-Bas Loos
On Fri, Jul 15, 2016 at 4:22 PM, Melvin Davidson wrote: > > As Adrian and I have said, pg_dump and pg_dumpall as backwards compatible > and the recommended method for porrting data from one version > to another. You will NOT get errors if you use plain format. > > I can't

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Adrian Klaver
On 07/15/2016 07:22 AM, Melvin Davidson wrote: On Fri, Jul 15, 2016 at 10:19 AM, Adrian Klaver > wrote: On 07/15/2016 07:07 AM, Willy-Bas Loos wrote: On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Willy-Bas Loos
On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver wrote: > > pg_dump is backwards compatible to version 7.0. > Yes but is it forward compatible from 7.0? I mean can i restore a dump made with pg_dump 7.0 on a 9.4 database? -- Willy-Bas Loos

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Adrian Klaver
On 07/15/2016 07:24 AM, Willy-Bas Loos wrote: On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver > wrote: pg_dump is backwards compatible to version 7.0. Yes but is it forward compatible from 7.0? Maybe. I have taken dumps from

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Melvin Davidson
On Fri, Jul 15, 2016 at 10:24 AM, Willy-Bas Loos wrote: > On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver > wrote: > >> >> pg_dump is backwards compatible to version 7.0. >> > > Yes but is it forward compatible from 7.0? > I mean can i restore a

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Adrian Klaver
On 07/15/2016 07:07 AM, Willy-Bas Loos wrote: On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson > wrote: Why can't he just do a pg_dump -F p his_dbname > his_dbname.sql Then copy dbname.sql to a jump/thumb drive on the old laptop

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Willy-Bas Loos
On Fri, Jul 15, 2016 at 4:13 PM, Melvin Davidson wrote: > If you dump with -F p (plain is default) then the version does not matter. > > That is the recommended method for upgrading from older versions. > https://www.postgresql.org/docs/9.4/static/upgrading.html > > Problem

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Melvin Davidson
On Fri, Jul 15, 2016 at 10:19 AM, Adrian Klaver wrote: > On 07/15/2016 07:07 AM, Willy-Bas Loos wrote: > >> >> On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson > > wrote: >> >> >> Why can't he just do a >>

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Willy-Bas Loos
On Fri, Jul 15, 2016 at 4:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > ​You need to also include "​--quote-all-identifiers" if you intend for the > dump to be restored onto a newer version of PostgreSQL. > > https://www.postgresql.org/docs/9.6/static/app-pgdump.html > > Hey

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Melvin Davidson
On Fri, Jul 15, 2016 at 10:07 AM, Willy-Bas Loos wrote: > > On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson > wrote: > >> >> Why can't he just do a >> pg_dump -F p his_dbname > his_dbname.sql >> Then copy dbname.sql to a jump/thumb drive on the old

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Willy-Bas Loos
On Fri, Jul 15, 2016 at 4:02 PM, Paul Ramsey wrote: > Make sure to pg_dump -Fc > (note the flags) > so that in case you need to do a hard restore (if you’re going from > postgis 1.x to 2.x) you can run the dump file through the special cleaning > script. > > See

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread David G. Johnston
On Fri, Jul 15, 2016 at 10:02 AM, Paul Ramsey wrote: > > Make sure to pg_dump -Fc > (note the flags) > > ​You need to also include "​--quote-all-identifiers" if you intend for the dump to be restored onto a newer version of PostgreSQL.

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Willy-Bas Loos
On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson wrote: > > Why can't he just do a > pg_dump -F p his_dbname > his_dbname.sql > Then copy dbname.sql to a jump/thumb drive on the old laptop > copy the data from the jump/thumb drive to the new laptop > create the new db in

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Paul Ramsey
> On Jul 15, 2016, at 6:55 AM, Melvin Davidson wrote: > > > > On Fri, Jul 15, 2016 at 9:46 AM, Willy-Bas Loos > wrote: > Hi, > > A coworker is getting a new laptop and he wants to migrate some data from his > old one to

Re: [GENERAL] migrating data from an old postgres version

2016-07-15 Thread Melvin Davidson
On Fri, Jul 15, 2016 at 9:46 AM, Willy-Bas Loos wrote: > Hi, > > A coworker is getting a new laptop and he wants to migrate some data from > his old one to the new one. So he installed postgres 9.5 on the new one and > is asking me how to migrate the data from the old 8.4

[GENERAL] migrating data from an old postgres version

2016-07-15 Thread Willy-Bas Loos
Hi, A coworker is getting a new laptop and he wants to migrate some data from his old one to the new one. So he installed postgres 9.5 on the new one and is asking me how to migrate the data from the old 8.4 database. This database includes postgis. I asked him to make a network connection so