psql: FATAL: role "postgres" does not exist

Question

I'm a postgres novice.

I installed the postgres.app for mac. I was playing around with the psql commands and I accidentally dropped the postgres database. I don't know what was in it.

I'm currently working on a tutorial: http://www.rosslaird.com/blog/building-a-project-with-mezzanine/

And I'm stuck at sudo -u postgres psql postgres

ERROR MESSAGE: psql: FATAL: role "postgres" does not exist

$ which psql

/Applications/Postgres.app/Contents/MacOS/bin/psql

This is what prints out of psql -l

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

So what are the steps I should take? Delete an everything related to psql and reinstall everything?

Thanks for the help guys!

Answer 1

NOTE: If you installed postgres using homebrew, see the comment from @user3402754 below.

Note that the error message does NOT talk about a missing database, it talks about a missing role. Later in the login process it might also stumble over the missing database.

But the first step is to check the missing role: What is the output within psql of the command \du ? On my Ubuntu system the relevant line looks like this:

                              List of roles
 Role name |            Attributes             | Member of 
-----------+-----------------------------------+-----------
 postgres  | Superuser, Create role, Create DB | {}        

If there is not at least one role with superuser, then you have a problem :-)

If there is one, you can use that to login. And looking at the output of your \l command: The permissions for user on the template0 and template1 databases are the same as on my Ubuntu system for the superuser postgres. So I think your setup simple uses user as the superuser. So you could try this command to login:

sudo -u user psql user

If user is really the DB superuser you can create another DB superuser and a private, empty database for him:

CREATE USER postgres SUPERUSER;
CREATE DATABASE postgres WITH OWNER postgres;

But since your postgres.app setup does not seem to do this, you also should not. Simple adapt the tutorial.

Answer 2

The key is "I installed the postgres.app for mac." This application sets up the local PostgreSQL installation with a database superuser whose role name is the same as your login (short) name.

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.

Some scripts (e.g., a database backup created with pgdump on a Linux systsem) and tutorials will assume the superuser has the traditional role name of postgres.

You can make your local install look a bit more traditional and avoid these problems by doing a one time:

/Applications/Postgres.app/Contents/Versions/9.*/bin/createuser -s postgres

which will make those FATAL: role "postgres" does not exist go away.

Answer 3

For MAC:

1. Install Homebrew
2. brew install postgres
3. initdb /usr/local/var/postgres
4. /usr/local/Cellar/postgresql/<version>/bin/createuser -s postgres or /usr/local/opt/postgres/bin/createuser -s postgres which will just use the latest version.
5. start postgres server manually: pg_ctl -D /usr/local/var/postgres start

---

To start server at startup

• mkdir -p ~/Library/LaunchAgents
• ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
• launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

---

Now, it is set up, login using psql -U postgres -h localhost or use PgAdmin for GUI.

By default user postgres will not have any login password.

Check this site for more articles like this: https://medium.com/@Nithanaroy/installing-postgres-on-mac-18f017c5d3f7

Answer 4

createuser postgres --interactive

or make a superuser postgresl just with

createuser postgres -s