【IT Notes】在Ubuntu20.04上安裝PostgreSQL資料庫&pgBouncer連接池

 去年安裝過PostgreSQL的主從架構資料庫，可是沒有做過pgBouncer的相關建置，pgBouncer是PostgreSQL前端的一個連接池，此pool可以提升資料庫效能、保護資料庫和維持穩定等優點，用戶連接到資料庫前，最好先透過pgBouncer，這樣對交易量大的資料庫環境會比較好，剛好順便也複習一下PostgreSQL的相關設定，這次就另外再做一遍筆記。

一‧事前準備

三台VM，基本配備：

4核心CPU、4GB RAM、30GB硬碟

二．安裝軟體及套件

$ sudo apt update

$ sudo apt -y install vim bash-completion wget

$ sudo apt -y upgrade

$ sudo apt -y install postgresql-12 postgresql-client-12

$ sudo service postgresql status

$ sudo service repmgrd status

$ sudo -u postgres psql  \\用postgres身份登入資料庫

[sudo] password for user:

psql (12.11 (Ubuntu 12.11-0ubuntu0.20.04.1))

Type "help" for help.

postgres=#

三．匯入sample資料庫

慣例到下面練習用的網站去下載資料，然後匯入到資料庫裡面。

https://www.postgresqltutorial.com/postgresql-getting-started/load-postgresql-sample-database/

1.建立自己的帳號，並給予管理員權限

CREATE USER twyichikuo WITH PASSWORD '123456';

ALTER USER twyichikuo WITH SUPERUSER;

GRANT ALL ON ALL TABLES IN SCHEMA "public" TO twyichikuo;

2.先建立資料庫

postgres=# CREATE DATABASE dvdrental;

postgres=# \q

$ pg_restore -Utwyichikuo -d dvdrental /home/twyichikuo/dvdrental.tar

\\用自己的身份將檔案會進去資料庫，User可以自選，不建議用postgres身份做，它是系統用的最高權限帳號，盡量別用在資料寫入或刪除等其他用途上。

$ sudo -u postgres psql

$ sudo -u postgres psql

psql (12.11 (Ubuntu 12.11-0ubuntu0.20.04.1))

Type "help" for help.

postgres=# \l

                             List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-----------+----------+----------+-------------+-------------+-------------------------

dvdrental | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

postgres=# \c dvdrental

You are now connected to database "dvdrental" as user "postgres".

dvdrental=# \dt

        List of relations

Schema | Name | Type | Owner

--------+---------------+-------+----------

public | actor | table | postgres

public | address | table | postgres

public | category | table | postgres

public | city | table | postgres

public | country | table | postgres

public | customer | table | postgres

public | film | table | postgres

public | film_actor | table | postgres

public | film_category | table | postgres

public | inventory | table | postgres

public | language | table | postgres

public | payment | table | postgres

public | rental | table | postgres

public | staff | table | postgres

public | store | table | postgres

(15 rows)

dvdrental=# \q

\\連進去後就能檢視資料匯入是否成功

四．安裝pgbouncer

$ sudo apt install pgbouncer -y

PostgreSQL基本指令

1.基本設定

要到/etc/pgbouncer/pgbouncer.ini檔案做幾個設定

[databases]

* = host=localhost \\設定的連線對象自定義，可以是全開或者是指定資料庫

[pgbouncer]

listen_addr = *

listen_port = 6432 \\pgbouncer的連線port是6432

auth_type = md5 \\帳號登入密碼是md5加密

auth_file = /etc/pgbouncer/userlist.txt \\配置要登入pgbouncer帳密的檔案位置

pool_mode = transaction

\\Session pooling：預設值，對於連線請求，分給一個 Session 到它結束

\\Transaction pooling：一次交易結束，才把連線交還 Pooling

\\Statement pooling：一個 Query 結束，就把連線交還 Pooling

2.登入帳密的設定

sudo -u postgres psql

Password for user postgres:

psql (12.4)

Type "help" for help.

postgres=# select rolname,rolpassword from pg_authid where rolname='twyichikuo';

rolname | rolpassword

------------+-------------------------------------

twyichikuo | md5fc5fa78dbffef80dc29f2b89fdb45aba

(1 row)

\\這一步是查詢帳號及加密過的密碼

echo '"twyichikuo" "md5fc5fa78dbffef80dc29f2b89fdb45aba"' > /etc/pgbouncer/userlist.txt

\\將帳密寫入userlist.txt

$ sudo systemctl enable pgbouncer

$ sudo systemctl start pgbouncer

\\測試pgbouncer連線

twyichikuo@mytest:/var/log/postgresql$ psql --port 6432 -Utwyichikuo -h127.0.0.1 -d dvdrental Password for user twyichikuo: psql (12.11 (Ubuntu 12.11-0ubuntu0.20.04.1)) Type "help" for help. dvdrental=#

單台的設定比較簡單，只要完成到這邊就算告一段落，網路上還有介紹多台的pgbouncer，這樣的load balance會做更完善，但設定也會更複雜，以後有需要就再繼續吧！

***補充連線設定檔路徑：
/etc/postgresql/12/main/pg_hba.conf.