---
name: "pg_staging"
section: "5"
platform: "linux"
package: "pgstaging"
version: "0.11-1"
release: "Debian 7 (Wheezy)"
platforms: ["linux"]
url: "https://new.carta.tech/pg_staging/5"
---

# pg\_staging(5)

Prepare a staging environment from http accessible backups

## NAME

pg\_staging - Prepare a staging environment from http accessible backups

## SYNOPSIS

```
[GLOBAL]
[database_name.env]
```

## DESCRIPTION

Configuration file is a .ini containing several sections: first a GLOBAL section containing common options to the following sections, then a section per target database.

## SECTIONS

The \[DEFAULT\] section allows to provide default values for any setting describe here, that you can override in any of the specific sections.

Each specific section represent a target database to restore. The options will allow to say from which backup and on which host. The host knob is used in commands such as pg\_restore, scp and ssh, so can be either an ip address or a hostname (resolv.conf applies).

## OPTIONS

backup\_host

Which host to connect to to fetch the backup file. HTTP only.

backup\_base\_url

Path leading to the backup for the section’s database. The database dump file will get expanded to dbname.date -I.dump, this is not (yet?) configurable as of version 0.5.

dumpall\_url

Path to the cluster pg\_dumpall -g accompanying file, e.g. 8.2-main.globals.sql.

host

Target where to restore the dump file and manage pgbouncer setup.

dbname

Database name, will get appended by the backup date to form a dbname\_YYYYMMDD name.

dbowner

Owner of the database to get restored.

dbuser

Maintenance database role used in the connections to create or drop databases.

maintdb

Maintenance database to connect to, usually postgres.

db\_encoding

Database encoding to use in the following statement:

```
CREATE DATABASE ... WITH OWNER "..." ENCODING ´...´;
```

postgres\_port

PostgreSQL port to use in pgbouncer configuration file.

postgres\_major

Major version of postgresql, allows to choose the pg\_restore binary with the default settings.

pgbouncer\_port

pgbouncer port to connect to, used for all pg\_staging operations.

pgbouncer\_conf

Configuration file name for pgbouncer, e.g. +/etc/pgbouncer/pgbouncer.ini/

remove\_dump

boolean (True or False) to control if pg\_staging removes the dump file after restore.

keep\_bases

How many databases to keep arround, pg\_staging drops less recent databases. Not Yet Implemented as of 0.5.

auto\_switch

boolean to control if pg\_staging switches pgbouncer configuration at restore time

use\_sudo

boolean, used when invoking staging-client.sh

pg\_restore

/usr/lib/postgresql/%(postgres\_major)s/bin/pg\_restore

pg\_restore\_st

boolean, use --single-statement option (-1) in pg\_restore.

restore\_vacuum

boolean, when true the restore command will VACUUM ANALYZE the database.

restore\_jobs

Number of working process to use at the same time while restoring. Will set the -j option to pg\_restore and will not check for you that you’re using a pg\_restore client version 8.4 or newer.

replication

configuration filename where to setup the replication options.

tmpdir

Directory where to fetch backup files, defaults to /tmp. Command line argument -t will override .ini setting.

sql\_path

Directory where to dispose SQL files to be sourced with the psql utility, in alphabetical order. This directory may contain a pre and a post subdirectories containing files following the shell \*.sql global pattern. No subdirectory of pre and post will get walked into. The name of the section using the files will always get expanded to the given sql\_path.

schemas

List of schema (comma separated) to restore. If present, any schema not listed here nor in schemas\_nodata will get filtered out from the pg\_restore catalog.

search\_path

List of schemas (comma separated) to add to the database search\_path, using the SQL command +ALTER DATABASE self.dbname SET search\_path TO +.

schemas\_nodata

List of schema (comma separated) to restore without content. The pg\_restore catalog TABLE DATA sections will get filtered out.

## EXAMPLE

```
[DEFAULT]
 backup_host     = backup1.backbone
 dbuser          = postgres
 maintdb         = postgres
 postgres_port   = 5432
 postgres_major  = 8.3
 pgbouncer_port  = 6432
 pgbouncer_conf  = /etc/pgbouncer/pgbouncer.ini
 remove_dump     = True
 keep_bases      = 2
 auto_switch     = True
 use_sudo        = True
 pg_restore      = /usr/lib/postgresql/%(postgres_major)s/bin/pg_restore
 pg_restore_st   = True
 restore_vacuum  = True
 restore_jobs    = 1
 replication     = replication.ini
```

```
[allopass_db.dev]
 backup_base_url = /PostgreSQL/allopass_db/nightly/
 dumpall_url     = /PostgreSQL/clusters/bdd.allopass/8.2-main.globals.sql
 host            = bdd.allopass.dev
 postgres_major  = 8.2
 dbname          = allopass_db
 dbowner         = root
 schemas         = public, payment, utils, jdb
```

## SEE ALSO

[pg\_staging(1)](/pg_staging/1.md)

## AUTHOR

pg\_staging is written by Dimitri Fontaine \<**dim@tapoueh.org**\[1\]\>.

## NOTES

- 1\.

  dim@tapoueh.org

  mailto:dim@tapoueh.org