Thread: migrating data from an old postgres version

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 ?

Make sure to pg_dump -Fc

(note the flags)

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.

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

​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

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

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

pg_dump is backwards compatible to version 7.0.

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?

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.

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