pg-osc: 在 PostgreSQL 中以零停机的模式更改表结构

pg-online-schema-change help perform

Usage:
  pg-online-schema-change perform -a, --alter-statement=ALTER_STATEMENT -d, --dbname=DBNAME -h, --host=HOST -p, --port=N -s, --schema=SCHEMA -u, --username=USERNAME

Options:
  -a, --alter-statement=ALTER_STATEMENT # The ALTER statement to perform the schema change
  -s, --schema=SCHEMA                   # The schema in which the table is. Default: public
  -d, --dbname=DBNAME                   # Name of the database
  -h, --host=HOST                       # Server host where the Database is located
  -u, --username=USERNAME               # Username for the Database
  -p, --port=N                          # Port for the Database. Default: 5432
  -w, [--password=PASSWORD]             # DEPRECATED: Password for the Database. Please pass PGPASSWORD environment variable instead.
  -v, [--verbose], [--no-verbose]       # Emit logs in debug mode
  -f, [--drop], [--no-drop]             # Drop the original table in the end after the swap
  -k, [--kill-backends], [--no-kill-backends] # Kill other competing queries/backends when trying to acquire lock for the shadow table creation and swap. It will wait for --wait-time-for-lock duration before killing backends and try upto 3 times.
  -w, [--wait-time-for-lock=N]          # Time to wait before killing backends to acquire lock and/or retrying upto 3 times. Default: 10. It will kill backends if --kill-backends is true, otherwise try upto 3 times and exit if it cannot acquire a lock.
  -c, [--copy-statement=COPY_STATEMENT] # Takes a .sql file location where you can provide a custom query to be played (ex: backfills) when pgosc copies data from the primary to the shadow table. More examples in README.
  -b, [--pull-batch-count=N]            # Number of rows to be replayed on each iteration after copy. Default: 1000. This can be tuned for faster catch up and swap. Best used with delta-count.
  -e, [--delta-count=N]                 # Indicates how many rows should be remaining before a swap should be performed. Default: 20. This can be tuned for faster catch up and swap, especially on highly volume tables. Best used with pull-batch-count.
  -o, [--skip-foreign-key-validation], [--no-skip-foreign-key-validation] # Skip foreign key validation after swap. You shouldn't need this unless you have a very specific use case, like manually validating foreign key constraints after swap.

Usage:
  pg-online-schema-change --version, -v

print the version

export PGPASSWORD=""
pg-online-schema-change perform \
  --alter-statement 'ALTER TABLE books RENAME COLUMN email TO new_email' \
  --dbname "postgres" \
  --host "localhost" \
  --username "jamesbond"

export PGPASSWORD=""
pg-online-schema-change perform \
  --alter-statement 'ALTER TABLE books ADD COLUMN "purchased" BOOLEAN DEFAULT FALSE; ALTER TABLE books RENAME COLUMN email TO new_email;' \
  --dbname "postgres" \
  --host "localhost" \
  --username "jamesbond" \
  --drop

export PGPASSWORD=""
pg-online-schema-change perform \
  --alter-statement 'ALTER TABLE books ADD COLUMN "purchased" BOOLEAN DEFAULT FALSE;' \
  --dbname "postgres" \
  --host "localhost" \
  --username "jamesbond" \
  --wait-time-for-lock 5 \
  --kill-backends \
  --drop

export PGPASSWORD=""
pg-online-schema-change perform \
  --alter-statement 'ALTER TABLE books ADD COLUMN "purchased" BOOLEAN DEFAULT FALSE;' \
  --dbname "postgres" \
  --host "localhost" \
  --username "jamesbond" \
  --pull-batch-count 2000
  --delta-count 500
  --wait-time-for-lock 5 \
  --kill-backends \
  --drop

-- file: /src/query.sql
INSERT INTO %{shadow_table}(foo, bar, baz, rental_id, tenant_id)
SELECT a.foo,a.bar,a.baz,a.rental_id,r.tenant_id AS tenant_id
FROM ONLY examples a
LEFT OUTER JOIN rentals r
ON a.rental_id = r.id

pg-online-schema-change perform \
  --alter-statement 'ALTER TABLE books ADD COLUMN "tenant_id" VARCHAR;' \
  --dbname "postgres" \
  --host "localhost" \
  --username "jamesbond" \
  --copy-statement "/src/query.sql" \
  --drop

docker run --network host -it --rm shayonj/pg-osc:latest \
    pg-online-schema-change perform \
    --alter-statement 'ALTER TABLE books ADD COLUMN "purchased" BOOLEAN DEFAULT FALSE; ALTER TABLE books RENAME COLUMN email TO new_email;' \
    --dbname "postgres" \
    --host "localhost" \
    --username "jamesbond" \
    --drop