dorfteich/apps/backup/src/pg.ts

import { execFile } from 'node:child_process';
import { promisify } from 'node:util';

const execFileAsync = promisify(execFile);

/**
 * Connection settings for the libpq CLI tools as environment variables.
 * Deliberately not `--dbname=<url>`: the URL carries the password, and argv
 * is world-readable inside the container (`/proc/<pid>/cmdline`) — the
 * credential-handling rule is env/file only, never argv.
 */
export function pgEnvFromUrl(databaseUrl: string): Record<string, string> {
  const url = new URL(databaseUrl);
  const env: Record<string, string> = {
    PGHOST: url.hostname,
    PGDATABASE: decodeURIComponent(url.pathname.replace(/^\//, '')),
  };
  if (url.port) env.PGPORT = url.port;
  if (url.username) env.PGUSER = decodeURIComponent(url.username);
  if (url.password) env.PGPASSWORD = decodeURIComponent(url.password);
  return env;
}

async function runPgTool(tool: string, args: string[], databaseUrl: string): Promise<void> {
  try {
    await execFileAsync(tool, args, {
      env: { ...process.env, ...pgEnvFromUrl(databaseUrl) },
      maxBuffer: 16 * 1024 * 1024,
    });
  } catch (error) {
    const stderr = (error as { stderr?: string }).stderr?.trim();
    throw new Error(`${tool} failed: ${stderr || (error as Error).message}`);
  }
}

/** `pg_dump -Fc` of the whole database into `outFile` (ADR 0015). */
export async function pgDump(databaseUrl: string, outFile: string): Promise<void> {
  await runPgTool('pg_dump', ['--format=custom', '--file', outFile], databaseUrl);
}

/**
 * Run before `pg_restore`: dropping and recreating the schema makes every
 * `--clean` drop a no-op and the restore faithful — objects created after
 * the backup do not survive it. Relying on `--clean` alone fails on
 * partitioned tables: the dump carries per-partition primary keys as own
 * entries, and the matching drops hit *inherited* constraints on the live
 * database, which PostgreSQL refuses (issue #288). The schema holds no
 * extension objects, so the CASCADE is bounded to our own objects.
 */
export const schemaResetSql = 'DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public;';

export function schemaResetArgs(database: string): string[] {
  return ['--dbname', database, '--set', 'ON_ERROR_STOP=1', '--command', schemaResetSql];
}

export function pgRestoreArgs(database: string, dumpFile: string): string[] {
  return ['--clean', '--if-exists', '--no-owner', '--dbname', database, dumpFile];
}

/**
 * Restores a custom-format dump into the live database: schema reset (see
 * above), then `pg_restore`. `--clean --if-exists` stays as belt and braces
 * — against the empty schema all its drops are no-ops (the runbook stops
 * the app services, not the db container).
 */
export async function pgRestore(databaseUrl: string, dumpFile: string): Promise<void> {
  const database = pgEnvFromUrl(databaseUrl).PGDATABASE ?? '';
  await runPgTool('psql', schemaResetArgs(database), databaseUrl);
  await runPgTool('pg_restore', pgRestoreArgs(database, dumpFile), databaseUrl);
}