Writing a PostgreSQL dump that actually replays: lessons from a database workbench

VM Doctor is the single-container operations dashboard we use to run our production server. One of its tabs is a database workbench: browse schemas, edit rows, run SQL in a console and export a schema as a .sql file. It started with MySQL only. When we added PostgreSQL, the export feature turned out to be the hardest part by far.

The reason is simple. A dump is only useful if you can run it back. Generating SQL that looks like a schema is easy. Generating SQL that a fresh database will accept, statement by statement, in order, with every value intact, is not. This post covers the mistakes our first version made.

Why not just run pg_dump?

The dashboard is one small Node.js container that connects to several databases over the network. Bundling the PostgreSQL client tools, matching their major version to every server, and shelling out from a web request would add a lot of moving parts for a feature that is used occasionally. A "pg_dump-lite" written against the system catalogs, streamed table by table to the browser, was a reasonable trade, as long as we were honest about its limits.

PostgreSQL has no SHOW CREATE TABLE, so the dashboard rebuilds each CREATE TABLE from pg_attribute, pg_constraint and pg_indexes, then streams the rows as batched multi-row INSERT statements (200 rows per statement).

Mistake 1: values written in MySQL dialect

The MySQL side already had a function that turns a JavaScript value into a SQL literal, and the first PostgreSQL version reused it. Text, numbers and dates round-tripped fine. Three types did not:

• Booleans. MySQL happily takes 1 and 0. PostgreSQL will not insert an integer into a boolean column. It needs TRUE or FALSE.
• Binary data. MySQL writes X'dead'. PostgreSQL's bytea hex input is '\xdead'.
• Backslashes and newlines. This one is the dangerous one. With standard_conforming_strings = on, which is the default, a backslash in a normal string literal is just a backslash. So a C-style escape like \n would be stored as two characters, not a newline. The data would not fail to load. It would load wrong.

So PostgreSQL got its own literal function:

export function pgSqlValue(v: unknown): string {
  if (v === null || v === undefined) return 'NULL'
  if (typeof v === 'number' || typeof v === 'bigint') return String(v)
  if (typeof v === 'boolean') return v ? 'TRUE' : 'FALSE'
  if (Buffer.isBuffer(v)) return `'\\x${v.toString('hex')}'` // bytea hex input
  if (v instanceof Date) return `'${v.toISOString()}'`

  // Objects cover json/jsonb, which round-trip through JSON.stringify.
  const s = typeof v === 'object' ? JSON.stringify(v) : String(v)
  const quoted = s.replaceAll("'", "''")
  if (!/[\\\n\r\x00]/.test(s)) return `'${quoted}'`

  return `E'${quoted
    .replaceAll('\\', '\\\\')
    .replaceAll('\n', '\\n')
    .replaceAll('\r', '\\r')
    .replace(/\x00/g, '\\0')}'`
}

Plain strings stay plain. Only strings that contain a backslash, a newline, a carriage return or a NUL become E'...' escape-string literals, where escapes mean what they say.

Mistake 2: views before the tables they read

The first version listed relations with ORDER BY relkind, relname. That looks tidy, but relkind is a single letter: m (materialized view) sorts before p (partitioned table) and r (table), and v (view) after them. A materialized view therefore came first in the file and failed on replay, because the tables it selects from did not exist yet.

The fix is an explicit replay order: all tables first, then views, then materialized views, since a materialized view may read from either.

ORDER BY CASE c.relkind WHEN 'r' THEN 0 WHEN 'p' THEN 0
                        WHEN 'v' THEN 1 WHEN 'm' THEN 2 END,
         c.relname

Mistake 3: foreign keys that point at the future

Tables were emitted in alphabetical order, with their foreign keys inside the CREATE TABLE. So orders referencing users failed, because users comes later in the alphabet.

The dump already began with:

SET session_replication_role = replica;

We had assumed that this setting made foreign keys a non-issue. It does not. It disables foreign key checks when rows are inserted, which is why inserting child rows before parent rows works. It does nothing for DDL: CREATE TABLE ... REFERENCES users still needs the users table to exist at that moment.

The fix is how pg_dump itself does it. Tables are created without their foreign keys. The keys are collected, and once every table and all its data are in, they are added as trailing statements:

--
-- Foreign keys
--
ALTER TABLE "app"."orders" ADD CONSTRAINT "orders_user_id_fkey"
  FOREIGN KEY (user_id) REFERENCES app.users(id);

In code, the table DDL builder takes a flag. The workbench's "show DDL" view keeps keys inline, because a human is reading it. The dump moves them to the end, because a database is replaying it.

Mistake 4: every partitioned row, twice

PostgreSQL's catalog lists a partitioned table and each of its partitions as separate relations. SELECT * on the parent already returns every row from every partition. The first version dumped the parent and each partition, so every row appeared twice, and the replay failed on the first duplicate primary key.

The query now skips partitions:

WHERE n.nspname = $1
  AND c.relkind IN ('r', 'p', 'v', 'm')
  AND NOT c.relispartition

and the code filters on relispartition again as a defensive check, so the rule holds even if a catalog query is changed later.

Mistake 5: a shared connection that remembered its schema

This bug was in the SQL console, not the dump, but we found it in the same pass. To run a query against a schema, the console does SET search_path TO <schema> on a pooled client. When the query finished, the client went back to the pool still pointing at that schema. The next request that borrowed the client ran in the wrong place.

try {
  if (db) await client.query(`SET search_path TO ${escapeId(db)}`)
  const raw = await client.query(sql)
  // ...
} finally {
  // Don't leak this statement's schema scoping to whoever borrows the client next.
  if (db) await client.query('RESET search_path').catch(() => undefined)
  client.release()
}

Any session-level SET on a pooled connection needs a matching reset before release.

Be explicit about what you do not support

A small dump tool will never match pg_dump. Ours writes its known gaps down in the code: array columns are not rendered as PostgreSQL array literals, PARTITION BY is not rebuilt (a partitioned parent replays as a plain table holding all the rows), identity and generated columns degrade to their default expression, and sequences, ownership and grants are not exported. Scalar types (text, numeric, boolean, bytea, json and jsonb, dates) round-trip correctly, and the tests check each one.

Listing the gaps is part of the feature. It tells the next person when to reach for the real tool.

Takeaways

• Test a dump by replaying it into an empty database, not by reading it.
• Never share literal-formatting code between SQL dialects.
• session_replication_role = replica skips foreign key checks, not foreign key DDL. Emit keys last.
• Exclude partitions when you dump their parent.
• Reset session state before returning a pooled connection.