PostgreSQL Advanced Cheat Sheet

SHOW wal_level;

# Inspect the current WAL level setting.

SHOW max_wal_senders;

# Check capacity for replication sender processes.

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

# Publish table changes for logical replication.

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

# Subscribe to logical replication changes from another server.

ALTER SUBSCRIPTION app_sub REFRESH PUBLICATION;

# Pull publication membership changes to the subscriber.

SELECT * FROM pg_create_physical_replication_slot('standby_1');

# Create a physical slot to retain WAL for a replica.

SELECT pg_drop_replication_slot('standby_1');

# Remove a replication slot when it is no longer needed.

SELECT pg_current_wal_lsn();

# Inspect the current write-ahead log position.

BEGIN;

# Start an explicit transaction block.

COMMIT;

# Persist all changes in the current transaction.

ROLLBACK;

# Discard all uncommitted work.

SAVEPOINT before_step_two;

# Mark a partial rollback point within a transaction.

ROLLBACK TO SAVEPOINT before_step_two;

# Undo work after the named savepoint while keeping the transaction open.

LOCK TABLE orders IN SHARE ROW EXCLUSIVE MODE;

# Acquire an explicit table lock.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# Change isolation for the current transaction.

SET statement_timeout = '5s';

# Abort long-running statements automatically.

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

# Install the built-in foreign data wrapper for PostgreSQL servers.

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

# Define a remote PostgreSQL server.

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

# Store remote authentication details for one local role.

IMPORT FOREIGN SCHEMA public FROM SERVER analytics_remote INTO analytics_fdw;

# Bring remote tables into a local schema automatically.

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

# Define a foreign table manually.

DROP SERVER analytics_remote CASCADE;

# Remove the foreign server and dependent objects.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

# Install trigram support for similarity search and GIN/GiST indexes.

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

# Accelerate ILIKE and fuzzy matches on text.

CREATE EXTENSION IF NOT EXISTS citext;

# Install case-insensitive text type support.

ALTER TABLE public.users ALTER COLUMN email TYPE citext;

# Make a column case-insensitive with the `citext` type.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

# Install size and tuple-stat inspection helpers.

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

# Estimate dead tuples and free space in a relation.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

# Install UUID generation helpers.

SELECT uuid_generate_v4();

# Generate a random UUID using `uuid-ossp`.