再大的 DDL 变更操作都不怕了！一条命令直接搞定

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-lock5 \
--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-lock5 \
--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