sudo rsync -avz /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.bkp
sudo rsync -avz /etc/pgbouncer/userlist.txt /etc/pgbouncer/userlist.txt.bkp
# restore default configuration
sudo rsync -avz /etc/pgbouncer/pgbouncer.ini.bkp /etc/pgbouncer/pgbouncer.ini
sudo cat /etc/pgbouncer/pgbouncer.ini
# find pgbouncer db pool
PG_DB_POOL_FIND=$(cat <<QRY
\[databases\]\n\
\n\
;; foodb over Unix socket\n\
;foodb =
QRY
)
# fill pgbouncer db pool
PG_DB_POOL_FILL=$(cat <<UPD
[databases]\n\
\n\
;; future db pool here\n\
academia_flair_staging = host=10.110.110.155 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'\n\
academia_audit_staging = host=10.110.110.155 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'\n\
academia_qoute_staging = host=10.110.110.155 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'\n\
academia_users_staging = host=10.110.110.155 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
UPD
)
sudo sed -z "s|${PG_DB_POOL_FIND}|$(echo "${PG_DB_POOL_FILL}")|" -i /etc/pgbouncer/pgbouncer.ini
# find pgbouncer user pool
PG_USER_POOL_FIND=$(cat <<QRY
\[users\]\n\
\n\
;user1 = pool_mode=transaction max_user_connections=10
QRY
)
# fill pgbouncer user pool
PG_USER_POOL_FILL=$(cat <<UPD
[users]\n\
\n\
;; future user pool here\n\
chorke = pool_mode=transaction max_user_connections=10
UPD
)
sudo sed -z "s|${PG_USER_POOL_FIND}|$(echo "${PG_USER_POOL_FILL}")|" -i /etc/pgbouncer/pgbouncer.ini
sudo sed -z "s|auth_type = trust|auth_type = md5|" -i /etc/pgbouncer/pgbouncer.ini
cat << USR | sudo tee -a /etc/pgbouncer/userlist.txt >/dev/null
"chorke" "md5$(printf '%s%s' 'p@$$w0rd' 'chorke'|md5sum|awk '{print $1}')"
"shahed" "md5$(printf '%s%s' 'sadaqah!' 'shahed'|md5sum|awk '{print $1}')"
USR
[databases]
academia_flair_staging = host=127.0.0.1 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
academia_audit_staging = host=127.0.0.1 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
academia_qoute_staging = host=127.0.0.1 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
academia_users_staging = host=127.0.0.1 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
[users]
chorke = pool_mode=transaction max_user_connections=20
[pgbouncer]
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
ignore_startup_parameters = extra_float_digits
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5
max_db_connections = 10
max_user_connections = 10
sudo su
su postgres
pgbouncer -R /etc/pgbouncer/pgbouncer.ini
SHOW max_connections;
RESET max_connections;
ALTER SYSTEM SET max_connections = 200;
SELECT current_setting('max_connections');
SELECT sum(numbackends) FROM pg_stat_database;
SELECT * FROM pg_settings WHERE name = 'max_connections';
SELECT state, count(*) FROM pg_stat_activity WHERE pid <> pg_backend_pid() GROUP BY 1 ORDER BY 1;
-- pg_lsclusters
SHOW data_directory;
SELECT setting FROM pg_settings WHERE name = 'data_directory';
SELECT name, setting FROM pg_settings WHERE setting LIKE '/%';
WITH conn_limit AS (
SELECT setting::int "max_conn_size"
FROM pg_settings WHERE name=$$max_connections$$
),
conn_super AS (
SELECT setting::int "max_conn_admin"
FROM pg_settings WHERE name=$$superuser_reserved_connections$$
)
SELECT count(*) "max_conn_used", (SELECT * FROM conn_super), (SELECT * FROM conn_limit)
FROM pg_stat_activity;