1

I'm trying to upgrade from one minor version (11.1) to another minor version (11.6) using the pg_upgrade tool but it is failing in the below command:

su - postgres -c '/usr/pgsql-11/bin/pg_upgrade -d /opt/collabnet/teamforge/var/pgsql/11.1/data -D /opt/collabnet/teamforge/var/pgsql/11.6/data -b /usr/pgsql-11/bin -B /usr/pgsql-11/bin'

with below error:

Performing Consistency Checks

Checking cluster versions                                   ok

Cannot upgrade to/from the same system catalog version when
using tablespaces.
Failure, exiting

and this below error in postgresql.log file:

could not open directory "pg_tblspc/16400/PG_11_201809051": No such file or directory

Any ideas about how to overcome it with the same pg_upgrade tool?

pg_upgrade_server.log

could not open directory "pg_tblspc/16400/PG_11_201809051": No such file or directory
Ranjith
  • 13
  • 1
  • 8

1 Answers1

2

You don't use pg_upgrade for a minor upgrade.

Just stop the server, replace the 11.1 binaries with the 11.6 ones (you should actually be using 11.7) and start the server. By “replacing the binaries” I mean that you should install the 11.6 release of PostgreSQL in the same directories as the 11.1 release, thereby overwriting the previous installation.

That is all! You should also read the release notes, sometimes some additional fixes in the database metadata are necessary.

The error you encounter is because you are using tablespaces. In every tablespace, PostgreSQL creates a subdirectory whose name contains the catalog version. During pg_upgrade, a new such subdirectory is created in each tablespace. This of course cannot work if the catalog versions are the same.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • So it's not possible for an upgrade from 11.1 to 11.6 to work using pg_upgrade. Is that right? The only way is by installing new binaries. – Ranjith Feb 27 '20 at 06:17
  • It definitely does not work if you are using tablespaces, as you found out. It is much better to upgrade by just restarting with the new binaries than by using `pg_upgrade` - with `pg_upgrade`, you have to install the new binaries as well, and you need extra steps. So why would you want to do that if it is not necessary? – Laurenz Albe Feb 27 '20 at 06:49
  • PostgreSQL data directory is an NFS mounted directory. Does this have anything to do with that tablespace issue? Because this same scenario works (11.1 to 11.6 using pg_upgrade) in other servers also without mounted data directory. – Ranjith Mar 03 '20 at 07:29
  • No, the file system type is irrelevant. The deciding factor is if you have a tablespace or not. Did you not read the error message? By the way, I hope that you are using hard, foreground mounts and you have a good version of the NFS client, otherwise NFS is dangerous. – Laurenz Albe Mar 03 '20 at 07:34
  • My question is this same scenario work in almost most of the servers only in this case it's not working. Even with tablespace, it works in other boxes. – Ranjith Mar 03 '20 at 07:48
  • The question is if `SELECT * FROM pg_tablespace;` gives you more then the two default entries. – Laurenz Albe Mar 03 '20 at 09:22
  • SELECT * FROM pg_tablespace; this gives the same two default entries only. I cannot check this when the Postgres server is down. I'm sure about this failed server also had the same two default entries but it fails on Cannot upgrade to/from the same system catalog version when using tablespaces – Ranjith Mar 04 '20 at 05:05
  • "I'm sure" is not enough. I think that the error message is clear, isn't it? – Laurenz Albe Mar 04 '20 at 10:14
  • Yes. I understood the error. This same scenario is working on every other server. Why it is failing here only? That is my question. Is it because data is corrupted? or some other reason. – Ranjith Mar 04 '20 at 10:31
  • No, because you have an extra tablespace on the instance where it fails. I find it annoying that you refuse to believe me. According to the identical question you asked on DBA Stackexchange, the OID of that tablespace is 16400. So start the database, check up on `pg_tablespace` and see for yourself. – Laurenz Albe Mar 04 '20 at 11:30
  • I believe you man. But these differences are making me ask like this. Thanks for the reply. – Ranjith Mar 05 '20 at 04:55