After so many attempts, I found the culprit, and it was config/puma.rb. The following two configs raise the issue:

workers 2
preload_app!

workers > 0 means Puma is in cluster mode.

preload_app! loads the whole Rails app once, then forks it into 2 workers.

As far as I understand, this version of pg gem is unable to handle this, and that's why it crashes.

Setting worker 0 fixes the issue.