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
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.comNow, 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.
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 <mailto: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 <http://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.
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
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.
https://www.postgresql.org/docs/9.6/static/app-pgdump.html
And don't forget pg_dumpall to get the globals - or to do the whole thing.
https://www.postgresql.org/docs/9.6/static/app-pg-dumpall.html
David J.
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
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.
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!
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
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
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.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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 LoosYes, 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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.
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
