'\" t
.\"     Title: pg_staging
.\"    Author: [see the "AUTHOR" section]
.\" Generator: DocBook XSL Stylesheets v1.75.2 <http://docbook.sf.net/>
.\"      Date: 11/24/2009
.\"    Manual: [FIXME: manual]
.\"    Source: [FIXME: source]
.\"  Language: English
.\"
.TH "PG_STAGING" "1" "11/24/2009" "[FIXME: source]" "[FIXME: manual]"
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
pg_staging \- Prepare a staging environment from http accessible backups
.SH "SYNOPSIS"
.sp
pg_staging [\-\-version] [\-c configuration file] [\-t tmpdir] [\-d debug] [\-v verbose] [\-q quiet] <command> <dbname> [<date>]
.SH "DESCRIPTION"
.sp
pg_staging is a tool to prepare and maintain a staging environment from http accessible backups\&. Its main job (see command restore) is to:
.sp
.RS 4
.ie n \{\
\h'-04' 1.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  1." 4.2
.\}
create target database, named
dbname_YYYYMMDD
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 2.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  2." 4.2
.\}
add this database to the
pgbouncer
setup, reload
pgbouncer
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 3.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  3." 4.2
.\}
fetch a backup file named
dbname\&.date \-I\&.dump
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 4.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  4." 4.2
.\}
prepare a filtered catalog for the given dump, depending on configuration
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 5.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  5." 4.2
.\}
given
sql_path
and a
pre
directory,
psql \-f *\&.sql
in there
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 6.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  6." 4.2
.\}

pg_restore
the backup with the custom catalog to the created database
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 7.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  7." 4.2
.\}
if
restore_vacuum
is true,
VACUUM ANALYZE
the database
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 8.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  8." 4.2
.\}
given
sql_path
setup and a
post
directory,
psql \-f *\&.sql
in there
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 9.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  9." 4.2
.\}
switch
pgbouncer
entry for
dbname
to target
dbname_YYYYMMDD
and reload
pgbouncer
again\&.
.RE
.sp
pg_staging is able to do some more, mainly the commands given allow to give fine grain control over what it does rather than only providing the full restore option\&.
.SH "DEPENDENCIES"
.sp
pg_staging will use the following external tools:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}

pgbouncer
in order to maintain more than one staging database
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}

pg_restore
which major version must match target database cluster
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}

scp
to upload new
pgbouncer\&.ini
configuration files
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}

ssh
to run the
staging\-client\&.sh
on the target host
.RE
.sp
See next section for how to prepare those components\&.
.SH "INITIAL SETUP"
.sp
In order for pg_staging to be able to manage any target you give it, the following conditions have to be met:
.sp
.RS 4
.ie n \{\
\h'-04' 1.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  1." 4.2
.\}
install
staging\-client\&.sh
on the target host
.sp
Currently you have to
ssh
non interactively (setup a password free ssh key authentication) to the target host\&.
pg_staging
will run the following command:
.sp
.if n \{\
.RS 4
.\}
.nf
ssh <host> sudo \&./staging\-client\&.sh <pgbouncer\&.xxxx\&.ini> <pgbouncer_port>
.fi
.if n \{\
.RE
.\}
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 2.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  2." 4.2
.\}
install and open pgbouncer "trust" connection as maintenance user (dbuser) on the maintenance database (maintdb)\&.
.sp
This connection will get used to
CREATE DATABASE
and
DROP DATABASE\&.
.RE
.SH "COMMANDS"
.PP
commands
.RS 4
This will show available commands and a docstring for each\&.
.RE
.SS "main operation"
.PP
init <dbname>
.RS 4
Prepare a cluster given a
pg_dumpall \-g
file, see option
dumpall_url\&.
.RE
.PP
dump <dbname> [<filename>]
.RS 4
dump given database to file, using pg_dump in custom format\&. The pgbouncer_port is used and the filename defaults to
dbname\&.YYYYMMDD\&.dump\&. The
pg_restore
directory part is considered for finding the
pg_dump
binary\&. This command will prevent you from overwriting an existing dump file, see
redump
if you do not want this behavior\&.
.RE
.PP
redump <dbname> [<filename>]
.RS 4
dump even when destination filename already exists\&.
.RE
.PP
restore <dbname> [<YYYYMMDD>]
.RS 4
See
description
section, it explains the details\&. It may be of importance to recall that
restore
will clean up its temporary files, including the dump file itself\&. The clean up happens in case of success and in case of error\&. This command will source pre and post
sql
files, as per
sql_path
config\&.
.RE
.PP
drop <dbname> [<YYYYMMDD>]
.RS 4

DROP DATABASE
the given database, or today\(cqs one if none given\&. It won\(cqt edit
pgbouncer
configuration accordingly though, as of version
0\&.5\&.
.RE
.PP
switch <dbname> [<YYYYMMDD>]
.RS 4
Change the canonical <dbname> entry in
pgbouncer
to point to given dated instance, default to today\(cqs one\&.
.RE
.PP
purge <dbname>
.RS 4
Clean the database section by dropping out the older databases and keeping online only the
keep_bases
most recent\&.
.RE
.PP
vacuumdb <dbname> [<YYYYMMDD>]
.RS 4
VACUUM ANALYZE given database\&.
.RE
.PP
load <dbname> <filename>
.RS 4

pg_restore
given dump file, this allow to skip the auto downloading part of the
restore
command\&.
.RE
.PP
fetch <dbname> [<YYYYMMDD>]
.RS 4
Only fetch the dump, do not restore it, do not remove it afterwards\&.
.RE
.PP
presql <dbname> [<YYYYMMDD>]
.RS 4
Source the
sql_path/pre/*\&.sql
files into the database by means of
psql \-f, in alphabetical order,
\fBwithout\fR
recursive walking into subdirs\&.
.RE
.PP
postsql <dbname> [<YYYYMMDD>]
.RS 4
Source the
sql_path/post/*\&.sql
files into the database by means of
psql \-f, in alphabetical order,
\fBwithout\fR
recursive walking into subdirs\&.
.RE
.SS "listings"
.PP
databases
.RS 4
Show the list of database sections parsed into the \&.ini file\&.
.RE
.PP
backups <dbname> [<YYYYMMDD>]
.RS 4
Show <dbname> available backups on the http host\&.
.RE
.PP
dbsize <dbname> [<YYYMMDD>]
.RS 4
Show database size of given instance, as returned by
SELECT pg_size_pretty(pg_database_size(dbname_YYYYMMDD));
.RE
.PP
dbsizes \-\-all | \-\-match <pattern> | <dbname>
.RS 4
Show database sizes of given instances\&. With
\-\-all
show sizes of all instances of all configured section, with
\-\-match
you can reduce the listing to regexp matching section names, with a
<dbname>
it\(cqll show sizes of all instances of given section\&.
.RE
.PP
show <dbname> [<YYYYMMDD>] <setting>
.RS 4
Show current value of
<setting>
for given database\&.
.RE
.SS "pgbouncer"
.PP
pgbouncer <dbname>
.RS 4
Show
pgbouncer
database listing for given dbname\&.
.RE
.PP
pause <dbname> [<YYYMMDD>]
.RS 4
Issue a
pgbouncer
pause <dbname>
command\&.
.RE
.PP
resume <dbname> [<YYYMMDD>]
.RS 4
Issue a
pgbouncer
resume <dbname>
command\&.
.RE
.SS "londiste"
.PP
londiste <dbname> [<YYYMMDD>]
.RS 4
Prepare londiste configuration files in
TMPDIR, then send them over to the provider hosts in
~pgstating/londiste
and start the daemons (pgqadm\&.py
and
londiste\&.py)\&.
.RE
.SS "remote service management"
.sp
Note that all actions (start, stop, restart, status) are not available to all services (londiste, ticker, pgbouncer)\&.
.PP
start <service> <dbname> [<YYYMMDD>]
.RS 4
Start remote service for given
dbname, where service is one of
londiste,
ticker, or
pgbouncer\&.
.RE
.PP
stop <service> <dbname> [<YYYMMDD>]
.RS 4
Stop remote service for given
dbname, where service is one of
londiste,
ticker, or
pgbouncer\&.
.RE
.PP
restart <service> <dbname> [<YYYMMDD>]
.RS 4
Restart remote service for given
dbname, where service is one of
londiste,
ticker, or
pgbouncer\&.
.RE
.PP
status <service> <dbname> [<YYYMMDD>]
.RS 4
Show status remote service for given
dbname, where service is one of
londiste,
ticker, or
pgbouncer\&.
.RE
.SS "experimental and internal"
.PP
nodata
.RS 4
Show tables we want to skip loading DATA for, those we are a subscriber of\&.
.RE
.PP
catalog
.RS 4
Show the filtered out catalog we\(cqll give to
pg_restore \-L\&.
.RE
.PP
triggers
.RS 4
Show the schema\-qualified functions used by triggers, in order to be able to follow dependancies when filtering out a schema definition (such as
pgq
or
londiste)\&.
.RE
.SH "OPTIONS"
.sp
.if n \{\
.RS 4
.\}
.nf
Usage: pg_staging\&.py [\-c <config_filename>] command dbname <args>
.fi
.if n \{\
.RE
.\}
.sp
.if n \{\
.RS 4
.\}
.nf
Options:
  \-h, \-\-help            show this help message and exit
  \-\-version             show pg_staging version
  \-c CONFIG, \-\-config=CONFIG
                        configuration file, defauts to /etc/hm\-
                        tools/pg_staging\&.ini
  \-n, \-\-dry\-run         simulate operations, don\'t do them
  \-v, \-\-verbose         be verbose and about processing progress
  \-q, \-\-quiet           be terse, almost silent
  \-d, \-\-debug           provide python stacktrace when error
  \-t TMPDIR, \-\-tmpdir=TMPDIR
                        temp dir where to fetch dumps, /tmp
.fi
.if n \{\
.RE
.\}
.SH "CONSOLE"
.sp
If you start pg_staging without command, it will open up an interactive console with basic readline support\&. All previous commands are supported, except for the experimental ones, and the following are added\&.
.PP
config <filename>
.RS 4
read given filename as the current configuration file for
pg_staging\&.
.RE
.PP
set <section> <option> <value>
.RS 4
set given option to given value for current interactive session only\&.
.RE
.PP
get <section> <option>
.RS 4
print current value of given option\&.
.RE
.PP
verbose
.RS 4
switch
on
and
off
the verbosity of
pg_staging\&.
.RE
.SH "INTERNALS"
.sp
How we use tools\&. Will get expanded if questions arise\&.
.SH "SEE ALSO"
.sp
pg_staging(5)
.SH "AUTHOR"
.sp
pg_staging is written by Dimitri Fontaine <\m[blue]\fBdim@tapoueh\&.org\fR\m[]\&\s-2\u[1]\d\s+2>\&.
.SH "NOTES"
.IP " 1." 4
dim@tapoueh.org
.RS 4
\%mailto:dim@tapoueh.org
.RE