Download Railway database to localhost
Tags: railway, postgres, rails
Published: 2023-02-26 16:28:12

For my hobby projects I like to download live, production db and load it on my localhost environment so that I can quickly explore data or test potentially dangerous migrations.

---

## Assumptions

This post assumes your hosting is on [Railway](https://railway.app/), and you use [Postgresql](https://docs.railway.app/databases/postgresql) as a database.

Optional, but I'd also assume that you use [dotenv](https://github.com/bkeepers/dotenv) for your [Ruby on Rails](https://rubyonrails.org/) backend and your app is running locally on [Docker](https://www.docker.com/).

## Credentials

In my app I have two env files `./backend/.env.development`:

```sh
# localhost
PGDATABASE=some-local-database
PGUSER=postgres
PGPASSWORD=somepassword
PGHOST=db
PGPORT=5432

# docker:db container defaults
POSTGRES_PASSWORD=${PGPASSWORD}
```

Thanks to this config, my application will be able to connect to local database using the same set of [environment variables as Railway](https://docs.railway.app/databases/postgresql).

My Rails db config then looks like `backend/config/database.yml`:

```yml
default: &default
  adapter: postgresql
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000
  encoding: unicode
  database: <%= ENV["PGDATABASE"] %>
  username: <%= ENV["PGUSER"] %>
  password: <%= ENV["PGPASSWORD"] %>
  host: <%= ENV["PGHOST"] %>

production:
  <<: *default

development:
  <<: *default

test:
  <<: *default
  database: some_app_test
```

The aliasing of `POSTGRESQL_PASSWORD` then takes advantage of [default Postgresql Docker setup](https://github.com/docker-library/docs/blob/master/postgres/README.md#environment-variables) which will then set up respective user on container creation.

One thing to note is that [Railway uses TimescaleDB](https://docs.railway.app/databases/postgresql) so your `docker-compose.yml` will need to:

```
services:
  db:
    env_file: 
      - ./backend/.env.development
    image: timescale/timescaledb-postgis:latest-pg13
```

Second file looks very similar `./backend/.env.production`:

```sh
# railway.app
PGDATABASE=some-railway-database
PGUSER=postgres
PGHOST=some-railway-url.railway.app
PGPORT=1234
```

You need to fill this one with credentials found in your `Railway -> PostgreSQL -> Variables`.

## The script

Now for the actual dump/download/load I'm using following script `./bin/database_pull.sh`:

```sh
#!/bin/bash

DUMP_FILENAME='./some-app.com_dump.sql'

source ./backend/.env.production || exit $?
echo '💾 Creating production dump'
pg_dump -U $PGUSER -h $PGHOST -p $PGPORT -W -F t $PGDATABASE > $DUMP_FILENAME

source ./backend/.env.development || exit $?
echo '🎡 Loading dump on localhost'
dropdb $PGDATABASE -p $PGPORT -U $PGUSER -h $PGHOST
createdb $PGDATABASE -p $PGPORT -U $PGUSER -h $PGHOST
pg_restore -U $PGUSER -h $PGHOST -p $PGPORT -W -F t -d $PGDATABASE $DUMP_FILENAME

echo '✨ Success!'
```

This script [needs](https://www.postgresql.org/docs/15/libpq-envars.html) to be provided the values of `${PGPASSWORD}` manually. You have to provide **production** first and **local** second:

```sh
./bin/database_pull.sh
💾 Creating production dump
Password:
🎡 Loading dump on localhost
Password:
✨ Success!
```

## Conclusion

Developer Experience is important part of every developer life. Being able to use standard configuration and run one command to get my hands on the data I need in the format I prefer is IMO a huge DX improvement.

---

Image generated by Midjourney prompt: `railway hosting; blog post; downloading database; coding; ruby on rails --ar 16:9`