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
info@revsys.com.

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:

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