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 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
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 migratedimmediately?
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 beHmm, 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:
Show quoted text
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 migratedimmediately?
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 beHmm, 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, 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
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.