Postgresql

• SchemaVerse is a strategy game for learning postgresql.
• psql tips

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”