---
title: "Zero-downtime data reseeding with a custom command"
description: "On Wakfuli, game data (effects, spells, states, sublimations, cosmetic items) doesn't live in the application code: it's published separately, as a release, and…"
date: "2026-06-26T09:00:00.000Z"
updated: "2026-08-24T15:05:22.411Z"
locale: "en"
canonical: "https://ninhache.fr/en/blog/reseed-sans-downtime"
author: "Néo Almeida"
tags: "postgres, ops, database"
categories: "dev"
---

# Zero-downtime data reseeding with a custom command

On Wakfuli, game data (effects, spells, states, sublimations, cosmetic items) doesn't live in the application code: it's published separately, as a release, and reloaded periodically into the production database whenever a new version of the game ships. The problem is that "reloading game data into a production database" is exactly the kind of phrase that, if botched, ends with an empty database on a Saturday night.

An import that dies halfway through, a dump format that shifted slightly, a table renamed between two versions: any one of these three cases can turn a routine reseed into an incident. I ended up writing a dedicated ace command (`game:seed`, under AdonisJS) whose only job is to make this operation boring. No forgotten bash script tucked away somewhere, no sequence of psql commands copy-pasted from a README: a versioned command, with its own built-in safety net.

## The rule: never touch prod before validating elsewhere

The core principle fits in one sentence: before restoring anything into the real database, we restore the exact same thing into a disposable staging database, and check that it produced something sensible. If validation fails, we bail out before ever touching prod. Prod is only modified in the final step, once we have proof that the dump is usable.

**Download**

Fetch the `.sql.gz` dump for the target release, then decompress it.

**Back up**

Full backup of the current database, before any other change.

**Restore to staging**

Create a disposable staging database and restore the dump into it.

**Validate**

Count rows in staging to check it isn't empty.

**Apply to prod**

Only if validation passed: restore into the real database.

**Clean up**

Drop staging, then a final check.

```ts title="game_seed.ts"
// ── 8. Validate staging ───────────────────────────────────────────────────
const stagingEffects = await this.verifyRowCounts(dbHost, dbPort, gameUser, gamePass, stagingDb);
if (stagingEffects === 0) {
  this.logger.error(`Staging validation failed (0 effects). Backup available at: ${backupPath}`);
  this.exitCode = 1;
  return;
}
this.logger.success('Staging OK');

// ── 9. Restore to main DB ─────────────────────────────────────────────────
this.logger.info(`Applying ${tag} to ${gameDb}...`);
await this.restoreSql(dbHost, dbPort, superUser, superPass, gameDb, cleanSql); // [!code highlight]
```

Staging isn't a permanent database: it's created right before the attempt and dropped right after, whether the main restore succeeded or not. That costs a few extra dozen seconds per seed, but it shifts the worst-case scenario (a corrupted or empty dump) from a production incident to a simple error message in the command's logs.

The backup, on the other hand, doesn't depend on `pg_dump`. Since the command already runs in the application's context, with the ORM connected, I wrote a small custom export: list the tables in the public schema, then serialize each row as an `INSERT`, escaping single quotes and distinguishing types (numbers, booleans, dates, JSON). The output is gzipped on the fly rather than built up in memory, so it doesn't blow up RAM on large tables. It's not as complete as a real `pg_dump`, but it runs anywhere the app runs, with no system dependency to install.

## The naive SQL split trap

The detail that really bit me was how to split the dump into individual statements before replaying them. The natural instinct is to do `sql.split(';')` and execute each chunk. That works on a test dump, and it breaks on the real dump, silently, on one specific table.

The reason: some text values stored in the database are themselves scripts, game-criteria strings like `"...then (2);"`. The dump writes them as SQL literals between single quotes, semicolon included inside the string. A naive split on `;` cuts right through the middle of that string, and the following `INSERT` ends up with its second half chopped off.

**Piège**

The worst part is that it doesn't necessarily fail right away: sometimes the remaining fragment stays syntactically valid on its own, and the error only shows up in the row count, much later.

The fix is a splitter that tracks the "am I inside a string or not" state character by character, and ignores semicolons encountered inside a string:

```ts title="game_seed.ts"
private splitStatements(sql: string): string[] {
  const statements: string[] = [];
  let current = '';
  let inString = false;

  for (let i = 0; i < sql.length; i++) {
    const ch = sql[i];

    if (inString) {
      current += ch;
      if (ch === "'" && sql[i + 1] === "'") {
        // escaped single quote ''
        current += sql[++i];
      } else if (ch === "'") {
        inString = false;
      }
    } else if (ch === "'") {
      inString = true;
      current += ch;
    } else if (ch === ';') { // [!code highlight]
      const trimmed = current.trim();
      if (trimmed.length > 0) statements.push(trimmed);
      current = '';
    } else {
      current += ch;
    }
  }

  const trimmed = current.trim();
  if (trimmed.length > 0) statements.push(trimmed);
  return statements;
}
```

The only other case to handle in this state machine is the doubled single quote (`''`), the standard way to escape a quote inside a SQL string. Without this case, an escaped quote closes the string too early and the state gets thrown off for the rest of the file. This is neither elegant nor exotic as a technique, just a two-state parser, but it's exactly the right level of sophistication: no need for a real SQL parser for a known dump, just a need to respect the one structure that matters here, quotes.

## Tolerating schema drift instead of aborting everything

Second gotcha, more insidious because it doesn't break anything right away: the list of expected game tables and the actual content of a given dump can drift apart. A table can be added to the code before the data release that populates it even exists yet, or conversely, an old dump can reference a table that no longer exists in the current schema. This schema drift shouldn't, on its own, fail the entire reseed.

If the post-restore check treated this as a fatal error, a single version mismatch would be enough to block the entire reseed, including for the dozens of other perfectly consistent tables. The choice was to check whether the table exists before counting it, and to skip it with a warning rather than interrupting validation:

```ts title="game_seed.ts"
const exists = await client.query(`SELECT to_regclass('public."${table}"') IS NOT NULL AS ok`);
if (!exists.rows[0].ok) { // [!code highlight]
  this.logger.warning(`  ${table.padEnd(30)} (absent, ignorée)`);
  continue;
}
```

**Pivot table (effects)**

Missing or empty: the seed stops. This is the only check that decides on an abort.

**Other game tables**

Missing: a warning, the table is skipped, and the reseed continues.

The pivot table has been present in every version of the schema from the start. The rest is informational: useful for spotting drift early, but not blocking.

## Finding the "latest" tag without getting tripped up by sorting

One last pitfall, this one purely tied to the project's history: automatically resolving "the latest available version" can't be done with a simple lexicographic sort of tag names.

**Piège**

The tag format changed along the way: an old, short format, and a newer, longer one with more numeric segments. An alphabetical sort ranks one ahead of the other according to rules that have nothing to do with the actual chronological order of versions.

The most reliable solution was to not sort at all: the releases API already returns results in reverse-chronological order, so it's enough to walk that list in order and take the first entry matching the desired filter (not a draft, and the right publication "flavor"), rather than recomputing an order from the strings themselves.

## What I take away from this

**Leçon**

What these three pitfalls (SQL splitting, schema drift, tag sorting) have in common is that none of them shows up on a clean test dataset. They only appear on the real dump, with its real diversity of content and its real version history. The only protection that holds up in this kind of situation isn't anticipating every edge case in advance, it's building the restore path so that an unanticipated case results in a clean abort in staging, with a message pointing to the backup, rather than a half-overwritten prod. The disposable staging database isn't there to avoid bugs: it's there to guarantee that an unexpected bug costs a rerun command, not a night of emergency restoration.