launchpad-reviewers team mailing list archive
-
launchpad-reviewers team
-
Mailing list archive
-
Message #30445
[Merge] ~cjwatson/launchpad:charm-postgresql-extras into launchpad:master
Colin Watson has proposed merging ~cjwatson/launchpad:charm-postgresql-extras into launchpad:master.
Commit message:
charm: Add launchpad-postgresql-extras
Requested reviews:
Launchpad code reviewers (launchpad-reviewers)
For more details, see:
https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/450894
The current production databases have a few unusual quirks, applied using the `ols-postgresql-extras` subordinate charm. This hasn't been open-sourced and has quite a lot of cruft, so I thought it would be best to have our own modernized version of it. This charm should be suitable for use in all environments.
When related to the `postgresql` charm, this adds the following features:
* Installs `launchpad-database-dependencies` (thereby pulling in various extensions we need).
* Installs and configures `pgbouncer`, optionally reporting metrics to `telegraf` (requires https://code.launchpad.net/~cjwatson/charm-telegraf/+git/charm-telegraf/+merge/450883).
* Optionally installs a cron job to push full database backups to another machine, which can be used for the weekly sync of staging from production.
--
Your team Launchpad code reviewers is requested to review the proposed merge of ~cjwatson/launchpad:charm-postgresql-extras into launchpad:master.
diff --git a/charm/launchpad-postgresql-extras/README.md b/charm/launchpad-postgresql-extras/README.md
new file mode 100644
index 0000000..de51763
--- /dev/null
+++ b/charm/launchpad-postgresql-extras/README.md
@@ -0,0 +1,9 @@
+# Extra Launchpad PostgreSQL configuration
+
+The `launchpad-postgresql-extras` subordinate adds extra things needed on
+the Launchpad database units, particularly `pgbouncer` configuration and
+some refinements to backup handling.
+
+The following relation is useful:
+
+ juju relate postgresql:juju-info launchpad-postgresql-extras:juju-info
diff --git a/charm/launchpad-postgresql-extras/charmcraft.yaml b/charm/launchpad-postgresql-extras/charmcraft.yaml
new file mode 100644
index 0000000..bb8d8bc
--- /dev/null
+++ b/charm/launchpad-postgresql-extras/charmcraft.yaml
@@ -0,0 +1,47 @@
+type: charm
+bases:
+ - build-on:
+ - name: ubuntu
+ channel: "20.04"
+ architectures: [amd64]
+ run-on:
+ - name: ubuntu
+ channel: "20.04"
+ architectures: [amd64]
+parts:
+ charm-wheels:
+ source: https://git.launchpad.net/~ubuntuone-hackers/ols-charm-deps/+git/wheels
+ source-commit: "42c89d9c66dbe137139b047fd54aed49b66d1a5e"
+ source-submodules: []
+ source-type: git
+ plugin: dump
+ organize:
+ "*": charm-wheels/
+ prime:
+ - "-charm-wheels"
+ ols-layers:
+ source: https://git.launchpad.net/ols-charm-deps
+ source-commit: "f63ae0386275bf9089b30c8abae252a0ea523633"
+ source-submodules: []
+ source-type: git
+ plugin: dump
+ organize:
+ "*": layers/
+ stage:
+ - layers
+ prime:
+ - "-layers"
+ charm:
+ after:
+ - charm-wheels
+ - ols-layers
+ source: .
+ plugin: reactive
+ build-snaps: [charm]
+ build-packages: [libpq-dev, python3-dev]
+ build-environment:
+ - CHARM_LAYERS_DIR: $CRAFT_STAGE/layers/layer
+ - CHARM_INTERFACES_DIR: $CRAFT_STAGE/layers/interface
+ - PIP_NO_INDEX: "true"
+ - PIP_FIND_LINKS: $CRAFT_STAGE/charm-wheels
+ reactive-charm-build-arguments: [--binary-wheels-from-source]
diff --git a/charm/launchpad-postgresql-extras/config.yaml b/charm/launchpad-postgresql-extras/config.yaml
new file mode 100644
index 0000000..f9e9495
--- /dev/null
+++ b/charm/launchpad-postgresql-extras/config.yaml
@@ -0,0 +1,56 @@
+options:
+ cron_mailto:
+ type: string
+ description: Email address for output from cron jobs.
+ default: "error-reports@xxxxxxxxxxxxxx"
+ install_sources:
+ default: |
+ - ppa:launchpad/ppa
+ install_keys:
+ default: |
+ - null # PPA keys securely added via Launchpad.
+ pgbouncer_db_config:
+ type: string
+ default: ""
+ description: Content of pgbouncer.ini's [databases] section.
+ pgbouncer_extra_config:
+ type: string
+ default: |
+ client_idle_timeout = 0
+ client_login_timeout = 60
+ default_pool_size = 50
+ idle_transaction_timeout = 90
+ ignore_startup_parameters = application_name,extra_float_digits
+ max_client_conn = 3000
+ pool_mode = transaction
+ reserve_pool_size = 0
+ reserve_pool_timeout = 1
+ server_check_delay = 30
+ server_connect_timeout = 15
+ server_idle_timeout = 600
+ server_login_retry = 15
+ stats_users = nagios,telegraf_stats
+ description: >
+ Extra pgbouncer configuration, included as-is from the main config file.
+ pgbouncer_port:
+ type: int
+ default: 5433
+ description: Port number for pgbouncer.
+ pgbouncer_userlist:
+ type: string
+ default: ""
+ description: Content of pgbouncer's userlist.txt.
+ push_backups_databases:
+ type: string
+ default: ""
+ description: >
+ Space-separated list of database names for which to push full backups.
+ push_backups_destination:
+ type: string
+ default: ""
+ description: If set, push full database backups here.
+ push_backups_private_ssh_key:
+ type: string
+ default: ""
+ description: >
+ Base64-encoded private SSH key to use to push full database backups.
diff --git a/charm/launchpad-postgresql-extras/files/pgbouncer_override.conf b/charm/launchpad-postgresql-extras/files/pgbouncer_override.conf
new file mode 100644
index 0000000..8313ac0
--- /dev/null
+++ b/charm/launchpad-postgresql-extras/files/pgbouncer_override.conf
@@ -0,0 +1,5 @@
+# This file is managed by the launchpad-postgresql-extras charm. If you
+# change this, backport changes to the branch or risk losing them next charm
+# upgrade.
+[Service]
+LimitNOFILE=65535
diff --git a/charm/launchpad-postgresql-extras/files/push-backups b/charm/launchpad-postgresql-extras/files/push-backups
new file mode 100755
index 0000000..8a48574
--- /dev/null
+++ b/charm/launchpad-postgresql-extras/files/push-backups
@@ -0,0 +1,80 @@
+#! /usr/bin/python3
+# Push backups to another machine. This is useful for regularly restoring
+# staging from production.
+
+import glob
+import shlex
+import subprocess
+from argparse import ArgumentParser
+from pathlib import Path
+
+
+def find_latest_dumps(backups_path, database, count):
+ yield from sorted(backups_path.glob(f"{glob.escape(database)}.*.dump"))[
+ -count:
+ ]
+
+
+def main():
+ parser = ArgumentParser()
+ parser.add_argument(
+ "--backups-path",
+ type=Path,
+ default="/var/lib/postgresql/backups",
+ help=(
+ "Directory where backups are stored "
+ "(default: /var/lib/postgresql/backups)"
+ ),
+ )
+ parser.add_argument(
+ "--count",
+ type=int,
+ default=2,
+ help="Number of backups to push (default: 2)",
+ )
+ parser.add_argument("--bwlimit", help="Bandwidth limit")
+ parser.add_argument(
+ "--ssh-key",
+ help="SSH key to use to authenticate to destination system",
+ )
+ parser.add_argument(
+ "--dry-run",
+ default=False,
+ action="store_true",
+ help="Only show the command to run",
+ )
+ parser.add_argument(
+ "destination", help="Destination system, e.g. postgres@hostname:/path/"
+ )
+ parser.add_argument("databases", metavar="database", nargs="+")
+ args = parser.parse_args()
+
+ command = ["rsync"]
+ if args.bwlimit is not None:
+ command.append(f"--bwlimit={args.bwlimit}")
+ if args.ssh_key:
+ command.append(f"--rsh=ssh -i {shlex.quote(args.ssh_key)}")
+ else:
+ command.append("--rsh=ssh")
+ command.extend(["-a", "--delete-after"])
+ for database in args.databases:
+ for path in find_latest_dumps(args.backups_path, database, args.count):
+ command.extend(["--include", path.name])
+ command.extend(["--exclude", "*"])
+ # rsync wants a trailing slash on both source and destination.
+ # str(Path(...)) strips any trailing slash so we can add one
+ # unconditionally there; only add one to the destination if the command
+ # line didn't include it.
+ destination = args.destination
+ if not destination.endswith("/"):
+ destination += "/"
+ command.extend([f"{args.backups_path}/", destination])
+
+ if args.dry_run:
+ print("Would run:", " ".join(shlex.quote(arg) for arg in command))
+ else:
+ subprocess.run(command, check=True)
+
+
+if __name__ == "__main__":
+ main()
diff --git a/charm/launchpad-postgresql-extras/icon.svg b/charm/launchpad-postgresql-extras/icon.svg
new file mode 100644
index 0000000..b2889cc
--- /dev/null
+++ b/charm/launchpad-postgresql-extras/icon.svg
@@ -0,0 +1 @@
+<svg id="Layer_1" data-name="Layer 1" xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 165.39062 165.39062"><defs><style>.cls-1{fill:#e9500e;}.cls-2{fill:#fff;}</style></defs><rect class="cls-1" width="165.39062" height="165.39062"/><path class="cls-2" d="M29.63876,57.97189C43.189,67.692,61.13456,69.25577,77.65457,62.15038c16.25576-6.87157,27.74036-21.43444,29.97828-38.0075.04663-.34331.11016-.81367-1.59861-1.24044l-10.10934-2.197c-.3254-.04494-.79136-.04967-1.15258,1.22455C91.37844,36.07384,84.34062,45.04243,72.6347,50.1123c-11.77316,5.10029-23.18748,4.05279-35.91893-3.29386-.58119-.27843-.91909-.26086-1.45568.52577l-5.77947,8.65163A1.34512,1.34512,0,0,0,29.63876,57.97189Z" transform="translate(0.39062 0.39062)"/><path class="cls-2" d="M79.86106,139.66026l10.3631.565c1.74155.03446,1.79122-.42981,1.83717-.77312,2.23826-16.5734-4.97222-33.66107-18.81739-44.59422C59.196,83.62132,41.47815,80.36935,25.83365,86.14747a1.33956,1.33956,0,0,0-.67918,1.85373l3.28,9.88226c.30952.90153.62816,1.011,1.26443.89409,14.22464-3.70543,25.50717-1.68748,35.50635,6.3512,9.94174,7.9934,14.34865,18.50754,13.86883,33.08867C79.08524,139.50144,79.53735,139.615,79.86106,139.66026Z" transform="translate(0.39062 0.39062)"/><path class="cls-2" d="M86.50488,70.59048a10.50817,10.50817,0,0,0-1.39587-.09461A9.35237,9.35237,0,0,0,79.39915,72.382a9.61981,9.61981,0,1,0,7.10573-1.79156Z" transform="translate(0.39062 0.39062)"/><path class="cls-2" d="M138.26869,53.18923,133.457,43.97736c-.68628-1.51583-1.22793-1.36985-1.79594-1.17657-15.382,6.63165-25.99848,21.22156-28.40434,39.03776-2.40755,17.82971,3.97169,34.72681,17.0647,45.19906a1.177,1.177,0,0,0,.90794.32844,1.48362,1.48362,0,0,0,.99546-.54l6.76175-8.11166c.62342-.78393.35783-1.18333.0321-1.52461-10.60639-10.44454-14.5764-20.81677-12.84905-33.60769,1.73682-12.86121,8.51918-22.08254,21.34457-29.019C138.52854,53.95289,138.36533,53.421,138.26869,53.18923Z" transform="translate(0.39062 0.39062)"/></svg>
\ No newline at end of file
diff --git a/charm/launchpad-postgresql-extras/layer.yaml b/charm/launchpad-postgresql-extras/layer.yaml
new file mode 100644
index 0000000..879d9e6
--- /dev/null
+++ b/charm/launchpad-postgresql-extras/layer.yaml
@@ -0,0 +1,10 @@
+includes:
+ - layer:basic
+ - layer:apt
+ - interface:pgsql
+repo: https://git.launchpad.net/launchpad
+options:
+ apt:
+ packages:
+ - launchpad-database-dependencies
+ - pgbouncer
diff --git a/charm/launchpad-postgresql-extras/metadata.yaml b/charm/launchpad-postgresql-extras/metadata.yaml
new file mode 100644
index 0000000..af95541
--- /dev/null
+++ b/charm/launchpad-postgresql-extras/metadata.yaml
@@ -0,0 +1,20 @@
+name: launchpad-postgresql-extras
+display-name: launchpad-postgresql-extras
+summary: Customization for Launchpad's PostgreSQL services
+maintainer: Launchpad Developers <launchpad-dev@xxxxxxxxxxxxxxxxxxx>
+description: |
+ Launchpad is an open source suite of tools that help people and teams
+ to work together on software projects.
+
+ This charm adds pgbouncer and other necessary customizations to
+ Launchpad's PostgreSQL units.
+subordinate: true
+requires:
+ juju-info:
+ interface: juju-info
+ scope: container
+provides:
+ # For this to work, the pgbouncer_userlist option must include a line of
+ # the form `"telegraf_stats" "PASSWORD"`.
+ telegraf-stats:
+ interface: pgsql
diff --git a/charm/launchpad-postgresql-extras/reactive/launchpad-postgresql-extras.py b/charm/launchpad-postgresql-extras/reactive/launchpad-postgresql-extras.py
new file mode 100644
index 0000000..030a6df
--- /dev/null
+++ b/charm/launchpad-postgresql-extras/reactive/launchpad-postgresql-extras.py
@@ -0,0 +1,206 @@
+# Copyright 2022 Canonical Ltd. This software is licensed under the
+# GNU Affero General Public License version 3 (see the file LICENSE).
+
+import base64
+import os.path
+import re
+import shutil
+import subprocess
+from pathlib import Path
+from typing import Dict
+
+from charmhelpers.core import hookenv, host, templating
+from charms.reactive import (
+ clear_flag,
+ endpoint_from_flag,
+ hook,
+ set_flag,
+ when,
+ when_not,
+ when_not_all,
+)
+from psycopg2.extensions import make_dsn
+
+
+def install_scripts(config):
+ hookenv.log("Installing scripts.")
+ host.mkdir(config["scripts_dir"], perms=0o755)
+ shutil.copy2(
+ "files/push-backups", Path(config["scripts_dir"], "push-backups")
+ )
+ if config["push_backups_private_ssh_key"]:
+ postgres_ssh = Path(os.path.expanduser("~postgres"), ".ssh")
+ host.mkdir(
+ postgres_ssh, owner="postgres", group="postgres", perms=0o700
+ )
+ config["push_backups_private_ssh_key_path"] = str(
+ postgres_ssh / "id-launchpad-postgresql-extras"
+ )
+ host.write_file(
+ config["push_backups_private_ssh_key_path"],
+ base64.b64decode(config["push_backups_private_ssh_key"]),
+ owner="postgres",
+ group="postgres",
+ perms=0o600,
+ )
+
+
+def reload_or_restart_pgbouncer(service):
+ subprocess.run(
+ ["systemctl", "reload-or-restart", "pgbouncer.service"], check=True
+ )
+
+
+pgbouncer_config = Path("/etc/pgbouncer/pgbouncer.ini")
+pgbouncer_databases = Path("/etc/pgbouncer/databases.ini")
+pgbouncer_extra_config = Path("/etc/pgbouncer/extra_config.txt")
+pgbouncer_userlist = Path("/etc/pgbouncer/userlist.txt")
+pgbouncer_override = Path(
+ "/etc/systemd/system/pgbouncer.service.d/override.conf"
+)
+
+
+@host.restart_on_change(
+ {
+ str(pgbouncer_config): ["reload-pgbouncer"],
+ str(pgbouncer_databases): ["reload-pgbouncer"],
+ str(pgbouncer_extra_config): ["reload-pgbouncer"],
+ str(pgbouncer_userlist): ["reload-pgbouncer"],
+ str(pgbouncer_override): ["pgbouncer.service"],
+ },
+ restart_functions={"reload-pgbouncer": reload_or_restart_pgbouncer},
+)
+def configure_pgbouncer(config):
+ hookenv.log("Configuring pgbouncer.")
+ templating.render(
+ "pgbouncer.ini.j2",
+ pgbouncer_config,
+ config,
+ owner="postgres",
+ group="postgres",
+ perms=0o644,
+ )
+ host.write_file(
+ pgbouncer_databases,
+ config["pgbouncer_db_config"],
+ owner="postgres",
+ group="postgres",
+ perms=0o644,
+ )
+ host.write_file(
+ pgbouncer_extra_config,
+ config["pgbouncer_extra_config"],
+ owner="postgres",
+ group="postgres",
+ perms=0o644,
+ )
+ host.write_file(
+ pgbouncer_userlist,
+ config["pgbouncer_userlist"],
+ owner="postgres",
+ group="postgres",
+ perms=0o640,
+ )
+ host.mkdir(pgbouncer_override.parent, perms=0o755)
+ shutil.copy2("files/pgbouncer_override.conf", pgbouncer_override)
+ subprocess.run(["systemctl", "daemon-reload"], check=True)
+ hookenv.open_port(config["pgbouncer_port"])
+
+
+def configure_cron(config):
+ hookenv.log("Writing crontab.")
+ templating.render(
+ "crontab.j2",
+ "/etc/cron.d/launchpad-postgresql-extras",
+ config,
+ perms=0o644,
+ )
+
+
+@when_not("apt.queued_installs", "service.configured")
+def configure():
+ config = dict(hookenv.config())
+
+ config["scripts_dir"] = "/srv/launchpad/scripts"
+ install_scripts(config)
+
+ configure_pgbouncer(config)
+ host.service_resume("pgbouncer.service")
+ configure_cron(config)
+
+ set_flag("service.configured")
+
+
+@when("service.configured")
+def check_is_running():
+ hookenv.status_set("active", "Ready")
+
+
+@hook("upgrade-charm")
+def upgrade_charm():
+ clear_flag("service.configured")
+
+
+@when("config.changed")
+def config_changed():
+ clear_flag("service.configured")
+
+
+def parse_userlist(userlist: str) -> Dict[str, str]:
+ """Parse a pgbouncer userlist file.
+
+ This is a deliberately forgiving parser; we only need enough to extract
+ the telegraf_stats user's password. See the pgbouncer(5) manual page
+ for the format.
+ """
+ credentials = {}
+ for line in userlist.splitlines():
+ m = re.match(r'"([^"]*)" "([^"]*)".*', line)
+ if m is not None:
+ credentials[m.group(1)] = m.group(2)
+ return credentials
+
+
+@when("service.configured", "telegraf-stats.connected")
+@when_not("service.telegraf-stats.configured")
+def configure_telegraf_stats():
+ config = hookenv.config()
+ pgsql = endpoint_from_flag("telegraf-stats.connected")
+ credentials = parse_userlist(config["pgbouncer_userlist"])
+ telegraf_stats_password = credentials.get("telegraf_stats")
+ if not telegraf_stats_password:
+ hookenv.status_set(
+ "blocked", "No telegraf_stats password in pgbouncer_userlist"
+ )
+ return
+ for relation in pgsql.relations:
+ relation.to_publish_raw.update(
+ {
+ "allowed-subnets": ",".join(
+ sorted(
+ {
+ subnet: True
+ for subnet in hookenv.egress_subnets(
+ relation.relation_id, hookenv.local_unit()
+ )
+ }
+ )
+ ),
+ "master": make_dsn(
+ database="pgbouncer",
+ host=hookenv.ingress_address(
+ relation.relation_id, hookenv.local_unit()
+ ),
+ password=telegraf_stats_password,
+ port=config["pgbouncer_port"],
+ user="telegraf_stats",
+ ),
+ }
+ )
+ set_flag("service.telegraf-stats.configured")
+
+
+@when("service.telegraf-stats.configured")
+@when_not_all("service.configured", "telegraf-stats.connected")
+def deconfigure_telegraf_stats():
+ clear_flag("service.telegraf-stats.configured")
diff --git a/charm/launchpad-postgresql-extras/templates/crontab.j2 b/charm/launchpad-postgresql-extras/templates/crontab.j2
new file mode 100644
index 0000000..f873f31
--- /dev/null
+++ b/charm/launchpad-postgresql-extras/templates/crontab.j2
@@ -0,0 +1,9 @@
+TZ=UTC
+MAILTO={{ cron_mailto }}
+
+{% if push_backups_databases and push_backups_destination -%}
+# The PostgreSQL charm handles backups, but on production we also need to
+# push backups to staging for weekly restores.
+22 * * * * postgres {{ scripts_dir }}/push-backups --bwlimit=10m {% if push_backups_private_ssh_key_path %}--ssh-key {{ push_backups_private_ssh_key_path }} {% endif %}{{ push_backups_databases }} {{ push_backups_destination }}
+{% endif %}
+
diff --git a/charm/launchpad-postgresql-extras/templates/pgbouncer.ini.j2 b/charm/launchpad-postgresql-extras/templates/pgbouncer.ini.j2
new file mode 100644
index 0000000..aecd935
--- /dev/null
+++ b/charm/launchpad-postgresql-extras/templates/pgbouncer.ini.j2
@@ -0,0 +1,209 @@
+;; This file is maintained by Juju.
+;; lp:launchpad-postgresql-extras
+;;
+;; Edits may be made in place, but will be overwritten next
+;; time the charm is upgraded. If you make edits in place,
+;; make sure they are synced back to the above branch.
+;;
+;; We run pgbouncer with identical configuration on both primary
+;; and standbys. After failover or switchover, the hardcoded DNS
+;; names will need to be changed to match.
+
+
+%include /etc/pgbouncer/databases.ini
+
+[pgbouncer]
+
+logfile = /var/log/postgresql/pgbouncer.log
+pidfile = /var/run/postgresql/pgbouncer.pid
+
+listen_addr = *
+listen_port = {{ pgbouncer_port }}
+
+; unix socket is also used for -R.
+; On debian it should be /var/run/postgresql
+;unix_socket_dir = /tmp
+;unix_socket_mode = 0777
+;unix_socket_group =
+unix_socket_dir = /var/run/postgresql
+
+;;;
+;;; Authentication settings
+;;;
+
+; any, trust, plain, crypt, md5
+auth_type = md5
+;auth_file = /8.0/main/global/pg_auth
+auth_file = /etc/pgbouncer/userlist.txt
+
+; comma-separated list of users, who are allowed to change settings
+admin_users = pgbouncer,canonicaladmin
+
+; comma-separated list of users who are just allowed to use SHOW command
+;stats_users = nagios,telegraf_stats
+
+;;;
+;;; Pooler personality questions
+;;;
+
+; When server connection is released back to pool:
+; session - after client disconnects
+; transaction - after transaction finishes
+; statement - after statement finishes
+
+; pool_mode transaction assumes we don't use certain features of PG
+; See https://wiki.canonical.com/UbuntuOne/OperationsPlus/PoolingDBConnections#SQL feature map for pooling modes
+; This can now be overridden per database.
+;pool_mode = transaction
+
+;
+; Query for cleaning connection immediately after releasing from client.
+; No need to put ROLLBACK here, pgbouncer does not reuse connections
+; where transaction is left open.
+;
+; Query for 8.3+:
+; DISCARD ALL;
+;
+; Older versions:
+; RESET ALL; SET SESSION AUTHORIZATION DEFAULT
+;
+; Empty if transaction pooling is in use.
+;
+server_reset_query = DISCARD ALL
+
+;
+; Comma-separated list of parameters to ignore when given
+; in startup packet. Newer JDBC versions require the
+; extra_float_digits here.
+;
+;ignore_startup_parameters = application_name,extra_float_digits
+
+;
+; When taking idle server into use, this query is ran first.
+; SELECT 1
+;
+server_check_query = select 1
+
+; If server was used more recently that this many seconds ago,
+; skip the check query. Value 0 may or may not run in immediately.
+;server_check_delay = 30
+
+;;;
+;;; Connection limits
+;;;
+
+; total number of clients that can connect
+;max_client_conn = 3000
+
+; default pool size. 20 is good number when transaction pooling
+; is in use, in session pooling it needs to be the number of
+; max clients you want to handle at any moment
+;default_pool_size = 50
+
+; how many additional connection to allow in case of trouble
+;reserve_pool_size = 0
+
+; if a clients needs to wait more than this many seconds, use reserve pool
+;reserve_pool_timeout = 1
+
+; log if client connects or server connection is made
+;log_connections = 1
+
+; log if and why connection was closed
+;log_disconnections = 1
+
+; log error messages pooler sends to clients
+;log_pooler_errors = 1
+
+
+; If off, then server connections are reused in LIFO manner
+;server_round_robin = 0
+
+;;;
+;;; Timeouts
+;;;
+
+;; Close server connection if its been connected longer.
+server_lifetime = 3600
+
+;; Close server connection if its not been used in this time.
+;; Allows to clean unnecessary connections from pool after peak.
+;server_idle_timeout = 600
+
+;; Cancel connection attempt if server does not answer takes longer.
+;server_connect_timeout = 15
+
+;; If server login failed (server_connect_timeout or auth failure)
+;; then wait this many second.
+;server_login_retry = 15
+
+;; Dangerous. Server connection is closed if query does not return
+;; in this time. Should be used to survive network problems,
+;; _not_ as statement_timeout. (default: 0)
+;query_timeout = 0
+
+;; Dangerous. Client connection is closed if the query is not assigned
+;; to a server in this time. Should be used to limit the number of queued
+;; queries in case of a database or network failure. (default: 0)
+;query_wait_timeout = 0
+
+;; Dangerous. Client connection is closed if no activity in this time.
+;; Should be used to survive network problems. (default: 0)
+;client_idle_timeout = 0
+
+;; Disconnect clients who have not managed to log in after connecting
+;; in this many seconds.
+;client_login_timeout = 60
+
+;; Disconnect clients idling inside a transaction for this long. This
+;; is required to stop misbehaving clients victimizing other clients.
+;idle_transaction_timeout = 90
+
+;; Clean automatically created database entries (via "*") if they
+;; stay unused in this many seconds.
+; autodb_idle_timeout = 3600
+
+;;;
+;;; Low-level tuning options
+;;;
+
+;; buffer for streaming packets
+;pkt_buf = 2048
+
+;; man 2 listen
+;listen_backlog = 128
+
+;; networking options, for info: man 7 tcp
+
+;; Linux: notify program about new connection only if there
+;; is also data received. (Seconds to wait.)
+;; On Linux the default is 45, on other OS'es 0.
+;tcp_defer_accept = 0
+
+;; In-kernel buffer size (Linux default: 4096)
+;tcp_socket_buffer = 0
+
+;; whether tcp keepalive should be turned on (0/1)
+;tcp_keepalive = 1
+
+;; following options are Linux-specific.
+;; they also require tcp_keepalive=1
+
+;; count of keepalive packets
+;tcp_keepcnt = 0
+
+;; how long the connection can be idle,
+;; before sending keepalive packets
+;tcp_keepidle = 0
+
+;; The time between individual keepalive probes.
+;tcp_keepintvl = 0
+
+;; DNS lookup caching time
+;dns_max_ttl = 15
+
+;; DNS zone SOA lookup period
+;dns_zone_check_period = 0
+
+%include /etc/pgbouncer/extra_config.txt
+
diff --git a/charm/launchpad-postgresql-extras/wheelhouse.txt b/charm/launchpad-postgresql-extras/wheelhouse.txt
new file mode 100644
index 0000000..f0b5222
--- /dev/null
+++ b/charm/launchpad-postgresql-extras/wheelhouse.txt
@@ -0,0 +1 @@
+psycopg2==2.8.4
