A production Postgres migration, a column name that meant two different things, and a shell builtin that quietly ate every line of the import script.
On a travel marketplace I worked on, we needed to move a small set of vendor accounts into a fresh production Postgres database: their profiles, their listings, the locations those listings point at, and their wallets. Nothing else was to come across.
On paper it's a small job. Find the rows, export them, import them, done. I did it with a coding agent doing most of the hands-on querying while I made the calls, so "we" below means the two of us.
I didn't start this codebase. It had been through other hands before I joined, and a few of the decisions in it, like the one below, were made before I was there to question them. Most of what follows is what it takes to work safely inside choices you inherited.
Reading the code first
The backend is eight NestJS services sharing one Postgres database, everything in the public schema, no schema qualifier anywhere. So before touching any data we read the entities to work out which tables hold anything belonging to a vendor.
That's where the first problem turned up. Columns called vendor_id don't all mean the same thing. In some tables they hold the vendor profile's ID. In others they hold the ID of the user account behind that profile. The wallet table has both a vendor_id and a user_id, and you have to know which ID space each one lives in.
This is the kind of thing that doesn't fail. Filter a table on the wrong ID and you get zero rows back, which looks exactly like "this vendor has nothing in this table." The migration finishes, and the data you missed just isn't there.
Then not trusting the reading
A code read tells you where the code says vendor data lives. It doesn't tell you about a column someone added in another service, or a table nobody's entity maps.
So we swept the database instead. For each vendor, both IDs, the profile's and the account's, checked against every column in all 126 tables that could plausibly hold an ID. Postgres made us earn it: chat_message.user_id turned out to be text, not uuid, so the first pass died on
ERROR: operator does not exist: text = uuid
and needed a cast.
The sweep found hits in exactly 14 columns. The code read had them all. But now we knew that, rather than believing it.
The dry run
The plan was to export each table's rows to CSV, generate an import.sql full of \copy commands in dependency order, and run the whole thing inside a transaction that rolls back at the end. That's a real import, then undone.
This kind of dry run isn't a simulation. The inserts really happen, inside the transaction, and psql reports each one as it goes, one COPY n line per table, before the rollback throws it all away. Those reported counts are the whole point. They're what you check against what you expected to move.
Ours finished cleanly. No errors, no warnings, exit code 0. And not a single COPY line in the output. There was nothing for the rollback to undo.
Nothing had failed, because nothing had run. We opened the generated import.sql and it had no \copy lines in it.
The script built that file with echo, and it ran under zsh. zsh's builtin echo interprets backslash escapes by default, which bash's doesn't unless you pass -e. One of those escapes is \c, which means stop output here. Every line we were writing began with \copy. So every single one began with \c, and zsh printed nothing for each of them.
psql was handed a file with no commands and ran none of them, successfully.
# simplified
# what we had
echo "\\copy $t FROM 'export/$t.csv' CSV HEADER" >> import.sql
# what fixed it
printf '%s\n' "\\copy $t FROM 'export/$t.csv' CSV HEADER" >> import.sql
zsh caught us one more time in the same script. We kept the table order in a variable and looped over it:
for t in $ORDER; do ...
bash splits an unquoted variable on spaces. zsh doesn't, so the loop ran once, with the entire table list as a single name:
cat: export/user_account vendor_profile ... .cols: No such file or directory
That one I don't mind. It failed loudly, straight away, and told us exactly what it was holding. The echo bug did the opposite. Without the dry run and someone reading its output for those COPY lines, the real import would have "succeeded" in exactly the same way.
Checking the target before the data
When we first looked at the new database, it had fewer than half the tables production had. The migrations hadn't fully run. Once they were re-run, we compared the two schemas column by column, including ordinal position, and every enum type, and they matched exactly.
That matters because, without an explicit column list, \copy maps CSV fields by position. A column in a different place in the target table doesn't error if the types happen to line up. It just puts values in the wrong column.
Checksums, not counts
After the real import, the obvious check is row counts per table, old against new. We did that, but counts only tell you the right number of rows arrived. A row with a wrong value in it counts the same as a correct one.
So we hashed every migrated row in full on both sides and compared them:
-- simplified
SELECT md5(t::text) FROM vendor_profile t WHERE id = ANY($1) ORDER BY id;
Every table matched. Then we checked every foreign-key-like link from the migrated rows into the new database, including the ones that use the account ID and the ones that use the profile ID, and found no orphans.
The echo in that script is a printf now.
I write a weekly log of what I'm building: crypto payments infrastructure (CRail), a quant research stack, and Solidity.
