others-how to solve psql: FATAL: database does not exist in postgresql database

Problem

Assume that we have installed a postgresql server in ubuntu system, and we have done these jobs:

• Created a system/postgresql user named ‘bswen’
• Created a postgresql database named ‘bswendb’
• Changed the owner of the database ‘bswendb’ to user ‘bswen’

Now, we switched to user ‘bswen’ and then execute psql command in postgresql database environment , we got this problem:

root@launch-advisor-20191120:~# sudo -i -u bswen
bswen@launch-advisor-20191120:~$ psql
psql: FATAL:  database "bswen" does not exist

Environment

• Postgresql 10.15
• Ubuntu 18.04.4 LTS

Reason

By default, postgresql would expect the database name to be the same as the username of current logged in system user, e.g. the ‘bswen’ user need the ‘bswen’ database.

However, we did not create the ‘bswen’ databsae, but created the ‘bswendb’ database. To prove this, we can switch to the user ‘postgres’ in the system,and list databases, we get this result:

root@launch-advisor-20191120:~# sudo -i -u postgres
postgres@launch-advisor-20191120:~$ psql
postgres=# \l 

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 bswendb   | bswen    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
   

Notice that there is no database named ‘bswen’ exists in the postgresql server, there is only ‘bswendb’ database in the server. So , how to solve this problem?

Solution

Option #1: Create a database named ‘bswen’ in the postgresql server

# switch to the postgres user and enter the postgresql command context
root@launch-advisor-20191120:~# sudo -i -u postgres
postgres@launch-advisor-20191120:~$ psql

# create database bswen
postgres@launch-advisor-20191120:~$ createdb bswen

# change the owner of bswen database to user bswen
postgres@launch-advisor-20191120:~$ psql
postgres=# ALTER DATABASE bswen OWNER TO bswen;
ALTER DATABASE

# check the databases
postgres=# \l

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 bswen     | bswen    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 bswendb   | bswen    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

Now you can verify the effect:

# switch to the postgres user and enter the postgresql command context
root@launch-advisor-20191120:~# sudo -i -u bswen
bswen@launch-advisor-20191120:~$ psql
psql (10.15 (Ubuntu 10.15-0ubuntu0.18.04.1))
Type "help" for help.

bswen=> \conninfo
You are connected to database "bswen" as user "bswen" via socket in "/var/run/postgresql" at port "5432".
bswen=>

Success!

Option #2: Specify the database name when using psql command

If you don’t want to create database ‘bswen’ , and insist on using the database ‘bswendb’, you can do as follows:

# switch to the postgres user and enter the postgresql command context
root@launch-advisor-20191120:~# sudo -i -u bswen

# specify the database when using psql command
bswen@launch-advisor-20191120:~$ psql -d bswendb
psql (10.15 (Ubuntu 10.15-0ubuntu0.18.04.1))
Type "help" for help.

bswendb=> \conninfo
You are connected to database "bswendb" as user "bswen" via socket in "/var/run/postgresql" at port "5432".
bswendb=>

Notice that we used the command:

psql -d bswendb

You can view the help of psql like this:

 psql --help
 
 -d, --dbname=DBNAME      database name to connect to (default: "bswen")