Backup automation — pg_dump + cron

An explicit allow-list of user-data tables keeps backup scope obvious and reviewable.

1. Table allow-list

export const USER_DATA_TABLES = [
  'posts',
  'comments',
  'categories',
  'users',
  'wishlist_items',
  'purchases',
  'ledger_entries',
  'messages',
  'reports',
] as const;

2. pg_dump → gzip pipeline

import { spawn } from 'node:child_process';
import { createWriteStream } from 'node:fs';

export async function backupUserData(): Promise<string> {
  const date = new Date().toISOString().slice(0, 10);
  const file = `backups/user-data/user-data-${date}.sql.gz`;
  const out = createWriteStream(file);

  const pgdump = spawn('pg_dump', [
    '--host', process.env.BLOG_DB_HOST!,
    '--username', process.env.BLOG_DB_USER!,
    '--dbname', process.env.BLOG_DB_NAME!,
    '--no-owner', '--no-privileges',
    ...USER_DATA_TABLES.flatMap((t) => ['-t', t]),
  ], { env: { ...process.env, PGPASSWORD: process.env.BLOG_DB_PASSWORD } });

  const gzip = spawn('gzip', ['-c']);
  pgdump.stdout.pipe(gzip.stdin);
  gzip.stdout.pipe(out);

  await new Promise<void>((res, rej) => {
    gzip.on('close', (c) => c === 0 ? res() : rej());
  });
  return file;
}

• --no-owner --no-privileges — avoid permission errors on restore
• Pipe pg_dump → gzip — memory-friendly
• PGPASSWORD in env only — never logs

3. 7-day rolling retention

for (const f of await readdir(dir)) {
  if (!f.endsWith('.sql.gz')) continue;
  const s = await stat(path.join(dir, f));
  if ((Date.now() - s.mtimeMs) / 86400000 > 7) {
    await unlink(path.join(dir, f));
  }
}

4. Cron via instrumentation.ts

Next.js runs instrumentation.ts once at boot.

import cron from 'node-cron';

export async function register() {
  if (process.env.NEXT_RUNTIME !== 'nodejs') return;
  if (process.env.DISABLE_CRON === '1') return;

  cron.schedule('0 2 * * *', async () => {
    const { backupUserData, pruneOldBackups } = await import('@/shared/lib/backup-db');
    const file = await backupUserData();
    await pruneOldBackups(path.dirname(file));
    logger.info('backup_ok', { file });
  }, { timezone: 'Asia/Seoul' });
}

5. Admin UI

export default async function Page() {
  const files = await readdir('backups/user-data');
  const rows = await Promise.all(files.filter(f => f.endsWith('.sql.gz'))
    .map(async f => ({ name: f, ...(await stat(path.join('backups/user-data', f))) })));
  return <BackupsView rows={rows} />;
}

Download via streamed Route Handler.

export async function GET(_req, { params }) {
  const { name } = await params;
  if (!/^user-data-\d{4}-\d{2}-\d{2}\.sql\.gz$/.test(name))
    return new NextResponse('invalid', { status: 400 });
  const stream = createReadStream(path.join('backups/user-data', name));
  return new NextResponse(stream as any, {
    headers: {
      'Content-Type': 'application/gzip',
      'Content-Disposition': `attachment; filename="${name}"`,
    },
  });
}

Filename regex is essential — stops path traversal.

6. Restore

gunzip -c user-data-2026-05-06.sql.gz | psql -U postgres -d blog

Rehearse once a month against a throwaway container. A backup you have never restored is not a backup.

7. Gotchas

• Client pg_dump major version must match server
• Forgotten tables → silent data loss
• Missing cron timezone → runs at UTC
• 644 permissions on backups → other containers can read them. Use 600.

Closing

"Set and forget" is the real risk. Quarterly restore drills turn backups into an actual safety net.

Next

• 08-e2e-and-deploy