PostgreSQL - Troubleshooting04 Apr 2017
- problems with new homebrew versioning scheme
- psql: could not connect to server: Connection refused
- command not found: psql
- psql: could not connect to server: No such file or director
- Error: Invalid data directory for cluster 10 main
- FATAL: remaining connection slots are reserved for non-replication superuser connections
- FATAL: sorry, too many clients already
- could not access the server configuration file “/etc/postgresql/12/main/postgresql.conf”
problems with new homebrew versioning scheme
brew upgrade has created quite a mess for me because of a new
postgresql95 formula is replaced with
(this is because homebrew/core now supports multiple versions). but this
migration was not smooth and resulted in many errors, to name a few:
invalid value for parameter "TimeZone": "UTC"
should be fixed by restarting service or the whole system according to SO.
command not found: psql
psqlmust have a symlink in /usr/local/bin/ (it has been added to
PATHin ~/.zshenv in my case) - it’s gone now for some mysterious reason.
psql: FATAL: database "db_name" does not exist
this is because running
brew upgradehas created new data directory for
email@example.com(/firstname.lastname@example.org) while all my databases are stored in /usr/local/var/postgres.
so this is what I did to fix problems mentioned above:
brew untap 'homebrew/versions'(it’s deprecated now)
brew untap 'caskroom/versions'(it’s deprecated now)
brew uninstall postgresql postgresql95 email@example.com(remove everything)
brew install firstname.lastname@example.org(install latest 9.5 version)
brew switch email@example.com 9.5.6(switch to latest 9.5 version)
brew prune firstname.lastname@example.org(remove old 9.5 versions - if any)
brew link email@example.com --force(create symlink in /usr/local/bin)
it’s not recommended though - it must be better to add bin directory of specific PostgreSQL installation to
PATHexplicitly in ~/.zshenv.
cd /usr/local/var && mv postgres firstname.lastname@example.org(rename directory with databases)
it might be necessary to remove existing email@example.com directory beforehand that could be created when upgrading PostgreSQL (but still double check it doesn’t contain any databases).
rm /usr/local/Cellar/postgresql95(remove symlink to
I guess it has been created for compatibility reasons.
gem uninstall pg && bundle(reinstall
postgresql formula still installs the latest version (9.6 as
of now) - all directories are named just
postgresql accordingly. but its
binaries are not symlinked into usr/bin/local directory by default (now they
all point to 9.5 installation) - if you need it run
brew link postgresql --force manually.
psql: could not connect to server: Connection refused
$ psql -d <DB_NAME> psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
PG::ConnectionBad: could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432?
the problem usually appears after hard reboot. the latter doesn’t allow PostgreSQL to exit gracefully and delete its PID files - postmaster.pid in particular. so upon reboot PostgreSQL thinks it’s still running and corresponding service fails to start.
so just delete that PID file and start the service:
$ rm /firstname.lastname@example.org/postmaster.pid $ brew services start email@example.com
or else try to restart the service (stopping the service might remove obsolete PID file - I didn’t try this method though):
$ brew services restart firstname.lastname@example.org
command not found: psql
$ psql --version zsh: command not found: psql
in my case only versioned formula of PostgreSQL (
email@example.com) was installed
but it didn’t create a symlink to
psql in /usr/local/bin/.
to solve this problem either:
install the latest version of PostgreSQL (
unversioned formula creates a symlink in /usr/local/bin/ automatically.
create symlinks for binaries from old version of PostgreSQL manually
$ ln -s /usr/local/Cellarfirstname.lastname@example.org/9.5.10/bin/psql /usr/local/bin/psql
add the whole bin/ from old version of PostgreSQL to
psql: could not connect to server: No such file or director
$ psql -d postgres 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"?
to diagnose this and similar problems run
postgres in the foreground (see
brew info postgresql output):
$ pg_ctl -D /usr/local/var/postgres start waiting for server to start....  FATAL: database files are incompatible with server  DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.3. stopped waiting pg_ctl: could not start server Examine the log output.
it turns out /usr/local/var/postgres contains data for PostgreSQL 9.6 (when
postgresql formula was installed, 9.6 was the latest version).
=> it’s necessary to migrate existing data from a previous major version (9.6)
to the latest one (10) (see
brew info postgresql output):
$ brew postgresql-upgrade-database ==> brew install email@example.com ... ==> Upgrading postgresql data from 9.6 to 10... Stopping `postgresql`... (might take a while) ==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql) ==> Moving postgresql data from /usr/local/var/postgres to /usr/local/var/postgres.old... The files belonging to this database system will be owned by user "tap". This user must also own the server process. ... ==> Upgraded postgresql data from 9.6 to 10! ==> Your postgresql 9.6 data remains at /usr/local/var/postgres.old ==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
this command will install a previous major version of PostgreSQL (if it has been uninstalled) which is required to upgrade database.
after upgrade is complete, you can remove old major version along with its data (/usr/local/var/postgres.old):
$ brew uninstall firstname.lastname@example.org $ rm -rf /usr/local/var/postgres.old
now make sure
postgresql service is started:
$ brew services list Name Status User Plist memcached started tap /Users/tap/Library/LaunchAgents/homebrew.mxcl.memcached.plist postgresql started tap /Users/tap/Library/LaunchAgents/homebrew.mxcl.postgresql.plist redis started tap /Users/tap/Library/LaunchAgents/homebrew.mxcl.redis.plist
and try to run
$ psql -d postgres psql (10.3) Type "help" for help. postgres=#
Error: Invalid data directory for cluster 10 main
$ psql -U sith_production -d sith_production Error: Invalid data directory for cluster 10 main
-h localhost option:
$ psql -U sith_production -d sith_production -h localhost / enter password for database user sith_production
for this to be possible it’s necessary to have this line in /etc/postgresql/10/main/pg_hba.conf:
local sith_production sith_production md5
postgresql service for changes to take effect.
Authentication methods details: trust - anyone who can connect to the server is authorized to access the database peer - use client’s operating system user name as database user name to access it. md5 - password-base authentication
FATAL: remaining connection slots are reserved for non-replication superuser connections
this error indicates you have run out of connections.
see PostgreSQL - Tuning on how to increase the maximum number of allowed connections.
see PostgreSQL - Monitoring on how to monitor open connections.
FATAL: sorry, too many clients already
most likely this error is also caused by a low number of allowed connections - see solution for the error above.
could not access the server configuration file “/etc/postgresql/12/main/postgresql.conf”
$ sudo -u postgres /usr/lib/postgresql/12/bin/postgres \ -D /var/lib/postgresql/12/main \ -c config_file=/etc/postgresql/12/main/postgresql.conf postgres: could not access the server configuration file "/etc/postgresql/12/main/postgresql.conf": No such file or directory
the problem was with already existing cluster.
first I reinstalled PostgreSQL manually:
$ sudo apt autoremove --purge postgresql-12 $ sudo rm -rf /etc/postgresql $ sudo rm -rf /run/postgresql $ sudo apt install postgresql-12
but cluster from previous installation must have remained in the system so a new cluster was not created when running the last command:
$ sudo apt install postgresql-12 ... Configuring already existing cluster (configuration: /etc/postgresql/12/main, data: /var/lib/postgresql/12/main, owner: 113:118) Error: move_conffile: required configuration file /var/lib/postgresql/12/main/postgresql.conf does not exist Error: could not create default cluster. Please create it manually with pg_createcluster 12 main --start or a similar command (see 'man pg_createcluster').
=> solution is to remove old clusters manually and run installation again:
$ sudo rm -rf /var/lib/postgresql $ sudo apt install postgresql-12
see the tip on how to remove PostgreSQL completely in PostgreSQL - Tips.