[Rails] How to use PostgreSQL in Vagrant environment

Development environment

・ Ruby: 2.5.7 Rails: 5.2.4 ・ Vagrant: 2.2.7 -VirtualBox: 6.1 -PostgreSQL: 9.2.24 ・ OS: macOS Catalina

Implementation

1. Log in to vagrant ssh

Terminal

$ vagrant ssh

2. Update yum to the latest version

Terminal

$ sudo yum -y update

3. Install PostgreSQL

Terminal

$ sudo yum -y install postgresql

Terminal

$ sudo yum -y install postgresql-devel

Terminal

$ sudo yum -y install postgresql-server

Execute psql --version, and if the version is displayed, the installation is complete.

Terminal

$ psql --version
psql (PostgreSQL) 9.2.24

4. Initialize PostgreSQL

Terminal

$ sudo postgresql-setup initdb

5. Start PostgreSQL

Terminal

$ sudo service postgresql start

6. Create a PostgreSQL account and log in

Terminal

$ sudo -u postgres psql

Terminal

postgres=# create role [Any username] with createdb login password '[Any password]';

If Role is added by executing \ du, the account creation is completed.

Terminal

postgres=# \du
                                    List of roles
  Role name      |                   Attributes                   | Member of 
-------------+------------------------------------------------+-----------
 [Any username] | Create DB                                      | {}
 postgres        | Superuser, Create role, Create DB, Replication | {}

7. Check the database

Check the database created by sudo postgresql-setup initdb in step 4.

Terminal

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges   
-----------+----------+-----------+---------+-------+-----------------------
 postgres  | postgres | SQL_ASCII | C       | C     | 
 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
(3 rows)

** * Please refer to the following article for the difference between template0 and template1. ** **

[Difference between template0 and template1 of PostgreSQL](https://db.just4fun.biz/?PostgreSQL/template0%E3%81%A8template1%E3%81%AE%E9%81%95%E3%81%84%E3 % 81% AB% E3% 81% A4% E3% 81% 84% E3% 81% A6)

8. Create a Rails app

Since the default database is SQLite, specify PostgreSQL with the -d option.

Terminal

$ rails new sample -d postgresql

9. Edit database.yml

For template, specify template0 that can create a clean database.

config/database.yml

default: &default
  adapter: postgresql
  encoding: unicode
  template: template0
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username:Username set in step 6
  password:Password set in step 6

10. Create database

Terminal

$ rails db:create

* If you get the error FATAL: Ident authentication failed for user

** ① Open the PostgreSQL configuration file. ** **

Terminal

$ sudo vi /var/lib/pgsql/data/pg_hba.conf

** ② Edit the authentication method at the bottom. ** **

Change the METHOD to md5.

pg_hba.conf

.
.
.
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

[Frequently used authentication method]

ʻIdent: The DB owner authenticates if it matches the username of the OS running the shell, md5: Authenticate with a password. trust`: No authentication.

Frequently used commands

** [Before login] **

sudo service postgresql start: Start sudo service postgresql stop: stop sudo service postgresql restart: restart sudo -u postgres psql: Login

** [After login] **

\ q: Log out \?: Help display \ l: Database list display \ du: User list display \ c database name: connect to database \ z: Table list display