Connect to a database through a bastion

SSH_PRIVATE_KEY=~/.ssh/id_rsa

RDS_DATABASE_HOST=opszero-database.aasdasd.us-east-1.rds.amazonaws.com
RDS_DATABASE_PORT=5432
RDS_DATABASE_USERNAME=postgres
RDS_DATABASE_PASSWORD=postgres
RDS_DATABASE_DB=postgres_development

BASTION_USERNAME=ubuntu
BASTION_HOST=137.32.32.83

ssh -i ${SSH_PRIVATE_KEY} -f -N -L ${RDS_DATABASE_PORT}:${RDS_DATABASE_HOST}:${RDS_DATABASE_PORT} ${BASTION_USERNAME}@${BASTION_HOST} -v

# In another terminal

psql "postgresql://${RDS_DATABASE_USERNAME}:${RDS_DATABASE_PASSWORD}@127.0.0.1:5432/${RDS_DATABASE_DB}"

Dump and Restore

pg_dump 'postgresql://postgres:postgres@34.29.235.84:5432/db' > backup.sql
psql 'postgresql://${RDS_DATABASE_USERNAME}:${RDS_DATABASE_PASSWORD}@34.29.235.84:5432/restored_db -f backup.sql


Create a User

CREATE USER newuser123 WITH PASSWORD 'foobar123';

# Read only access
GRANT CONNECT ON DATABASE database_name TO newuser123;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO newuser123;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO newuser123;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO newuser123;

# Grant all on database
GRANT ALL PRIVILEGES ON DATABASE database_name TO newuser123;

# Turn User into Super User
ALTER USER newuser123 WITH SUPERUSER;


Useful Stats

-- show running queries (pre 9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

-- kill running query
SELECT pg_cancel_backend(procpid);

-- kill idle query
SELECT pg_terminate_backend(procpid);

-- vacuum command
VACUUM (VERBOSE, ANALYZE);

-- all database users
select * from pg_stat_activity where current_query not like '<%';

-- all databases and their sizes
select * from pg_user;

-- all tables and their size, with/without indexes
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;

-- cache hit rates (should not be less than 0.99)
SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;

-- table index usage rates (should not be less than 0.99)
SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- how many indexes are in cache
SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM pg_statio_user_indexes;