Postgresapp: Running pg_restore from 12 on psql server 12 causes process end with "a worker process died unexpectedly"

[PikachuEXE]

I am reporting here instead of on https://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
Since I did a successful parallel restore on ubuntu with pg_restore from 12 on psql server 12
which means this issue could be a build issue

I have tried https://github.com/PostgresApp/PostgresApp/releases/tag/v2.3.1 too, same result

Env

• PostgreSQL Server & psql:
  https://github.com/PostgresApp/PostgresApp/releases/tag/v2.3.2
  Postgres-2.3.2-10-11-12.dmg
• OSX 10.14.6

Failed case

/Applications/Postgres.app/Contents/Versions/12/bin/pg_restore --verbose --clean --no-acl --no-owner -h localhost -U pikachuexe -d spacious_production_temp --jobs=8 /path/to/backup.dump

Log

... (Too long, truncated)
pg_restore: processing item 6166 DEFAULT white_label_product_neighbourhoods id
pg_restore: creating DEFAULT "public.white_label_product_neighbourhoods id"
pg_restore: entering main parallel loop
pg_restore: launching item 7751 TABLE DATA listing_searches
pg_restore: launching item 7803 TABLE DATA property_images
pg_restore: launching item 7759 TABLE DATA listing_tracking_events
pg_restore: launching item 7932 TABLE DATA user_access_tokens
pg_restore: launching item 7653 TABLE DATA building_tracking_events
pg_restore: launching item 7763 TABLE DATA listings
pg_restore: launching item 7973 TABLE DATA listing_aggregated_tracking_count_entry_records
pg_restore: launching item 7747 TABLE DATA listing_search_neighbourhood_relationships
pg_restore: error: a worker process died unexpectedly

(No error logged in log file /Users/pikachuexe/Library/Application Support/Postgres/var-12/postgresql.log)

Success case

/Applications/Postgres.app/Contents/Versions/11/bin/pg_restore --verbose --clean --no-acl --no-owner -h /tmp -U pikachuexe -d spacious_production_temp --jobs=8 /path/to/backup.dump

or

# 1 job only, even setting 2 job makes fail
/Applications/Postgres.app/Contents/Versions/12/bin/pg_restore --verbose --clean --no-acl --no-owner -h localhost -U pikachuexe -d spacious_production_temp --jobs=1 /path/to/backup.dump

[cbandy]

@PikachuEXE do you have anything in ~/Library/Logs/DiagnosticReports or /Library/Logs/DiagnosticReports from that time? (The filename will probably start with pg_restore.)

[PikachuEXE]

I found many .crash files in ~/Library/Logs/DiagnosticReports prefixed with pg_restore:
Archive.zip

[cbandy]

I checked only two of the crash reports.¹ Both pass through gss_acquire_cred of com.apple.GSS before crashing in libdispatch.dylib. This sounds like an issue that has been discussed on the mailing list. See if setting PGGSSENCMODE="disable" environment variable resolves things.

Related: https://github.com/Homebrew/homebrew-core/pull/47494#issuecomment-562400120

¹ pg_restore_2019-12-06-141519_PikachuEXEs-MBP-2018.crash,
pg_restore_2019-12-06-141548_PikachuEXEs-MBP-2018.crash

[cbandy]

@PikachuEXE in your examples, Failed case and the first Success case appear to be the same command. Did the environment differ?

If I understand correctly, --jobs=1 is the only difference between the Failed case and the second Success case?

[jakob]

This makes me think whether we should even build Postgres.app with support for Kerberos? How many people use Kerberos vs. how many people stumble across problems with Kerberos?

It seems that the new encryption method has not been very well tested on macOS. The developers contributing to the feature don't test on macOS, and nobody seems to care at all about compatibility with the Kerberos library that macOS ships.

I spent a lot of time to debug the other GSS issue (#537), and the response on the mailing list was to use gssencmode=disable as a workaround. I hope they at least fix the server side problem.

Another possibility would be to change the default setting to gssencmode=disable, but that would probably require patching libpq in some way, and it might take away a bit of the "it just works" magic of Kerberos (assuming that it actually works).

[PikachuEXE]

@cbandy
First success case updated - the host should be /tmp (unix socket)
Second success case = Failure case but --jobs=1

[PikachuEXE]

Also the gssencmode=disable workaround works

PGGSSENCMODE="disable" pg_restore --verbose --clean --if-exists --create --no-acl --no-owner -h localhost -U pikachuexe -d spacious_production_temp --jobs=8 /path/to/backup.dump

[cbandy]

First success case updated - the host should be /tmp (unix socket)
Second success case = Failure case but --jobs=1

Also the gssencmode=disable workaround works

PGGSSENCMODE="disable" pg_restore --verbose --clean --if-exists --create --no-acl --no-owner -h localhost -U pikachuexe -d spacious_production_temp --jobs=8 /path/to/backup.dump

Excellent. This all confirms that Kerberos.framework+fork() triggers the crash.

This makes me think whether we should even build Postgres.app with support for Kerberos? How many people use Kerberos vs. how many people stumble across problems with Kerberos?

I have no idea how much demand there is for Kerberos. My _guess_ is that demand is low. My suspicion is that Kerberos functionality has been broken for a long time and no one has been trying to use it. Reports are popping up now because, _by default_, PostgreSQL 12 attempts to use some Kerberos functionality that it didn't before.

One way to find the answer is to build without GSSAPI and see how many users report the lack of support. 😇

I suspect the server will complain noticeably if one tries to use features that aren't compiled in (e.g. hostgssenc in pg_hba.conf) but I haven't tested it. I do know how to trigger such an error on the client: use gssencmode=require. (The behavior is similar to wanting TLS without compiled support.)

[mhanberg]

I am seeing the same behavior with Postgres 10. I was able to work around by running without the --jobs flag.

pg_restore latest.dump --verbose --clean --no-acl --no-owner --dbname <db name>

I ran into this when using parity, which calls pg_restore under the hood. It seems there is a similarly documented issue over there.

[etcook]

@mhanberg I believe the issue you linked to is distinct from this one based on the error reported (more specifically, lack thereof). As I clarified in the other issue, I've only had issues with restores from Heroku backups specifically, so it's either localized to Heroku or whatever flags they're using to generate the backup. Self-created backups and restores work with the jobs flag.

[jakob]

Short update: As discussed in #539, I've removed support for Kerberos from Postgres.app. It's been a bit more than a month, and so far noone has complained.

The consensus on the hackers mailing list also seems to be that the default Kerberos library is broken on macOS, so I really see no point in linking it.

Since removing Kerberos fixes the problem, I'm closing this issue now.

@cbandy: Thanks a lot for debugging this issue, I wouldn't have known where to even start with this.