CODEWITHSUNDEEP
postgresqlpsqlpostgres.app
Ryan Rich
Ryan Rich

Reputation: 12055

psql: FATAL: database "<user>" does not exist

I'm using Postgres.app for Mac. I've used it in the past on other machines but it's giving me some trouble when installing on my MacBook. I've installed the application and I ran:

psql -h localhost

It returns:

psql: FATAL:  database "<user>" does not exist

It seems I can't even run the console to create the database that it's attempting to find. The same thing happens when I just run:

psql

or if I launch psql from the application drop down menu:

Machine stats:

Any help is appreciated.

I've also attempted to install PostgreSQL via Homebrew and I'm getting the same issue. I've also read the application's documentation that states:

When Postgres.app first starts up, it creates the $USER database, which is the default database for psql when none is specified. The default user is $USER, with no password.

So it would seem the application is not creating $USER. However, I've installed->uninstalled-reinstalled several times now so it must be something with my machine.

I found the answer but I'm not sure exactly how it works as the user who answered on this thread -> Getting Postgresql Running In Mac: Database "postgres" does not exist didn't follow up. I used the following command to get psql to open:

psql -d template1

Upvotes: 1027

Views: 681547

Answers (25)

Cristea
Cristea

Reputation: 1077

Step 1:

psql -d template1

now you should be on psql terminal

Step 2:

CREATE DATABASE <db_name>;

make sure you use semicolon (;) after the database name;

optional: on psql terminal type \ls or \l to list all the databases;

Step 3:

psql -h localhost

now you should be connected;

Upvotes: 28

zhisme
zhisme

Reputation: 2800

tldr;

psql postgres

I found the simple and direct answer I was looking for. This works on macOS with PostgreSQL installed using the command brew install postgresql

long version

psql is the client that accesses the default postgresql database created by the installation script provided by the Homebrew package (brew). So, running the command psql means running the client program and connecting to the database named as the second argument, which is postgresql.

Regarding the issue mentioned in the subject, the tutorial (which I encourage you to read before posting on StackOverflow) precisely describes and provides the solution. Let's quote:

createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "joe" does not exist

where your own login name is mentioned. This will happen if the administrator has not created a PostgreSQL user account for you. (PostgreSQL user accounts are distinct from operating system user accounts.) If you are the administrator, see Chapter 22 for help creating accounts. You will need to become the operating system user under which PostgreSQL was installed (usually postgres) to create the first user account.

Quoting the tutorial once again, here is the solution to solve the problem.

To create a new database, in this example named joe, you use the following command:

$ createdb joe

And voilà, you can log into your scintillant psql and explore the database.

$ psql
psql (14.10 (Homebrew))
Type "help" for help.

postgres=#

Upvotes: 1

Zohaib Amir
Zohaib Amir

Reputation: 529

You can just run this command

createdb `whoami`

and then you can run psql command

Upvotes: 2

Gabriel soft
Gabriel soft

Reputation: 563

This worked for me when solving this problem

i ran sudo -i -u postgress --> to gain access to my postgres database.

Then enter your password.

it would allow you to now enter psql which would prompt you for other command

Cheers!

Upvotes: 0

Rithik Singh
Rithik Singh

Reputation: 115

Was running postgres in docker. In cli I was getting the error "root" role doesn't exist.

su - postgres

psql

solved the problem.

PostgreSQL has its own user on the system which is created when PostgreSQL is installed. The postgres user is able to log into PostgreSQL without using a password. No other user is able to log into PostgreSQL.

This means that before using PostgreSQL, you will need to switch to that user account with the command:

su - postgres You will then be able to log into the PosgreSQL client with the command:

psql You will not be able to access the database from the command line as any other user.

Upvotes: -1

John Doe
John Doe

Reputation: 219

This is a basic misunderstanding. Simply typing:

pgres

will result in this response:

pgres <db_name>

It will succeed without error if the user has the permissions to access the db.

One can go into the details of the exported environment variables but that's unnecessary .. this is too basic to fail for any other reason.

Upvotes: -22

Naveen Kashyap
Naveen Kashyap

Reputation: 372

you can set the database name you want to connect to in env variable PGDATABASE=database_name. If you dont set this psql default database name is as username. after setting this you don't have to createdb

Upvotes: -1

SuperNova
SuperNova

Reputation: 27466

Post installation of postgres, in my case version is 12.2, I did run the below command createdb.

$ createdb `whoami`

$ psql

psql (12.2)
Type "help" for help.

macuser=#

Upvotes: 29

Yonas Kassa
Yonas Kassa

Reputation: 3690

Not sure if it is already added in the answers, Anatolii Stepaniuk answer was very helpful which is the following.

psql -U Username postgres # when you have no databases yet

Upvotes: 8

hardik chugh
hardik chugh

Reputation: 1150

Try using-

psql -d postgres

I was also facing the same issue when I ran psql

Upvotes: 18

Sameer Kumar Choudhary
Sameer Kumar Choudhary

Reputation: 1581

Connect to postgres via existing superuser.

Create a Database by the name of user you are connecting through to postgres.

create database username;

Now try to connect via username

Upvotes: 0

Dhananjay
Dhananjay

Reputation: 3608

From the terminal, just Run the command on your command prompt window. (Not inside psql). 

createdb <user>

And then try to run postgres again.

Upvotes: 340

user5683940
user5683940

Reputation:

Had the same problem, a simple psql -d postgres did it (Type the command in the terminal)

Upvotes: 11

unom
unom

Reputation: 11476

Had this problem when installing postgresql via homebrew.

Had to create the default "postgres" super user with:

createuser --interactive postgres answer y to for super user

createuser --interactive user answer y to for super user

Upvotes: -1

Dinesh Pallapa
Dinesh Pallapa

Reputation: 1212

I faced the same error when I trying to open postgresql on mac

psql: FATAL:  database "user" does not exist

I found this simple command to solve it:

method1

$ createdb --owner=postgres --encoding=utf8 user

and type

 psql

Method 2:

psql -d postgres

Upvotes: 28

Sky Notify
Sky Notify

Reputation: 919

First off, it's helpful to create a database named the same as your current use, to prevent the error when you just want to use the default database and create new tables without declaring the name of a db explicitly.

Replace "skynotify" with your username:

psql -d postgres -c "CREATE DATABASE skynotify ENCODING 'UTF-8';"

-d explicitly declares which database to use as the default for SQL statements that don't explicitly include a db name during this interactive session.

BASICS FOR GETTING A CLEAR PICTURE OF WHAT YOUR PostgresQL SERVER has in it.

You must connect to an existing database to use psql interactively. Fortunately, you can ask psql for a list of databases:

psql -l

.

                                          List of databases
               Name               | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 
----------------------------------+-----------+----------+-------------+-------------+-------------------
 skynotify                        | skynotify | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 myapp_dev                        | skynotify | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres                         | skynotify | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 ruby-getting-started_development | skynotify | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0                        | skynotify | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/skynotify          +
                                  |           |          |             |             | skynotify=CTc/skynotify
 template1                        | skynotify | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/skynotify          +
                                  |           |          |             |             | skynotify=CTc/skynotify
(6 rows)

This does NOT start the interactive console, it just outputs a text based table to the terminal.

As another answers says, postgres is always created, so you should use it as your failsafe database when you just want to get the console started to work on other databases. If it isn't there, then list the databases and then use any one of them.

In a similar fashion, select tables from a database:

psql -d postgres -c "\dt;"

My "postgres" database has no tables, but any database that does will output a text based table to the terminal (standard out).

And for completeness, we can select all rows from a table too:

psql -d ruby-getting-started_development -c "SELECT * FROM widgets;"

.

 id | name | description | stock | created_at | updated_at 
----+------+-------------+-------+------------+------------
(0 rows)

Even if there are zero rows returned, you'll get the field names.

If your tables have more than a dozen rows, or you're not sure, it'll be more useful to start with a count of rows to understand how much data is in your database:

 psql -d ruby-getting-started_development -c "SELECT count(*) FROM widgets;"

.

 count 
-------
     0
(1 row)

And don't that that "1 row" confuse you, it just represents how many rows are returned by the query, but the 1 row contains the count you want, which is 0 in this example.

NOTE: a db created without an owner defined will be owned by the current user.

Upvotes: 3

user686249
user686249

Reputation: 679

As the createdb documentation states:

The first database is always created by the initdb command when the data storage area is initialized... This database is called postgres.

So if certain OS/postgresql distributions do that differently, it is certainly not the default/standard (just verified that initdb on openSUSE 13.1 creates the DB "postgres", but not "<user>"). Long story short, psql -d postgres is expected to be used when using a user other than "postgres".

Obviously the accepted answer, running createdb to create a DB named like the user, works as well, but creates a superfluous DB.

Upvotes: 5

gogasca
gogasca

Reputation: 10058

Login using default template1 database:

#psql -d template1
#template1=# \l

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

Create a database with your userId:

template1=# CREATE DATABASE gogasca WITH OWNER gogasca ENCODING 'UTF8';
CREATE DATABASE

Quit and then login again

template1=# \q
gonzo:~ gogasca$ psql -h localhost
psql (9.4.0)
Type "help" for help.

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

Upvotes: 60

user286539
user286539

Reputation: 959

  1. Login as default user: sudo -i -u postgres
  2. Create new User: createuser --interactive
  3. When prompted for role name, enter linux username, and select Yes to superuser question.
  4. Still logged in as postgres user, create a database: createdb <username_from_step_3>
  5. Confirm error(s) are gone by entering: psql at the command prompt.
  6. Output should show psql (x.x.x) Type "help" for help.

Upvotes: 85

Andreas Covidiot
Andreas Covidiot

Reputation: 4755

had the problem with using the JDBC driver, so one just has to add the database (maybe redundantly depending on the tool you may use) after the host name in the URL, e.g. jdbc:postgres://<host(:port)>/<db-name>

further details are documented here: http://www.postgresql.org/docs/7.4/static/jdbc-use.html#JDBC-CONNECT

Upvotes: 2

sdhca
sdhca

Reputation: 81

This error can also occur if the environment variable PGDATABASE is set to the name of a database that does not exist.

On OSX, I saw the following error while trying to launch psql from the Postgress.app menu:

psql: FATAL: database "otherdb" does not exist

The solution to the error was to remove export PGDATABASE=otherdb from ~/.bash_profile:

Further, if PGUSER is set to something other than your username, the following error will occur:

psql: FATAL: role "note" does not exist

The solution is to remove export PGUSER=notme from ~/.bash_profile.

Upvotes: 8

Babken Vardanyan
Babken Vardanyan

Reputation: 15040

Since this question is the first in search results, I'll put a different solution for a different problem here anyway, in order not to have a duplicate title.

The same error message can come up when running a query file in psql without specifying a database. Since there is no use statement in postgresql, we have to specify the database on the command line, for example:

psql -d db_name -f query_file.sql

Upvotes: 4

matw50
matw50

Reputation: 1

I still had the issue above after installing postgresql using homebrew - I resolved it by putting /usr/local/bin in my path before /usr/bin

Upvotes: -3

Aneer Geek
Aneer Geek

Reputation: 2995

By default, postgres tries to connect to a database with the same name as your user. To prevent this default behaviour, just specify user and database:

psql -U Username DatabaseName

Upvotes: 267

Kirk Roybal
Kirk Roybal

Reputation: 17837

It appears that your package manager failed to create the database named $user for you. The reason that

psql -d template1

works for you is that template1 is a database created by postgres itself, and is present on all installations. You are apparently able to log in to template1, so you must have some rights assigned to you by the database. Try this at a shell prompt:

createdb

and then see if you can log in again with 

psql -h localhost

This will simply create a database for your login user, which I think is what you are looking for. If createdb fails, then you don't have enough rights to make your own database, and you will have to figure out how to fix the homebrew package.

Upvotes: 1520

Related Questions