PostgreSQL 粗心设错session_preload_libraries的补救方法

标签

PostgreSQL , session_preload_libraries , psql: FATAL: could not access file "pg_pathman": No such file or directory

背景

由于粗心，设错了session_preload_libraries，里面包含了一个不存在的SO，连接数据库报错：

postgres=# alter role all set session_preload_libraries='pg_pathman';  
ALTER ROLE  
postgres=# \q  
postgres@pg11-test-> psql  
psql: FATAL:  could not access file "pg_pathman": No such file or directory  

补救方法

1、session_preload_libraries是一个会话可设置的参数，所以可以在连接时直接指定为正确值，例如

postgres@pg11-test-> psql options=-csession_preload_libraries='auto_explain'  
psql (11.1)  
Type "help" for help.  
  
postgres=# show session_preload_libraries ;  
 session_preload_libraries   
---------------------------  
 auto_explain  
(1 row)  

或者

postgres@pg11-test-> psql options=-csession_preload_libraries=''  
psql (11.1)  
Type "help" for help.  
  
  
postgres=# show session_preload_libraries ;  
 session_preload_libraries   
---------------------------  
   
(1 row)  

进入数据库后，重新设置为正确值，或者重置即可：

postgres=# alter role all reset session_preload_libraries;  
ALTER ROLE