Psql error could not connect to server fatal role postgres does not exist

На чтение 5 мин. Просмотров 14 Опубликовано 15.12.2019

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

A PostgreSQL Fatal error like role or username does not exist. This is a very common error which is facing by PostgreSQL Users.

Actually, this is happening because of misconfiguration of System username and Database Username.

Most of the Linux users are trying to log in PostgreSQL using root user. But actually, this is wrong.

You will need to become the operating system user under which PostgreSQL was installed to create the first user account.

Generally, the default user name is Postgres and default database is also Postgres.
Once you install PostgreSQL on Linux, you should set the password for the Postgres user which is the default super user of PostgreSQL.

Please do not try to log in with any other default Linux or Unix system user name.

Set password for the default Postgres user:

Create another role using “postgres”:
Creating new role “dbrnd”. -p = set the password and -d = allow to create database.

Содержание

Please visit other related articles.
Leave a Reply Cancel reply
16 Answers 16

Please visit other related articles.

thank you for this very useful information
but I still face the same trouble everytime I followed the commands that you wrote but still getting the same fatal error
i am running the database through docker …

16 Answers 16

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:

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:

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

Источник

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!

asked Mar 8 ’13 at 19:31

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.

answered Mar 9 ’13 at 10:13

The key is «I installed the postgres.app for mac.» This application sets up the local PostgresSQL 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.

answered Jul 23 ’13 at 14:25

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://sites.google.com/site/nitinpasumarthy/blog/installingpostgresonmac

answered Feb 10 ’16 at 6:19

createuser postgres --interactive

or make a superuser postgresl just with

createuser postgres -s

answered Aug 10 ’17 at 20:10

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.

answered Jul 29 ’15 at 22:54

Running this on the command line should fix it

/Applications/Postgres.app/Contents/Versions/9.4/bin/createdb <Mac OSX Username Here>

answered Feb 13 ’15 at 21:41

I needed to unset $PGUSER:

$ unset PGUSER
$ createuser -s postgres

answered Jan 2 at 6:54

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.

answered Mar 8 ’13 at 20:53

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

answered Jan 11 ’17 at 15:37

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.

answered Mar 9 ’13 at 14:36

This is the only one that fixed it for me :

createuser -s -U $USER

answered Mar 2 ’16 at 0:10

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.

answered Jun 26 ’17 at 12:33

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.

answered Jun 28 at 8:32

Not the answer you’re looking for? Browse other questions tagged macos postgresql terminal or ask your own question.

Источник

In this document I am going to discuss about some of the most frequent errors faced by users while connecting to PostgreSQL. This is document is to help the beginners who started working on PostgreSQL. PostgreSQL has a process called postmaster which acts as listener process, by default the process listens on 5432.

There are different tools available to connect to PostgreSQL, here I am going to use the psql command line tool but there is no difference in error messages.

ERROR – 1

[root@localhost ~]# psql
psql: error: FATAL: role “root” does not exist

By default “psql” command line tool use the operating system user as the database user and localhost the hostname if the user name is not passed as argument, here I logged in as the root user and tried to login to PostgreSQL without username which caused the error.

Solution

give the username when logging into the database, by default the psql command line take the username as the database name, so no need to pass the database as I am trying to connect to PostgreSQL.

psql -U postgres -h localhost

ERROR – 2

[root@localhost ~]# psql -U postgres
psql: error: FATAL: Peer authentication failed for user “postgres”

Solution

By default PostgreSQL installation using yum repository configure the pg_hba.conf with peer, ident authentication. So change the peer and ident authentication methods to md5 and reload the configuration.

[root@localhost data]# psql -U postgres
Password for user postgres: 
psql (12.5)
Type "help" for help.

postgres=#

ERROR – 3

[root@localhost data]# psql -h 192.168.159.151 -U postgres -d postgres
psql: error: could not connect to server: Connection refused
Is the server running on host “192.168.159.151” and accepting
TCP/IP connections on port 5432?

This is the common error users get when they connect to PostgreSQL. The error represents there is no listener for host and port combination.

Solution

Most common issue is listener_address parameter is set to ‘localhost’ or port set to a non default value.

postgres=# show listen_addresses ;
 listen_addresses 
------------------
 localhost
(1 row)

postgres=# show port;
 port 
------
 5432
(1 row)

Here the issue is listener_address is localhost, so changed it to ‘*’ where it listens on all ip’s in my host. I have only one ip configured, if your server has multiple ip’s then set the ip address on which the PostgreSQL connections should happen.

Configure the listener_address or change the port and restart the PostgreSQL cluster.

postgres=# alter system set listen_addresses to '*';
ALTER SYSTEM
postgres=# q
-bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/project/data restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-12-20 07:12:20.317 PST [3051] LOG:  starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-12-20 07:12:20.318 PST [3051] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-12-20 07:12:20.318 PST [3051] LOG:  listening on IPv6 address "::", port 5432
2020-12-20 07:12:20.319 PST [3051] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-12-20 07:12:20.320 PST [3051] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 07:12:20.332 PST [3051] LOG:  redirecting log output to logging collector process
2020-12-20 07:12:20.332 PST [3051] HINT:  Future log output will appear in directory "log".
 done
server started

ERROR – 4

-bash-4.2$ psql -h 192.168.159.151 -U postgres -d postgres
psql: error: FATAL: no pg_hba.conf entry for host “192.168.159.151”, user “postgres”, database “postgres”, SSL off

This is another common error which users will face due to missing entry for the user host address (ip address) in pg_hba.conf file. host based authentication file presents in data directory which has a list of ip addresses or host addresses allowed to connect to PostgreSQL.

Solution

Make an entry like below for the user host or subnet in pg_hba.conf file. Any ip address with 192.168.X.X will be able to connect to the host.

host	all		all		192.168.0.0/16		md5

ERROR – 5

-bash-4.2$ psql -h 192.168.159.151 -U postgres -d postgres
psql: error: FATAL: sorry, too many clients already

-bash-4.2$ psql -h 192.168.159.151 -U viswamitra -d postgres
psql: error: FATAL: remaining connection slots are reserved for non-replication superuser connections

-bash-4.2$ psql -U viswamitra -d postgres -h 192.168.159.151
psql: error: FATAL: too many connections for role “viswamitra”

This is also a common error users face after started using the database in application. This errors will come after the connection limit is reached to the maximum configured value.

Connection limit can be set at different levels

cluster – which is applicable for all users and databases
user – which is applicable for the user
database – which is applicable for the given database

Solution

Login to the database as a super user, if there are any idle connections from longer time close them and check the application connection pool settings to close the idle connections after some time interval.

postgres=# select pid,datname,usename,application_name,state,now()-state_change as idle_duration from pg_stat_activity where state = 'idle';
 pid  | datname  |  usename   | application_name | state |  idle_duration  
------+----------+------------+------------------+-------+-----------------
 3656 | alex     | postgres   | psql             | idle  | 00:14:06.647055
 3652 | alex     | postgres   | psql             | idle  | 00:14:11.718486
 3623 | postgres | viswamitra | psql             | idle  | 00:15:47.530912
(3 rows)

postgres=# select pg_terminate_backend(3656);
 pg_terminate_backend 
----------------------
 t
(1 row)

If there are no idle sessions to cancel and the error is first and second one in the list increase the “max_connections” in postgresql.conf file. This requires a reboot of the PostgreSQL cluster to make it effect.

postgres=# alter system set max_connections to '1000';
ALTER SYSTEM
postgres=# 

/usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/project/data restart

For third error (5.3) , the connection limit is configured at the user level, it can be changed using the alter user statement.

postgres=# alter user viswamitra connection limit 100;
ALTER ROLE

Источник

Please visit other related articles.

Leave a Reply Cancel reply

Anvesh Patel

Email Subscription !

About Me!

About DBRND !

16 Answers 16

Not the answer you’re looking for? Browse other questions tagged macos postgresql terminal or ask your own question.