AWS RDS / Aurora
Contents
AWS RDS / Aurora#
PostgreSQL#
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:[email protected]: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';
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;
Turn User into Super User#
ALTER USER newuser123 WITH SUPERUSER; #Uncomment for 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;