2

Trying to find a migration route from PostgreSQL 10.6 to postgreSQL 11.1.

Using pg_upgrade, from both versions, give me errors. From 11.1 which I thought would be used to bring outdated tables to currency:

$ /usr/local/Cellar/postgresql/11.1/bin/pg_upgrade -v \
>     -b /usr/local/Cellar/postgresql@10/10.6/bin \
>     -B /usr/local/Cellar/postgresql/11.1/bin \
>     -d /usr/local/var/postgres \
>     -D /usr/local/var/postgres
Running in verbose mode
Performing Consistency Checks
-----------------------------
Checking cluster versions
This utility can only upgrade to PostgreSQL version 11.
Failure, exiting

From 10.6, the expected result, as it cannot see a forward structure:

/usr/local/Cellar/postgresql@10/10.6/bin/pg_upgrade -v \
>     -b /usr/local/Cellar/postgresql@10/10.6/bin \
>     -B /usr/local/Cellar/postgresql/11.1/bin \
>     -d /usr/local/var/postgres \
>     -D /usr/local/var/postgres
Running in verbose mode
Performing Consistency Checks
-----------------------------
Checking cluster versions
New cluster data and binary directories are from different major versions.
Failure, exiting

How can I migrate the tables to work with version 11.1 properly? I am assuming the latter version is the one to use, and my directories are valid.

Rich_F
  • 1,830
  • 3
  • 24
  • 45
  • Just a note to anyone stupid enough out there (like me) to not check if the update was done within the same **major version**, this won't obviously work if you upgrade from say **12.1** to **12.3**, because there's no need for `pg_upgrade` to run. But the same error message `New cluster data and binary directories are from different major versions.` will pop up. – Torxed May 27 '20 at 07:52

1 Answers1

3

Yes you need to use pg_upgrade of the version you upgrade to (11.1).

Your -b and -B options look ok. Your -d and -D however cannot be the same! The config location also define the data locations and these cannot be the same. You need to specify the config location of your initialized 11.1 cluster with -D.

If you do not want pg_upgrade to copy all data use the --link option to use hard links instead of copying. After the upgrade you can remove old data dir.

Eelke
  • 20,897
  • 4
  • 50
  • 76
  • Started a new question as the `postmaster` is apparently looking at the old cluster and won't shut down. – Rich_F Dec 30 '18 at 14:48