Common PostgreSQL Error Messages and Possible Solutions

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:

• Common PostgreSQL Problem this blog post describes this error in more detail
• Database Roles and Privileges The official PostgreSQL documentation on setting up users and access permissions
• Managing Databases The official docs on creating and maintaining databases

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 27704  2936 14:47 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres  4337  0.0  9972   548 14:47 postgres: logger process
postgres  4339  0.0 27704   804 14:47 postgres: writer process
postgres  4340  0.0 10972   544 14:47 postgres: stats buffer process
postgres  4341  0.0 10148   668 14:47 postgres: stats collector process
frank     4346  0.0  3912   676 14:47 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 know 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:

• PostgreSQL roles and privileges explains users, roles, and database privileges
• PostgreSQL authentication
• ALTER TABLE command reference
• GRANT command reference

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