PostgreSQL: Connection and Role error solutions
I am glad to be writing this blog to showcase how I was able to fix errors when trying to use PostgreSQL as the database for my project. I tried to create the database using rails db:create but it had other plans and threw errors. After searching for hours on StackOverflow and copying the top commented answers without a second thought(because they are always right… duh!), I had no luck. It all started when I was… wait... you’re probably on this blog to find answers for your errors, so let me just get straight to the point.
Connection Error
The first error that came up was a connection error.
Running via Spring preloader in process 9221
/usr/share/rvm/gems/ruby-2.6.1/gems/spring-3.1.1/lib/spring/application.rb:209: warning: Insecure world writable dir /mnt/c in PATH, mode 040777
connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
Couldn't create 'bounce_backend_development' database. Please check your configuration.
rake aborted!
ActiveRecord::NoDatabaseError: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?...Caused by:
PG::ConnectionBad: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
Now I did not understand what any of it meant. My coach told me that I probably did have PostgreSQL installed, but I did.
To see the Postgre cluster running on your device type in your terminal pg_lsclusters and it outputted:
Ver Cluster Port Status Owner Data directory Log file11 main 5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log13 main 5432 down,binaries_missing postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log14 main 5434 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
Now I saw that I had 3 versions and version 13 has port 5432 which is the one having connection errors. I’m not sure how I ended up with 3 versions (probably because I did not install it properly and did multiple takes). My coach then sent me a link to a proper guide for installing PostgreSQL, so I uninstalled the PostgreSQL I had downloaded before using this code.
sudo apt-get --purge remove postgresql postgresql-*If you do not wish to uninstall everything then refer to this forum: https://askubuntu.com/questions/32730/how-to-remove-postgres-from-my-installation
The next step was to reinstall PostgreSQL. Instead of copying the guide, here is the link my coach sent me in which I used.
If the above solution does not work for you then you can also read this blog: PostgreSQL In Windows Subsystem for Linux (WSL) by Harshit Yadav
Role Error
YAY! We fixed the connection error… oh wait… now I’m getting these role errors.
“ A role is an entity that can own database objects and have database privileges; a role can be considered a “user”, a “group”, or both depending on how it is used.” — PostgreSQL
FATAL: role "<role_name>" does not exist
Couldn't create '<app_name>_development' database. Please check your configuration.
PG::ConnectionBad: FATAL: role "<role_name>" does not existLuckily the solution was simple and all I had to do was create a role.
#start the server if not started
sudo service postgresql start#open the psql shell
sudo -u postgres psql#create role
CREATE ROLE name LOGIN PASSWORD 'my_password'
#when you enter this code it should return the name of the role back to you.
This created the role for me and I was thankful that it did. If CREATE ROLE name LOGIN PASSWORD 'my_password' does not work for you then you could refer to this CREATE ROLE documentation by PostgreSQL.
Now my role was created, but my problem with roles was not over as I received this new error:
PG::InsufficientPrivilege: ERROR: permission denied for relation schem_migrationsFirst, go into your psql shell using sudo -u postgres psql then enter in \du in which something like the following should pop up:
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
new_role | | {}
other | Superuser, Create role| {}As you can see, the new role that I created did not have any attributes. I had to upgrade my new role to Superuser.
#go into your psql shell
sudo -u postgres psql#alter your role
ALTER USER rolename WITH SUPERUSER;
With that PostgreSQL errors were fixed and when I ran rails db:create it created the database for me and my hope is that this blog helped you in fixing your errors. Finally, all my problems came to an end… for now.
