Thread: psql backward compatibility
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!
> 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
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.
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
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
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.
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.
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
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.
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.
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.