服务器类型？
基于PG的数据库见这里。
服务器版本？
详见版本策略和发布情况。PG主版本软件支持生命周期为5年。
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 16.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)
postgres=# show server_version;
server_version
----------------
16.9
(1 row)
postgres=# show server_version_num;
server_version_num
--------------------
160009
(1 row)
大版本号为16，小版本号为9。
操作系统命令：
$ pg_config --version
PostgreSQL 16.9
$ cat $PGDATA/PG_VERSION
16
服务器正常运行时间是多少？
参考系统信息函数。
SELECT date_trunc('second', CURRENT_TIMESTAMP - pg_postmaster_start_time()) AS uptime;
uptime
----------
01:59:58
(1 row)
查找数据库服务器文件
postgres=# show data_directory;
data_directory
------------------------
/var/lib/pgsql/16/data
(1 row)
postgres=# show data_directory_mode;
data_directory_mode
---------------------
0700
(1 row)
postgres=# \! echo $PGDATA
/var/lib/pgsql/16/data
initdb 会创建一个新的 PostgreSQL 数据库集群。数据库集群是由单个服务器实例管理的数据库集合。创建数据库集群包括创建数据库数据所在的目录、生成共享目录表（属于整个集群而非任何特定数据库的表），以及创建 template1 和 postgres 数据库。initdb 会初始化数据库集群的默认语言环境和字符集编码。
数据库日志在哪里？
postgres=# show log_filename;
log_filename
-------------------
postgresql-%a.log
(1 row)
postgres=# \! ls -1 $PGDATA/log
postgresql-Fri.log
postgresql-Mon.log
postgresql-Sat.log
postgresql-Sun.log
postgresql-Thu.log
postgresql-Tue.log
postgresql-Wed.log
postgres=# show log_
log_autovacuum_min_duration log_file_mode log_parameter_max_length log_statement
log_checkpoints log_filename log_parameter_max_length_on_error log_statement_sample_rate
log_connections log_hostname log_parser_stats log_statement_stats
log_destination log_line_prefix log_planner_stats log_temp_files
log_directory log_lock_waits log_recovery_conflict_waits log_timezone
log_disconnections log_min_duration_sample log_replication_commands log_transaction_sample_rate
log_duration log_min_duration_statement log_rotation_age log_truncate_on_rotation
log_error_verbosity log_min_error_statement log_rotation_size
log_executor_stats log_min_messages log_startup_progress_interval
postgres=# show log_destination;
log_destination
-----------------
stderr
(1 row)
查找数据库的系统标识符
查找Database system identifier项：
$ pg_controldata $PGDATA
pg_control version number: 1300
Catalog version number: 202307071
Database system identifier: 7516897327084901236
Database cluster state: in production
pg_control last modified: Wed 09 Jul 2025 02:45:02 AM UTC
Latest checkpoint location: 0/4B000110
Latest checkpoint's REDO location: 0/4B0000D8
Latest checkpoint's REDO WAL file: 00000004000000000000004B
Latest checkpoint's TimeLineID: 4
Latest checkpoint's PrevTimeLineID: 4
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:5716
Latest checkpoint's NextOID: 39275
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 722
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 5716
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Wed 09 Jul 2025 02:45:02 AM UTC
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: logical
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce: 23fac3bfa38c9430ea0819c4669226bc5d4e96f829cfccc6f325c0c1db52e345
列出数据库服务器上的数据库
$ psql -l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
--------------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
demo | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
sampledb | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
world_temperatures | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
(6 rows)
psql元命令\l输出同上。
也可从系统表查询：
postgres=# select datname from pg_database;
datname
--------------------
postgres
template1
template0
world_temperatures
demo
sampledb
(6 rows)
数据库中有多少个表？
通过查询information schema。需要DBA权限，需要切换到相应的数据库中。
根据文档：
information schema 由一组视图组成，这些视图包含当前数据库中定义的对象的相关信息。信息模式定义在 SQL 标准中，因此可以预期其可移植且稳定——这与 system catalog 不同，system catalog 是 PostgreSQL 特有的，并且是根据实现问题建模的。然而，information schema 视图不包含有关 PostgreSQL 特定功能的信息；要查询这些功能，您需要查询 system catalog 或其他 PostgreSQL 特定视图。
这句话是什么意思呢？例如分区表，由于PG有特殊的实现，所以从information schema 是无法分辨出来分区的父表和子表的，这需要查询pg_class。
sampledb=> select table_name, table_type from information_schema.tables where table_name in ('costs', 'costs_1995');
table_name | table_type
------------+------------
costs | BASE TABLE
costs_1995 | BASE TABLE
(2 rows)
SELECT relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.oid IN (
SELECT partrelid FROM pg_partitioned_table
)
AND relname = 'costs';
information_schema表的定义及示例：
postgres=# \d information_schema.tables
View "information_schema.tables"
Column | Type | Collation | Nullable | Default
------------------------------+----------------+-----------+----------+---------
table_catalog | sql_identifier | | |
table_schema | sql_identifier | | |
table_name | sql_identifier | | |
table_type | character_data | | |
self_referencing_column_name | sql_identifier | | |
reference_generation | character_data | | |
user_defined_type_catalog | sql_identifier | | |
user_defined_type_schema | sql_identifier | | |
user_defined_type_name | sql_identifier | | |
is_insertable_into | yes_or_no | | |
is_typed | yes_or_no | | |
commit_action | character_data | | |
postgres=# select table_schema, count(*) from information_schema.tables group by table_schema;
table_schema | count
--------------------+-------
pg_catalog | 140
sh | 65
hr | 8
information_schema | 69
(4 rows)
当前schema中所有表：
sampledb=> select current_user;
current_user
--------------
hr
(1 row)
sampledb=> \d
List of relations
Schema | Name | Type | Owner
--------+------------------+----------+-------
hr | countries | table | hr
hr | departments | table | hr
hr | departments_seq | sequence | hr
hr | emp_details_view | view | hr
hr | employees | table | hr
hr | employees_seq | sequence | hr
hr | job_history | table | hr
hr | jobs | table | hr
hr | locations | table | hr
hr | locations_seq | sequence | hr
hr | regions | table | hr
(11 rows)
当前用户可以看到的所有schema：
sampledb=> select current_user;
current_user
--------------
hr
(1 row)
sampledb=> SELECT nspname, count(*) FROM pg_class, pg_namespace
where pg_class.relnamespace = pg_namespace.oid
and relkind = 'r'
group by nspname;
nspname | count
--------------------+-------
pg_catalog | 64
sh | 63
hr | 7
information_schema | 4
(4 rows)
数据库占用多少磁盘空间？
参见系统管理函数。
-- 单位是字节
sampledb=> SELECT pg_database_size(current_database());
pg_database_size
------------------
95465955
(1 row)
sampledb=> SELECT pg_size_pretty(pg_database_size(current_database()));
pg_size_pretty
----------------
91 MB
(1 row)
sampledb=> SELECT oid FROM pg_database WHERE datname = current_database();
oid
-------
37435
(1 row)
sampledb=> SELECT pg_database_size(37435);
pg_database_size
------------------
95465955
(1 row)
sampledb=> \df+ pg_database_size
List of functions
Schema | Name | Result data type | Argument data types | Type | Vola
tility | Parallel | Owner | Security | Access privileges | Language | Internal
name | Description
------------+------------------+------------------+---------------------+------+-----
-------+----------+----------+----------+-------------------+----------+-------------
----------+---------------------------------------------------
pg_catalog | pg_database_size | bigint | name | func | vola
tile | safe | postgres | invoker | | internal | pg_database_
size_name | total disk space usage for the specified database
pg_catalog | pg_database_size | bigint | oid | func | vola
tile | safe | postgres | invoker | | internal | pg_database_
size_oid | total disk space usage for the specified database
(2 rows)
数据库当前占用多少内存……
参见PG用于监控的wiki，以及pg_buffercache — inspect PostgreSQL buffer cache state。
pg_buffercache 模块提供了一种实时检查共享缓冲区缓存中发生情况的方法。它还提供了一种低级方法来从中移除数据，以用于测试目的。
共享缓存中的每个缓冲区都有一行。未使用的缓冲区显示为空，除 bufferid 外，所有字段均为空。共享系统目录显示为属于数据库 0。
SELECT
datname,
pg_size_pretty(CAST(current_setting('block_size') AS bigint) * count(*))
FROM
pg_buffercache c
LEFT JOIN pg_database d ON c.reldatabase = d.oid
GROUP BY
datname
ORDER BY
datname;
datname | pg_size_pretty
----------+----------------
postgres | 2888 kB
sampledb | 87 MB
| 39 MB
(3 rows)
PG的block大小默认为8K：
sampledb=> show block_size;
block_size
------------
8192
(1 row)
sampledb=> select current_setting('block_size');
current_setting
-----------------
8192
(1 row)
一个表占用多少磁盘空间？
sampledb=> select pg_total_relation_size('sh.products');
pg_total_relation_size
------------------------
49152
(1 row)
sampledb=> select pg_relation_size('sh.products');
pg_relation_size
------------------
16384
(1 row)
sampledb=> select pg_table_size('sh.products');
pg_table_size
---------------
49152
(1 row)
-- 为0，因为是分区表的母表
sampledb=> select pg_total_relation_size('sh.costs');
pg_total_relation_size
------------------------
0
(1 row)
sampledb=> select count(*) from sh.costs;
count
-------
82112
(1 row)
我最大的表有哪些？
SELECT
table_schema || '.' || table_name AS name,
pg_total_relation_size(table_schema || '.' || table_name) AS size
FROM
information_schema.tables
WHERE
table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY
size DESC
LIMIT 10;
name | size
------------------+----------
sh.customers | 13737984
sh.sales_q4_2001 | 4808704
sh.sales_q3_1999 | 4759552
sh.sales_q3_2001 | 4538368
sh.sales_q1_2000 | 4497408
sh.sales_q1_1999 | 4497408
sh.sales_q2_2001 | 4497408
sh.sales_q4_1999 | 4300800
sh.sales_q3_2000 | 4235264
sh.sales_q1_2001 | 4186112
(10 rows)
一个表中有多少行？
sampledb=> select count(*) from sh.customers;
count
-------
55500
(1 row)
快速估算表中的行数
通过优化器。
sampledb=> explain select * from sh.customers;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on customers (cost=0.00..2227.00 rows=55500 width=228)
(1 row)
其实也可以从系统表中获得，其中数据是通过VACUUM 和 ANALYZE获得的：
sampledb=> SELECT oid FROM pg_class WHERE relname = 'customers';
oid
-------
39581
(1 row)
sampledb=> select reltuples from pg_class where oid = 39581;
reltuples
-----------
55500
(1 row)
sampledb=> select reltuples from pg_class where oid = 'customers'::regclass;;
reltuples
-----------
55500
(1 row)
regclass属于Object Identifier Type，详见这里。
计算平均每block中的行数：
sampledb=> select reltuples/relpages from pg_class where oid = 'customers'::regclass; ?column?
-------------------
33.19377990430622
(1 row)
列出此数据库中的扩展
只列出已安装的：
sampledb=> SELECT * FROM pg_extension \gx
-[ RECORD 1 ]--+---------
oid | 13590
extname | plpgsql
extowner | 10
extnamespace | 11
extrelocatable | f
extversion | 1.0
extconfig |
extcondition |
-[ RECORD 2 ]--+---------
oid | 39604
extname | file_fdw
extowner | 10
extnamespace | 2200
extrelocatable | t
extversion | 1.0
extconfig |
extcondition |
可用的extension：
$ pg_config --sharedir
/usr/pgsql-16/share
$ ls /usr/pgsql-16/share/extension/*.control|grep file_fdw
/usr/pgsql-16/share/extension/file_fdw.control
$ ls /usr/pgsql-16/share/extension/*.control|wc -l
53
详见这里。
了解对象依赖关系
这里指的是参照一致性。
sampledb=> \d employees
Table "hr.employees"
Column | Type | Collation | Nullable | Default
----------------+-----------------------+-----------+----------+---------
employee_id | numeric(6,0) | | not null |
first_name | character varying(20) | | |
last_name | character varying(25) | | not null |
email | character varying(25) | | not null |
phone_number | character varying(20) | | |
hire_date | date | | not null |
job_id | character varying(10) | | not null |
salary | numeric(8,2) | | |
commission_pct | numeric(2,2) | | |
manager_id | numeric(6,0) | | |
department_id | numeric(4,0) | | |
Indexes:
"emp_emp_id_pk" PRIMARY KEY, btree (employee_id)
"emp_department_ix" btree (department_id)
"emp_email_uk" UNIQUE CONSTRAINT, btree (email)
"emp_job_ix" btree (job_id)
"emp_manager_ix" btree (manager_id)
"emp_name_ix" btree (last_name, first_name)
Check constraints:
"emp_salary_min" CHECK (salary > 0::numeric)
Foreign-key constraints:
"emp_dept_fk" FOREIGN KEY (department_id) REFERENCES departments(department_id)
"emp_job_fk" FOREIGN KEY (job_id) REFERENCES jobs(job_id)
"emp_manager_fk" FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
Referenced by:
TABLE "departments" CONSTRAINT "dept_mgr_fk" FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
TABLE "employees" CONSTRAINT "emp_manager_fk" FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
TABLE "job_history" CONSTRAINT "jhist_emp_fk" FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
Triggers:
update_job_history AFTER UPDATE OF job_id, department_id ON employees FOR EACH ROW EXECUTE FUNCTION update_job_history_trigger()
Disabled user triggers:
secure_employees BEFORE INSERT OR DELETE OR UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION secure_employees_trigger()
sampledb=> select * from pg_constraint where confrelid = 'employees'::regclass \gx
-[ RECORD 1 ]--+---------------
oid | 37516
conname | dept_mgr_fk
connamespace | 37436
contype | f
condeferrable | f
condeferred | f
convalidated | t
conrelid | 37447
contypid | 0
conindid | 37483
conparentid | 0
confrelid | 37451
confupdtype | a
confdeltype | a
confmatchtype | s
conislocal | t
coninhcount | 0
connoinherit | t
conkey | {3}
confkey | {1}
conpfeqop | {1752}
conppeqop | {1752}
conffeqop | {1752}
confdelsetcols |
conexclop |
conbin |
-[ RECORD 2 ]--+---------------
oid | 37531
conname | emp_manager_fk
connamespace | 37436
contype | f
condeferrable | f
condeferred | f
convalidated | t
conrelid | 37451
contypid | 0
conindid | 37483
conparentid | 0
confrelid | 37451
confupdtype | a
confdeltype | a
confmatchtype | s
conislocal | t
coninhcount | 0
connoinherit | t
conkey | {10}
confkey | {1}
conpfeqop | {1752}
conppeqop | {1752}
conffeqop | {1752}
confdelsetcols |
conexclop |
conbin |
-[ RECORD 3 ]--+---------------
oid | 37541
conname | jhist_emp_fk
connamespace | 37436
contype | f
condeferrable | f
condeferred | f
convalidated | t
conrelid | 37472
contypid | 0
conindid | 37483
conparentid | 0
confrelid | 37451
confupdtype | a
confdeltype | a
confmatchtype | s
conislocal | t
coninhcount | 0
connoinherit | t
conkey | {1}
confkey | {1}
conpfeqop | {1752}
conppeqop | {1752}
conffeqop | {1752}
confdelsetcols |
conexclop |
conbin |
删除示例：
sampledb=> drop table employees;
ERROR: cannot drop table employees because other objects depend on it
DETAIL: view emp_details_view depends on table employees
constraint dept_mgr_fk on table departments depends on table employees
constraint jhist_emp_fk on table job_history depends on table employees
HINT: Use DROP ... CASCADE to drop the dependent objects too.
sampledb=> drop table employees cascade;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to view emp_details_view
drop cascades to constraint dept_mgr_fk on table departments
drop cascades to constraint jhist_emp_fk on table job_history
DROP TABLE