# 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 ec2-user@ec2-107-20-223-147.compute-1.amazonaws.com "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 ec2-user@ec2-107-20-223-147.compute-1.amazonaws.com "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 {}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://omalab.gitbook.io/guide/engineering-wiki/database-and-system-maintenance/administration-recipes.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.