Common PostgreSQL Error Messages and Possible Solutions

Below is a resource I've put together for newer PostgreSQL database users. Listed are some of the most common error messages you may encounter. While the error messages do in fact tell you exactly what is causing the problem, I find that users often don't know what next steps to take in resolving the issue.

If you there is a common error you would like me to include in this list or you find anything inaccurate on this page, please drop me an E-mail at info@revsys.com.

List of Errors

Error: psql: FATAL: database "root" does not exist

Common Cause: A database named 'root' does not exist on your system

This error trips up new PostgreSQL users quite often. When you simply run psql from the command line it, by default, attempts to log you into a database with the same name as your current Unix user name. In this case that is 'root', but it could be 'postgres', or 'bob'.

If you are setting up your database for the first time, you will need to become the PostgreSQL user ( typically 'postgres' ) which can be accomplished by either:

  • logging in as that user
  • su'ing to root and as root su'ing to the postgres user

Once you are the postgres user you will need to setup one or more databases and some users. See the following articles for more information on this:

Error: 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"?

Common Cause: The postmaster or PostgreSQL's server daemon process is not running.

Typically this error means PostgreSQL is not currently running on the system you are logged into. You will need to start the daemon, which is typically done through your distribution's init system.

The easiest way to determine if PostgreSQL is running is to look for it using ps. For example when I run the command on my Fedora system:

ps auxw | grep post

I see the following processes:

[frank@host:~]$ ps auxw | grep post
postgres  4335  2.4  0.2  27704  2936 ?        S    14:47   0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres  4337  0.0  0.0   9972   548 ?        S    14:47   0:00 postgres: logger process
postgres  4339  0.0  0.0  27704   804 ?        S    14:47   0:00 postgres: writer process
postgres  4340  0.0  0.0  10972   544 ?        S    14:47   0:00 postgres: stats buffer process
postgres  4341  0.0  0.0  10148   668 ?        S    14:47   0:00 postgres: stats collector process
frank     4346  0.0  0.0   3912   676 pts/5    S+   14:47   0:00 grep post
[frank@host:~]$

Which as you can see means PostgreSQL is running on my local system. If we had received no results or just the final result line then we would no for certain that it is not running on this system.

Error: psql: could not connect to server: Connection refused Is the server running on host "192.168.0.1" and accepting TCP/IP connections on port 5432?

Common Cause: The postmaster or PostgreSQL's server daemon process is not running or configured incorrectly.

When you receive this error most of the time it is due to not having PostgreSQL configured to allow TCP/IP connections or at least not connections from your particular workstation. If you have verified that postmaster is indeed running on the host you are trying to connect to then here is a list of common causes to this problem:

  • postgresql.conf not setup to allow TCP/IP connections. You'll want to look at the listen_address configuration parameter.
  • postgresql.conf is not setup to allow connections on a non-standard port number. To determine this look at the port configuration option.
  • authentication rules in PostgreSQL's access configuration file (pg_hba.conf) are not setup to allow either your uses or IP address to connect to that database. See the official documentation for more information on setting up your pg_hba.conf properly.
  • ensure that there are no firewalls, such as iptables that are keeping your local system from even establishing a connection to the remote host

Error: user X has no permission for table Y

Common Cause: You are not the owner of the table or the owner has not granted you the proper permissions

Depending on what sort of authentication methods (trust, md5, etc. ) you have setup, you might receive the following error when attempting to work with a particular table. This is usually caused by creating the table as say user 'postgres', and then attempting to use it as user 'bob'. There are two possible solutions to this problem:

  • Change the ownership of the table to the user you need. You will need to login as the current owner or a superuser account and execute ALTER TABLE table_name OWNER TO new_owner_name
  • You can also GRANT other users to access this account with GRANT ALL ON table_name TO user_name. Please note this will give the user full access to select, insert, update, and delete from this table. You can limit their access to SELECT access with GRANT SELECT ON table_name TO user_name

For more information please see the following documentation:

Thanks go to Jacob Kaplan-Moss for suggesting this error be included here.

Frank Wiles has been an avid user of PostgreSQL for over 10 years. Revolution Systems provides PostgreSQL Optimizations and commercial support of PostgreSQL.