Обсуждение: 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.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:
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.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.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 laptoppg_dump -F p his_dbname > his_dbname.sql
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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
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: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.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.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,
(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.sqlThen copy dbname.sql to a jump/thumb drive on the old laptopcopy the data from the jump/thumb drive to the new laptopcreate the new db in 9.5and 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
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.
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.sqlThen copy dbname.sql to a jump/thumb drive on the old laptopcopy the data from the jump/thumb drive to the new laptopcreate the new db in 9.5and 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
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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
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
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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
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
On Fri, Jul 15, 2016 at 4:22 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
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.
-- As Adrian and I have said, pg_dump and pg_dumpall as backwards compatible and the recommended method for porrting data from one versionto 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.Thanks for your help though :)
Willy-Bas Loos
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
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
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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
On Fri, Jul 15, 2016 at 4:40 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
Hi, i've learned to dump the database with the pg_dump version of the target platform.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.
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
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
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