mmrwoods

#!/bin/bash

# Vacuum, analyze and re-index all non-template postgres databases
# Should be run as root to avoid any permissions issues

db_list_sql="SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn ORDER BY datname"

for db in $(su - postgres -c "psql -At -c '$db_list_sql'"); do
  su - postgres -c "vacuumdb --analyze --quiet $db"
  su - postgres -c "PGOPTIONS='--client-min-messages=warning' reindexdb --quiet $db"

mmrwoods

#!/bin/bash

# Create a clean SQL dump of each non-template postgres database
# Should be run as root to avoid any permissions issues

dest_dir=/var/local/backup/postgres

db_list_sql="SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn ORDER BY datname"

for db in $(su - postgres -c "psql -At -c '$db_list_sql'"); do

dustinfarris

#!/usr/bin/env python

import sys
sys.platform = 'linux2'

import os.path
import mimetypes
import urlparse

import requests

stengland

#EXTM3U
#EXTINF:-1,BBC - Radio 1
http://bbcmedia.ic.llnwd.net/stream/bbcmedia_radio1_mf_p
#EXTINF:-1,BBC - Radio 2
http://bbcmedia.ic.llnwd.net/stream/bbcmedia_radio2_mf_p
#EXTINF:-1,BBC - Radio 3
http://open.live.bbc.co.uk/mediaselector/5/select/version/2.0/mediaset/http-icy-aac-lc-a/format/pls/vpid/bbc_radio_three.pls
#EXTINF:-1,BBC - Radio 4
http://bbcmedia.ic.llnwd.net/stream/bbcmedia_radio4fm_mf_p
#EXTINF:-1,BBC - Radio 5 live

eddieantonio

"""
Installs your SSH key on other hosts. A fabfile for lazy people.
"""

from fabric.api import task, run, put, env, cd

# Use sh instead of bash.
env.shell = '/bin/sh -l -c'

@task

rgreenjr

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

-- show running queries (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%' 

mmrwoods

# dump all databases once every 24 hours
45 4 * * * root nice -n 19 su - postgres -c "pg_dumpall --clean" | gzip -9 > /var/local/backup/postgres/postgres_all.sql.gz

# vacuum all databases every night (full vacuum on Sunday night, lazy vacuum every other night)
45 3 * * 0 root nice -n 19 su - postgres -c "vacuumdb --all --full --analyze"
45 3 * * 1-6 root nice -n 19 su - postgres -c "vacuumdb --all --analyze --quiet"

# re-index all databases once a week
0 3 * * 0 root nice -n 19 su - postgres -c 'psql -t -c "select datname from pg_database order by datname;" | xargs -n 1 -I"{}" -- psql -U postgres {} -c "reindex database {};"'