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

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

1、开启数据库用户登录审计日志
(1)编辑配置文件
$ vi postgresql.conf
(2)启用以下参数
log_checkpoints = on
log_connections = on
log_disconnections = on
(3)加载配置文件
$ pg_ctl reload -D /pgdata12/
server signaled

等效于:

htdb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

日志输出如下:

...
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"
(4)数据库查询参数
htdb=# select name,setting,context from pg_settings where name = 'log_checkpoints';
      name       | setting | context 
-----------------+---------+---------
 log_checkpoints | on      | sighup
(1 row)
2、用户登录测试
$ psql -Uhtuser htdb
psql (12.3)
Type "help" for help.

htdb=# 

日志输出如下:

...
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
3、查看数据库对象信息
(1)创建测试表
htdb=# create table httab(id serial primary key,name text,age int);
CREATE TABLE

查看表信息

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=#
(2)创建索引
htdb=# create index idx_name on httab(name);
CREATE INDEX

查看表信息

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)

插入测试数据

htdb=# insert into  httab(name,age)  select  md5(random()::text),rn from  generate_series(1,10000) rn;
INSERT 0 10000
4、查看表和索引大小
htdb=# \dt+ httab
                   List of relations
 Schema | Name  | Type  | Owner  |  Size  | Description 
--------+-------+-------+--------+--------+-------------
 public | httab | table | htuser | 784 kB | 
(1 row)
htdb=# \di+ idx_name
                         List of relations
 Schema |   Name   | Type  | Owner  | Table |  Size  | Description 
--------+----------+-------+--------+-------+--------+-------------
 public | idx_name | index | htuser | httab | 712 kB | 
(1 row)
htdb=# select pg_relation_size('httab') tabsize,pg_relation_size('idx_name') indsize;
 tabsize | indsize 
---------+---------
  770048 |  729088
(1 row)
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)
5、查看表数据文件路径
htdb=# select pg_relation_filepath('httab') tab_path;
     tab_path     
------------------
 base/16384/16399
(1 row)

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
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值