Thread: migrating data from an old postgres version

migrating data from an old postgres version

From
Willy-Bas Loos
Date:
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 that he can make a dump from the 9.5 machine, but that seems to be tricky for him.
He is inserting a harddrive in his new laptop which has the data directory on it.

So i went to look for a 8.4 installer but it has been EOL for a while now, so i found no official source for that. But i found a 8.4.12 installer on filehorse.com

Now, maybe this is overcautious, but being a linux man myself, i dislike installing unknown software. So is there an md5 signature for that installer around?
Or maybe a more official source for a 8.4 installer?

Cheers,


--
Willy-Bas Loos

Re: migrating data from an old postgres version

From
Melvin Davidson
Date:


On Fri, Jul 15, 2016 at 9:46 AM, Willy-Bas Loos <willybas@gmail.com> 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 database includes postgis.

I asked him to make a network connection so that he can make a dump from the 9.5 machine, but that seems to be tricky for him.
He is inserting a harddrive in his new laptop which has the data directory on it.

So i went to look for a 8.4 installer but it has been EOL for a while now, so i found no official source for that. But i found a 8.4.12 installer on filehorse.com

Now, maybe this is overcautious, but being a linux man myself, i dislike installing unknown software. So is there an md5 signature for that installer around?
Or maybe a more official source for a 8.4 installer?

Cheers,


--
Willy-Bas Loos

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 to load the his_dbname.sql ?

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: migrating data from an old postgres version

From
Paul Ramsey
Date:

On Jul 15, 2016, at 6:55 AM, Melvin Davidson <melvin6925@gmail.com> wrote:



On Fri, Jul 15, 2016 at 9:46 AM, Willy-Bas Loos <willybas@gmail.com> 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 database includes postgis.

I asked him to make a network connection so that he can make a dump from the 9.5 machine, but that seems to be tricky for him.
He is inserting a harddrive in his new laptop which has the data directory on it.

So i went to look for a 8.4 installer but it has been EOL for a while now, so i found no official source for that. But i found a 8.4.12 installer on filehorse.com

Now, maybe this is overcautious, but being a linux man myself, i dislike installing unknown software. So is there an md5 signature for that installer around?
Or maybe a more official source for a 8.4 installer?

Cheers,


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 “hard upgrade” for full info on doing a clean 1->2 upgrade. 

If he’s already blown away his old environment then yeah, you’re going to have to exactly duplicate it first so you can run 8.4 *and* an old version of postgis (have to match major version numbers) and get a clean dump file out of it.

P.

Re: migrating data from an old postgres version

From
Willy-Bas Loos
Date:

On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson <melvin6925@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 the jump/thumb drive to the new laptop
create the new db in 9.5
and use pg_restore to load the his_dbname.sql ?

Because it's not the same version, so that will cause some errors.
But i guess we should be able to live with that.
Thanks, i guess i was thinking a bit too rigid.

Cheers,
--
Willy-Bas Loos

Re: migrating data from an old postgres version

From
"David G. Johnston"
Date:
On Fri, Jul 15, 2016 at 10:02 AM, Paul Ramsey <pramsey@cleverelephant.ca> 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.


And don't forget pg_dumpall to get the globals - or to do the whole thing.


David J.

Re: migrating data from an old postgres version

From
Willy-Bas Loos
Date:
On Fri, Jul 15, 2016 at 4:02 PM, Paul Ramsey <pramsey@cleverelephant.ca> 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 “hard upgrade” for full info on doing a clean 1->2 upgrade. 

If he’s already blown away his old environment then yeah, you’re going to have to exactly duplicate it first so you can run 8.4 *and* an old version of postgis (have to match major version numbers) and get a clean dump file out of it.

P.

Yeah, that perl script really works well, used it a lot.
 Only downside is the downtime you need for it, on servers that is.
I really like pg_upgrade, and i have been able to use that since apt.postgresql.org offers several versions per distro, and includes postgis too.
Great stuff, upgrade a big db in single minutes of downtime.

--
Willy-Bas Loos

Re: migrating data from an old postgres version

From
Melvin Davidson
Date:


On Fri, Jul 15, 2016 at 10:07 AM, Willy-Bas Loos <willybas@gmail.com> wrote:

On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson <melvin6925@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 the jump/thumb drive to the new laptop
create the new db in 9.5
and use pg_restore to load the his_dbname.sql ?

Because it's not the same version, so that will cause some errors.
But i guess we should be able to live with that.
Thanks, i guess i was thinking a bit too rigid.

Cheers,
--
Willy-Bas Loos

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

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: migrating data from an old postgres version

From
Willy-Bas Loos
Date:

On Fri, Jul 15, 2016 at 4:09 PM, David G. Johnston <david.g.johnston@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.



Hey that's new, i'll be sure to use it!

Re: migrating data from an old postgres version

From
Willy-Bas Loos
Date:

On Fri, Jul 15, 2016 at 4:13 PM, Melvin Davidson <melvin6925@gmail.com> 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 and the versions differ between the postgres installations.
So we have to upgrade postgis in the process.
We use a perl script for that, which gets a list of the objects in the backup and then filters what will be restored. But for that it needs a custom dump, hence Paul Ramsey's advice.

--
Willy-Bas Loos

Re: migrating data from an old postgres version

From
Adrian Klaver
Date:
On 07/15/2016 07:07 AM, Willy-Bas Loos wrote:
>
> On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson <melvin6925@gmail.com
> <mailto:melvin6925@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 the jump/thumb drive to the new laptop
>     create the new db in 9.5
>     and use pg_restore to load the his_dbname.sql ?
>
>
> Because it's not the same version, so that will cause some errors.

pg_dump is backwards compatible to version 7.0.

> But i guess we should be able to live with that.
> Thanks, i guess i was thinking a bit too rigid.
>
> Cheers,
> --
> Willy-Bas Loos


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: migrating data from an old postgres version

From
Melvin Davidson
Date:

On Fri, Jul 15, 2016 at 10:19 AM, Adrian Klaver <adrian.klaver@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 <melvin6925@gmail.com
<mailto:melvin6925@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 the jump/thumb drive to the new laptop
    create the new db in 9.5
    and use pg_restore to load the his_dbname.sql ?


Because it's not the same version, so that will cause some errors.

pg_dump is backwards compatible to version 7.0.


But i guess we should be able to live with that.
Thanks, i guess i was thinking a bit too rigid.

Cheers,
--
Willy-Bas Loos


--
Adrian Klaver
adrian.klaver@aklaver.com

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.



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: migrating data from an old postgres version

From
Willy-Bas Loos
Date:
On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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: migrating data from an old postgres version

From
Melvin Davidson
Date:

On Fri, Jul 15, 2016 at 10:24 AM, Willy-Bas Loos <willybas@gmail.com> wrote:
On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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

Yes, That is the whole point of backwards compatability!  That is how upgrades are done.

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: migrating data from an old postgres version

From
Adrian Klaver
Date:
On 07/15/2016 07:24 AM, Willy-Bas Loos wrote:
> On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@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 close(one
major version apart) to a new PG version and made it work, but I would
not count on it. The old version of pg_dump can't see into the future
and know what changes have occurred:)

> I mean can i restore a dump made with pg_dump 7.0 on a 9.4 database?

No

>
> --
> Willy-Bas Loos


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: migrating data from an old postgres version

From
Willy-Bas Loos
Date:
On Fri, Jul 15, 2016 at 4:22 PM, Melvin Davidson <melvin6925@gmail.com> 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 errors with plain in this scenario before. Especially around sequences, that could be "OWNED BY" someone since 8.4 if i remember correctly.
But there were more things.
A new thing is  ERROR:  unrecognized configuration parameter "row_security" , but that doesn't reallyt do any harm.
But there's more that i don't remember, since i learned how to avoid them.

Thanks for your help though :)
--
Willy-Bas Loos

Re: migrating data from an old postgres version

From
Adrian Klaver
Date:
On 07/15/2016 07:22 AM, Melvin Davidson wrote:
>
> On Fri, Jul 15, 2016 at 10:19 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@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
>         <melvin6925@gmail.com <mailto:melvin6925@gmail.com>
>         <mailto:melvin6925@gmail.com <mailto:melvin6925@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 the jump/thumb drive to the new laptop
>             create the new db in 9.5
>             and use pg_restore to load the his_dbname.sql ?
>
>
>         Because it's not the same version, so that will cause some errors.
>
>
>     pg_dump is backwards compatible to version 7.0.
>
>
>         But i guess we should be able to live with that.
>         Thanks, i guess i was thinking a bit too rigid.
>
>         Cheers,
>         --
>         Willy-Bas Loos
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
> 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.

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?

>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: migrating data from an old postgres version

From
Adrian Klaver
Date:
On 07/15/2016 07:26 AM, Melvin Davidson wrote:
>
> On Fri, Jul 15, 2016 at 10:24 AM, Willy-Bas Loos <willybas@gmail.com
> <mailto:willybas@gmail.com>> wrote:
>
>     On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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
>
>
> Yes, That is the whole point of backwards compatability!  That is how
> upgrades are done.

I think you are misunderstanding.  Willy is asking if you use the 7.0
version of pg_dump to dump a 7.0 database, can you then restore that
dump to a 9.4 database?

>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: migrating data from an old postgres version

From
Willy-Bas Loos
Date:


On Fri, Jul 15, 2016 at 4:31 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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 at melvin

--
Willy-Bas Loos

Re: migrating data from an old postgres version

From
Melvin Davidson
Date:


On Fri, Jul 15, 2016 at 10:34 AM, Willy-Bas Loos <willybas@gmail.com> wrote:


On Fri, Jul 15, 2016 at 4:31 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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 at melvin

--
Willy-Bas Loos

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 ignore.

ALL THE SQL is the same. So CREATE, COPY, etc. will all work.
I have previously sent you the url that shows you pg_dump and pg_dump are what is needed.

But if you don't believe me, I will comment no further.

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: migrating data from an old postgres version

From
Willy-Bas Loos
Date:


On Fri, Jul 15, 2016 at 4:40 PM, Melvin Davidson <melvin6925@gmail.com> 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 ignore.

ALL THE SQL is the same. So CREATE, COPY, etc. will all work.
I have previously sent you the url that shows you pg_dump and pg_dump are what is needed.

But if you don't believe me, I will comment no further.

Hi, i've learned to dump the database with the pg_dump version of the target platform.
That way you get 0 errors.
There can be inconsistencies in the database if you do it the way you suggest (like sequences missing and the like), BUT that is the best plan for my current situation.
I will use the "​--quote-all-identifiers" option.

(the manual says:"It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL")

Thank you,

--
Willy-Bas Loos

Re: migrating data from an old postgres version

From
Tom Lane
Date:
Willy-Bas Loos <willybas@gmail.com> 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
risk, a dump made by an older version of pg_dump usually should load into
a newer server.  We try *very* hard to avoid breaking syntaxes used by old
pg_dump versions.  The possibility of identifiers conflicting with new
reserved words is a hazard of course, which is what
"--quote-all-identifiers" was invented to address.  But if you don't use
that option, you're at risk for that regardless of whether you dumped with
the older or new pg_dump.

            regards, tom lane


Re: migrating data from an old postgres version

From
Willy-Bas Loos
Date:


On Fri, Jul 15, 2016 at 5:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Willy-Bas Loos <willybas@gmail.com> 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
risk, a dump made by an older version of pg_dump usually should load into
a newer server.  We try *very* hard to avoid breaking syntaxes used by old
pg_dump versions.  The possibility of identifiers conflicting with new
reserved words is a hazard of course, which is what
"--quote-all-identifiers" was invented to address.  But if you don't use
that option, you're at risk for that regardless of whether you dumped with
the older or new pg_dump.

                        regards, tom lane

Ah, ok. So maybe the biggest problem was the thing with the sequences.
One usually only gets into such an incident once, a big db with no sequences is no fun.
@Melvin so then you were more correct than i would give you credit for.

Cheers,
--
Willy-Bas Loos