Postgresql存储结构

# initdb -D /home/mingjie.gmj/databases/data/pgdata8412

The files belonging to this database system will be owned by user "mingjie.gmj".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /home/mingjie.gmj/databases/data/pgdata8412 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /home/mingjie.gmj/databases/data/pgdata8412 -l logfile start

.
|-- base
|   |-- 1
|   |   |-- 112
|   |   |-- 113
|   |   |-- 1247
|   |   |-- 1247_fsm
|   |   |-- 1247_vm
.........
|   |   |-- 828
|   |   |-- pg_filenode.map
|   |   `-- PG_VERSION
|   |-- 13157
|   |   |-- 112
|   |   |-- 113
|   |   |-- 1247
|   |   |-- 1247_fsm
|   |   |-- 1247_vm
.........
|   |   |-- 827
|   |   |-- 828
|   |   |-- pg_filenode.map
|   |   `-- PG_VERSION
|   `-- 13158
|       |-- 112
|       |-- 113
|       |-- 1247
|       |-- 1247_fsm
|       |-- 1247_vm
.........
|       |-- 827
|       |-- 828
|       |-- pg_filenode.map
|       `-- PG_VERSION
|-- global
|   |-- 1136
|   |-- 1136_fsm
|   |-- 1136_vm
.........
|   |-- 6114
|   |-- 6115
|   |-- pg_control
|   |-- pg_filenode.map
|   `-- pg_internal.init
|-- pg_commit_ts
|-- pg_dynshmem
|-- pg_hba.conf
|-- pg_ident.conf
|-- pg_logical
|   |-- mappings
|   |-- replorigin_checkpoint
|   `-- snapshots
|-- pg_multixact
|   |-- members
|   |   `-- 0000
|   `-- offsets
|       `-- 0000
|-- pg_notify
|   `-- 0000
|-- pg_replslot
|-- pg_serial
|-- pg_snapshots
|-- pg_stat
|-- pg_stat_tmp
|-- pg_subtrans
|   `-- 0000
|-- pg_tblspc
|-- pg_twophase
|-- PG_VERSION
|-- pg_wal
|   |-- 000000010000000000000001
|   `-- archive_status
|-- pg_xact
|   `-- 0000
|-- postgresql.auto.conf
`-- postgresql.conf

postgres=# select oid, datname, dattablespace from pg_database ;
  oid  |  datname  | dattablespace
-------+-----------+---------------
 13158 | postgres  |          1663
     1 | template1 |          1663
 13157 | template0 |          1663``


~/databases/data/pgdata8412/base][12]$ ll
total 20
drwx------ 2 mingjie.gmj users  4096 Apr 22 20:17 1
drwx------ 2 mingjie.gmj users  4096 Apr 22 20:17 13157
drwx------ 2 mingjie.gmj users 12288 Apr 22 20:17 13158

postgres=# create table tbl1 (id int, info text);
CREATE TABLE

postgres=# select relname, relfilenode from pg_class where relname='tbl1';
 relname | relfilenode
---------+-------------
 tbl1    |       16384
 
postgres=# SELECT pg_relation_filepath('tbl1');
 pg_relation_filepath
----------------------
 base/13158/16384
(1 row)

ls -al  $PGDATA/base/13158/16384
-rw------- 1 mingjie.gmj users 8192 Apr 22 20:47 16384

postgres=# select relname, relfilenode from pg_class where relname='tbl1';
 relname | relfilenode
---------+-------------
 tbl1    |       16384
(1 row)

postgres=# truncate tbl1;
TRUNCATE TABLE
postgres=# select relname, relfilenode from pg_class where relname='tbl1';
 relname | relfilenode
---------+-------------
 tbl1    |       16390
(1 row)

postgres=# select oid,relname, relfilenode from pg_class where relname='pg_class';
 oid  | relname  | relfilenode
------+----------+-------------
 1259 | pg_class |           0
 
postgres=# SELECT pg_relation_filepath('pg_class');
 pg_relation_filepath
----------------------
 base/13158/1259
(1 row)

postgres=# select oid,relname, relfilenode from pg_class where relname='pg_database';
 oid  |   relname   | relfilenode
------+-------------+-------------
 1262 | pg_database |           0

postgres=# SELECT pg_relation_filepath('pg_database');
 pg_relation_filepath
----------------------
 global/1262

ll | grep 1247
-rw------- 1 mingjie.gmj users  73728 Apr 22 20:47 1247
-rw------- 1 mingjie.gmj users  24576 Apr 22 20:17 1247_fsm
-rw------- 1 mingjie.gmj users   8192 Apr 22 20:47 1247_vm

# fsm文件：空闲空间映射表
# vm文件：可见性映射表

CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

postgres=# create tablespace tmpspace location '/tmp/tmptsp';
CREATE TABLESPACE

postgres=# CREATE TABLE foo(i int) TABLESPACE tmpspace;
CREATE TABLE

postgres=# select oid,datname from pg_database where datname='postgres';
  oid  | datname
-------+----------
 13158 | postgres
 
postgres=# select oid,relfilenode from pg_class where relname='foo';
  oid  | relfilenode
-------+-------------
 46807 |       46807
(1 row)

postgres=# select pg_relation_filepath('foo');
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/46803/PG_10_201707211/13158/46807
(1 row)

ll /tmp/tmptsp/PG_10_201707211/13158/46807
-rw------- 1 mingjie.gmj users 0 Apr 23 10:55 /tmp/tmptsp/PG_10_201707211/13158/46807

PG _ 'Major version' _ 'Catalogue version number'

typedef struct PageHeaderData
{
	/* XXX LSN is member of *any* block, not only page-organized ones */
	PageXLogRecPtr pd_lsn;		/* LSN: next byte after last byte of xlog
								 * record for last change to this page */
	uint16		pd_checksum;	/* checksum */
	uint16		pd_flags;		/* flag bits, see below */
	LocationIndex pd_lower;		/* offset to start of free space */
	LocationIndex pd_upper;		/* offset to end of free space */
	LocationIndex pd_special;	/* offset to start of special space */
	uint16		pd_pagesize_version;
	TransactionId pd_prune_xid; /* oldest prunable XID, or zero if none */
	ItemIdData	pd_linp[FLEXIBLE_ARRAY_MEMBER]; /* line pointer array */
} PageHeaderData;

typedef struct ItemIdData
{
	unsigned	lp_off:15,		/* offset to tuple (from start of page) */
				lp_flags:2,		/* state of item pointer, see below */
				lp_len:15;		/* byte length of tuple */
} ItemIdData;

struct HeapTupleHeaderData
{
	union
	{
		HeapTupleFields t_heap;
		DatumTupleFields t_datum;
	}			t_choice;

	ItemPointerData t_ctid;		/* current TID of this or newer tuple (or a
								 * speculative insertion token) */

	/* Fields below here must match MinimalTupleData! */

	uint16		t_infomask2;	/* number of attributes + various flags */

	uint16		t_infomask;		/* various flag bits, see below */

	uint8		t_hoff;			/* sizeof header incl. bitmap, padding */

	/* ^ - 23 bytes - ^ */

	bits8		t_bits[FLEXIBLE_ARRAY_MEMBER];	/* bitmap of NULLs */

	/* MORE DATA FOLLOWS AT END OF STRUCT */
};

typedef struct HeapTupleFields
{
	TransactionId t_xmin;		/* inserting xact ID */
	TransactionId t_xmax;		/* deleting or locking xact ID */

	union
	{
		CommandId	t_cid;		/* inserting or deleting command ID, or both */
		TransactionId t_xvac;	/* old-style VACUUM FULL xact ID */
	}			t_field3;
} HeapTupleFields;