migration.js

const { Pool } = require('pg')
const map = require('p-map')
const cliProgress = require('cli-progress')
const retry = require('p-retry')

const START = 0;
const SKIPTO = 0;
const LIMIT = 65000000;
const BATCH_SIZE = 1000;

const bar = new cliProgress.SingleBar({
  stopOnComplete: true,
  etaBuffer: 100
}, cliProgress.Presets.shades_classic);

const exec = async () => {
  const client = new Pool({
    connectionString: process.env.DB,
    max: 50,
    statement_timeout: 360000,
    ssl: {
      rejectUnauthorized: false
    }
  })
  await client.connect()

  console.log('Connected!')

  let queries = []
  for (let i = START * BATCH_SIZE; i < LIMIT; i += BATCH_SIZE) {
    queries.push(`UPDATE "upload" SET "backup_urls" = (SELECT COALESCE(array_agg("url"), '{}') FROM "backup" WHERE "upload_id" = "upload"."id") WHERE ("id" BETWEEN ${i} AND ${i + BATCH_SIZE - 1}) AND "backup_urls" IS NULL;`)
  }

  bar.start(queries.length + 1, SKIPTO)

  if (SKIPTO) queries = queries.slice(SKIPTO)

  await map(queries, async (query, idx) => {
    const id = `Query ${idx}/${queries.length}: ${query}`
    await retry(async () => client.query(query), { retries: 10 })
    bar.increment()
  }, { concurrency: 1 })
  const end = `UPDATE "upload" SET "backup_urls" = (SELECT COALESCE(array_agg("url"), '{}') FROM "backup" WHERE "upload_id" = "upload"."id") WHERE "id" >= ${LIMIT} AND "backup_urls" IS NULL;`
  await retry(async () => client.query(end), { retries: 10 })
  bar.increment()
  await client.end()
  console.log('Done!')
  process.exit()
}

exec()

migration.sql

ALTER TABLE "upload" ADD COLUMN IF NOT EXISTS "backup_urls" TEXT[];