Postgresql

Tooling

  • pgloader - can be used for live database migration or from files

pg_dump

  • Can lock tables
  • Can be run against read replicas, but you run into the issue of it being cancelled if your read replica falls to far behind. This can also be a problem which can be worked around by pausing replication

Foreign Data Wrappers

CREATE USER read_data_science WITH PASSWORD 'REPLACETHIS';
GRANT CONNECT ON DATABASE currica_development TO read_data_science;
GRANT USAGE ON SCHEMA public to read_data_science;
GRANT SELECT ON public.visits to read_data_science;

  BEGIN;
  CREATE EXTENSION IF NOT EXISTS postgres_fdw;

  DROP SERVER IF EXISTS prod_read CASCADE;

  CREATE SERVER prod_read
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'localhost', dbname 'currica_development');

  CREATE USER MAPPING FOR CURRENT_USER
    SERVER prod_read
    OPTIONS (user 'data_science_, password 'secret123');

  DROP SCHEMA IF EXISTS app;

  CREATE SCHEMA app;

  IMPORT FOREIGN SCHEMA public
    FROM SERVER prod_read
    INTO app;

  COMMIT;

Source: Thoughtbot - “PostgreSQL’s Foreign Data Wrapper”

Links to this note