Common PostgreSQL Error Messages and Possible Solutions
by Frank Wiles
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 email@example.com.
List of Errors
- psql: FATAL: database "root" does not exist
- 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"?
- 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?
- user X has no permission for table Y
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
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
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
ps. For example when I run the command on my
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
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.confnot setup to allow TCP/IP connections. You'll want to look at the
postgresql.confis not setup to allow connections on a non-standard port number. To determine this look at the
authentication rules in PostgreSQL's access configuration
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
ensure that there are no firewalls, such as
iptablesthat 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
GRANTother 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
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 TABLEcommand reference
Thanks go to Jacob Kaplan-Moss for suggesting this error be included here.