CODEWITHSUNDEEP
postgresql
Devil's Dream
Devil's Dream

Reputation: 715

Upgrade Postgresql from 9.6 to 11 with different data directory

I am trying to upgrade PostgreSQL version from 9.6 to 11. I am following this link. The problem is I have changed my default location of PostgreSQL data directory from /var/lib/postgresql/ to /opt/postgresql/. So when I am using pg_lsclusters it is returning following

enter image description here

When I am running the sudo pg_upgradecluster -m upgrade 9.6 main its returning following error

Error: move_conffile: required configuration file /var/lib/postgresql/11/main/postgresql.conf does not exist
Error: Could not create target cluster

Upvotes: 0

Views: 4134

Answers (1)

exhuma
exhuma

Reputation: 21697

It sounds like you moved the folder before running pg_upgradecluster so now it can no longer find the config file.

If you already have a backup, I would do the following:

  • Move /opt/postgresql/9.6/main back to where it was before (in /var/lib/postgresql)
  • (unsure about this one): Remove /opt/postgresql/11/main
  • Run pg_upgradecluster 9.6 main /opt/postgresql/11 This will tell it to use the default location of 9.6/main and store the upgraded version into /opt/postgresql/11

I am unsure about the second point as I don't remember how pg_upgradecluster deals with existing folders. But as long as you keep a backup, you can play around with it. My best guess is that it will exit with an error if it sees that the folder already exists to ensure nothing is overwritten. So you may need to delete it.

You could try to move the folder back to the old location and then run pg_upgradecluster again. But you have to be careful: The version 9.6 to 11 is a major upgrade which changes the on-disk binary format. pg_upgradecluster should take care of this by making a dump/load operation.

If the above fails

Here are some pointers to get you going:

  • pg_upgradecluster does nothing more than a dump/load but with a more convenient CLI interface
  • You can manually start a cluster using postgres -D </path/to/postgres/9.6/main> -c </path/to/9.6/main/postgresql.conf> -p <port>
  • Once manually started, you can run pg_dumpall --cluster 9.6/main -Ft -f dump.tar
  • Run pg_createcluster -d /opt/postgresql --start 11 main (using --start will start it up after creation).
  • Once this is started, you should be able to reload the previous dump into that cluster. You should be able to target that new cluster using pg_restore --cluster 11/main -Ft -f dump.tar for your dump/load needs

Upvotes: 1

Related Questions