pg14資料庫引數修改方式小結

賀子_DBA時代發表於2023-01-30
一、檢視pg資料庫配置引數的基本資訊!-----書本的圖片來自pg修煉之道從小工到專家,是基於pg12版本;
可以透過context欄位值來了解修改該引數是否需要重啟資料庫!具體如下圖片所示:
postgres=# select name,context  from  pg_settings;

。。。。。。我的測試是基於pg14.6。。。。。
二、以引數maintenance_work_mem為例子,來說明alter system  set 和 set兩種方式修改引數的區別
2.1、先檢視該引數的修改方式,如下所示發現是user型別的,可以直接set,也可以alter  system  set這樣修改;
postgres=# select name,context  from  pg_settings  where  name='maintenance_work_mem';
name         | context
----------------------+---------
maintenance_work_mem | user
(1 row)
2.2、兩種方法修改的區別:
1、PostgreSQL9.4開始提供了類似Oracle的命令 ALTER SYSTEM 修改伺服器引數並被持久化,透過system修改某個引數後,就自動在資料檔案目錄下生成一個名字為postgresql.auto.conf配置檔案,用來儲存剛才的修改(優先順序高於postgresql.conf),需要重新reload下配置檔案讓修改生效,並且對所有的連線都生效,包括本連線以及已經建立的連線,同時最新的修改即使在下次重啟也會生效。具體如下
會話1:
postgres=# show  maintenance_work_mem; maintenance_work_mem
256MB
(1 row)
postgres=# alter  system set  maintenance_work_mem='256MB'; ALTER SYSTEM postgres=# show  maintenance_work_mem;   #發現無效 maintenance_work_mem
64MB
(1 row)
postgres=# select pg_reload_conf();    #reload下配置檔案,另一中reload方法:pg_ctl -D  $PGDATA  reload
pg_reload_conf
----------------
t
(1 row)
postgres=# show  maintenance_work_mem;
maintenance_work_mem
----------------------
256MB
(1 row)
會話2:
postgres=# show  maintenance_work_mem;
maintenance_work_mem
----------------------
128MB
會話1reload後再次檢視發現已經生效,說明對已經建立的連線也有效果!
postgres=# show  maintenance_work_mem;
maintenance_work_mem
----------------------
256MB
2、類似於MySQL那樣直接set 修改,但是隻對當前連線有效,新建連線無效了,重啟後自然也失效,具體如下所示
[postgres@B-JS25-BASE79-00 data]$ psql
psql (14.6)
Type "help" for help.
postgres=# show  maintenance_work_mem; maintenance_work_mem
256MB
(1 row)
postgres=# set  maintenance_work_mem ='128MB'; SET postgres=# postgres=# show  maintenance_work_mem; maintenance_work_mem
128MB
(1 row)
postgres=# exit
[postgres@B-JS25-BASE79-00 data]$ psql
psql (14.6)
Type "help" for help.
postgres=# show  maintenance_work_mem; maintenance_work_mem
256MB
(1 row)
三、大部分型別的引數都可以透過 alter  system set的方式修改,除了internal型別的引數不可以!但是生效方式不同;
具體如下:
3.1、sighup型別,可以透過alter system set的方式修改;reload就生效了,
postgres=# select name,context  from  pg_settings   where name='archive_timeout';
name       | context
-----------------+---------
archive_timeout | sighup
(1 row)
postgres=# show archive_timeout;
archive_timeout
-----------------
1s
(1 row)
postgres=# alter system set  archive_timeout=2;
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show archive_timeout;
archive_timeout
-----------------
2s
(1 row)
3.2、superuser型別的引數,可以透過alter system set的方式修改;reload就生效了
postgres=# select name,context  from  pg_settings   where name='allow_in_place_tablespaces';
name            |  context
----------------------------+-----------
allow_in_place_tablespaces | superuser
(1 row)
postgres=# show allow_in_place_tablespaces;
allow_in_place_tablespaces
----------------------------
on
(1 row)
postgres=# alter  system set  allow_in_place_tablespaces=off;
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show allow_in_place_tablespaces;
allow_in_place_tablespaces
----------------------------
off
(1 row)
3.3、postmaster型別的引數,可以透過alter system set的方式修改;reload無法生效,需要重啟才能生效!
postgres=# select name,context  from  pg_settings   where name='data_sync_retry';
name       |  context
-----------------+------------
data_sync_retry | postmaster
(1 row)
postgres=# show data_sync_retry;
data_sync_retry
-----------------
off
(1 row)
postgres=# alter system set  data_sync_retry=on;
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show data_sync_retry;
data_sync_retry
-----------------
off
(1 row)
重啟資料庫後生效:
[postgres@B-JS25-BASE79-00 data]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-01-30 22:08:09.359 CST [122668] LOG:  redirecting log output to logging collector process
2023-01-30 22:08:09.359 CST [122668] HINT:  Future log output will appear in directory "log".
done
server started
[postgres@B-JS25-BASE79-00 data]$ psql
psql (14.6)
Type "help" for help.
postgres=# show data_sync_retry;  #生效了 data_sync_retry
on
(1 row)
3.4、internal型別的引數無法透過alter system修改,只能在初始化的時候指定;
postgres=# select name,context  from  pg_settings   where name='block_size';
name    | context
------------+----------
block_size | internal
(1 row)
postgres=# show block_size;
block_size
------------
8192
(1 row)
postgres=# alter system set  block_size=16384;
ERROR:  parameter "block_size" cannot be changed
postgres=#
3.5、user前面已經介紹了也是可以透過alter system修改;
四、總結,針對pg14.6引數的修改的規則:
1、可以先透過select name,context  from  pg_settings檢視context值來了解引數屬於那個型別,
2、其中sighup,superuser,user這三種型別可以透過alter system set修改,並且reload就可以生效,並且對所有連線都有效果,包括已經建立的和未來建立的;
3、postmaster型別的也可以透過alter system set修改。但是需要重啟資料庫才可以生效;
4、透過alter  system set修改的引數重啟也有效果;
5、透過alter system set 修改的引數會自動記錄到引數檔案postgresql.auto.conf中,並且優先順序高於postgresql.conf;
6、簡單原則:針對pg9.4版本開始,引數修改可以就認準alter system  set 這樣修改即可!reload後生效就生效了,沒生效就是需要重啟資料庫!



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29654823/viewspace-2933337/,如需轉載,請註明出處,否則將追究法律責任。

相關文章