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

User · Answer

On Ubuntu system  I purged the PostgreSQL and re-installed it  All the databases are restored  This solved the problem for me   Advice - Take the backup of the databases to be on the safer side

User · Answer

I became stuck on this issue having executed brew services stop postgresql the day prior   The day following  brew services start postgresql would not work  This is because as is shown when you install using homebrew  postgresql uses a launchd     which loads when your computer is powered on   resolution brew services start postgresql  Restart your computer

User · Answer

This is the only one that fixed it for me    createuser -s -U  USER

User · Answer

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

User · Answer

Running this on the command line should fix it   Applications Postgres app Contents Versions 9 4 bin createdb  lt Mac OSX Username Here gt

User · Answer

I needed to unset  PGUSER     unset PGUSER   createuser -s postgres

User · Answer

createuser postgres --interactive   or make a superuser postgresl just with  createuser postgres -s

User · Answer

If you installed postgres from brew  run this in your terminal    usr local opt postgres bin createuser -s postgres

User · Answer

For what it is worth  i have ubuntu and many packages installed and it went in conflict with it   For me the right answer was   sudo -i -u postgres-xc psql

User · Answer

For me  this code worked    Applications Postgres app Contents Versions 9 4 bin createuser -s postgres   it came from here  http   talk growstuff org t fatal-role-postgres-does-not-exist 216 4

User · Answer

First you need create a user   sudo -u postgres createuser --superuser  USER   After you create a database   sudo -u postgres createdb  USER   Change  USER to your system username   You can see the the complete solution here

User · Answer

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

User · Answer

For MAC    Install Homebrew  brew install postgres initdb  usr local var postgres  usr local Cellar postgresql  lt version gt  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

User · Answer

I don t think  that sudo is needed here because psql -l returns a list of databases   This tells me that initdb was run under the user s current user  not under the postgres user   You can just   psql   And continue the tutorial   I would suggest A H s general points of creating the postgres user and db because many applications may expect this to exist   A brief explanation   PostgreSQL will not run with administrative access to the operating system   Instead it runs with an ordinary user  and in order to support peer authentication  asking the OS who is trying to connect  it creates a user and db with the user that runs the initialization process   In this case it was your normal user

User · Answer

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

User · Answer

If you re using docker  make sure you re NOT using POSTGRES USER something else  as this variable is used by the standard image to know the name of the PostgreSQL admin user  default as postgres   In my case  I was using this variable with the intent to set another user to my specific database  but it ended up of course changing the main PostgreSQL user

User · Answer

The  du command return      Role name   postgres implicit files   And that command postgres    password postgres return error      ERROR   role  postgres  does not exist    But that postgres    password postgres implicit files run fine   Also after  sudo -u postgres createuser -s postgres the first variant also work

User · Answer

Dropping the postgres database doesn t really matter  This database is initially empty and its purpose is simply for the postgres user to have a kind of  home  to connect to  should it need one   Still you may recreate it with the SQL command CREATE DATABASE postgres    Note that the tutorial mentioned in the question is not written with postgres app in mind  Contrary to PostgreSQL for Unix in general  postgres app tries to look like a normal application as opposed to a service that would be run by a dedicated postgres user having different privileges than your normal user  postgres app is run and managed by your own account   So instead of this command  sudo -u postgres psql -U postgres  it would be more in the spirit of postgres app to just issue  psql  which automatically connects to a database matching your users s name  and with a db account of the same name that happens to be superuser  so it can do anything permissions-wise

[macos] psql: FATAL: role "postgres" does not exist

Examples related to macos

Examples related to postgresql

Examples related to terminal