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 md5ERROR – 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 restartFor 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
My first attempt to install a module. In the process I’ve got an error:
socket.error: [Errno 98] Address already in use in OpenERP
Tried to solve like this:
ps aux | grep openerp
sudo kill -9 procees id
sudo /etc/init.d/openerp-server restart (=> this doesn't work)
Now I get an other error OperationalError: FATAL: role «root» does not exist and the website is down.
Can someone help me out?
6Ответы
500 internal server error (Linux)
OperationalError: FATAL: role «root» does not exist
You find this error in file : /var/log/postgrsql/postgrsql-10-main.log (on linux system)
The solution may be :
1- Open Terminal and enter => sudo su postgres
2- Enter password for postgres System user (if it is necessary)
3- createuser root -s
4- psql template1
5- alter role root with password ‘yourpassword’;
6- q
7- exit
8- service odoo restart
Now try again to localhost:8069
Dennis,
You have to create user role for postgres.
sudo -u postgres createuser -s username
sudo su postgres psql alter user username with password 'password'; q exit
me pueden ayudarme para darle permiso para ingresar odoo
Thank you so much, it worked for me
Thanks so much, it work, this fix my problem.
Thanks so much, it works for me too, I solved my problem using this command.
Here’s a question from one of our regular reader Sam. He says that PostgreSQL does not allow to create user or database and fails with an error message “Createuser could not connect to database postgres” while issuing createuser command and “createdb: could not connect to database template1” while executing createdb command. The command fails when executed as privileged user as well. Here’s the solution for this error.
Before we see the solution, have a look at the error message:
[sam@openca ]$ createdb openca createdb: could not connect to database template1: FATAL: role "sam" does not exist [sam@openca ]$ su - [root@ra openca ]# createuser openca createuser: could not connect to database postgres: FATAL: role "root" does not exist
How to fix the error – createuser could not connect to database postgres
According to the snapshot, createuser and createdb commands were executed as ‘sam’ and ‘root’ user. It means, the PostgreSQL administrator has not created a PostgreSQL user account for ‘sam’ & ‘root’. Note, the PostgreSQL user accounts are different from the regular UNIX user accounts. So, even if you have valid UNIX accounts, it’s not a valid PostgreSQL user account until administrator creates one. That’s the reason, PostgreSQL denied users ‘sam’ & ‘root’ from creating user or database. In order to fix this error, you need to switch to a user that’s running PostgreSQL server. By default, UNIX user 'postgres' is the one that will be running PostgreSQL server. So the createdb & createuser commands should be executed as 'postgres' user account as shown below.
$ sudo su - postgres
(or)
$ sudo -u postgres -i
You can now create database as ‘postgres’ user as shown below:
$ createdb openca
Create PostgreSQL user account as below:
$ createuser sam
Verify, if PostgreSQL user is created properly by logging-in to ‘sam’ user and type psql as shown below.
# sudo su - sam [sam@openca ]$ psql psql (9.2.23) Type "help" for help. postgres=# q
That’s it!
Note:
You don’t require to enter password if the PostgreSQL allows ident based authentication.
How to allow users to create PostgreSQL database?
By default, PostgreSQL does not allow users to create or drop database. If you ever want to allow users to create PostgreSQL database, then the administrator has to provide necessary privileges.
To do that, switch to the user that is running PostgreSQL server.
$ sudo su - postgres
Type psql to enter PostgreSQL prompt.
$ psql psql (9.2.23) Type "help" for help. postgres=#
Grant CREATEDB privilege using ALTER statement as shown below:
postgres=# ALTER USER sam CREATEDB; ALTER ROLE
Note:
Replace ‘sam’ with the user account you wish to grant privilege.
Test if user ‘sam’ has enough privileges to create a database.
[sam@openca ]$ createdb test [sam@openca ]$ psql -d test psql (9.2.23) Type "help" for help. test=>
In case, if you don’t have privilege to create database in PostgreSQL, then you will see an error as shown below:
# createdb createdb: could not connect to database template1: FATAL: role "sam" does not exist