Hi
On our servers, we are running different PostgreSQL versions because we can
not migrate every application at the same time to Pg 9.6…
Since we have several disks, we use tablespaces and, for historical reasons,
we used the same folder for both Pg versions, say /mnt/ssd/postgres
The server has absolutely no issue with that, there is not a single warning
when running CREATE TABLESPACE.
But it all gets messy when we want to create a streaming standby server using
pg_basebackup. When backuping Pg 9.5, there is no issue, but backuping Pg 9.6
afterwards will say "directory "/mnt/ssd/postgres" exists but is not empty".
The attached script to this mail will easily reproduce that issue if you did
not understand me. Just... read it before launching it, it's a dirty script to
reproduce the issue. :)
I have looked a bit at the pg_basebackup code and the replication protocol,
and I did not find any simple way to solve the issue. PostgreSQL use the
CATALOG_VERSION_NO to have one folder per Pg version, but that constant is not
accessible out of the PostgreSQL code and is not exposed in the replication
protocol as described in the documentation
https://www.postgresql.org/docs/current/static/protocol-replication.html
The only easy way I see to fix that issue is to alter the replication protocol
to expose the CATALOG_VERSION_NO constant, making it possible for
pg_basebackup to know the proper path to check. Another way would be to change
the pg_basebackup logic and backup the main data folder in order to be able to
read the pg_control file… but this seems ugly too.
I am willing to write the patch for this issue, but I would appreciate some
help to find a proper way to fix it.
Thanks
Pierre