Skip to content

PostgreSQL backup tool with Point-In-Time-Recovery and restore verification

11.6 KB
Raw Blame
/* eslint-disable i18next/no-literal-string -- shell commands and paths, shown verbatim in every language */
import type { TranslationKey } from '../../../../shared/i18n';

// Builds the shell commands the restore dialog shows. Pure string assembly - no
// network or React - so the wiring (which flag appears when, host vs Docker) is
// unit-tested in isolation. The served recovery_script.sh accepts optional
// `--pg-bin <dir>` / `--combine-image <image>` flags plus positional
// `<bundle> [output-dir]`; the manual steps mirror what that script does by hand for
// users who would rather not pipe curl into sh. Decompression always runs with the
// host's zstd; on Docker only the version-specific pg_combinebackup runs in a
// container.

export type RestoreEnvironment = 'host' | 'docker';

// PostgreSQL 18 moved the Docker image's PGDATA to a version-specific path and the
// VOLUME up to /var/lib/postgresql; PG <=17 keeps the flat /var/lib/postgresql/data.
// The restored cluster must be bind-mounted at exactly this PGDATA, or the entrypoint
// initializes a fresh empty cluster instead.
export const containerDataDir = (pgVersion: string): string =>
  Number(pgVersion) >= 18 ? `/var/lib/postgresql/${pgVersion}/docker` : '/var/lib/postgresql/data';

export const containerVolumeDir = (pgVersion: string): string =>
  Number(pgVersion) >= 18 ? '/var/lib/postgresql' : '/var/lib/postgresql/data';

// Where the recovery script builds the cluster under the output dir, mirroring the image's
// PGDATA layout so a volume-root mount finds it: PG 18 nests <major>/docker, PG <=17 uses data.
export const clusterDataDir = (outputDir: string, pgVersion: string): string =>
  Number(pgVersion) >= 18 ? `${outputDir}/${pgVersion}/docker` : `${outputDir}/data`;

// Outside the data directory, which is why a base backup never contains these files. The cluster is
// conventionally named "main" but pg_createcluster allows any name, so this is the convention rather
// than a fact about the user's source.
export const debianConfigDir = (pgVersion: string): string => `/etc/postgresql/${pgVersion}/main`;

export const defaultPgBinDir = (pgVersion: string): string =>
  `/usr/lib/postgresql/${pgVersion}/bin`;

export const defaultDockerImage = (pgVersion: string): string => `postgres:${pgVersion}`;

export const buildChownCommand = (dataDir: string): string =>
  `chown -R postgres:postgres ${dataDir}`;

export const buildPgCtlStartCommand = (dataDir: string): string => `pg_ctl -D ${dataDir} start`;

interface DockerRunCommandParams {
  hostDir: string;
  containerDir: string;
  pgVersion: string;
}

export const buildDockerRunCommand = ({
  hostDir,
  containerDir,
  pgVersion,
}: DockerRunCommandParams): string =>
  `docker run -e POSTGRES_PASSWORD=... -v "$PWD/${hostDir}:${containerDir}" ${defaultDockerImage(pgVersion)}`;

export interface ScriptCommandParams {
  scriptUrl: string;
  bundleUrl: string;
  outputDir: string;
  pgBin: string;
  // PostgreSQL-parseable UTC timestamp; empty for a "latest" restore.
  targetTime: string;
}

export interface DockerScriptCommandParams {
  scriptUrl: string;
  bundleUrl: string;
  outputDir: string;
  image: string;
  // PostgreSQL-parseable UTC timestamp; empty for a "latest" restore.
  targetTime: string;
}

export interface ManualStepsParams {
  bundleUrl: string;
  outputDir: string;
  // the source cluster's major version - decides the cluster's layout under outputDir.
  pgVersion: string;
  pgBin: string;
  image: string;
  environment: RestoreEnvironment;
  // a per-backup restore ships no WAL; a point-in-time / latest restore does and so
  // needs the recovery-wiring step.
  hasWal: boolean;
  // preformatted timestamp PostgreSQL can parse (empty for a "latest" restore).
  targetTime?: string;
}

export interface RestoreStep {
  titleKey: TranslationKey;
  code: string;
}

const combineBinary = (pgBin: string): string => {
  const trimmed = pgBin.trim();

  return trimmed ? `"${trimmed}/pg_combinebackup"` : 'pg_combinebackup';
};

// Each backup ships as bundle/<dir>/base.tar<ext> (still compressed) beside its
// backup_manifest. Decompress every one into bundle/recon/<dir> and restore the
// manifest there - exactly what recovery_script.sh does - so pg_combinebackup can
// read decompressed PGDATA directories. Runs on the host (zstd lives there, not in
// the official postgres image).
const decompressBackupsBlock = (): string =>
  [
    `for b in bundle/full $(ls -d bundle/incr-* 2>/dev/null | sort -V); do`,
    `  d="bundle/recon/$(basename "$b")"`,
    `  mkdir -p "$d"`,
    `  blob=$(ls "$b"/base.tar* 2>/dev/null | head -n1)`,
    `  case "$blob" in`,
    `    *.zst) zstd -dqc "$blob" | tar -xf - -C "$d" ;;`,
    `    *.gz)  gzip -dc "$blob" | tar -xf - -C "$d" ;;`,
    `    *)     tar -xf "$blob" -C "$d" ;;`,
    `  esac`,
    `  cp "$b/backup_manifest" "$d/backup_manifest"`,
    `done`,
  ].join('\n');

interface ClusterConfigCheckParams {
  dataDir: string;
  pgVersion: string;
}

// Mirrors the check the served recovery_script.sh runs - the two must stay in sync. Reports rather
// than repairs: pg_hba.conf is an access-control policy for the restored data, so which rules apply
// is the user's call, not ours.
const checkClusterConfigBlock = ({ dataDir, pgVersion }: ClusterConfigCheckParams): string =>
  [
    `for f in postgresql.conf pg_hba.conf pg_ident.conf; do`,
    `  [ -f "${dataDir}/$f" ] || echo "MISSING: $f"`,
    `done`,
    `# Nothing printed? Skip the rest of this step.`,
    `# A base backup copies only the data directory. Debian/Ubuntu keep the configuration in`,
    `# ${debianConfigDir(pgVersion)}, outside it, so it is not in the backup and never can be.`,
    `# Ask the source cluster where its own files are (needs superuser or pg_read_all_settings):`,
    `#   psql -h <source-host> -U postgres -Atc "SHOW config_file"`,
    `#   psql -h <source-host> -U postgres -Atc "SHOW hba_file"`,
    `#   psql -h <source-host> -U postgres -Atc "SHOW ident_file"`,
    `# Copy all three into "${dataDir}/" (sudo if they are root-only), then in postgresql.conf:`,
    `#   comment out data_directory, hba_file, ident_file, external_pid_file`,
    `#   comment out ssl, ssl_cert_file, ssl_key_file, ssl_ca_file, or copy the certs too`,
    `#     ("ssl = on" on its own fails as well - it then looks for server.crt in the data directory)`,
    `#   mkdir "${dataDir}/conf.d" for the include_dir Debian ships`,
    `#   set listen_addresses = '*' when the cluster runs in a container`,
  ].join('\n');

// `bundle/recon/full` plus every `bundle/recon/incr-N` in numeric order - the inputs
// pg_combinebackup needs, oldest to newest. Output goes to the version-specific cluster dir.
const combineCommand = (pgBin: string, dataDir: string): string =>
  `${combineBinary(pgBin)} bundle/recon/full $(ls -d bundle/recon/incr-* 2>/dev/null | sort -V) -o "${dataDir}"`;

// Reads the restored cluster's control file, where the source's recovery-required parameters live.
// Runs pg_controldata the same way the reconstruct step runs pg_combinebackup - in the container on
// Docker, off the host tools otherwise.
const controlDataCommand = (
  environment: RestoreEnvironment,
  image: string,
  pgBin: string,
  dataDir: string,
): string => {
  if (environment === 'docker') {
    return `docker run --rm -v "$PWD:/work" -w /work ${image} pg_controldata "${dataDir}"`;
  }

  const trimmed = pgBin.trim();
  const binary = trimmed ? `"${trimmed}/pg_controldata"` : 'pg_controldata';

  return `${binary} "${dataDir}"`;
};

export const buildScriptCommand = ({
  scriptUrl,
  bundleUrl,
  outputDir,
  pgBin,
  targetTime,
}: ScriptCommandParams): string => {
  const pgBinArg = pgBin.trim() ? `--pg-bin "${pgBin.trim()}" ` : '';
  const targetArg = targetTime.trim() ? `--target-time "${targetTime.trim()}" ` : '';

  return `curl -fsSL "${scriptUrl}" | sh -s -- ${pgBinArg}${targetArg}"${bundleUrl}" "${outputDir}"`;
};

// Runs the script on the host (host zstd decompresses the bundle); only
// pg_combinebackup runs in the container via --combine-image. The script downloads
// the bundle itself, so the single-use token is consumed exactly once.
export const buildDockerScriptCommand = ({
  scriptUrl,
  bundleUrl,
  outputDir,
  image,
  targetTime,
}: DockerScriptCommandParams): string => {
  const targetArg = targetTime.trim() ? `--target-time "${targetTime.trim()}" ` : '';

  return `curl -fsSL "${scriptUrl}" | sh -s -- --combine-image "${image}" ${targetArg}"${bundleUrl}" "${outputDir}"`;
};

export const buildManualSteps = ({
  bundleUrl,
  outputDir,
  pgVersion,
  pgBin,
  image,
  environment,
  hasWal,
  targetTime,
}: ManualStepsParams): RestoreStep[] => {
  const dataDir = clusterDataDir(outputDir, pgVersion);
  const combine =
    environment === 'docker'
      ? [
          decompressBackupsBlock(),
          // --user keeps the output host-owned so the chmod and WAL-wiring below run
          // on the host without sudo; only pg_combinebackup needs the container.
          `docker run --rm --user "$(id -u):$(id -g)" -v "$PWD:/work" -w /work ${image} \\`,
          `  ${combineCommand('', dataDir)}`,
          `chmod 700 "${dataDir}"`,
        ].join('\n')
      : [decompressBackupsBlock(), combineCommand(pgBin, dataDir), `chmod 700 "${dataDir}"`].join(
          '\n',
        );

  const steps: RestoreStep[] = [
    {
      titleKey: 'backups.physical.restore.manualSteps.download',
      code: `curl -fsSL "${bundleUrl}" -o restore.tar`,
    },
    {
      titleKey: 'backups.physical.restore.manualSteps.extract',
      code: `mkdir -p bundle\ntar -xf restore.tar -C bundle`,
    },
    {
      titleKey: 'backups.physical.restore.manualSteps.verify',
      code: `(cd bundle && sha256sum -c MANIFEST.sha256)`,
    },
    {
      titleKey: 'backups.physical.restore.manualSteps.reconstruct',
      code: combine,
    },
    {
      titleKey: 'backups.physical.restore.manualSteps.checkConfig',
      code: checkClusterConfigBlock({ dataDir, pgVersion }),
    },
  ];

  if (hasWal) {
    const targetLines = targetTime
      ? `\n  echo "recovery_target_time = '${targetTime}'"\n  echo "recovery_target_action = 'promote'"`
      : '';

    steps.push({
      titleKey: 'backups.physical.restore.manualSteps.wireUpRecovery',
      // WAL is inflated inside PGDATA and restore_command is relative to it, so replay works
      // whether the cluster starts on the host or in a container (the WAL travels with PGDATA).
      code: [
        `mkdir -p "${dataDir}/databasus_wal_restore"`,
        `for f in bundle/wal/*; do`,
        `  [ -e "$f" ] || continue`,
        `  case "$f" in`,
        `    *.zst) zstd -dq "$f" -o "${dataDir}/databasus_wal_restore/$(basename "\${f%.zst}")" ;;`,
        `    *) cp "$f" "${dataDir}/databasus_wal_restore/" ;;`,
        `  esac`,
        `done`,
        `{`,
        `  echo "restore_command = 'cp \\"databasus_wal_restore/%f\\" \\"%p\\"'"${targetLines}`,
        `} >> "${dataDir}/postgresql.auto.conf"`,
        `touch "${dataDir}/recovery.signal"`,
      ].join('\n'),
    });

    steps.push({
      titleKey: 'backups.physical.restore.manualSteps.recoveryParameters',
      // Archive recovery aborts when these are below the source's values - the served script sets
      // them from the control file automatically, but a hand-run restore must do it too.
      code: [
        `# Read the source's recovery-required parameters from the restored control file:`,
        controlDataCommand(environment, image, pgBin, dataDir),
        `# then set each at or above the printed value in "${dataDir}/postgresql.auto.conf" and restart:`,
        `#   max_connections, max_worker_processes, max_wal_senders,`,
        `#   max_prepared_transactions, max_locks_per_transaction`,
      ].join('\n'),
    });
  }

  return steps;
};