Debug PostgreSQL - thuy-econsys/rails_app GitHub Wiki

could not connect to server

If there is an indication of could not connect to server: like PG::ConnectionBad (could not connect to server: Connection refused) make sure that the PostgreSQL server is running. If no, then start it:

$ pg_ctl status
pg_ctl: no server running
$ pg_ctl start

FATAL: role "user_name" does not exist

If this is the first time and the user_name is your operating system user name and you plan on using that name to access databases, you will need to create a postgres user/role for your OS name. To confirm that the role doesn't exist, login to the interactive PostgreSQL client as the postgres OS superuser (also, the default PostgreSQL Superuser), and run psql command to list users as well as user permissions \du:

$ sudo -u postgres psql
[sudo] password for user_name:
psql (12.3)
Type "help" for help.

postgres=# \du
                                     List of roles
   Role name |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Create your user/role with privileges of being able to create a database CREATEDB, login as a user LOGIN, and set the password PASSWORD 'somepassword', replacing user_name and 'somepassword' with the desired name for your user/role and your desired password, respectively:

postgres=# CREATE ROLE user_name WITH CREATEDB LOGIN PASSWORD 'somepassword';
CREATE ROLE
postgres=# \du
                                   List of roles
   Role name |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user_name   | Create DB                                                  | {}

An additional privilege that can be added to the user is SUPERUSER, but use that with caution.

FATAL: password authentication failed for user "user_name" (PG::ConnectionBad)

User is possibly missing a password, so check to make sure one exists for user. Note that the password is encrypted in pg_shadow table. If it doesn't exist, create one

postgres=# SELECT * FROM pg_shadow;
    usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig 
------------+----------+-------------+----------+---------+--------------+--------+----------+-----------
 postgres   |       10 | t           | t        | f       | f            |        |          | 
 user_name  |    16384 | t           | t        | f       | f            |        |          | 

postgres=# ALTER USER user_name WITH PASSWORD 'somepassword';
ALTER USER

postgres=# SELECT * FROM pg_shadow;
    usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil | useconfig 
------------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
 postgres   |       10 | t           | t        | f       | f            |                                     |          | 
 user_name  |    16384 | t           | t        | f       | f            | md58a5f57b1348cdf801de20ec30bb8b5af |          |

References