Recommended Free Tools
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A PostgreSQL migration dry run is meaningful only if it executes the intended import and produces evidence that rows were processed. A zero exit code by itself proves neither. In one reported migration, an import script was empty, so the transaction had nothing to roll back.
How an empty migration script looked like a successful dry run
Damilare Agba describes moving a small set of vendor accounts and related records from a production PostgreSQL database into a fresh database. The source application had several services sharing one database, and columns called vendor_id did not all refer to the same ID space. Agba says the team reviewed the entities and plausible profile- and account-ID columns, and that its map of relevant columns was complete for that project. That finding is specific to the migration, not a guarantee that similarly named columns mean the same thing elsewhere.
The planned dry run performed the import inside a transaction and then rolled it back. The expected evidence was a COPY n message for each table. Instead, psql exited with code 0 and printed no COPY lines. Inspection showed that import.sql contained no copy commands. Because the client had no import work to execute, the transaction had nothing to undo.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Why the generated command file was empty
The command file was generated with zsh’s echo builtin. In the reported script, each intended command began with copy; zsh interpreted the backslash-c escape, suppressing the subsequent characters and final newline. The zsh manual documents this behavior and recommends printf for portable text output: zsh manual: Shell Builtin Commands.
#1 Best Overall
Agba fixed the generation step by using printf '%sn' to write each line literally. This avoids asking a shell builtin to interpret command text as escape sequences. The key diagnostic was not a PostgreSQL error: it was the missing content in the generated file.
A separate shell-loop failure
The account also describes a loop whose unquoted table-list variable split into separate items in bash but not in zsh. In zsh, the loop treated the whole list as one filename and raised an error. Unlike the empty script, this failure was loud and therefore easier to diagnose. The comparison is Agba’s account of that script; shell expansion should be tested in the shell that will actually run the migration.
Rank #2
How to verify a dry run before trusting it
- Inspect the generated file. Open
import.sqlbefore execution and confirm that it contains the expectedcopycommands, with the intended tables and file paths. An empty or incomplete file means there is no useful import to dry-run. - Check observable execution output. During the transaction, look for the expected per-table
COPY nmessages and compare the reported row counts with the counts expected for the selected records. A successful process exit without those messages is not evidence that the import ran. - Verify the target schema first. Agba reports rerunning incomplete target migrations and comparing schema columns and enum types before importing. Position-mapped CSV data depends on compatible column order and types, so establish that the target is ready rather than assuming that a fresh database is compatible.
- Validate the committed result separately. After the real import, compare row counts to detect missing or extra rows, then inspect content as well. Agba reports comparing full-row hashes and checking foreign-key-like links. Matching counts alone cannot show that every value or relationship is correct.
- Test command generation in its runtime shell. Use
printffor literal text output and run the generation script under the shell that will execute it. Do not assume that echo handling or variable splitting behaves identically across shells.
What a transaction-based dry run can—and cannot—show
A transaction-and-rollback dry run is not a simulation: it executes the import work and then discards the changes. It can expose issues encountered while running those operations, but only if the intended commands are actually present and executed. It also does not replace validation of the data after the real import. In Agba’s incident, the lack of COPY output and the empty command file showed that the apparent dry run had not tested the import at all.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
