psql: FATAL: role "postgres" does not exist
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!
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.
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.
For MAC:
- Install Homebrew
brew install postgres
initdb /usr/local/var/postgres
-
/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. - 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
createuser postgres --interactive
or make a superuser postgresl just with
createuser postgres -s
This happens when you run initdb
with a user whose ID is not postgres
, without specifying the postgres
username with --username=postgres
or -U postgres
.
The database cluster is then created with the system's user account that you used to run initdb, and it is given superuser permissions.
To fix it, simply create a new user named postgres
with the option --superuser
using the createuser
utility that comes with Postgres. The utility can be found in the Postgres' bin
directory. e.g.
createuser --superuser postgres
If you have a custom hostname or port then be sure to set the appropriate options.
Don't forget to delete the other user account that was created for you by initdb.