Last updated on

Pulling Production PostgreSQL Data Into Local Dev, Safely

Local dev on this site has always run against Postgres in a docker-compose container, seeded with whatever I'd typed in by hand or left over from testing. That's fine for checking that a migration doesn't blow up, and it's useless for almost everything else. You can't tell if a new list view looks right with three fake posts in it. You can't catch a rendering edge case that only shows up because a real post has an unusually long title or an empty field a fake one never would. At some point "works on my seed data" and "works" stop being the same claim, and the gap between them is exactly the stuff that gets discovered in production instead of before it.

The fix is obvious in shape: pull a real copy of production's data down into local dev. The interesting part turned out to be everything else — direction, credentials, and one bash bug that cost more time than the actual database logic did.

One-way, on purpose

The script is scripts/pull-prod-db.sh, run as pnpm db:pull-prod. It dumps production Postgres and restores it into local dev, overwriting whatever's there. That's the whole feature. There's no push-to-prod counterpart, and that omission is deliberate, not an oversight I haven't gotten to.

This codebase actually had a local-to-production script once — migrate-db-to-production.sh, used for the original one-time migration when this site's data first moved to its real production database. It got deleted on purpose right after that migration finished. Re-running a --clean restore in that direction, against a production database that by then held real content only production had, would silently destroy it. A restore doesn't ask "are you sure this is what you meant" — it drops and recreates. Keeping a script like that around after its one legitimate use is a loaded gun sitting in the repo. Better to delete it and, if that direction is ever genuinely needed again, write a new one with its own safeguards — diff, dry-run, explicit confirmation — rather than repoint the old one and hope nobody runs it at the wrong moment.

So pull-prod-db.sh only reads from production. It never writes to it. Worst case if something goes wrong is a broken local database, which is a docker compose down -v away from just starting over.

The wall: Vercel won't give the value back

The obvious first move was vercel env pull — pull down DATABASE_URI the same way any other env var gets synced locally. That produced a connection string that looked like this:

DATABASE_URI="[SENSITIVE]"

Not a placeholder I forgot to fill in — that's the literal value Vercel hands back. DATABASE_URI is marked as a "sensitive" environment variable in production, which is Vercel's own default for production and preview vars. Sensitive means genuinely unreadable after creation, not just hidden behind a click-to-reveal in the dashboard. vercel env pull, vercel env ls, and the API all return the same masked string, for anyone, including the project owner. There's no flag or auth level that unlocks it — that's the point of the feature, not a gap in it.

Which meant the actual production connection string had to come from somewhere Vercel doesn't control: Neon, the Postgres provider underneath it. Vercel's dashboard has a shortcut for this — the project's Storage tab, the Postgres database, an "Open in Neon" link — which drops you into Neon's own console, where the real connection string is sitting in plain view under Connection Details.

Naming it wrong would have been worse than not having it

Getting the real value out of Neon solved the can't read it problem. It introduced a smaller, sharper one: what to call it once it's saved locally.

The obvious name is DATABASE_URI — that's what the app already calls it everywhere else. It's also the wrong name, for a reason that has nothing to do with this script and everything to do with how Next.js loads env files. .env.production.local gets loaded automatically whenever NODE_ENV=production, and that includes an ordinary local pnpm build && pnpm start — not just a real deploy. If the production connection string were sitting under the key DATABASE_URI in that file, a routine local production-build smoke test would silently read production's real database instead of local's. No warning, no separate flag to opt in — just the app quietly talking to the wrong database because a filename convention did its job a little too well.

The fix costs nothing: name it PROD_DATABASE_URI instead, checked in this order — an already-exported shell variable, then a PROD_DATABASE_URI= line in .env, then the same line in .env.production.local for anyone who'd rather keep it physically separate from local secrets. Different key, same footgun avoided, regardless of which file it ends up in.

The bug that actually took the longest

With a real, correctly-named connection string finally sitting in .env, the script still didn't work. The first version read it by sourcing the file — source .env in a subshell — which is a completely normal way to load a .env file and also, it turns out, not a safe one.

Real Neon connection strings commonly include &channel_binding=require in the query string. An unquoted & in the middle of a line means something specific to bash: background everything before it as its own job. Source a line like


PROD_DATABASE_URI=postgres://user:pass@host/db?sslmode=require&channel_binding=require

and bash reads that as "run PROD_DATABASE_URI=postgres://... in the background," which executes in a subshell, assigns the variable there, and throws that subshell away the instant it finishes. Back in the shell that sourced the file, $PROD_DATABASE_URI is empty. No error. No warning. Just a variable that silently isn't there, in a script that had just spent two iterations getting to the point of finally having the right value to read.

I confirmed it with the smallest possible repro before touching the real script again:


$ FOO=bar&baz=qux
$ echo "$FOO"



Empty. That's the whole bug, isolated from Neon, Vercel, and everything else — an ordinary shell semantics trap that happens to be invisible until a value contains the one character that triggers it.

The fix is to never hand .env to the shell as code in the first place. Instead of sourcing the file, the script greps for the one matching line as plain text and strips the key off with parameter expansion:


read_prod_database_uri_from() {
  local file="$1" line value
  [[ -f "$file" ]] || return 0
  line="$(grep -m1 '^PROD_DATABASE_URI=' "$file" || true)"
  [[ -n "$line" ]] || return 0
  value="${line#PROD_DATABASE_URI=}"
  if [[ "$value" == \"*\" ]]; then
    value="${value#\"}"
    value="${value%\"}"
  elif [[ "$value" == \'*\' ]]; then
    value="${value#\'}"
    value="${value%\'}"
  fi
  printf '%s' "$value"
}


Nothing in that function executes anything from the file — &, ;, $, backticks, whatever else might show up in a real password or query string, all come through as inert text. It's a slightly longer way to read one line out of a file, and it's the difference between a script that works and one that fails silently on exactly the kind of value it exists to handle.

What actually runs

Once the connection string is in hand, the rest is two Postgres tools most people already know, run inside the existing local Postgres container so there's no need for pg_dump/pg_restore installed natively on the host:


docker compose exec -T postgres pg_dump \
  --format=custom --no-owner --no-privileges \
  --dbname="$prod_database_uri" \
  --file=/tmp/prod-pull.dump

docker compose exec -T postgres pg_restore \
  --clean --if-exists --no-owner --no-privileges \
  --dbname="postgres://payload:payload@localhost:5432/dfadler_cms" \
  /tmp/prod-pull.dump


--clean --if-exists is the same flag pair that made the old local-to-production script dangerous — it drops existing objects before recreating them. Here the target is always the local container, so dropping and recreating is exactly the right behavior, not a landmine. --no-owner --no-privileges sidesteps a restore trying to reassign ownership to a role that only exists in production. Before any of that runs, there's a one-line confirmation prompt (skippable with --yes for anyone scripting around this), and a password-redacted print of which host is about to get hit — so a stale or wrong PROD_DATABASE_URI shows up as an obvious sanity-check line instead of a pg_dump error that takes a minute to place.


==> Using production database: postgres://neondb_owner:***@***.neon.tech/neondb


What this doesn't cover

Media is the obvious gap. Production's uploaded images live in Vercel Blob storage; local dev falls back to disk under ./media. This script only ever touches Postgres — after a pull, the database will reference media files that may not exist locally. That's a real limitation, not an edge case I forgot; solving it means a second, separate sync path for blob storage, which didn't seem worth building until the database sync itself was proven out.

And "proven out" is honest phrasing, not a formality — the connection-string handling above has been verified against the real, actual production credential (Vercel's sensitive-var wall, the naming footgun, the sourcing bug: all three reproduced and fixed against the genuine value, not a fixture). The dump-and-restore itself is exactly the two pg_dump/pg_restore invocations Postgres has run the same way for years, which is reassuring, but running the whole script start to finish against the real production database is still the step that turns "should work" into "does work." That's next.