Useful PG commands

blocking_queries.sql

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

cdc_replication_init.sql

CREATE USER replication_user PASSWORD 'xxx';

GRANT USAGE ON SCHEMA "public" TO replication_user;
GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO replication_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO replication_user;
GRANT rds_replication TO replication_user;

CREATE PUBLICATION publication_name FOR table x, y;

dead_tuples_percent.sql

SELECT relname AS table_name, 
       n_live_tup, 
       n_dead_tup, 
       n_dead_tup::float / (n_live_tup + n_dead_tup) * 100 AS dead_tuple_percentage
FROM pg_stat_all_tables

forgein_data_wrapper.sql

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

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

CREATE USER MAPPING FOR CURRENT_USER
SERVER api_pg
OPTIONS (user 'read-only', password 'pass');

create schema api_external;

CREATE FOREIGN TABLE api_external.table_name (
    created_at_ms int8,
    id uuid
)
SERVER api_pg
OPTIONS (schema_name 'public', table_name 'table_name');

   
GRANT USAGE ON schema api_external TO CURRENT_USER;
grant select on api_external.some_table to CURRENT_USER;