8000 feat: pgbouncer optimization · sorokinvld/postgres@eaff28e · GitHub
[go: up one dir, main page]
Skip to content

Commit eaff28e

Browse files
daroradarora
committed
feat: pgbouncer optimization
1 parent 37ac0a9 commit eaff28e

File tree

9 files changed

+45
-41
lines changed

9 files changed

+45
-41
lines changed

‎ansible/files/generate-optimizations.service.j2

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,8 +3,9 @@ Description=Postgresql optimizations
33

44
[Service]
55
Type=oneshot
6-
# we don't want failures from this command to cause PG startup to fail
7-
ExecStart=/bin/bash -c "/opt/supabase-admin-api optimize db --destination-config-file-path /etc/postgresql-custom/generated-optimizations.conf ; exit 0"
6+
# we do not want failures from these commands to cause downstream service startup to fail
7+
ExecStart=-/opt/supabase-admin-api optimize db --destination-config-file-path /etc/postgresql-custom/generated-optimizations.conf
8+
ExecStart=-/opt/supabase-admin-api optimize pgbouncer --destination-config-file-path /etc/pgbouncer-custom/generated-optimizations.ini
89
User=adminapi
910

1011
[Install]

‎ansible/files/pgbouncer_config/pgbouncer.ini.j2

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -358,3 +358,6 @@ default_pool_size = 15
358358

359359
;; Read additional config from other file
360360
;%include /etc/pgbouncer/pgbouncer-other.ini
361+
362+
%include /etc/pgbouncer-custom/generated-optimizations.ini
363+
%include /etc/pgbouncer-custom/custom-overrides.ini

‎ansible/files/pgbouncer_config/pgbouncer.service.j2

Lines changed: 6 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -1,40 +1,20 @@
1-
# Example systemd service unit for PgBouncer
2-
#
3-
# - Adjust the paths in ExecStart for your installation.
4-
#
5-
# - The User setting requires careful consideration. PgBouncer needs
6-
# to be able to place a Unix-domain socket file where PostgreSQL
7-
# clients will look for it. In the olden days, this was in /tmp,
8-
# but systems using systemd now prefer something like
9-
# /var/run/postgresql/. But then some systems also lock down that
10-
# directory so that only the postgres user can write to it. That
11-
# means you need to either
12-
#
13-
# - run PgBouncer as the postgres user, or
14-
#
15-
# - create a separate user and add it to the postgres group and
16-
# make /var/run/postgresql/ group-writable, or
17-
#
18-
# - use systemd to create the sockets; see pgbouncer.socket nearby.
19-
#
20-
# For packagers and deployment systems, this requires some
21-
# coordination between the PgBouncer and the PostgreSQL
22-
# packages/components.
23-
#
241
[Unit]
252
Description=connection pooler for PostgreSQL
263
Documentation=man:pgbouncer(1)
274
Documentation=https://www.pgbouncer.org/
285
After=network.target
29-
#Requires=pgbouncer.socket
6+
{% if supabase_internal is defined %}
7+
Requires=generate-optimizations.service
8+
After=generate-optimizations.service
9+
{% endif %}
3010

3111
[Service]
3212
Type=notify
3313
User=pgbouncer
3414
ExecStart=/usr/local/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini
3515
ExecReload=/bin/kill -HUP $MAINPID
3616
KillSignal=SIGINT
37-
#LimitNOFILE=1024
17+
LimitNOFILE=65536
3818

3919
[Install]
40-
WantedBy=multi-user.target
20+
WantedBy=multi-user.target

‎ansible/playbook.yml

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,11 @@
2929
- name: Install Postgres extensions
3030
import_tasks: tasks/setup-extensions.yml
3131

32+
- name: Install PgBouncer
33+
import_tasks: tasks/setup-pgbouncer.yml
34+
tags:
35+
- install-pgbouncer
36+
3237
- name: Install Supabase specific content
3338
import_tasks: tasks/setup-supabase-internal.yml
3439
tags:
@@ -61,11 +66,6 @@
6166
- name: Install WAL-G
6267
import_tasks: tasks/setup-wal-g.yml
6368

64-
- name: Install PgBouncer
65-
import_tasks: tasks/setup-pgbouncer.yml
66-
tags:
67-
- install-pgbouncer
68-
6969
- name: Install PostgREST
7070
import_tasks: tasks/setup-postgrest.yml
7171
tags:

‎ansible/tasks/internal/admin-api.yml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
- name: adminapi - system user
22
user:
33
name: adminapi
4-
groups: root,admin,postgres
4+
groups: root,admin,postgres,pgbouncer
55
append: yes
66

77
- name: disk script

‎ansible/tasks/setup-pgbouncer.yml

Lines changed: 22 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -41,22 +41,42 @@
4141
target: install
4242
become: yes
4343

44-
# Create pgbouncer user
4544
- name: Create pgbouncer user
4645
user:
4746
name: pgbouncer
4847
shell: /bin/false
4948
comment: PgBouncer user
5049
groups: postgres,ssl-cert
5150

52-
# Create /etc/postgresql directory and make sure postgres group owns it
5351
- name: PgBouncer - create a directory if it does not exist
5452
file:
5553
path: /etc/pgbouncer
5654
state: directory
5755
owner: pgbouncer
56+
group: pgbouncer
5857
mode: '0700'
5958

59+
- name: PgBouncer - create a directory if it does not exist
60+
file:
61+
state: directory
62+
owner: pgbouncer
63+
group: pgbouncer
64+
path: '{{ item }}'
65+
mode: '0775'
66+
with_items:
67+
- '/etc/pgbouncer-custom'
68+
69+
- name: create placeholder config files
70+
file:
71+
path: '/etc/pgbouncer-custom/{{ item }}'
72+
state: touch
73+
owner: pgbouncer
74+
group: pgbouncer
75+
mode: 0664
76+
with_items:
77+
- 'generated-optimizations.ini'
78+
- 'custom-overrides.ini'
79+
6080
- name: PgBouncer - adjust pgbouncer.ini
6181
copy:
6282
src: files/pgbouncer_config/pgbouncer.ini.j2

‎ansible/tasks/setup-postgres.yml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -103,7 +103,7 @@
103103
with_items:
104104
- '/etc/postgresql-custom'
105105

106-
- name: create placeholder files
106+
- name: create placeholder config files
107107
file:
108108
path: '/etc/postgresql-custom/{{ item }}'
109109
state: touch

‎ansible/vars.yml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -34,10 +34,10 @@ node_exporter_release_checksum:
3434
arm64: sha256:eb5e7d16f18bb3272d0d832986fc8ac6cb0b6c42d487c94e15dabb10feae8e04
3535
amd64: sha256:8c1f6a317457a658e0ae68ad710f6b4098db2cad10204649b51e3c043aa3e70d
3636

37-
adminapi_release: 0.16.2
37+
adminapi_release: 0.17.1
3838
adminapi_release_checksum:
39-
arm64: sha256:94fc9d916ce7b54c20e4cb9e0898a2aed205436ee604663636f38a40183163f3
40-
amd64: sha256:134be79a98416e8fcefe2bebf85c6fad56d23c0f99252c8566889c879b9651b5
39+
arm64: sha256:72ee4c1c9026c9935ab19bc96f1c8e49529549299cc654cd91d54ae7177143b7
40+
amd64: sha256:9392c3b2c12967efad9e90223d12eaaf788b56fa7c5d206ef30e434e5416ed7f
4141

4242
# Postgres Extensions
4343
postgis_release: "3.1.4"

‎common.vars.json

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,3 @@
11
{
2-
"postgres-version": "14.1.0.15"
2+
"postgres-version": "14.1.0.16-rc0"
33
}

0 commit comments

Comments
 (0)
0