Skip to main content

PostgreSQL-migrations-help-center

!!! TLDR: Refer to Strong Migrations gem for how to consider a migration safe/unsafe and how to write it. !!!

Also read safe-pg-migrations gem about how to write safe migrations.

Database operation complexity (as of 9.6)​

change_column_default​

aka ALTER TABLE table ALTER COLUMN column SET DEFAULT TO value (ref) Really fast and simple operation.

  • full table lock
  • full table scan
  • slow operation
  • rails schema cache invalidation
  • prepared statements invalidation

remove_column​

migration guide

aka ALTER TABLE table DROP COLUMN column Really fast operation UNLESS vacuum tasks are running on the table.

  • full table lock
  • full table scan
  • slow operation
  • rails schema cache invalidation (workaround)
  • prepared statements invalidation (workaround)

add_column​

migration guide

aka ALTER TABLE "table_name" ADD "some_column" any type DEFAULT anything

Really fast operation UNLESS a default value (including NULL) is specified, in that case the whole table needs to be rewritten in postgresql < 11 and it prevents any other operation, including reads, for the involved table.

Workaround is highlighted in link, generally speaking even 2/300k records in a table can lead to downtime, see this incident report for more details.

Create read-only and read-write non root users​

Full references can be found here

REVOKE ALL ON SCHEMA "public" FROM public;
CREATE USER {user_ro} WITH ENCRYPTED PASSWORD '{password}';
GRANT CONNECT ON DATABASE {database} TO {user_ro};
GRANT USAGE ON SCHEMA "public" TO {user_ro};
\c {database}
GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO {user_ro};
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO {user_ro};
CREATE USER {user_rw} WITH ENCRYPTED PASSWORD '{password}';
GRANT CONNECT ON DATABASE {database} TO {user_rw};
GRANT USAGE ON SCHEMA public TO {user_rw};
\c {database}
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "public" TO {user_rw};
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO {user_rw};
GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA "public" TO {user_rw};
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT, UPDATE ON SEQUENCES TO {user_rw};

Import a single table from dump​

When importing a database, it can be useful to have a partial import with a single table. To do that, the pg_restore utility can help, but it requires the dump to be created with --format custom. If the dump is a plain text file, sed can help us extracting a portion of it; obviously, depending on dump size, timing change. For example, to extract SQL commands for a single table:

sed -n '/^COPY public.TABLE /,/^\\\.$/p' DUMP.sql.dump > TABLE_ONLY.sql.dump

This will extract the COPY statements only, so the database schema needs to be in place before importing it. To import that, a simple psql import command can be used:

psql -U USER -d DATABASE -f TABLE_ONLY.sql.dump

NOTE: if you're getting errors about wrong column format on the last line of the file, try editing it. With nano, you can use Ctrl-W Ctrl-V to reach the end of the file.