postgresql-基本管理

查看控制文件内容

$ pg_controldata 
pg_control version number:            1201
Catalog version number:               201909212
Database system identifier:           7253057961538375212
Database cluster state:               in production
....
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            5d83d710493320e2862843fd7c4e6d539a4929a41e34b192397cdc197a20418d

查看表对应的OID

test=# select relfilenode from pg_class where relname='t1';
 relfilenode 
-------------
       16385

test=# select pg_relation_filepath('t1');
 pg_relation_filepath 
----------------------
 base/16384/16385

test=# show data_directory;
 data_directory 
----------------
 /pgdata

查看WAL日志信息

## 查看当前记录的日志文件
test=# select pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 000000010000000000000001

## wal日志文件信息
test=# select * from pg_lsn_waldir() order by modification asc;
ERROR:  function pg_lsn_waldir() does not exist
LINE 1: select * from pg_lsn_waldir() order by modification asc;
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
test=# select * from pg_ls_waldir() order by modification asc;
           name           |   size   |      modification      
--------------------------+----------+------------------------
 000000010000000000000001 | 16777216 | 2023-07-18 21:10:50+08

## 切换wal日志文件
test=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/16507F8

使用pg_waldump命令查看wal日志文件内容

$ pg_waldump 000000010000000000000001

设置归档

## 开启归档
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

重启数据库

$ pg_ctl restart

插入数据测试

insert into t1 values (generate_series(1,1000));
select pg_switch_wal();