How to set up a local Postgres database for Rails development with Ubuntu 20.04

Install and configure Postgres

1. Install Postgres: sudo apt install postgresql

2. Start the postgres console as postgres user so you can add a user with your linux' user name: sudo -u postgres psql

3. Create a user with the same username as your linux user and the right to create databases: postgres=# CREATE USER judith WITH createdb superuser;
   More info in the postgres docs Show archive.org snapshot

4. Check that the command succeeded by listing the users known to postgres: \du

   All together:

   Copy~ >sudo -u postgres psql
   psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
   Type "help" for help.
   
   postgres=# CREATE USER judith WITH createdb superuser;
   CREATE ROLE
   postgres=# \du
                                   List of roles
    Role name |                         Attributes                         | Member of 
   -----------+------------------------------------------------------------+-----------
    judith    | Superuser, Create DB                                       | {}
    postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
   
   

5. Check /etc/postgresql/12/main/postgresql.conf. Ensure that listen is restricted to localhost:

   Copy#listen_addresses = 'localhost'		# what IP address(es) to listen on;
     # comma-separated list of addresses;
     # defaults to 'localhost'; use '*' for all
   

6. Edit pg_hba.conf to trust all local connections. This will fix fe_sendauth: no password supplied errors.
   Open file /etc/postgresql/12/main/pg_hba.conf and change to following lines to trust:

   Copy# Database administrative login by Unix domain socket
   local   all             postgres                                trust
   
   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
   # "local" is for Unix domain socket connections only
   local   all             all                                     trust
   # IPv4 local connections:
   host    all             all             127.0.0.1/32            trust
   # IPv6 local connections:
   host    all             all             ::1/128                 trust
   

   More info in postgres docs Show archive.org snapshot

7. Restart postgres so that the changes are applied: sudo systemctl restart postgresql

Configure your Rails app

1. Add gem 'pg' to your Gemfile and bundle

2. If you encounter problems with libpq-fe.h when bundling, install libpq-dev: sudo install libpq-dev

3. Change your projects config/database.yml to use postgres:

   Copydefault: &default
     adapter: postgresql
     encoding: unicode
     host: localhost
     username:
     password:
   
   development:
     <<: *default
     database: zitate_dev
   
   test:
     <<: *default
     database: zitate_test
   

   Note that you do not have to include a username, password and port (at least as long as postgres runs on its default port 5432)