[Ruby] [Rails] How to use PostgreSQL in Vagrant environment

2 minute read

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. Login 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 you execute \du and Role is added, the account creation is completed.

terminal


postgres=# \du
                                    List of roles
  Role name | Attributes | Member of
- ------------+------------------------------------ ------------+-----------
 [Any user name] | 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

8. Create 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 fresh database.

config/database.yml


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

10. Create the 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 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 methods]

ident: Authenticate whether the DB owner matches the user name 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: Logout \?: Help display \l: Database list display \du: Display user list \c database name: connect to database \z: Display table list