130

I have just installed Postgres and have been tinkering with it and various configurations for 1-2 hours.

I am stuck on being unable to change to the postgres-user

$ su - postgres yields the following error: su: unknown login: postgres

$ sudo -u postgres psql yields the following error: sudo: unknown user: postgres

These attempts are made as the normal user. Trying them as root has the exact same results. I have installed postgres via Homebrew on OS X, and I have read the instructions multple times.

Improve this question
5
  • Are you just trying to run psql? Commented Jan 14, 2014 at 19:55
  • Well yes, I want to run as the proper postgres user. Commented Jan 14, 2014 at 19:57
  • On some Mac OS X flavours, with some installation methods, I think the user is created as postgres_ instead. Note trailing underscore. Also, are you sure you (or the installer)actually created the PostgreSQL instance and started the database? Commented Jan 15, 2014 at 2:32
  • 3
    I was too unable to switch to postgres user. Solved the problem after installing postgresql-server package. Commented May 22, 2019 at 4:43
  • @krystah I believe you should rather mark kangkyu's answer at the correct answer. That's the most appropriate solution to the above query. Commented Jan 3, 2021 at 18:00

11 Answers 11

Reset to default
109

psql: Logs me in with my default username

psql -U postgres: Logs me in as the postgres user

Sudo doesn't seem to be required for me.

I use Postgres.app for my OS X postgres database. It removed the headache of making sure the installation was working and the database server was launched properly. Check it out here: http://postgresapp.com

Edit: Credit to @Erwin Brandstetter for correcting my use of the arguments.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I installed using Brew and it didn't create a user called postgres or _postgres (as far as I can see) so this failed for me. @kangkyu solution explains and walks you through the issues - assuming you don't have postgres user then: psql postgres and psql -d postgres.
52

By psql --help, when you didn't set options for database name (without -d option) it would be your username, if you didn't do -U, the database username would be your username too, etc.

But by initdb (to create the first database) command it doesn't have your username as any database name. It has a database named postgres. The first database is always created by the initdb command when the data storage area is initialized. This database is called postgres.

So if you don't have another database named your username, you need to do psql -d postgres for psql command to work. And it seems it gives -d option by default, psql postgres also works.

If you have created another database names the same to your username, (it should be done with createdb) then you may command psql only. And it seems the first database user name sets as your machine username by brew.

psql -d <first database name> -U <first database user name>

or,

psql -d postgres -U <your machine username>
psql -d postgres

would work by default.

Improve this answer

1 Comment

Still works. I installed postgres 13 with homebrew and this one has worked for me.
19

OS X tends to prefix the system account names with "_"; you don't say what version of OS X you're using, but at least in 10.8 and 10.9 the _postgres user exists in a default install. Note that you won't be able to su to this account (except as root), since it doesn't have a password. sudo -u _postgres, on the other hand, should work fine.

Improve this answer

2 Comments

Thanks! This is the answer I was looking for. Is there anything I can do to rename the user to just "postgres"? Logging in as that _postgres user leads to psql complaining that the "_postgres" user doesn't exist. I'm trying to restore a clean postgres instance with a backup file from another database
@ChrisReyes Changing the name of system accounts may break system components that use them, so I'd strongly recommend against it. You could create another user account with the name "postgres" and use that, but if I understand right (which I might not), what you're running into is that the user account within the postgres database is not named _postgres. AIUI the account names within the DB are separate from the system account names, so there shouldn't be a problem running a DB instance under the "_postgres" system account, and opening that DB with the "postgres" database account.
11

For me This was the solution on macOS ReInstall the psql

brew install postgres

Start PostgreSQL server

pg_ctl -D /usr/local/var/postgres start

Initialize DB

initdb /usr/local/var/postgres

If this command throws an error the rm the old database file and re-run the above command

rm -r /usr/local/var/postgres

Create a new database

createdb postgres_test
psql -W postegres_test

You will be logged into this db and can create a user in here to login

Improve this answer

2 Comments

Great Answer! Helped me. There's a small typo in create-a-new-database: createdb postgres_testand then psql -W postgres_test. You have an extra "e" in postegres_test.
@Fredster Glad, it helped you!
5 
psql -U postgres

Worked fine for me in case of db name: postgres & username: postgres. So you do not need to write sudo.

And in the case other db, you may try

psql -U yourdb postgres

As it is given in Postgres help: 

psql [OPTION]... [DBNAME [USERNAME]]
Improve this answer

2 Comments

My case: $ psql -U postgres generates output psql: FATAL: role "postgres" does not exist, but psql postgres works.
I got error psql: error: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
5

I get exactly the same errors as kryshah with su - postgres and sudo -u postgres psql. DanielM's answer gives also errors.

Outputs when wrong settings

Answer however from przbabu's comment. 

masi$ psql
psql: FATAL:  database "masi" does not exist
masi$ psql -U postgres
psql: FATAL:  role "postgres" does not exist
masi$ psql postgres
psql (9.4.1)
Type "help" for help.

I think the some part of this problem may be in owner settings in OSX

masi$ ls -al /Users/
total 0
drwxr-xr-x   7 root      admin  238 Jul  3 09:50 .
drwxr-xr-x  37 root      wheel 1326 Jul  2 19:02 ..
-rw-r--r--   1 root      wheel    0 Sep 10  2014 .localized
drwxrwxrwt   7 root      wheel  238 Apr  9 19:49 Shared
drwxr-xr-x   2 root      admin   68 Jul  3 09:50 postgres
drwxr-xr-x+ 71 masi      staff 2414 Jul  3 09:50 masi

but doing sudo chown -R postgres:staff /Users/postgres gives chown: invalid user: ‘postgres:staff’.

In short, this is not the solution the problem. Use the tools provided by the postgres installation to create a user and database.

To get right settings and outputs

There are specific commands after postgres installation to add a new user to the database system. After initdb, run the following as described here

createuser --pwprompt postgres
createdb -Opostgres -Eutf8 masi_development
psql -U postgres -W masi_development

To avoid the password request all the time, you have three choices as described here.

Improve this answer

Comments

3

the discussion and answer here was massively helpful to me:

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

Improve this answer

1 Comment

And this one stackoverflow.com/questions/17633422/…
0

You can try this to create a new role, you can name :

sudo adduser <new_user_role>
Improve this answer

Comments

0

Install postgres-server:

yum install postgresql-server -y
Improve this answer

Comments

0

I installed using homebrew and it didn't create a user

Step 1:

psql postgres

Step 2:

psql -d postgres

Create Database

Improve this answer

Comments

-3

The solution is simple:
log in as root
and after: 

su - postgres

psql
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.