psql backward compatibility

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

psql backward compatibility

Stephen Haddock
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!
Reply | Threaded
Open this post in threaded view
|

Re: psql backward compatibility

Christophe Pettus-2


> On Nov 18, 2020, at 08:05, Stephen Haddock <[hidden email]> 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?

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

--
-- Christophe Pettus
   [hidden email]



Reply | Threaded
Open this post in threaded view
|

Re: psql backward compatibility

David G Johnston
In reply to this post by Stephen Haddock
On Wed, Nov 18, 2020 at 9:05 AM Stephen Haddock <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: psql backward compatibility

Adrian Klaver-4
In reply to this post by Stephen Haddock
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: psql backward compatibility

Adrian Klaver-4
In reply to this post by Stephen Haddock
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: psql backward compatibility

Álvaro Herrera
In reply to this post by Stephen Haddock
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.


Reply | Threaded
Open this post in threaded view
|

Re: psql backward compatibility

David G Johnston
In reply to this post by Adrian Klaver-4
On Wed, Nov 18, 2020 at 9:16 AM Adrian Klaver <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: psql backward compatibility

Stephen Haddock
In reply to this post by Adrian Klaver-4
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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: psql backward compatibility

Laurenz Albe
In reply to this post by Stephen Haddock
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.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: psql backward compatibility

David G Johnston
On Wed, Nov 18, 2020 at 9:30 AM Laurenz Albe <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: psql backward compatibility

Ron-2
In reply to this post by Adrian Klaver-4
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.


Reply | Threaded
Open this post in threaded view
|

Re: psql backward compatibility

Ron-2
In reply to this post by Laurenz Albe
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.