--- name: "pg_staging" section: "1" platform: "linux" package: "pgstaging" version: "0.11-1" release: "Debian 7 (Wheezy)" platforms: ["linux"] url: "https://new.carta.tech/pg_staging" --- # pg\_staging(1) Prepare a staging environment from http accessible backups ## NAME pg\_staging - Prepare a staging environment from http accessible backups ## SYNOPSIS pg\_staging \[--version\] \[-c configuration file\] \[-t tmpdir\] \[-d debug\] \[-v verbose\] \[-q quiet\] \ \ \[\\] ## DESCRIPTION pg\_staging is a tool to prepare and maintain a staging environment from http accessible backups. Its main job (see command restore) is to: 1\. create target database, named dbname\_YYYYMMDD 2\. add this database to the pgbouncer setup, reload pgbouncer 3\. fetch a backup file named dbname.date -I.dump 4\. prepare a filtered catalog for the given dump, depending on configuration 5\. given sql\_path and a pre directory, psql -f \*.sql in there 6\. pg\_restore the backup with the custom catalog to the created database 7\. if restore\_vacuum is true, VACUUM ANALYZE the database 8\. given sql\_path setup and a post directory, psql -f \*.sql in there 9\. switch pgbouncer entry for dbname to target dbname\_YYYYMMDD and reload pgbouncer again. 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. ## DEPENDENCIES pg\_staging will use the following external tools: • pgbouncer in order to maintain more than one staging database • pg\_restore which major version must match target database cluster • scp to upload new pgbouncer.ini configuration files • ssh to run the staging-client.sh on the target host See next section for how to prepare those components. ## INITIAL SETUP In order for pg\_staging to be able to manage any target you give it, the following conditions have to be met: 1\. install staging-client.sh on the target host 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: ``` ssh sudo ./staging-client.sh ``` 2\. install and open pgbouncer "trust" connection as maintenance user (dbuser) on the maintenance database (maintdb). This connection will get used to CREATE DATABASE and DROP DATABASE. ## COMMANDS commands This will show available commands and a docstring for each. ### main operation init \ Prepare a cluster given a pg\_dumpall -g file, see option dumpall\_url. dump \ \[\\] 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. redump \ \[\\] dump even when destination filename already exists. restore \ \[\\] 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. drop \ \[\\] DROP DATABASE the given database, or today’s one if none given. It won’t edit pgbouncer configuration accordingly though, as of version 0.5. switch \ \[\\] Change the canonical \ entry in pgbouncer to point to given dated instance, default to today’s one. purge \ Clean the database section by dropping out the older databases and keeping online only the keep\_bases most recent. vacuumdb \ \[\\] VACUUM ANALYZE given database. load \ \ pg\_restore given dump file, this allow to skip the auto downloading part of the restore command. fetch \ \[\\] Only fetch the dump, do not restore it, do not remove it afterwards. presql \ \[\\] Source the sql\_path/pre/\*.sql files into the database by means of psql -f, in alphabetical order, **without** recursive walking into subdirs. postsql \ \[\\] Source the sql\_path/post/\*.sql files into the database by means of psql -f, in alphabetical order, **without** recursive walking into subdirs. ### listings databases Show the list of database sections parsed into the .ini file. backups \ \[\\] Show \ available backups on the http host. dbsize \ \[\\] Show database size of given instance, as returned by SELECT pg\_size\_pretty(pg\_database\_size(dbname\_YYYYMMDD)); dbsizes --all \| --match \ \| \ 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 \ it’ll show sizes of all instances of given section. show \ \[\\] \ Show current value of \ for given database. ### pgbouncer pgbouncer \ Show pgbouncer database listing for given dbname. pause \ \[\\] Issue a pgbouncer pause \ command. resume \ \[\\] Issue a pgbouncer resume \ command. ### londiste londiste \ \[\\] 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). ### remote service management Note that all actions (start, stop, restart, status) are not available to all services (londiste, ticker, pgbouncer). start \ \ \[\\] Start remote service for given dbname, where service is one of londiste, ticker, or pgbouncer. stop \ \ \[\\] Stop remote service for given dbname, where service is one of londiste, ticker, or pgbouncer. restart \ \ \[\\] Restart remote service for given dbname, where service is one of londiste, ticker, or pgbouncer. status \ \ \[\\] Show status remote service for given dbname, where service is one of londiste, ticker, or pgbouncer. ### experimental and internal nodata Show tables we want to skip loading DATA for, those we are a subscriber of. catalog Show the filtered out catalog we’ll give to pg\_restore -L. triggers 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). ## OPTIONS ``` Usage: pg_staging.py [-c ] command dbname ``` ``` 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 ``` ## CONSOLE 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. config \ read given filename as the current configuration file for pg\_staging. set \ \ \ set given option to given value for current interactive session only. get \ \ print current value of given option. verbose switch on and off the verbosity of pg\_staging. ## INTERNALS How we use tools. Will get expanded if questions arise. ## SEE ALSO [pg\_staging(5)](/pg_staging/5.md) ## AUTHOR pg\_staging is written by Dimitri Fontaine \<**dim@tapoueh.org**\[1\]\>. ## NOTES - 1\. dim@tapoueh.org mailto:dim@tapoueh.org