1.1 修改配置文件“postgresql.conf”后,要想让修改生效,有以下两种方法。
maxwelldb=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
maxwelldb=#
注意,如果是需要重启数据库服务才能使修改生效的配置项,使用上面的方法无效。使用上面的方法能使修改生效的配置项都是不需要重启数据库服务就能使修改生效的配置项。
1.2 切换log日志文件到下一个,命令如下:
maxwelldb=# select pg_rotate_logfile();
pg_rotate_logfile
-------------------
t
(1 row)
maxwelldb=#
1.3 切换WAL日志文件,命令如下:
1.4 通常先查询pg_stat_activity以找出长时间运行的SQL命令,命令如下:
maxwelldb=# select pid,usename,query_start,query from pg_stat_activity;
pid | usename | query_start | query
--------+----------+------------------------------+-------------------------------------------------------------
340546 | | |
340548 | postgres | |
342960 | maxwell | 2022-08-30 20:01:46.94341+08 | select pid,usename,query_start,query from pg_stat_activity;
340544 | | |
340543 | | |
340545 | | |
(6 rows)
maxwelldb=#
然后再使用pg_cancel_backend()取消该SQL命令,如果pg_cancel_backend()取消失败,再使用pg_terminate_backend(),命令如下:
maxwelldb=# select pg_cancel_backend(340548);
pg_cancel_backend
-------------------
t
(1 row)
maxwelldb=# select pid,usename,query_start,query from pg_stat_activity;
pid | usename | query_start | query
--------+----------+-------------------------------+-------------------------------------------------------------
340546 | | |
343044 | postgres | |
342960 | maxwell | 2022-08-30 20:05:51.915423+08 | select pid,usename,query_start,query from pg_stat_activity;
340544 | | |
340543 | | |
340545 | | |
(6 rows)
maxwelldb=# select pg_terminate_backend(343044);
pg_terminate_backend
----------------------
t
(1 row)
maxwelldb=# select pid,usename,query_start,query from pg_stat_activity;
pid | usename | query_start | query
--------+---------+-------------------------------+-------------------------------------------------------------
340546 | | |
342960 | maxwell | 2022-08-30 20:06:43.865371+08 | select pid,usename,query_start,query from pg_stat_activity;
340544 | | |
340543 | | |
340545 | | |
(5 rows)
maxwelldb=#