PostgreSQL Advanced Cheat Sheet

SHOW wal_level;

SHOW max_wal_senders;

CREATE PUBLICATION app_pub FOR TABLE public.users, public.orders;

CREATE SUBSCRIPTION app_sub
CONNECTION 'host=primary-db port=5432 dbname=appdb user=replicator password=secret'
PUBLICATION app_pub;

ALTER SUBSCRIPTION app_sub REFRESH PUBLICATION;

SELECT * FROM pg_create_physical_replication_slot('standby_1');

SELECT pg_drop_replication_slot('standby_1');

SELECT pg_current_wal_lsn();

BEGIN;

COMMIT;

ROLLBACK;

SAVEPOINT before_step_two;

ROLLBACK TO SAVEPOINT before_step_two;

LOCK TABLE orders IN SHARE ROW EXCLUSIVE MODE;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET statement_timeout = '5s';

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER analytics_remote
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'analytics-db', dbname 'analytics', port '5432');

CREATE USER MAPPING FOR appuser
SERVER analytics_remote
OPTIONS (user 'analytics_user', password 'secret');

IMPORT FOREIGN SCHEMA public FROM SERVER analytics_remote INTO analytics_fdw;

CREATE FOREIGN TABLE analytics_fdw.events (
  event_id bigint,
  payload jsonb
)
SERVER analytics_remote
OPTIONS (schema_name 'public', table_name 'events');

DROP SERVER analytics_remote CASCADE;

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_users_email_trgm ON public.users USING GIN (email gin_trgm_ops);

CREATE EXTENSION IF NOT EXISTS citext;

ALTER TABLE public.users ALTER COLUMN email TYPE citext;

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('public.users');

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SELECT uuid_generate_v4();