Administration recipes

postgresql

listing and aborting long running queries

# lists info about currently running queries
SELECT procpid, datname, query_start, current_query FROM pg_stat_activity; 

# full command for oma production database (mind the SSH key path!!!)
ssh -t -i ~/.ssh/authorized_keys/steig_3.pem [e-mail] "sudo su - postgres -c 'psql -c \"SELECT procpid, datname, query_start, current_query FROM pg_stat_activity\"'"

# to stop a query, you need its procpid and feed it to pg_cancel_backend command
SELECT pg_cancel_backend(procpid);

# again, full command (mind the SSH key path and replace procpid!!!)
ssh -t -i ~/.ssh/authorized_keys/steig_3.pem [e-mail] "sudo su - postgres -c 'psql -c \"SELECT pg_cancel_backend(procpid)\"'"

mongo

listing and aborting long running queries

mongo localhost:27018/admin -umongoadmin -pg3tr8je1
# select right database
use marketfu_web_production” to change db.
# list running operations
db.currentOp()
# kill an operation (replace the id with one from the list)
db.killOp(id)

s3

listing and deleting snapshots w/ certain descriptions (etc.)

# from console:
aws ec2 describe-snapshots > snapshots_state.json

# then irb:
require 'json'
f = File.read "snapshots_state.json"; nil
data = JSON.parse(f); nil
data['Snapshots'].select { |h| h['Description'] =~ /^Snapshot of PGDATA/ }.count
data['Snapshots'].select { |h| h['Description'] =~ /^Snapshot of PGDATA/ }.map { |h| h['VolumeSize'] }
ids = data['Snapshots'].select { |h| h['Description'] =~ /^Snapshot of PGDATA/ }.map { |h| h['SnapshotId'] }
File.open('snapshot_ids.txt','w') { |f| f.write ids.join("\n") }

# then, back to console
wc -l snapshot_ids.txt
cat snapshot_ids.txt | xargs -i aws ec2 delete-snapshot --snapshot-id {}