Setting Up Postgres for Rails Development In WSL2

If you are setting up a Rails application for development, and you need to run Postgres locally, you have two options:

1. Install Postgres using the operating system package manager (e.g. apt get)
2. Start a PostgreSQL container

Using the Ubuntu package manager

Installing locally is fairly trivial and involves just two commands:

$ sudo apt install postgresql
$ sudo systemctl enable postgresql

Once PostgreSQL is installed and running, you only need to create a Postgres role that matches your OS username and a default database since Postgres will raise a FATAL: database "<your_account_name>" does not exist error unless you specify a database:

$ sudo -u postgres psql

and write the following SQL commands:

CREATE ROLE <your_account_name> WITH SUPERUSER LOGIN PASSWORD 'password';
CREATE DATABASE <your_account_name> OWNER <your_account_name>;

Note: This approach also works in WSL2 since September 2022, when Microsoft added support for Systemd.

Using Docker

The other option is to start a container:

$ sudo docker run -d --restart unless-stopped -p "127.0.0.1:5432:5432" \
    --shm-size=1g --name=postgres17 -e POSTGRES_HOST_AUTH_METHOD=trust postgres:17

Using the POSTGRES_HOST_AUTH_METHOD=trust bypasses the authentication mechanism.

Environment Variables

If you have a vanilla config/database.yml file (e.g. after running rails new your_app), ActiveRecord will try to connect to the localhost using a socket instead of the exposed port, causing a ActiveRecord::ConnectionNotEstablished error because the Docker container only exposes a port.

However, if you set up the PGHOST environment variable to point to an IP address (in this case, 127.0.0.1) then Postgres will use the 5432 port automatically, without having to edit the config/database.yml file:

export PGHOST=127.0.0.1
export PGUSER=postgres
export PGPASSWORD=password

As an alternative, you can create a pgpass file where you can store the password instead of using the PGPASSWORD environment variable.