Page MenuHomeSoftware Heritage
Differential D1550

Add pgbouncer configuration
ClosedPublic
Actions

Authored by ardumont on Jun 5 2019, 1:37 PM.

Details

Reviewers
olasd
ftigeot
douardda
Group Reviewers
Reviewers
Commits
rSPSITE6f7fc6fe41f3: pgbouncer: Adapt listen_addr, admin_users config appropriately
rSPSITE56bbad5d8a63: Reference actual pgbouncer configuration
rSPSITE438377ce0287: Puppetfile: Reference pgbouncer module
Summary

This installs a pgbouncer instance on the belvedere machine.

The setup matches closely the one already installed on both prado and belvedere (mostly the db connections for now only bounces to swh on prado, swh-deposit, swh-scheduler, swh-scheduler-updater to belvedere itself, and then keep the initial number of connections, the type of connection trust, already setuped there etc...).

Once deployed, the differences i saw will be:

  • the current /etc/pgbouncer/pgbouncer.ini is complete and commented (probably installed through a template file then got adapted). The new one will only be a subset of what's defined here (without comments).
  • the actual /etc/pgbouncer/userlist.txt is plain and the password is human-readable, it will no longer be the case. The password will be hashed (md5 though).
  • dropped the default host * default connection. I prefer we have failure connections when something is not defined.

Related T1784
Related T1234

Test Plan
bin/octocatalog-diff --octocatalog-diff-args --no-truncate-details --to update_configuration belvedere
Found host belvedere.internal.softwareheritage.org
Cloning into '/tmp/swh-ocd.Inbkkk8A/environments/production/data/private'...
done.
Cloning into '/tmp/swh-ocd.Inbkkk8A/environments/update_configuration/data/private'...
done.
*** Running octocatalog-diff on host belvedere.internal.softwareheritage.org
I, [2019-06-06T11:21:01.348618 #7626]  INFO -- : Catalogs compiled for belvedere.internal.softwareheritage.org
I, [2019-06-06T11:21:02.050810 #7626]  INFO -- : Diffs computed for belvedere.internal.softwareheritage.org
diff origin/production/belvedere.internal.softwareheritage.org current/belvedere.internal.softwareheritage.org
*******************************************
+ Anchor[pgbouncer::begin]
*******************************************
+ Anchor[pgbouncer::end]
*******************************************
+ Concat::Fragment[/tmp/pgbouncer-paramtmpfile_database] =>
   parameters =>
      "order": "01"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
;; Section autogenerated by puppet module
;; Do not manually update

[databases]
<<<
*******************************************
+ Concat::Fragment[/tmp/pgbouncer-paramtmpfile_params] =>
   parameters =>
      "order": "03"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
;; Section autogenerated by puppet module
;; Do not manually update

[pgbouncer]

;; Set hieradata hash value for "pgbouncer::params::config_params" to override
;; below values
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
unix_socket_dir = /var/run/postgresql
auth_file = /etc/pgbouncer/userlist.txt
listen_addr = 127.0.0.1,192.168.100.210
listen_port = 5432
admin_users = postgres,olasd
stats_users = postgres
auth_type = hba
pool_mode = session
server_reset_query = DISCARD ALL
server_check_query = select 1
server_check_delay = 30
max_client_conn = 2000
default_pool_size = 2000
client_tls_sslmode = allow
client_tls_ca_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
client_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key
client_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
server_tls_sslmode = allow
auth_hba_file = /etc/postgresql/11/secondary/pg_hba.conf
max_db_connections = 2000
max_user_connections = 2000
log_connections = 0
log_disconnections = 0
<<<
*******************************************
+ Concat::Fragment[/tmp/pgbouncer-paramtmpfile_users] =>
   parameters =>
      "order": "05"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
;; Section autogenerated by puppet module
;; Do not manually update

[users]
<<<
*******************************************
+ Concat::Fragment[postgres] =>
   parameters =>
      "order": "01"
      "target": "/etc/pgbouncer/userlist.txt"
      "content": >>>
"postgres" "md5e8a48653851e28c69d0506508fb27fc5"
<<<
*******************************************
+ Concat::Fragment[postgres_users] =>
   parameters =>
      "order": "06"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
; Created from: pgbouncer_module_userlist
<<<
*******************************************
+ Concat::Fragment[softwareheritage_postgres] =>
   parameters =>
      "order": "02"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
; Created from: pgbouncer_module_databases
softwareheritage = host=prado.internal.softwareheritage.org port=5433 user=postgres
softwareheritage-deposit = host=belvedere.internal.softwareheritage.org port=5434 user=postgres
softwareheritage-scheduler = host=belvedere.internal.softwareheritage.org port=5434 user=postgres
softwareheritage-scheduler-updater = host=belvedere.internal.softwareheritage.org port=5434 user=postgres
<<<
*******************************************
+ Concat[/etc/pgbouncer/pgbouncer.ini] =>
   parameters =>
      "backup": "puppet"
      "ensure": "present"
      "ensure_newline": false
      "group": "pgbouncer"
      "mode": "0640"
      "order": "alpha"
      "owner": "pgbouncer"
      "path": "/etc/pgbouncer/pgbouncer.ini"
      "replace": true
      "show_diff": true
      "warn": false
*******************************************
+ Concat[/etc/pgbouncer/userlist.txt] =>
   parameters =>
      "backup": "puppet"
      "ensure": "present"
      "ensure_newline": false
      "group": "pgbouncer"
      "mode": "0640"
      "order": "alpha"
      "owner": "pgbouncer"
      "path": "/etc/pgbouncer/userlist.txt"
      "replace": true
      "show_diff": true
      "warn": false
*******************************************
+ Concat_file[/etc/pgbouncer/pgbouncer.ini] =>
   parameters =>
      "backup": "puppet"
      "ensure_newline": false
      "group": "pgbouncer"
      "mode": "0640"
      "order": "alpha"
      "owner": "pgbouncer"
      "replace": true
      "show_diff": true
      "tag": "_etc_pgbouncer_pgbouncer.ini"
*******************************************
+ Concat_file[/etc/pgbouncer/userlist.txt] =>
   parameters =>
      "backup": "puppet"
      "ensure_newline": false
      "group": "pgbouncer"
      "mode": "0640"
      "order": "alpha"
      "owner": "pgbouncer"
      "replace": true
      "show_diff": true
      "tag": "_etc_pgbouncer_userlist.txt"
*******************************************
+ Concat_fragment[/tmp/pgbouncer-paramtmpfile_database] =>
   parameters =>
      "order": "01"
      "tag": "_etc_pgbouncer_pgbouncer.ini"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
;; Section autogenerated by puppet module
;; Do not manually update

[databases]
<<<
*******************************************
+ Concat_fragment[/tmp/pgbouncer-paramtmpfile_params] =>
   parameters =>
      "order": "03"
      "tag": "_etc_pgbouncer_pgbouncer.ini"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
;; Section autogenerated by puppet module
;; Do not manually update

[pgbouncer]

;; Set hieradata hash value for "pgbouncer::params::config_params" to override
;; below values
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
unix_socket_dir = /var/run/postgresql
auth_file = /etc/pgbouncer/userlist.txt
listen_addr = 127.0.0.1,192.168.100.210
listen_port = 5432
admin_users = postgres,olasd
stats_users = postgres
auth_type = hba
pool_mode = session
server_reset_query = DISCARD ALL
server_check_query = select 1
server_check_delay = 30
max_client_conn = 2000
default_pool_size = 2000
client_tls_sslmode = allow
client_tls_ca_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
client_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key
client_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
server_tls_sslmode = allow
auth_hba_file = /etc/postgresql/11/secondary/pg_hba.conf
max_db_connections = 2000
max_user_connections = 2000
log_connections = 0
log_disconnections = 0
<<<
*******************************************
+ Concat_fragment[/tmp/pgbouncer-paramtmpfile_users] =>
   parameters =>
      "order": "05"
      "tag": "_etc_pgbouncer_pgbouncer.ini"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
;; Section autogenerated by puppet module
;; Do not manually update

[users]
<<<
*******************************************
+ Concat_fragment[postgres] =>
   parameters =>
      "order": "01"
      "tag": "_etc_pgbouncer_userlist.txt"
      "target": "/etc/pgbouncer/userlist.txt"
      "content": >>>
"postgres" "md5e8a48653851e28c69d0506508fb27fc5"
<<<
*******************************************
+ Concat_fragment[postgres_users] =>
   parameters =>
      "order": "06"
      "tag": "_etc_pgbouncer_pgbouncer.ini"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
; Created from: pgbouncer_module_userlist
<<<
*******************************************
+ Concat_fragment[softwareheritage_postgres] =>
   parameters =>
      "order": "02"
      "tag": "_etc_pgbouncer_pgbouncer.ini"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
; Created from: pgbouncer_module_databases
softwareheritage = host=prado.internal.softwareheritage.org port=5433 user=postgres
softwareheritage-deposit = host=belvedere.internal.softwareheritage.org port=5434 user=postgres
softwareheritage-scheduler = host=belvedere.internal.softwareheritage.org port=5434 user=postgres
softwareheritage-scheduler-updater = host=belvedere.internal.softwareheritage.org port=5434 user=postgres
<<<
*******************************************
+ File[/etc/default/pgbouncer] =>
   parameters =>
      "content": "START=1"
      "ensure": "file"
*******************************************
+ Package[pgbouncer] =>
   parameters =>
      "ensure": "installed"
*******************************************
+ Pgbouncer::Databases[pgbouncer_module_databases] =>
   parameters =>
      "databases": [{"source_db"=>"softwareheritage", "host"=>"prado.internal.softwareheritage.org", "auth_user"=>"postgres", "port"=>"5433"}, {"source_db"=>"softwareheritage-deposit", "host"=>"belvedere.internal.softwareheritage.org", "auth_user"=>"postgres", "port"=>"5434"}, {"source_db"=>"softwareheritage-scheduler", "host"=>"belvedere.internal.softwareheritage.org", "auth_user"=>"postgres", "port"=>"5434"}, {"source_db"=>"softwareheritage-scheduler-updater", "host"=>"belvedere.internal.softwareheritage.org", "auth_user"=>"postgres", "port"=>"5434"}]
*******************************************
+ Pgbouncer::Userlist[pgbouncer_module_userlist] =>
   parameters =>
      "auth_list": [{"user"=>"postgres", "password"=>""}]
      "paramtmpfile": "/tmp/pgbouncer-paramtmpfile"
*******************************************
+ Service[pgbouncer] =>
   parameters =>
      "enable": true
      "ensure": "running"
      "subscribe": ["Concat[/etc/pgbouncer/pgbouncer.ini]", "Concat[/etc/pgbouncer/userlist.txt]"]
*******************************************

Diff Detail

Repository
rSPSITE puppet-swh-site
Branch
update_configuration
Lint
No Linters Available
Unit
No Unit Test Coverage
Build Status
Buildable 6033
Build 8297: arc lint + arc unit

Event Timeline

ardumont created this revision.Jun 5 2019, 1:37 PM
Harbormaster completed remote builds in B6033: Diff 5086.Jun 5 2019, 1:37 PM
ardumont updated this revision to Diff 5097.Jun 5 2019, 3:15 PM
  • pgbouncer: Adapt listen_addr, admin_users config appropriately
Harbormaster completed remote builds in B6044: Diff 5097.Jun 5 2019, 3:15 PM
ardumont edited the test plan for this revision. (Show Details)Jun 5 2019, 3:17 PM
ardumont added projects: Puppet recipes, System administration.
ftigeot accepted this revision.Jun 6 2019, 8:57 AM

Looks good for a first draft.

This revision is now accepted and ready to land.Jun 6 2019, 8:57 AM
ardumont edited the summary of this revision. (Show Details)Jun 6 2019, 9:54 AM
ardumont edited the summary of this revision. (Show Details)Jun 6 2019, 10:22 AM
ardumont edited the summary of this revision. (Show Details)
ardumont planned changes to this revision.Jun 6 2019, 11:09 AM
ardumont added inline comments.
data/defaults.yaml
1402

Heads up, i'll fill some undeclared options here as well.
Explicit is better than implicit and all that.

1429

Heads up, i'm missing ports here as we are using different clusters.

ardumont updated this revision to Diff 5103.Jun 6 2019, 11:20 AM

As mentioned:

  • add the missing destination db cluster port (that would have been a source of connection failures)
  • explicit all actual existing pgbouncer options (this diff now gives the same output as grep -v '^;' /etc/pgbouncer/pgbouncer.ini)
This revision is now accepted and ready to land.Jun 6 2019, 11:20 AM
Harbormaster completed remote builds in B6050: Diff 5103.Jun 6 2019, 11:20 AM
ardumont edited the test plan for this revision. (Show Details)Jun 6 2019, 11:23 AM
ardumont edited the test plan for this revision. (Show Details)
ardumont updated this revision to Diff 5104.Jun 6 2019, 11:27 AM

Remove dest_db as the source and destination names are the same

Harbormaster completed remote builds in B6051: Diff 5104.Jun 6 2019, 11:27 AM
ardumont edited the test plan for this revision. (Show Details)Jun 6 2019, 11:28 AM
ardumont edited the summary of this revision. (Show Details)Jun 6 2019, 11:30 AM
ardumont updated this revision to Diff 5105.Jun 6 2019, 11:31 AM

Branch diff to production branch

Harbormaster completed remote builds in B6052: Diff 5105.Jun 6 2019, 11:31 AM
Closed by commit rSPSITE438377ce0287: Puppetfile: Reference pgbouncer module (authored by ardumont). · Explain WhyJun 6 2019, 11:32 AM
This revision was automatically updated to reflect the committed changes.

Revision Contents
Changeset List

PathSize
4 lines
data/
49 lines
manifests/
1 line
site-modules/
profile/
manifests/
12 lines

Diff 5086

View Options

Puppetfile

Loading...
View Options

data/defaults.yaml

Loading...
View Options

manifests/site.pp

Loading...
View Options

site-modules/profile/manifests/pgbouncer.pp

Loading...
About · Developer information · Legal