PostgreSQL体系结构--物理结构

base 				--存放默认数据库的目录
global				--存放的数据库相关的字典视图或者表文件		
pg_commit_ts		    --事务存放的提交的时间戳数据
pg_dynshmem		--动态内存分配存放的空间(dynamic share memeory)
pg_hba.conf			--基于主机的配置文件
pg_ident.conf		    --基于对等认证的配置文件
pg_logical			--存储数据库内部状态的逻辑解码数据
pg_multixact		    --存放多事务状态的数据
pg_notify			    --消息通知目录(LISTEN状态目录)
pg_replslot			--存放复制槽的数据
pg_serial			    --提交的可串行化事务的状态数据
pg_snapshots		    --执行导出快照函数时的状态信息数据
pg_stat 			    --统计信息目录
pg_stat_tmp 		    --临时统计信息目录
pg_subtrans	        --子事务目录
pg_tblspc            --表空间映射目录
pg_twophase         --两阶段提交状态的数据
PG_VERSION	        --存放主版本编号的文件
pg_wal				--存储 WAL 文件的目录
pg_xact 			    --事务提交的状态数据
postgresql.auto.conf --存储通过 ALTER SYSTEM 命令修改的参数文件（可以手动修改）
postgresql.conf       --数据库的参数配置文件
postmaster.opts       --上一次数据库启动状态的命令
postmaster.pid        --存放当前数据库的主进程编号及相关目录及端口的信息

postgres=# create table test(id int,name varchar);
CREATE TABLE

postgres=# select oid,relname from pg_class where relname='test';
  oid  | relname 
-------+---------
 16405 | test
(1 row)

postgres=# select pg_relation_filepath('test');
 pg_relation_filepath 
----------------------
 base/14187/16405
(1 row)

[postgres@training ~]$ cd $PGDATA/base/14187

[postgres@training 14187]$ ls -lh 16405
-rw------- 1 postgres postgres 0 Feb 19 10:40 16405

postgres=# insert into test values (1,'postgresql');
INSERT 0 1

postgres=# select pg_size_pretty(pg_relation_size('test'));
 pg_size_pretty 
----------------
 8192 bytes
(1 row)

[postgres@training 14187]$ ls -lh 16405
-rw------- 1 postgres postgres 8.0K Feb 19 10:45 16405

postgres=# insert into test select id,id||'dagedaged' from generate_series(1,10000000) as id;

postgres=# select pg_size_pretty(pg_relation_size('test'));
 pg_size_pretty 
----------------
 498 MB
(1 row)

[postgres@training 14187]$ ls -lh 16405*
-rw------- 1 postgres postgres 498M Feb 19 10:54 16405
-rw------- 1 postgres postgres 144K Feb 19 10:53 16405_fsm

postgres=# insert into test select id,id||'dageaged' from generate_series(10000000,25000000) as id;
INSERT 0 15000001

postgres=# select pg_size_pretty(pg_relation_size('test'));
 pg_size_pretty 
----------------
 1244 MB
(1 row)

[postgres@training 14187]$ ls -lh 16405*
-rw------- 1 postgres postgres 1.0G Feb 19 10:58 16405
-rw------- 1 postgres postgres 221M Feb 19 10:57 16405.1
-rw------- 1 postgres postgres 336K Feb 19 10:57 16405_fsm

postgres=# select oid,relfilenode from pg_class where relname='test';
  oid  | relfilenode 
-------+-------------
 16405 |       16405
(1 row)

postgres=# truncate table test;
TRUNCATE TABLE

postgres=# select oid,relfilenode from pg_class where relname='test';
  oid  | relfilenode 
-------+-------------
 16405 |       16411
(1 row)

[postgres@training 14187]$ ls -lh 16405*
-rw------- 1 postgres postgres 0 Feb 19 11:04 16405
[postgres@training 14187]$ ls -lh 16411*
-rw------- 1 postgres postgres 0 Feb 19 11:04 16411

postgres=# insert into test select id,id||'dagedaged' from generate_series(1,1000000) as id;
INSERT 0 1000000

postgres=# select oid,relfilenode from pg_class where relname='test';
  oid  | relfilenode 
-------+-------------
 16405 |       16411
(1 row)

[postgres@training 14187]$ ls -lh 16405*
ls: cannot access 16405*: No such file or directory

[postgres@training 14187]$ ls -lh 16411*
-rw------- 1 postgres postgres 50M Feb 19 11:08 16411
-rw------- 1 postgres postgres 32K Feb 19 11:08 16411_fsm

postgres=# \db
          List of tablespaces
    Name    |  Owner   |    Location    
------------+----------+----------------
 pg_default | postgres | 
 pg_global  | postgres |

test_db=# \c mydb
You are now connected to database "mydb" as user "postgres".

mydb=# create table test1(id int,name varchar);
CREATE TABLE

mydb=# \d
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | test1 | table | postgres
(1 row)

mydb=# select pg_relation_filepath('test1');
 pg_relation_filepath 
----------------------
 base/16393/16420
(1 row)

postgres=# \c test_db
You are now connected to database "test_db" as user "postgres".

test_db=# create table test1(id int,name varchar);
CREATE TABLE

test_db=# \d
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | test1 | table | postgres
(1 row)

test_db=# select oid,datname from pg_database where datname='test_db';
  oid  | datname 
-------+---------
 16403 | test_db
(1 row)

test_db=# select pg_relation_filepath('test1');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/16402/PG_12_201909212/16403/16414
(1 row)

[postgres@training 14187]$ cd $PGDATA/pg_tblspc

[postgres@training pg_tblspc]$ ll
total 0
lrwxrwxrwx 1 postgres postgres 14 Feb 19 00:13 16402 -> /data/tbs_test

postgres=# select ctid,id,name from test where id=5;
 ctid  | id |    name    
-------+----+------------
 (0,5) |  5 | 5dagedaged
(1 row)