How to make a replica copy of a DB

1. First, let’s set up a repo which will hold our test db:

pnpm init
pnpm install prisma --save-dev
pnpm install typescript ts-node @types/node --save-dev
npx tsc --init
npx prisma init --datasource-provider postgresql

1. Add a dockerfile for the test db:

version: '3.8'

services:
  postgres:
    image: postgres:14
    ports:
      - '54320:5432'
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=dump-test

2. Start the DB container:

docker-compose up -d postgres

3. Copy the data over from the remote DB to your docker DB:

export PGPASSWORD=remote-db-pw

# Before | creates the backup
# After | pipes the DB dump into the docker db
pg_dump "connection string for remote DB" | docker exec -i postgres-container-name /bin/bash -c "PGPASSWORD=postgres psql --username db-username docker-db-name"

Now you have your DB set up, check it out in your favorite DB application (pg admin, Postico, Prisma Studio etc)

4. Given we want to replicate our Prisma setup, not just the DB itself, we need to copy over the Prisma schema and migrations:

• Paste your real prisma schema into the test db schema
• Paste the migrations folder too
• Run prisma generate

Extras

If you want pg_dump and psql without the full postgres install you can do the following:

brew install libpq
# For Mac M1
echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc