Psql error database does not exist

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.