如何判断一个数据库集群是主库还是备库？

核心思想是使用pg_is_in_recovery()函数。下面的SQL就完成这个任务：

oracle=# select case when pg_is_in_recovery() then 'Standby/Reader DB (Read Only)' else 'Primary/writer DB (Read write)' end as standby_mode;
          standby_mode          
--------------------------------
 Primary/writer DB (Read write)
(1 row)

如果pg_is_in_recovery()函数返回true，则表明该数据库集群是只读的备库，否则则该数据库集群是可读可写的主库。

下述SQL查询出一些全局信息：

oracle=# select  now () as "Date" ,pg_postmaster_start_time() as "DB_START_DATE", current_timestamp - pg_postmaster_start_time() as "UP_TIME"  ,current_database() as "DB_connected" ,current_user USER_NAME,inet_server_port() as "DB_PORT ",version()  as "DB_Version" , setting AS block_size FROM pg_settings WHERE name = 'block_size';
-[ RECORD 1 ]-+-------------------------------------------------------------------------------------------------------
Date          | 2025-09-08 18:56:24.409477+00
DB_START_DATE | 2025-09-08 18:51:25.155216+00
UP_TIME       | 00:04:59.254261
DB_connected  | oracle
user_name     | postgres
DB_PORT       | 
DB_Version    | PostgreSQL 18rc1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
block_size    | 8192

通过这个SQL我们可以学习到如下几个函数的使用：

1. pg_postmaster_start_time()记录了该数据库集群的启动时间
2. current_database()函数记录了当前正在连接的数据库。
3. current_user()记录了当前正在连接的用户名
4. 从pg_settings中查询'block_size'就可以知道数据块的大小。

查询数据库中已经安装的插件：

oracle=# SELECT e.extname AS "Extension Name", e.extversion AS "Version", n.nspname AS "Schema",pg_get_userbyid(e.extowner)  as Owner,  c.description AS "Description" , e.extrelocatable as "relocatable to another schema", e.extconfig ,e.extcondition
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
ORDER BY 1;
-[ RECORD 1 ]-----------------+-----------------------------------------------------------------------
Extension Name                | pageinspect
Version                       | 1.13
Schema                        | public
owner                         | postgres
Description                   | inspect the contents of database pages at a low level
relocatable to another schema | t
extconfig                     | 
extcondition                  | 
-[ RECORD 2 ]-----------------+-----------------------------------------------------------------------
Extension Name                | pg_stat_statements
Version                       | 1.12
Schema                        | public
owner                         | postgres
Description                   | track planning and execution statistics of all SQL statements executed
relocatable to another schema | t
extconfig                     | 
extcondition                  | 
-[ RECORD 3 ]-----------------+-----------------------------------------------------------------------
Extension Name                | pg_visibility
Version                       | 1.2
Schema                        | public
owner                         | postgres
Description                   | examine the visibility map (VM) and page-level visibility info
relocatable to another schema | t
extconfig                     | 
extcondition                  | 
-[ RECORD 4 ]-----------------+-----------------------------------------------------------------------
Extension Name                | plpgsql
Version                       | 1.0
Schema                        | pg_catalog
owner                         | postgres
Description                   | PL/pgSQL procedural language
relocatable to another schema | f
extconfig                     | 
extcondition                  | 

这些信息主要来自pg_catalog.pg_extension这个系统表，上述SQL不难理解。

PG数据库集群比较重要的控制内存的参数

postgres=# (
select name as parameter_name , setting , unit, (setting::BIGINT/1024)::BIGINT  as "size_MB" ,(setting::BIGINT/1024/1024)::BIGINT  as "size_GB" ,  pg_size_pretty((setting::BIGINT*1024)::BIGINT)
from pg_settings where name in ('work_mem','maintenance_work_mem')
)
UNION ALL
(
select name as parameter_name, setting , unit , (((setting::BIGINT)*8)/1024)::BIGINT  as "size_MB" ,(((setting::BIGINT)*8)/1024/1024)::BIGINT  as "size_GB", pg_size_pretty((((setting::BIGINT)*8)*1024)::BIGINT)
from pg_settings where name in ('shared_buffers','wal_buffers','effective_cache_size','temp_buffers')
) order by 4  desc;
    parameter_name    | setting | unit | size_MB | size_GB | pg_size_pretty 
----------------------+---------+------+---------+---------+----------------
 effective_cache_size | 524288  | 8kB  |    4096 |       4 | 4096 MB
 shared_buffers       | 16384   | 8kB  |     128 |       0 | 128 MB
 maintenance_work_mem | 65536   | kB   |      64 |       0 | 64 MB
 temp_buffers         | 1024    | 8kB  |       8 |       0 | 8192 kB
 work_mem             | 4096    | kB   |       4 |       0 | 4096 kB
 wal_buffers          | 512     | 8kB  |       4 |       0 | 4096 kB
(6 rows)

一共6个参数，其数据来源都来自pg_settings这个系统表，不难理解。

共享池命中率hit_radio

postgres=# select                          
round((sum(blks_hit)::numeric / (sum(blks_hit) + sum(blks_read)::numeric))*100,2) as cache_read_hit_percentage
from pg_stat_database ;
 cache_read_hit_percentage 
---------------------------
                     98.98
(1 row)

Hit_radio这个参数的值越大越好。该值越大，表明当后台进程需要查询某一个数据页时，会有更大的几率在共享池中找到它。如果没有在共享池中找到它，就需要从磁盘上读取，这是性能下降的明显特征。

这个值最好是99.9999999%，越接近100%越好。如果这个指标低，DBA就要考虑增大shared_buffers的尺寸，并考虑使用pg_prewarm去提前加载需要频繁访问的数据块到内存中。

这个查询不难理解，所有的数据都来自pg_stat_database这个系统表。