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

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 is that the newer version might > contain

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

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. But discounting that r

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

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 > comment them oout or just ign

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

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 plain_sql.txt custom_dump.out >> >> Not sure what errors you

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

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 my previous answer directed a

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

On 07/15/2016 07:26 AM, Melvin Davidson wrote: On Fri, Jul 15, 2016 at 10:24 AM, Willy-Bas Loos mailto:willy...@gmail.com>> wrote: On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: pg_dump is backwards compatible to version 7.0. Yes

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

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 use plain. And i've had

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

On 07/15/2016 07:22 AM, Melvin Davidson wrote: On Fri, Jul 15, 2016 at 10:19 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 07/15/2016 07:07 AM, Willy-Bas Loos wrote: On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson mailto:melvin6...@gmail.com>

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

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

On 07/15/2016 07:24 AM, Willy-Bas Loos wrote: On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> 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 and old PG version that was cl

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

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 dump made with pg_dump 7.0 on a 9.4 database? > > -

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

On 07/15/2016 07:07 AM, Willy-Bas Loos wrote: On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson mailto:melvin6...@gmail.com>> 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 th

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

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 is that we use postgis

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

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 >> pg_dump -F p his_dbname > his_dbname.sql >> Then c

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

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 that

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

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 laptop >> copy the data from the jump/thumb dr

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

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 postgis documentation notes on “ha

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

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. https://www.postgresql.org/docs/9.6/static/app-pgdump.html

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

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 9.5 > and use pg_restore

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

> 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 the new one. So he installed postgres 9.5 on

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

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 database. This > databas