Thread: psql backward compatibility

psql backward compatibility

From
Stephen Haddock
Date:
Hello,

When upgrading an older version of postgres, version 8.4 for example, to a newer version such as 9.6, does the data have to be migrated immediately?

It looks like the recommended method is to dump the data, upgrade, initialize a new cluster, and then restore the dumped data into the newer version. My question is whether the data dump and restore must be done immediately. It appears that 9.6 is able to run against the older cluster (DB service starts, queries work, etc), and the data could be migrated days or weeks later. I don't know if that is asking for issues down the line though such as 9.6 corrupting the data due to incompatibilities between the two versions.

Thanks!

Re: psql backward compatibility

From
Christophe Pettus
Date:

> On Nov 18, 2020, at 08:05, Stephen Haddock <haddock.stephenm@gmail.com> wrote:
> When upgrading an older version of postgres, version 8.4 for example, to a newer version such as 9.6, does the data
haveto be migrated immediately? 

Yes.  You cannot run binaries from a newer major version of PostgreSQL on a cluster that was initialized with an older
majorversion.  You'll need to do a pg_dump/pg_restore, or use  pg_upgrade to create a new cluster. 

--
-- Christophe Pettus
   xof@thebuild.com




Re: psql backward compatibility

From
"David G. Johnston"
Date:
On Wed, Nov 18, 2020 at 9:05 AM Stephen Haddock <haddock.stephenm@gmail.com> wrote:
It appears that 9.6 is able to run against the older cluster (DB service starts, queries work, etc)

If this is indeed what you've observed you've found a bug because a 9.6 service should not start at all if the data directory it is being pointed to is from a different major version.

David J.

Re: psql backward compatibility

From
Adrian Klaver
Date:
On 11/18/20 8:05 AM, Stephen Haddock wrote:
> Hello,
> 
> When upgrading an older version of postgres, version 8.4 for example, to 
> a newer version such as 9.6, does the data have to be migrated immediately?
> 
> It looks like the recommended method is to dump the data, upgrade, 
> initialize a new cluster, and then restore the dumped data into the 
> newer version. My question is whether the data dump and restore must be 
> done immediately. It appears that 9.6 is able to run against the older 
> cluster (DB service starts, queries work, etc), and the data could be 
> migrated days or weeks later. I don't know if that is asking for issues 
> down the line though such as 9.6 corrupting the data due to 
> incompatibilities between the two versions.

https://www.postgresql.org/docs/9.6/app-pgdump.html

"Because pg_dump is used to transfer data to newer versions of 
PostgreSQL, the output of pg_dump can be expected to load into 
PostgreSQL server versions newer than pg_dump's version. pg_dump can 
also dump from PostgreSQL servers older than its own version. 
(Currently, servers back to version 7.0 are supported.) "


The above is for Postgres 9.6 version of pg_dump. Newer versions(10+) go 
back to Postgres 8.0.  You can dump the old server at anytime. The 
important thing to remember is to dump the old server using the new 
servers version of pg_dump. So in your case pg_dump(9.6) against 
server(8.4).

> 
> Thanks!


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: psql backward compatibility

From
Adrian Klaver
Date:
On 11/18/20 8:05 AM, Stephen Haddock wrote:
> Hello,
> 
> When upgrading an older version of postgres, version 8.4 for example, to 
> a newer version such as 9.6, does the data have to be migrated immediately?
> 
> It looks like the recommended method is to dump the data, upgrade, 
> initialize a new cluster, and then restore the dumped data into the 
> newer version. My question is whether the data dump and restore must be 
> done immediately. It appears that 9.6 is able to run against the older 
> cluster (DB service starts, queries work, etc), and the data could be 

Hmm, missed that. As David said that should not happen and if you are 
running a new binary against an old cluster then you will get corruption.

> migrated days or weeks later. I don't know if that is asking for issues 
> down the line though such as 9.6 corrupting the data due to 
> incompatibilities between the two versions.
> 
> Thanks!


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: psql backward compatibility

From
Alvaro Herrera
Date:
On 2020-Nov-18, Stephen Haddock wrote:

> Hello,
> 
> When upgrading an older version of postgres, version 8.4 for example, to a
> newer version such as 9.6, does the data have to be migrated immediately?

As others have said: yes.

> It looks like the recommended method is to dump the data, upgrade,
> initialize a new cluster, and then restore the dumped data into the newer
> version.

Actually, you can also use pg_upgrade, which might be more convenient,
particularly if your database is large.



Re: psql backward compatibility

From
"David G. Johnston"
Date:
On Wed, Nov 18, 2020 at 9:16 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/18/20 8:05 AM, Stephen Haddock wrote:
> Hello,
>
> When upgrading an older version of postgres, version 8.4 for example, to
> a newer version such as 9.6, does the data have to be migrated immediately?
>
> It looks like the recommended method is to dump the data, upgrade,
> initialize a new cluster, and then restore the dumped data into the
> newer version. My question is whether the data dump and restore must be
> done immediately. It appears that 9.6 is able to run against the older
> cluster (DB service starts, queries work, etc), and the data could be

Hmm, missed that. As David said that should not happen and if you are
running a new binary against an old cluster then you will get corruption.


Actually, upon re-reading I suspect you are more likely correct.  Depending on the package/installer both 8.4 and 9.6 are both able to run on the server simultaneously - on different ports.  Upgrading PostgreSQL to 9.6 only installs the database programs and, usually, a default cluster (using the next available port number) having a "postgres" database (it's not really an upgrade if the major version changes, it's a new install).  Separately, the DBA must initiate an upgrade of clusters (or dump/reload of individual databases) that they wish to run under the newly installed 9.6 version.

David J.

Re: psql backward compatibility

From
Stephen Haddock
Date:
Thanks for the quick responses!

I'll double-check the configuration. Given your responses it is highly likely that the older version is still running the server and I'm simply running the client in 9.6. 

On Wed, Nov 18, 2020, 11:16 Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/18/20 8:05 AM, Stephen Haddock wrote:
> Hello,
>
> When upgrading an older version of postgres, version 8.4 for example, to
> a newer version such as 9.6, does the data have to be migrated immediately?
>
> It looks like the recommended method is to dump the data, upgrade,
> initialize a new cluster, and then restore the dumped data into the
> newer version. My question is whether the data dump and restore must be
> done immediately. It appears that 9.6 is able to run against the older
> cluster (DB service starts, queries work, etc), and the data could be

Hmm, missed that. As David said that should not happen and if you are
running a new binary against an old cluster then you will get corruption.

> migrated days or weeks later. I don't know if that is asking for issues
> down the line though such as 9.6 corrupting the data due to
> incompatibilities between the two versions.
>
> Thanks!


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: psql backward compatibility

From
"David G. Johnston"
Date:
On Wed, Nov 18, 2020 at 9:30 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2020-11-18 at 11:05 -0500, Stephen Haddock wrote:
> When upgrading an older version of postgres, version 8.4 for example, to a newer
>  version such as 9.6, does the data have to be migrated immediately?

Since nobody mentioned that explicitly: do not upgrade to 9.6.
If you upgrade, move to v13.


Not sure I'd suggest people upgrade to v13.  If they are in a position to do so and accept the risk involved with a first year point release great, but I wouldn't make that assumption when making a blind suggestion.  v12 would be the best from an efficiency/risk perspective at this moment in time, IMO.  v9.6 is only being supported for one more year would be the reason to avoid choosing it.

David J.

Re: psql backward compatibility

From
Ron
Date:
On 11/18/20 10:13 AM, Adrian Klaver wrote:
> On 11/18/20 8:05 AM, Stephen Haddock wrote:
>> Hello,
>>
>> When upgrading an older version of postgres, version 8.4 for example, to 
>> a newer version such as 9.6, does the data have to be migrated immediately?
>>
>> It looks like the recommended method is to dump the data, upgrade, 
>> initialize a new cluster, and then restore the dumped data into the newer 
>> version. My question is whether the data dump and restore must be done 
>> immediately. It appears that 9.6 is able to run against the older cluster 
>> (DB service starts, queries work, etc), and the data could be migrated 
>> days or weeks later. I don't know if that is asking for issues down the 
>> line though such as 9.6 corrupting the data due to incompatibilities 
>> between the two versions.
>
> https://www.postgresql.org/docs/9.6/app-pgdump.html
>
> "Because pg_dump is used to transfer data to newer versions of PostgreSQL, 
> the output of pg_dump can be expected to load into PostgreSQL server 
> versions newer than pg_dump's version. pg_dump can also dump from 
> PostgreSQL servers older than its own version. (Currently, servers back to 
> version 7.0 are supported.) "
>
>
> The above is for Postgres 9.6 version of pg_dump. Newer versions(10+) go 
> back to Postgres 8.0.  You can dump the old server at anytime. The 
> important thing to remember is to dump the old server using the new 
> servers version of pg_dump. So in your case pg_dump(9.6) against server(8.4).

This is especially useful, since the 9.6 pg_dump is able to do parallel 
operations against 8.4.

-- 
Angular momentum makes the world go 'round.



Re: psql backward compatibility

From
Ron
Date:
On 11/18/20 10:30 AM, Laurenz Albe wrote:
> On Wed, 2020-11-18 at 11:05 -0500, Stephen Haddock wrote:
>> When upgrading an older version of postgres, version 8.4 for example, to a newer
>>   version such as 9.6, does the data have to be migrated immediately?
> Since nobody mentioned that explicitly: do not upgrade to 9.6.
> If you upgrade, move to v13.

Unless the software is only certified up to 9.6.

-- 
Angular momentum makes the world go 'round.