PostgreSQL启用数据库日志与查看数据库对象

基础操作之___启用数据库日志与查看数据库对象

1、开启数据库用户登录审计日志

（1）编辑配置文件

$ vi postgresql.conf
1

（2）启用以下参数

log_checkpoints = on
log_connections = on
log_disconnections = on
1
2
3

（3）加载配置文件

$ pg_ctl reload -D /pgdata12/
server signaled

等效于：

htdb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
1
2
3
4
5
6
7
8
9
10

日志输出如下：

...
2022-08-20 00:06:06.121 CST [9852] LOG:  parameter "log_checkpoints" changed to "on"
2022-08-20 00:06:06.121 CST [9852] LOG:  parameter "log_connections" changed to "on"
2022-08-20 00:06:06.121 CST [9852] LOG:  parameter "log_disconnections" changed to "on"
1
2
3
4

（4）数据库查询参数

htdb=# select name,setting,context from pg_settings where name = 'log_checkpoints';
      name       | setting | context 
-----------------+---------+---------
 log_checkpoints | on      | sighup
(1 row)
1
2
3
4
5

2、用户登录测试

$ psql -Uhtuser htdb
psql (12.3)
Type "help" for help.

htdb=# 
1
2
3
4
5

日志输出如下：

...
2022-08-20 00:06:24.954 CST [9971] LOG:  connection received: host=[local]
2022-08-20 00:06:24.955 CST [9971] LOG:  connection authorized: user=htuser database=htdb application_name=psql
1
2
3

3、查看数据库对象信息

（1）创建测试表

htdb=# create table httab(id serial primary key,name text,age int);
CREATE TABLE
1
2

查看表信息

htdb=# \d httab
                            Table "public.httab"
 Column |  Type   | Collation | Nullable |              Default              
--------+---------+-----------+----------+-----------------------------------
 id     | integer |           | not null | nextval('httab_id_seq'::regclass)
 name   | text    |           |          | 
 age    | integer |           |          | 
Indexes:
    "httab_pkey" PRIMARY KEY, btree (id)

htdb=#
1
2
3
4
5
6
7
8
9
10
11

（2）创建索引

htdb=# create index idx_name on httab(name);
CREATE INDEX
1
2

查看表信息

htdb=# \d httab
                            Table "public.httab"
 Column |  Type   | Collation | Nullable |              Default              
--------+---------+-----------+----------+-----------------------------------
 id     | integer |           | not null | nextval('httab_id_seq'::regclass)
 name   | text    |           |          | 
 age    | integer |           |          | 
Indexes:
    "httab_pkey" PRIMARY KEY, btree (id)
    "idx_name" btree (name)
1
2
3
4
5
6
7
8
9
10

插入测试数据

htdb=# insert into  httab(name,age)  select  md5(random()::text),rn from  generate_series(1,10000) rn;
INSERT 0 10000
1
2

4、查看表和索引大小

htdb=# \dt+ httab
                   List of relations
 Schema | Name  | Type  | Owner  |  Size  | Description 
--------+-------+-------+--------+--------+-------------
 public | httab | table | htuser | 784 kB | 
(1 row)
1
2
3
4
5
6

htdb=# \di+ idx_name
                         List of relations
 Schema |   Name   | Type  | Owner  | Table |  Size  | Description 
--------+----------+-------+--------+-------+--------+-------------
 public | idx_name | index | htuser | httab | 712 kB | 
(1 row)
1
2
3
4
5
6

htdb=# select pg_relation_size('httab') tabsize,pg_relation_size('idx_name') indsize;
 tabsize | indsize 
---------+---------
  770048 |  729088
(1 row)
1
2
3
4
5

htdb=# select pg_size_pretty(pg_relation_size('httab')) tabsize,pg_size_pretty(pg_relation_size('httab')) indsize;
 tabsize | indsize 
---------+---------
 752 kB  | 752 kB
(1 row)
1
2
3
4
5

5、查看表数据文件路径

htdb=# select pg_relation_filepath('httab') tab_path;
     tab_path     
------------------
 base/16384/16399
(1 row)
1
2
3
4
5

16384表示库ID

16399表示表（对象）ID

不涉及表空间时，一般就2层目录

6、pg目录结构说明

请参阅官方文档：点击此处

[postgres@junzi pgdata12]$ ll
total 68
drwx------. 6 postgres postgres    54 Aug 18 19:11 base
-rw-------. 1 postgres postgres    44 Aug 20 00:06 current_logfiles
drwx------. 2 postgres postgres  4096 Aug 19 23:55 global
drwx------. 2 postgres postgres  4096 Aug 20 00:00 log
drwx------. 2 postgres postgres     6 Aug 17 23:32 pg_commit_ts
drwx------. 2 postgres postgres     6 Aug 17 23:32 pg_dynshmem
-rw-------. 1 postgres postgres  4581 Aug 18 19:50 pg_hba.conf
-rw-------. 1 postgres postgres  1636 Aug 17 23:32 pg_ident.conf
drwx------. 4 postgres postgres    68 Aug 20 00:30 pg_logical
drwx------. 4 postgres postgres    36 Aug 17 23:32 pg_multixact
drwx------. 2 postgres postgres    18 Aug 19 23:54 pg_notify
drwx------. 2 postgres postgres     6 Aug 17 23:32 pg_replslot
drwx------. 2 postgres postgres     6 Aug 17 23:32 pg_serial
drwx------. 2 postgres postgres     6 Aug 17 23:32 pg_snapshots
drwx------. 2 postgres postgres     6 Aug 19 23:54 pg_stat
drwx------. 2 postgres postgres    84 Aug 20 00:34 pg_stat_tmp
drwx------. 2 postgres postgres    18 Aug 17 23:32 pg_subtrans
drwx------. 2 postgres postgres     6 Aug 17 23:32 pg_tblspc
drwx------. 2 postgres postgres     6 Aug 17 23:32 pg_twophase
-rw-------. 1 postgres postgres     3 Aug 17 23:32 PG_VERSION
drwx------. 3 postgres postgres    60 Aug 17 23:32 pg_wal
drwx------. 2 postgres postgres    18 Aug 17 23:32 pg_xact
-rw-------. 1 postgres postgres    88 Aug 17 23:32 postgresql.auto.conf
-rw-------. 1 postgres postgres 26612 Aug 20 00:06 postgresql.conf
-rw-------. 1 postgres postgres    43 Aug 19 23:54 postmaster.opts
-rw-------. 1 postgres postgres    79 Aug 19 23:54 postmaster.pid
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

（初次学习、诸多不足、请多指教）