Postgres的服务控制pgbouncer的安装与配置

参考文档&＃xff1a; PostgreSQL 9 Administration Cookbook &＃xff08;第二版&＃xff09;中文版

-- pg的关闭 &＃xff08;pt_ctl&＃xff0c;具体可以查看帮助&＃xff09;

pg_ctl -D datadir -m fast stop
pg_ctl -D datadir stop -m immediate -- 相当O的shutdown abort

-- 重新加载配置文件

pg_ctl -D datadir reload
select pg_reload_conf()

-- 查看修改的部分参数

select name,setting,unit,(source&＃61;&＃39;default&＃39;) as is_default from pg_settings
where context&＃61;&＃39;sighup&＃39;
and (name like &＃39;%delay&＃39; or name like &＃39;%timeout&＃39;)
and setting !&＃61; &＃39;0&＃39;;mydb&＃61;# select name,setting,unit,(source&＃61;&＃39;default&＃39;) as is_default from pg_settings
mydb-# where context&＃61;&＃39;sighup&＃39;
mydb-# and (name like &＃39;%delay&＃39; or name like &＃39;%timeout&＃39;)
mydb-# and setting !&＃61; &＃39;0&＃39;;name | setting | unit | is_default
------------------------------&＃43;---------&＃43;------&＃43;------------authentication_timeout | 60 | s | tautovacuum_vacuum_cost_delay | 20 | ms | tbgwriter_delay | 200 | ms | tcheckpoint_timeout | 300 | s | tmax_standby_archive_delay | 30000 | ms | tmax_standby_streaming_delay | 30000 | ms | twal_receiver_timeout | 60000 | ms | twal_sender_timeout | 60000 | ms | twal_writer_delay | 200 | ms | t
(9 rows)mydb&＃61;#

-- 快速重启服务&＃xff0c;先做一个checkpoint&＃xff0c;将shared_buffer里面的脏数据刷新到数据文件中

psql -c "checkpoint";
pg_ctl -D datadir restart -m fast


-- 阻止新的连接
通过设置连接限制为0来限制指定的数据库的连接数为0 ,-1 解除限制

alter database foo_db connection limit 0;

通过设置连接数限制为0&＃xff0c;来限制指定的用户的连接数为0

alter user foo connection limit 0;

或者设置基于主机的认证文件pg_hba.conf

限制用户的连接数为1 &＃xff0c;-1 解除限制

alter role fred connection limit 1;
mydb&＃61;# alter database mydb connection limit -1;
ALTER DATABASE
mydb&＃61;# select rolconnlimit from pg_roles where rolname &＃61;&＃39;xxx&＃39;; -- 查看连接限制
select * from pg_stat_activity where usename &＃61;&＃39;postgres&＃39;; -- 查看连接数

-- 断开用户连接 &＃xff0c;使用pg提供的函数pg_terminate_backend()

select pg_terminate_backend(pid) from pg_stat_activity; -- 会断开当前的链接
postgres&＃61;# select pg_terminate_backend(pid) from pg_stat_activity;
FATAL: terminating connection due to administrator command
server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres&＃61;#

一个比较好的办法&＃xff0c;是通过视图pg_stat_activity 找到pid&＃xff0c;然后使用pg_terminate_backend(pid)终结会话

select * from pg_stat_activity -- 找到pid是21044
select pg_terminate_backend(21044); -- 该语句kill掉该会话postgres&＃61;# select now();now
-------------------------------2021-01-12 14:29:17.877438&＃43;08
(1 row)postgres&＃61;# select now();
FATAL: terminating connection due to administrator command
server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres&＃61;# -- 或者这样
select count(pg_terminate_backend(pid)) from pg_stat_activity where usename not in
(select usename from pg_user where usesuper);


-- 使用多个模式
可以把一组表划分到他们自己的“命名空间”&＃xff0c;也就是PG的模式&＃xff08;schema&＃xff09;。很多情况下&＃xff0c;被认为类似目录。

create schema finance;
create schema sales;mydb&＃61;# create table finance.t1(id integer,name varchar(10)); -- 创建一个表&＃xff0c;在schema finance下
CREATE TABLE
mydb&＃61;# mydb&＃61;# select current_schema; -- 查看当前的schema&＃xff0c;是publiccurrent_schema
----------------public
(1 row)mydb&＃61;#

-- 不写schema&＃xff0c;查询不到t1&＃xff0c;如果想让用户能够访问某组表&＃xff0c;需要修改他的search_path

mydb&＃61;# select * from t1;
ERROR: relation "t1" does not exist
LINE 1: select * from t1;^
mydb&＃61;# mydb&＃61;# alter role postgres set search_path&＃61;&＃39;finance&＃39;;
ALTER ROLE
mydb&＃61;# grant all on schema finance to postgres;
GRANT
mydb&＃61;# select * from t1;id | name
----&＃43;------
(0 rows)mydb&＃61;#
mydb&＃61;# select * from t1;id | name
----&＃43;------
(0 rows)mydb&＃61;# select current_schema;current_schema
----------------finance
(1 row)mydb&＃61;# show search_path
mydb-# ;search_path
-------------finance
(1 row)mydb&＃61;#
mydb&＃61;# set search_path&＃61;"$user", public;
SET
mydb&＃61;# show search_path;search_path
-----------------"$user", public
(1 row)mydb&＃61;#

-- 单独给用户分配数据库

create user fred;
create database fred owner &＃61; fred ;

这个时候&＃xff0c;切换到fred数据库还是可以的&＃xff0c;提示使用postgres用户连接到了fred数据库

mydb&＃61;# \c fred
You are now connected to database "fred" as user "postgres".
fred&＃61;#

收回权限,尝试以postgres来连接数据库.或者&＃xff0c;建立一个用户bob&＃xff0c;使用bob访问fred数据库&＃xff0c;没有权限。只能使用有权限的用户fred连接到数据库。

revoke connect on database fred from public;
grant connect on database fred to fred ;fred&＃61;# revoke connect on database fred from public;
REVOKE
fred&＃61;# grant connect on database fred to fred ;
GRANT
fred&＃61;# create user bob ;
alter user bob with password &＃39;oracle&＃39;;
psql -U bob fred postgres&＃61;# alter user bob with password &＃39;oracle&＃39;;
ALTER ROLE-bash-4.2$ psql -d fred -U bob
Password for user bob:
psql.bin: FATAL: permission denied for database "fred"
DETAIL: User does not have CONNECT privilege.
-bash-4.2$ -bash-4.2$ psql -U fred -d fred
Password for user fred:
psql.bin (10.15)
Type "help" for help.fred&＃61;>

-- 配置连接池  pgbouncer  
https://www.pgbouncer.org/install.html

http://rpm.pbone.net/info_idpl_68340409_distro_redhatel7_com_pgbouncer-1.9.0-3.rhel7.x86_64.rpm.html
--

-- pgbouncer的安装

yum install pgbouncer-1.9.0-3.rhel7.x86_64.rpmInstalled:pgbouncer.x86_64 0:1.9.0-3.rhel7 Dependency Installed:c-ares.x86_64 0:1.10.0-3.el7 postgresql-libs.x86_64 0:9.2.24-1.el7_5 python-psycopg2.x86_64 0:2.5.1-3.el7Complete![root&＃64;asm12c tmp]# which pgbouncer
/usr/bin/pgbouncer
[root&＃64;asm12c tmp]#

-- 安装后的位置&＃xff0c;在/etc/pgbouncer下 &＃xff0c;可以通过 rpm -ql | grep pgbouncer 来查询

[root&＃64;asm12c ~]# rpm -ql pgbouncer
/etc/logrotate.d/pgbouncer
/etc/pgbouncer
/etc/pgbouncer/mkauth.py
/etc/pgbouncer/mkauth.pyc
/etc/pgbouncer/mkauth.pyo
/etc/pgbouncer/pgbouncer.ini
/etc/sysconfig/pgbouncer
/run/pgbouncer
/usr/bin/pgbouncer
/usr/lib/systemd/system/pgbouncer.service
/usr/lib/tmpfiles.d/pgbouncer.conf
/usr/share/doc/pgbouncer
/usr/share/doc/pgbouncer/NEWS.rst
/usr/share/doc/pgbouncer/README.rst
/usr/share/doc/pgbouncer/pgbouncer.ini
/usr/share/doc/pgbouncer/userlist.txt
/usr/share/licenses/pgbouncer-1.9.0
/usr/share/licenses/pgbouncer-1.9.0/COPYRIGHT
/usr/share/man/man1/pgbouncer.1.gz
/usr/share/man/man5/pgbouncer.5.gz
[root&＃64;asm12c ~]#

-- 编辑pgbouncer.ini文件

[databases]
mydb&＃61; host&＃61;127.0.0.1 port&＃61;5432 user&＃61;postgres password&＃61;oracle
postgres&＃61; host&＃61;127.0.0.1 port&＃61;5432 user&＃61;postgres password&＃61;oracle
korean&＃61; host&＃61;127.0.0.1 port&＃61;5432 user&＃61;postgres password&＃61;oracle
fred&＃61; host&＃61;127.0.0.1 port&＃61;5432 user&＃61;fred password&＃61;oracle
[pgbouncer]
logfile &＃61; /postgres/10/data/log/pgbouncer_log/pgbouncer.log
pidfile &＃61; /tmp/pgbouncer.pid
listen_addr &＃61; *
listen_port &＃61; 6432
auth_type &＃61; trust -- 如果是md5&＃xff0c;需要通过pg_shadow 查询到用户的md5值&＃xff0c;或者使用MD5函数计算出加密后的值
auth_file &＃61; /etc/pgbouncer/userlist.txt
admin_users &＃61; postgres,fred
pool_mode &＃61; session
server_reset_query &＃61; DISCARD ALL
ignore_startup_parameters &＃61; extra_float_digits
max_client_conn &＃61; 1000
default_pool_size &＃61; 200

-- 计算MD5值

\o users.txt
\t
SELECT &＃39;"&＃39;||rolname||&＃39;" "&＃39;||rolpassword||&＃39;"&＃39;
FROM pg_authid;
\qpostgres&＃61;# \o users.txt
postgres&＃61;# \t
Tuples only is on.
postgres&＃61;# SELECT &＃39;"&＃39;||rolname||&＃39;" "&＃39;||rolpassword||&＃39;"&＃39;
postgres-# FROM pg_authid;
postgres&＃61;# \q-bash-4.2$ more users.txt
?column? |
---------&＃43;-------------------------------------------------
?column? |
---------&＃43;-------------------------------------------------
?column? |
---------&＃43;-------------------------------------------------
?column? |
---------&＃43;-------------------------------------------------
?column? |
---------&＃43;-------------------------------------------------
?column? | "postgres" "md57e1a83ffb47df2396c5eafd05bac4ade"
---------&＃43;-------------------------------------------------
?column? | "bob" "md535bb865af345b47dcbebab163fe54390"
---------&＃43;-------------------------------------------------
?column? | "fred" "md569361356a5bde6d88405a0f45d28bb30"-bash-4.2$

-- 编辑userlist.txt&＃xff0c; 因为上面认证使用的是trust&＃xff0c;所以文件中写明文密码了。&＃xff08;生产不建议&＃xff09;

[root&＃64;asm12c ~]# more /etc/pgbouncer/userlist.txt
"postgres" "oracle"
"fred" "oracle"
[root&＃64;asm12c ~]#

-- 启动pgbouncer

su - postgres
pgbouncer -d /etc/pgbouncer/pgbouncer.ini[root&＃64;asm12c ~]# netstat -anlpt | grep 6432
tcp 0 0 0.0.0.0:6432 0.0.0.0:* LISTEN 12514/pgbouncer
tcp 0 0 127.0.0.1:9637 127.0.0.1:6432 ESTABLISHED 12759/psql.bin
tcp 0 0 127.0.0.1:6432 127.0.0.1:9637 ESTABLISHED 12514/pgbouncer
tcp6 0 0 :::6432 :::* LISTEN 12514/pgbouncer
[root&＃64;asm12c ~]#

-- 登录到pgbouncer 

psql -Upostgres -dpgbouncer -p6432 -h127.0.0.1pgbouncer&＃61;# \x
Expanded display is on.
pgbouncer&＃61;# show clients;
-[ RECORD 1 ]&＃43;--------------------
type | C
user | postgres
database | pgbouncer
state | active
addr | 127.0.0.1
port | 9637
local_addr | 127.0.0.1
local_port | 6432
connect_time | 2021-01-13 15:32:45
request_time | 2021-01-13 15:48:53
wait | 964
wait_us | 397897
close_needed | 0
ptr | 0x18d7730
link |
remote_pid | 0
tls | pgbouncer&＃61;#

-- 查看连接池

pgbouncer&＃61;# show pools;database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------&＃43;-----------&＃43;-----------&＃43;------------&＃43;-----------&＃43;---------&＃43;---------&＃43;-----------&＃43;----------&＃43;---------&＃43;------------&＃43;-----------korean | postgres | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | sessionmydb | postgres | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | sessionpgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statementpostgres | postgres | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | session
(4 rows)pgbouncer&＃61;#

-- 列出数据库&＃xff0c;报错&＃xff0c;难道pgbouncer版本和postgres版本不兼容&＃xff1f;

pgbouncer&＃61;# \l
ERROR: invalid command &＃39;SELECT d.datname as "Name",pg_catalog.pg_get_userbyid(d.datdba) as "Owner",pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",pg_catalog.array_to_string(d.datacl, &＃39;\n&＃39;) AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;&＃39;, use SHOW HELP;
pgbouncer&＃61;#

-- 重新加载pgbouncer的配置信息

pgbouncer&＃61;# reload
pgbouncer-# ;
RELOAD
pgbouncer&＃61;#


-- 重启pgbouncer

su - postgres
pgbouncer -R /etc/pgbouncer/pgbouncer.ini

-- 关闭pgbouncer

cat /tmp/pgbouncer.pid | xargs kill -9 -- 这个是网上看到的
psql -p 6432 pgbouncer -c "SHUTDOWN"


-- 查看帮助

pgbouncer&＃61;# show help;
NOTICE: Console usage
DETAIL: SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSIONSHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEMSHOW DNS_HOSTS|DNS_ZONESSHOW STATS|STATS_TOTALS|STATS_AVERAGESSET key &＃61; argRELOADPAUSE []RESUME []DISABLE ENABLE RECONNECT []KILL SUSPENDSHUTDOWNSHOW
pgbouncer&＃61;#

-- pgadmin的连接&＃xff0c;可以连接。

END