Homebrew-core: brew postgresql-upgrade-database update to new version (e.g 11-12) fails when postgis is installed

[pauldaustin]

Please note we will close your issue without comment if you delete, do not read or do not fill out the issue checklist below and provide ALL the requested information. If you repeatedly fail to use the issue template, we will block you from ever submitting issues to Homebrew again.

• [x ] ran brew update and can still reproduce the problem?
• [ x] ran brew doctor, fixed all issues and can still reproduce the problem?
• [ x] ran brew gist-logs <formula> (where <formula> is the name of the formula that failed) and included the output link?
• [ x] if brew gist-logs didn't work: ran brew config and brew doctor and included their output with your issue?

What you were trying to do (and why)

brew postgresql-upgrade-database

To dump the old database to the new database

From 11 -> 12 which also had an upgrade of postgis from 2.5 -> 3

What happened (include command output)

Command output

pg_upgrade_dump_16405.log

pg_dump: error: query failed: ERROR:  could not access file "$libdir/postgis-2.5": No such file or directory

  

What you expected to happen

Backup the old data to the new

Step-by-step reproduction instructions (by running brew install commands)

brew upgrade
brew postgresql-upgrade-database

additional comments

The upgrade from one Postgres + postgis version to another has never worked correctly.

As each version is incompatible it would almost be better to have different formula for each version of Postgres and postgis combination. That way you would have to select to upgrade rather than being left with a broken system and a bunch of manual steps to revert back to include the correct extensions.

An alternative would be to have the upgrade process create a backup of the old version with all the extensions that were installed. That way the update process would have access to all the libraries installed in the previous version.

Sorry for the short descriptions.

[Bo98]

Thanks for raising this. Looks like a hole in the upgrade process. The postgis upgrade was necessary as 3.0 brought in PostgreSQL 12 support.

I suppose even if the upgrades were done weeks apart - someone who only upgrades every few months may have had the same issue.

As each version is incompatible it would almost be better to have different formula for each version of Postgres and postgis combination

[email protected] is a possibility, though upgrading will still bring you to 3.0 so downloading the older version would still need incorporating into postgresql-upgrade-database. It's also complicated in as you say, there's multiple combinations (postgis 2.5 + postgresql 11, postgis 2.5 + postgresql 12, postgis 3.0 + postgresql 11, etc) to consider - typically postgis only installs to the latest postgresql.

An alternative would be to have the upgrade process create a backup of the old version with all the extensions that were installed.

The problem is brew postgresql-upgrade-database is done after all that has happened. It isn't a one stage process. First brew upgrade does what is usually does and installs the new version. brew postgresql-upgrade-database then downloads a fresh copy of postgresql@<previous> and uses that.

I suppose the first question would be: if left with the data files alone, is there a nice way of seeing which postgis version is required?

[pauldaustin]

Select * from pg_extension might help

On Nov 22, 2019, at 17:11, Bo Anderson [e-mail] wrote:



Thanks for raising this. Looks like a hole in the upgrade process. The postgis upgrade was necessary as 3.0 brought in PostgreSQL 12 support.

I suppose even if the upgrades were done weeks apart - someone who only upgrades every few months may have had the same issue.

As each version is incompatible it would almost be better to have different formula for each version of Postgres and postgis combination

[email protected] is a possibility, though upgrading will still bring you to 3.0 so downloading the older version would still need incorporating into postgresql-upgrade-database. It's also complicated in as you say, there's multiple combinations (postgis 2.5 + postgresql 11, postgis 2.5 + postgresql 12, postgis 3.0 + postgresql 11, etc) to consider - typically postgis only installs to the latest postgresql.

An alternative would be to have the upgrade process create a backup of the old version with all the extensions that were installed.

The problem is brew postgresql-upgrade-database is done after all that has happened. It isn't a one stage process. First brew upgrade does what is usually does and installs the new version. brew postgresql-upgrade-database then downloads a fresh copy of postgresql@ and uses that.

I suppose the first question would be: if left with the data files alone, is there a nice way of seeing which postgis version is required?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHubhttps://github.com/Homebrew/homebrew-core/issues/47077?email_source=notifications&email_token=AADMYYPXSPRKCZFFVJKCXWDQVB7NVA5CNFSM4JQXWZ52YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEE7JLFA#issuecomment-557749652, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AADMYYKWBTMZDCGDODFLFDLQVB7NVANCNFSM4JQXWZ5Q.

[Bo98]

My follow up question: How does a postgis upgrade typically work out? Presumably this issue doesn't happen if the PostgreSQL version doesn't change?

[Bo98]

I ask because I see a hard -2.5 reference so I just wanted to double check that it worked in that scenario - it may well do.

[pauldaustin]

https://postgis.net/workshops/postgis-intro/upgrades.html

I’m away from my computer so can’t check how it links to the version

On Nov 22, 2019, at 17:18, Bo Anderson [e-mail] wrote:



I ask because I see a hard -2.5 reference so I just wanted to double check that it worked in that scenario - it may well do.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHubhttps://github.com/Homebrew/homebrew-core/issues/47077?email_source=notifications&email_token=AADMYYLBCNCFCU7BDRDFVUTQVCAHXA5CNFSM4JQXWZ52YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEE7JSHA#issuecomment-557750556, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AADMYYKF7P7IKBKTQFE5S43QVCAHXANCNFSM4JQXWZ5Q.

[Bo98]

Okay thank you for that. Sounds like a couple of scenarios to consider. PostGIS being a major version upgrade also required a full database dump and restore which is the most complicated part.

With all this said, apparently they have dropped the minor version in the library file. So 3.0 -> 3.1 will be pain free if a PostgreSQL major version upgrade happened at the same time. I know that doesn't help for this occasion but is worth noting for the future.

[masterkain]

I got the upgrade from 11 to 12 fail with

lc_ctype values for database "postgres" do not match:  old "UTF-8", new "en_US.UTF-8"
Failure, exiting
Error: Upgrading postgresql data from 11 to 12 failed!

[Bo98]

Looks like a separate issue. Can I get the output of psql -l on the original database?

Unless you have the old server still up, the process is probably something like:

/usr/local/opt/postgresql@11/bin/pg_ctl -D /usr/local/var/postgres.old start
/usr/local/opt/postgresql@11/bin/psql -l
/usr/local/opt/postgresql@11/bin/pg_ctl -D /usr/local/var/postgres.old stop

[Bo98]

Actually I see you opened a new issue so let's move it there: #47110.

[pauldaustin]

I think that the problem is that the pg_upgrade is doing a binary custom format backup. This is including the full actual definition of all the functions in the extension.

What I was able to do was copy across from a second machine the postgres 11.5_1 and postgis 2.5.3 and also do cp 2.5.3/lib/postgresql/2.5 /usr/local/lib/postgresql so that PostgreSQL 11 could find the extension.

Then export a SQL dump using the following

pg_dump --file "~/bcdem.sql" --host "localhost" --port "5432" --verbose --format=p "bcdem"

In there it just has the following line for the extension, so not tied to a specific version of the extension.

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;

The key thing is to make sure that the version of postgres that you're backing up has access to the original versions of the extension libraries.

I'm wondering is it possible for a specific formula to instruct home-brew to not auto clean the previous versions of a formula (auto clean was introduced in 2.0.0 I think). That way the old postgres and postgis would be available for the update/restore.

[pauldaustin]

I created the following shell script to backup and restore each database.

DB=...
USER=...
pg_dump --file "/usr/local/var/postgres/$DB.sql" --host "localhost" --port "54320" --username "$USER" --no-password --verbose --format=p --create "$DB"
psql -U $USER -d postgres < $DB.sql

[ecatkins]

Sorry, asking the noob question here. I'm unclear what I should be doing if I find myself in this situation?

I currently have an unusable database I believe. I tried to downgrade to postgresql version 11, but now I found myself with the incompatible postgis 3.0, as 2.5 is no longer available on home brew?

[Rikuoja]

@ecatkins This has caused a considerable amount of trouble for me too. The only way to go back seems to be to 1) clone homebrew-core locally, 2) check which commit is the last one with postgis 2.5 with git log master -- Formula/postgis.rb, 3) then checkout that commit locally and 4) do an install from the old homebrew commit with brew install Formula/postgis.rb.

On top of that, I found out only belatedly that my projects crash with Geos 3.0, which brew automatically updates when any version of Postgis is installed, so it seems I am unable to roll back to working Postgis even with the method above. Don't have a solution for myself at the moment, because even the old Postgis can no longer find the old Geos version, even if I install it. All in all, support for complex version dependencies, where you know you want particular versions for each dependency, is sorely lacking in Homebrew; this is because each particular formula automatically depends on latest versions of other formulae, even when the user might not be able to use them for unrelated reasons.

[pauldaustin]

Doesn't help for this upgrade, but pin postgres and postgis so it doesn't auto upgrade. Then you can backup the old one (databases and the application code). Then unpin upgrade and restore.

brew pin postgis postgresql

[SMillerDev]

If you need specific older versions your best option will be to maintain a tap. https://docs.brew.sh/How-to-Create-and-Maintain-a-Tap will help you with that, and you could use the brew extract command to copy over old versions of formula.

[ecatkins]

@Rikuoja I have just had some success by uninstalling postGIS on brew, and recompiling v2.5 from source (which FYI wasn't straight forward for me, I had to do a couple of hacky things to get it to work)

[trcull]

I also got totally hosed by this just now. First, accidentally upgraded my postgres from 11.6 to 12 when I'd mean to just reinstall 11.6. Then the newly upgraded 12 was trying to find the old postgis 2.5 libraries and was failing and so the whole database was now hosed. Tried to fix it by running brew install postgis, which now brought down postgis 3.0.0. But my app actually didn't work with the 12/3.0.0 combination (and, anyway, my prod environment is 11.6/2.5), so tried to downgrade back to postpresql@11, but there was no "normal" way to force postgis back to 2.5, so I forced it back there by brew installing to the specific postgis 2.5 commit, which kind of worked but then postgis 2.5 listed postgres (no version) as a dependency, which led to postgres 12 being installed again even though I didn't want it installed. And now I'm back to the hosed database I had in the beginning.

In short, it seems that, since Oct, anyone who touches either their postgres or their postgis brews is very likely to hose themselves completely.

[JonRowe]

For future travellers, I added older versions of the dependencies required through manual copying of the files. This allowed me to at least start postgres and copy old dbs, the automatic tool still didn't work correctly. The files are here.

[devjones]

Here are the steps I took to go back to postgres 11 AND postgis 2.5:

Following up on @Rikuoja's comment to locally checkout a postgis 2.5 formula:
1) git clone https://github.com/Homebrew/homebrew-core.git (clone homebrew locally)
2) git checkout bca96164362bfa3 (postgis 2.5.3 commit)
3) brew install Formula/postgis.rb (will install postgis 2.5.3)
4) brew services stop postgresql (stop any postgresql server)
5) cp -rf /usr/local/Cellar/postgresql@11/11.6 /usr/local/Cellar/postgresql/
6) brew switch postgresql 11.6
7) brew services start postgresql

I would also recommend pinning postgresql and postgis to avoid future problems:
8) brew pin postgis postgresql

Note:
Postgresql is by far the most important brew formula I use and I almost never want to automatically upgrade it, since it not only interfaces with system libraries but more importantly my local data. And I always want a deterministic version of the local database version in sync with whatever remote apps I'm working with.

I suppose remembering to pin these key brew formulas is the only solution right now...

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.

[JonRowe]

It has no recent activity because no-one is working on it but its still an issue stale-bot!

[SMillerDev]

Homebrew doesn't keep issues around just because. Unless someone is actively working on it, stalebot was correct.

[pennstatephil]

@devjones I'm hoping you can help me unravel this mystery-- I've followed your instructions and am still having issues.

after starting postgresql, I try to connect with psql and get:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

So I try to start the server manually:

pg_ctl -D /usr/local/var/postgres start
waiting for server to start....2020-01-30 16:28:48.110 MST [43934] FATAL:  database files are incompatible with server
2020-01-30 16:28:48.110 MST [43934] DETAIL:  The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 11.6.
 stopped waiting
pg_ctl: could not start server
Examine the log output.

At this point, I have no idea what to do. I started by removing all versions of postgres/postgis via brew, installed v11, then followed the instructions (which caused v12 to be installed as a dependency).

I was able to brew services start postgresql@11, and could connect with psql normally. However, when I try to enable postGIS (via create extension if not exists postgis), I get:

[ERROR] Message    : ERROR: could not open extension control file "/usr/local/opt/postgresql@11/share/postgresql@11/extension/postgis.control": No such file or directory

Any ideas?

[JonRowe]

@pennstatephil usually the trick would be to use brew postgresql-upgrade-database but that is broken for databases using postgis. You need to install postgres@11 manually and then install postgis into it by hand, my modified formulas for this are here.

[pennstatephil]

@JonRowe this is great! I missed your comment on Dec 24th. Would love to see these merged into the repo so others don't need to go through this mess.

[darkmoves]

I've just stumble on this issue today. Is there an open issue, or possible a PR, meant to address this conflict?

[JonRowe]

@darkmoves This is the issue about it, its not resolved to the best of my knowledge, home brew closed this issue because no one was actively fixing it.

[jaydorsey]

There is definitely a problem upgrading from 11 to 12 when you have postgis installed, at least under certain scenarios; we've had some install it without issues.

@pennstatephil I'm seeing the same issue with the postgis.control file message, when trying to install the postgis extensions in v11. We didn't find a resolution, but instead opted to (since this was locally) uninstall postgres 11, and force update to 12 and create new tables & data sources

If I did it over again, I'd use pg_dump to create snapshots of the tables I wanted, uninstall 11, install 12, install postgis, then load the data that I'd dumped. I know that's not feasible for certain scenarios but in my case it was local development (hopefully nobody uses homebrew on a production server)

[pennstatephil]

Yeah, for posterity, I ended up switching to using a docker image of postgis locally. It has simplified things quite a bit.