Page MenuHomeSoftware Heritage

Add pgbouncer configuration
ClosedPublic

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

Details

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
Lint
Automatic diff as part of commit; lint not applicable.
Unit
Automatic diff as part of commit; unit tests not applicable.

Event Timeline

ardumont created this revision.Wed, Jun 5, 1:37 PM
ardumont updated this revision to Diff 5097.Wed, Jun 5, 3:15 PM
  • pgbouncer: Adapt listen_addr, admin_users config appropriately
ardumont edited the test plan for this revision. (Show Details)Wed, Jun 5, 3:17 PM
ftigeot accepted this revision.Thu, Jun 6, 8:57 AM

Looks good for a first draft.

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

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

1430 ↗(On Diff #5097)

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

ardumont updated this revision to Diff 5103.Thu, Jun 6, 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.Thu, Jun 6, 11:20 AM
ardumont edited the test plan for this revision. (Show Details)Thu, Jun 6, 11:23 AM
ardumont edited the test plan for this revision. (Show Details)
ardumont updated this revision to Diff 5104.Thu, Jun 6, 11:27 AM

Remove dest_db as the source and destination names are the same

ardumont edited the test plan for this revision. (Show Details)Thu, Jun 6, 11:28 AM
ardumont edited the summary of this revision. (Show Details)Thu, Jun 6, 11:30 AM
ardumont updated this revision to Diff 5105.Thu, Jun 6, 11:31 AM

Branch diff to production branch

This revision was automatically updated to reflect the committed changes.