config.sh.example

#!/bin/bash

# config.sh.example - example configuration.
#
# Rename it to config.sh and adjust the settings for your needs.
#
# Settings can be written in the form
#
#     SETTING=${SETTING:-'some value'}
#
# that means you can also set them from the command line, eg.
#
#     SETTING='another value' bin/some_script.sh
#
# Copyright (c) 2013-2015 Sergey Konoplev
#
# Sergey Konoplev <gray.ru@gmail.com>

# Common settings for all the scripts

# plain: plain log (default)
# kv: key/value pairs
# json: JSON
LOG_FORMAT=${LOG_FORMAT:-'plain'}

HOST=${HOST:-}
PORT=${PORT:-}
USER=${USER:-}

DSN=$(
    echo $([ ! -z "$HOST" ] && echo "-h $HOST") \
         $([ ! -z "$PORT" ] && echo "-p $PORT") \
         $([ ! -z "$USER" ] && echo "-U $USER"))

PSQL="/usr/bin/psql $DSN"
PGDUMP="/usr/bin/pg_dump $DSN"
PGRESTORE="/usr/bin/pg_restore $DSN"
PGDUMPALL="/usr/bin/pg_dumpall $DSN"
PGBASEBACKUP="/usr/bin/pg_basebackup $DSN"
PGRECEIVEXLOG="/usr/bin/pg_receivexlog $DSN"
RSYNC='/usr/bin/rsync -aq'
SSH='/usr/bin/ssh'
SSHADD='/usr/bin/ssh-add'
SSHAGENT='/usr/bin/ssh-agent'
GIT='/usr/bin/git'

# archive_tables.sh

ARCHIVE_DRY_RUN=${ARCHIVE_DRY_RUN:-false}
ARCHIVE_DBNAME_LIST='dbname1 dbname2'
ARCHIVE_LOCAL_DIR=
ARCHIVE_ARCHIVE_DIR='/mnt/archive/parts'
ARCHIVE_PARTS_SQL=$(cat <<EOF
SELECT quote_ident(schemaname), quote_ident(tablename)
FROM pg_tables
WHERE
    tablename ~ E'(table1|table2)_\\\\d{4}\\\\d{2}$' AND
    to_timestamp(regexp_replace(tablename, '^.+_', ''), 'YYYYMM') <
        date_trunc('month', now()) - '1 year'::interval
UNION
SELECT quote_ident(schemaname), quote_ident(tablename)
FROM pg_tables
WHERE
    tablename ~ E'(table3|table4)_\\\\d{4}\\\\d{2}$' AND
    to_timestamp(regexp_replace(tablename, '^.+_', ''), 'YYYYMM') <
        date_trunc('month', now()) - '2 years'::interval
EOF
)
function ARCHIVE_COMMAND_BEFORE_DROP() {
    # /usr/bin/londiste /etc/skytools/londiste-host2-$1.ini provider remove $2
    echo
}
function ARCHIVE_COMMAND_AFTER_DROP() {
    # $PSQL -h host2 $1 -c "DROP TABLE $2"
    echo
}

# commit_schema.sh

SCHEMA_DBNAME_LIST='dbname1 dbname2'
SCHEMA_DIR='/mnt/archive/repo'
SCHEMA_EXCLUDE_SCHEMA_SQL=$(cat <<EOF
SELECT quote_ident(nspname)
FROM pg_namespace
WHERE nspname ~ '^(pgq.*|londiste|_slony)$'
EOF
)
SCHEMA_EXCLUDE_TABLE_SQL=$(cat <<EOF
SELECT quote_ident(schemaname), quote_ident(tablename)
FROM pg_tables
WHERE schemaname = 'partitions'
EOF
)
SCHEMA_SSH_KEY=${SCHEMA_SSH_KEY:-}
SCHEMA_ACTION=${SCHEMA_ACTION:-}

# manage_dumps.sh

DUMPS_DBNAME_LIST='dbname1 dbname2'
DUMPS_LOCAL_DIR=
DUMPS_ARCHIVE_DIR='/mnt/archive/dumps'
DUMPS_KEEP_DAILY_PARTS='3 days'
DUMPS_KEEP_WEEKLY_PARTS='1 month'
DUMPS_KEEP_MONTHLY_PARTS='1 year'
DUMPS_PBZIP2=false

# manage_pitr.sh

PITR_WAL=${PITR_WAL:-false}
PITR_LOCAL_DIR=
PITR_ARCHIVE_DIR='/mnt/archive/basebackups'
PITR_WAL_ARCHIVE_DIR='/mnt/archive/wal'
PITR_WAL_RECEIVER_LOCK_FILE=${PITR_WAL_RECEIVER_LOCK_FILE:-$(
    echo '/tmp/wal_receiver.'$(
        echo $PITR_WAL_ARCHIVE_DIR | sed 's/\//_/g'
    )"-$HOST-$PORT-$USER")}
PITR_KEEP_BACKUPS=2

# process_until_0.sh

PROCESS_DBNAME=${PROCESS_DBNAME:-}

# refresh_matviews.sh

MATVIEWS_DBNAME_LIST='dbname1 dbname2'

# replica_lag.sh

LAG_DBNAME='postgres'
LAG_DSN=${LAG_DSN:-}
LAG_RECEIVE=$((32 * 1024 * 1024))
LAG_REPLAY=$((32 * 1024 * 1024))
LAG_REPLAY_AGE='5 minutes'

# restore_dump.sh

RESTORE_DROP=${RESTORE_DROP:-true}
RESTORE_DBNAME=${RESTORE_DBNAME:-'dbname1_latest'}
RESTORE_FILE=${RESTORE_FILE:-$(
    find $DUMPS_ARCHIVE_DIR -maxdepth 1 -mindepth 1 2>/dev/null | \
    sort | tail -n 1)'/dbname1.dump.gz'}
RESTORE_THREADS=4
RESTORE_FILTER_SQL=$(cat <<EOF
SELECT schemaname, tablename
FROM pg_tables
WHERE tablename ~ 'temp1|temp2'
EOF
)
RESTORE_FILTER_DATA_SQL=$(cat <<EOF
SELECT schemaname, tablename
FROM pg_tables
WHERE tablename ~ 'log1|log2'
EOF
)
RESTORE_FILTER_DATA_PART_SQL=$(cat <<EOF
SELECT schemaname, tablename, 'status <> 1' AS conditions
FROM pg_tables
WHERE tablename ~ 'data1|data2'
EOF
)
RESTORE_PRESERVE_DIR='/mnt/archive/tmp'
RESTORE_PRESERVE_SQL=$(cat <<EOF
SELECT schemaname, tablename
FROM pg_tables
WHERE tablename ~ 'pres1|pres2'
EOF
)

# ssh_tunnel.sh

TUNNEL_PORT=${TUNNEL_PORT:-2345}
TUNNEL_HOST_PORT=${TUNNEL_HOST_PORT:-5432}
TUNNEL_HOST=${TUNNEL_HOST:-'host2'}
TUNNEL_COMP_LEVEL=2
TUNNEL_RETRY_DELAY=60
TUNNEL_LOCK=${TUNNEL_LOCK:-"$TUNNEL_PORT-$TUNNEL_HOST_PORT-$TUNNEL_HOST"}
TUNNEL_LOCK_FILE=${TUNNEL_LOCK_FILE:-"/tmp/ssh_tunnel.$TUNNEL_LOCK"}

# stat_pgbouncer.sh

STAT_PGBOUNCER_FILE=${STAT_PGBOUNCER_FILE:-'/tmp/stat_pgbouncer'}

# stat_postgres_buffercache.sh

STAT_POSTGRES_BUFFERCACHE_TOP_DATABASES_N=5
STAT_POSTGRES_BUFFERCACHE_TOP_TABLES_N=5
STAT_POSTGRES_BUFFERCACHE_TOP_INDEXES_N=5

# stat_postgres_objects.sh

STAT_POSTGRES_OBJECTS_TOP_DATABASES_N=5
STAT_POSTGRES_OBJECTS_TOP_TABLES_N=5
STAT_POSTGRES_OBJECTS_TOP_INDEXES_N=5

# stat_postgres.sh

STAT_POSTGRES_FILE=${STAT_POSTGRES_FILE:-'/tmp/stat_postgres'}

# stat_skytools.sh

STAT_SKYTOOLS_OBJECTS_TOP_N=5

# stat_statements.sh

STAT_DBNAME='postgres'
STAT_REPLICA_DSN=${STAT_REPLICA_DSN:-}
STAT_SNAPSHOT=${STAT_SNAPSHOT:-false}
STAT_SINCE=${STAT_SINCE:-$(date -I)}
STAT_TILL=${STAT_TILL:-$(date -I --date='+1 day')}
STAT_N=${STAT_N:-10}
STAT_ORDER=${STAT_ORDER:-0}
STAT_KEEP_SNAPSHOTS='7 days'

# stat_system_programs.sh

STAT_SYSTEM_TOP_N=5

# stat_system.sh

STAT_SYSTEM_FILE=${STAT_SYSTEM_FILE:-'/tmp/stat_system'}

# terminate_activity.sh

TERMINATE_CONDITIONS=$(cat <<EOF
state <> 'idle' AND -- Recommended due to a pg_stat_activity bug when
                    -- sometimes state is not in sync with xact_start.
                    -- Makes sence for >=9.2.
(
    (
        usename IN ('user1', 'user2') AND
        now() - xact_start > '10 second'::interval
    ) OR (
        usename NOT IN ('postgres') AND
        now() - xact_start > '60 second'::interval
    )
)
EOF
)