18 | | == 資料庫列表 == |
| 20 | == 新增使用者為管理者 == |
| 21 | |
| 22 | {{{ |
| 23 | ~$ sudo su - postgres |
| 24 | -bash-4.2$ psql -d postgres ### 使用 postgres 資料庫 |
| 25 | postgres=# ALTER USER 使用者名稱 WITH SUPERUSER; |
| 26 | }}} |
| 27 | |
| 28 | |
| 29 | == 使用者 == |
| 30 | |
| 31 | === 新增使用者 === |
| 32 | |
| 33 | {{{ |
| 34 | ~$ sudo su - postgres |
| 35 | -bash-4.2$ createuser 使用者名稱 |
| 36 | }}} |
| 37 | |
| 38 | == 系統 == |
| 39 | |
| 40 | === 查詢目前連線數 === |
| 41 | * 查連線總數 |
| 42 | {{{ |
| 43 | postgres=# SELECT count(*) FROM pg_stat_activity; |
| 44 | count |
| 45 | ------- |
| 46 | 52 |
| 47 | (1 row) |
| 48 | }}} |
| 49 | * 依資料庫查詢各別連線數 |
| 50 | {{{ |
| 51 | postgres=# SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname; |
| 52 | }}} |
| 53 | |
| 54 | === 修改同時連線數上限 === |
| 55 | |
| 56 | * 修改 postgresql.conf |
| 57 | {{{ |
| 58 | $ sudo grep max_connections /var/lib/postgresql/postgresql.conf |
| 59 | max_connections = 300 # (change requires restart) |
| 60 | # Note: Increasing max_connections costs ~400 bytes of shared memory per |
| 61 | # max_locks_per_transaction * (max_connections + max_prepared_transactions) |
| 62 | }}} |
| 63 | |
| 64 | === 查詢同時連線數上限 === |
| 65 | |
| 66 | * 使用 `SHOW` 指令 |
| 67 | {{{ |
| 68 | postgres=# SHOW max_connections; |
| 69 | }}} |
| 70 | * 或從 `pg_settings` 資料表查詢 |
| 71 | {{{ |
| 72 | postgres=# SELECT * FROM pg_settings WHERE name = 'max_connections'; |
| 73 | }}} |
| 74 | * [https://stackoverflow.com/questions/8288823/query-a-parameter-postgresql-conf-setting-like-max-connections 參考] |
| 75 | |
| 76 | |
| 77 | |
| 78 | == 資料庫 == |
| 79 | |
| 80 | === 資料庫列表 === |
58 | | |
59 | | == 新增使用者為管理者 == |
60 | | |
61 | | {{{ |
62 | | ~$ sudo su - postgres |
63 | | -bash-4.2$ psql -d postgres ### 使用 postgres 資料庫 |
64 | | postgres=# ALTER USER 使用者名稱 WITH SUPERUSER; |
65 | | }}} |
66 | | |
67 | | == 查詢目前連線數 == |
68 | | * 查連線總數 |
69 | | {{{ |
70 | | postgres=# SELECT count(*) FROM pg_stat_activity; |
71 | | count |
72 | | ------- |
73 | | 52 |
74 | | (1 row) |
75 | | }}} |
76 | | * 依資料庫查詢各別連線數 |
77 | | {{{ |
78 | | postgres=# SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname; |
79 | | }}} |
80 | | |
81 | | == 修改同時連線數上限 == |
82 | | |
83 | | * 修改 postgresql.conf |
84 | | {{{ |
85 | | $ sudo grep max_connections /var/lib/postgresql/postgresql.conf |
86 | | max_connections = 300 # (change requires restart) |
87 | | # Note: Increasing max_connections costs ~400 bytes of shared memory per |
88 | | # max_locks_per_transaction * (max_connections + max_prepared_transactions) |
89 | | }}} |
90 | | |
91 | | == 查詢同時連線數上限 == |
92 | | |
93 | | * 使用 `SHOW` 指令 |
94 | | {{{ |
95 | | postgres=# SHOW max_connections; |
96 | | }}} |
97 | | * 或從 `pg_settings` 資料表查詢 |
98 | | {{{ |
99 | | postgres=# SELECT * FROM pg_settings WHERE name = 'max_connections'; |
100 | | }}} |
101 | | * [https://stackoverflow.com/questions/8288823/query-a-parameter-postgresql-conf-setting-like-max-connections 參考] |