PostgreSQL体系架构

PostgreSQL数据组织结构

在PostgreSQL数据库系统中,数据的组结结构分为以下五层:

  • 实例:一个安装的数据目录$PGDATA,即一个实例
  • 数据库:一个数据库服务下可以管理多个数据库
  • 模式:一个数据库可以创建多个不同的名称空间Schema,用于分隔不同的业务数据
  • 表和索引:一个数据库可以有多个表和索引,PostgreSQL中表的术语称为Relation
  • 行和列:每张表中有很多列和行数据,PostgreSQL中术语为Tuple

PostgreSQL是进程架构模型,MySQL是线程架构模型

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
[root@Rocky8-2 ~]# pstree -p
systemd(1)─┬─NetworkManager(831)─┬─{NetworkManager}(857)
│ └─{NetworkManager}(861)
├─VGAuthService(832)
├─agetty(907)
├─anacron(1326)
├─atd(898)
├─auditd(805)───{auditd}(806)
├─chronyd(847)
├─crond(903)
├─dbus-daemon(830)
├─gssproxy(874)─┬─{gssproxy}(877)
│ ├─{gssproxy}(878)
│ ├─{gssproxy}(879)
│ ├─{gssproxy}(880)
│ └─{gssproxy}(881)
├─irqbalance(835)───{irqbalance}(849)
├─polkitd(1293)─┬─{polkitd}(1299)
│ ├─{polkitd}(1302)
│ ├─{polkitd}(1303)
│ ├─{polkitd}(1304)
│ ├─{polkitd}(1305)
│ ├─{polkitd}(1306)
│ └─{polkitd}(1311)
├─postgres(855)─┬─postgres(914)
│ ├─postgres(915)
│ ├─postgres(920)
│ ├─postgres(921)
│ └─postgres(922)
├─rpcbind(803)
├─rsyslogd(946)─┬─{rsyslogd}(962)
│ └─{rsyslogd}(964)
├─sshd(871)─┬─sshd(1338)───sshd(1350)───bash(1351)───pstree(1484)
│ └─sshd(1352)───sshd(1369)───sftp-server(1382)
├─systemd(1343)───(sd-pam)(1344)
├─systemd-journal(676)
├─systemd-logind(839)
├─systemd-udevd(715)
├─tuned(867)─┬─{tuned}(1292)
│ ├─{tuned}(1307)
│ ├─{tuned}(1308)
│ └─{tuned}(1309)
└─vmtoolsd(833)─┬─{vmtoolsd}(936)
├─{vmtoolsd}(937)
└─{vmtoolsd}(950)

PSQL客户端工具

psql是PostgreSQL中的一个命令行交互式客户端工具,类似于Mysql中的mysql和Oracle中的sqlplus,PostgreSQL图形化客户端工具(pgadmin)

  • 可以使用上下键把使用过的命令或SQL语句调出来
  • 连续按两个TAB键,表示把命令补全或给出提示输入

命令格式:

1
2
3
psql -h <hostname/ip>  -p <端口> [数据库名称] -U [用户名称]

[root@Rocky8-2 ~]# psql -h 10.0.0.12 -p 5432 hellodb postgres

-h ## 指定要连接的数据库主机或IP地址,默认local socket登录(unix_socket_directories指定)

-p ## 指定连接的数据库端口

这些连接参数也可用环境变量指定,如:

1
2
3
4
export PGDATABASE=testdb
export PGHOST=10.0.0.12
export PGPOFT=5432
export PGUSER=postgres
psql帮助
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
29
postgres=# \help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit


postgres-# \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display result in crosstab
\errverbose show most recent error message at maximum verbosity
\g [(OPTIONS)] [FILE] execute query (and send result to file or |pipe);
\g with no arguments is equivalent to a semicolon
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store result in psql variables
\gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
\q quit psql
\watch [SEC] execute query every SEC seconds

Help
\? [commands] show help on backslash commands
\? options show help on psql command-line options
\? variables show help on special variables
\h [NAME] help on syntax of SQL commands, * for all commands

PostgreSQL数据库目录介绍

20230901153453

数据库数据存放在变量PGDATA指向的目录,安装数据库时需要指定一个合适的目录做为数据目录的根目录,目录的初始化使用initdb来完成。

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
[root@Rocky8-2 ~]# ll $PGDATA
total 64
drwx------ 5 postgres postgres 33 Aug 7 14:06 base
drwx------ 2 postgres postgres 4096 Aug 31 16:01 global
drwx------ 2 postgres postgres 6 Aug 7 14:06 pg_commit_ts
drwx------ 2 postgres postgres 6 Aug 7 14:06 pg_dynshmem
-rw------- 1 postgres postgres 4535 Aug 7 14:06 pg_hba.conf
-rw------- 1 postgres postgres 1636 Aug 7 14:06 pg_ident.conf
drwx------ 4 postgres postgres 68 Aug 31 16:05 pg_logical
drwx------ 4 postgres postgres 36 Aug 7 14:06 pg_multixact
drwx------ 2 postgres postgres 6 Aug 7 14:06 pg_notify
drwx------ 2 postgres postgres 6 Aug 7 14:06 pg_replslot
drwx------ 2 postgres postgres 6 Aug 7 14:06 pg_serial
drwx------ 2 postgres postgres 6 Aug 7 14:06 pg_snapshots
drwx------ 2 postgres postgres 6 Aug 31 16:00 pg_stat
drwx------ 2 postgres postgres 6 Aug 7 14:06 pg_stat_tmp
drwx------ 2 postgres postgres 18 Aug 7 14:06 pg_subtrans
drwx------ 2 postgres postgres 6 Aug 7 14:06 pg_tblspc
drwx------ 2 postgres postgres 6 Aug 7 14:06 pg_twophase
-rw------- 1 postgres postgres 3 Aug 7 14:06 PG_VERSION
drwx------ 3 postgres postgres 60 Aug 7 14:06 pg_wal
drwx------ 2 postgres postgres 18 Aug 7 14:06 pg_xact
-rw------- 1 postgres postgres 88 Aug 7 14:06 postgresql.auto.conf
-rw------- 1 postgres postgres 29412 Aug 7 14:06 postgresql.conf
-rw------- 1 postgres postgres 44 Aug 31 16:00 postmaster.opts
-rw------- 1 postgres postgres 76 Aug 31 16:00 postmaster.pid

配置文件

初始化完成后,PGDATA目录下会生成三个配置文件:

  • postgresql.conf # 数据库实例的主配置文件,基本所有的配置参数都在此文件中
  • pg_hba.conf # 认证配置文件,配置允许哪此IP主机访问数据库,认证的方法等信息
  • pg_ident.conf # 认证方式ident的用户映射文件。
postgresql.conf

postgresql.conf 优先级低于 postgresql.auto.conf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
listen_addresses=‘*’       			 #监听客户端的地址,默认是本地的localhost,需要修改为*或者0.0.0.0
port = 5432 #pg端口,默认是5432
max_connections = 2000 #最大连接数,默认100
unix_socket_directories #socket文件的位置,默认在/tmp下面
shared_buffers #数据缓存区,建议值1/4--1/2主机内存,和oracle的buffer cache类似
maintenance_work_mem #维护工作内存,用于vacuum , create index, reindex等。建议值(1/4主机内存)/autovacuum_max_workers
max_worker_processes #总worker数
max_parallel_workers_per_gather #单条QUERY中,每个node最多允许开启的并行计算wORKER数
wal_level #wa1级别,版本11+默认是replica
wal_buffers #类似oracle的1og buffer
checkpoint_timeout #checkpoint时间间隔
max_wal_size #控制wa1的最大数量
min_wal_size #控制wa1的最小数量
archive_command #开启归档命令,示例: 'test ! -f /arch/%f && cp %p /arch/%f'
autovacuum #开启自动vacuum
pg_ident.conf

用户映身配置文件,结合pg_hba.conf文件,’method‘为‘ident’可以用特定的操作系统用户以指定的数据库用户身份登录数据库

这个文件记录着与操作系统用户匹配的数据库用户,如果某操作系统用户在本文件中没有映射用户,则默认的映射数据据主用户与操作系统用户同名

pg_hba.conf

HBA:host-based authentication,基于主机的认证,实现防火墙功能,可以控制允许哪些IP的机器访问数据库服务器

pg_hba.conf文件每行由5部分组成

1
TYPE    DATABASE        USER            ADDRESS                 METHOD

数据文件

每一个索引和表都是一个单独文件,称为Segment.文件大小可以在initdb时通过选项–with-segsize=SEGSIZE指定

物理位置:

1
$PGDATA/BASE/DATABASE_OID/PG_CLASS.RELFILENODE

控制文件

控制文件存放当前数据库的状态,存放在 $PGDATA/global/pg_control 下

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
[root@Rocky8-2 ~]# pg_controldata $PGDATA
pg_control version number: 1300
Catalog version number: 202209061
Database system identifier: 7264457813843350549
Database cluster state: in production
pg_control last modified: Mon 18 Sep 2023 05:05:40 PM CST
Latest checkpoint location: 0/64ED35A8
Latest checkpoint's REDO location: 0/64ED3570
Latest checkpoint's REDO WAL file: 000000010000000000000064
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:749
Latest checkpoint's NextOID: 24580
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 717
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 749
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Mon 18 Sep 2023 05:05:39 PM CST
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce: 04dab1c27ec1349493a9e5db6327dd88cc59873f0486b64c238c1c1e47d28d69

日志文件

  • 运行日志:$PGDATA/log 默认不存在,需要开启配置项loggin_collector

  • 在线重做日志:$PGDATA/pg_wal

  • 事务提交日志:$PGDATA/pg_xact

  • 服务器日志:可以在服务启动时指定 pg_ctl start -l ./logfile

开启远程连接

默认安装完成后,postgresql只监听local,需要添加监听对外提供服务的ip地址

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
## 修改postgres用户密码
[root@Rocky8-2 ~]# psql
postgres=# alter user postgres with password '123456';

## 修改监听地址和端口
[root@Rocky8-2 ~]# vim /pgsql/data/postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
#listen_addresses = 'localhost' # what IP address(es) to listen on;

## 在pg_hba.conf中添加一行
host all all 0.0.0.0/0 md5

[root@Rocky8-2 ~]# vim /pgsql/data/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 0.0.0.0/0 md5

## 重启服务
[root@Rocky8-2 ~]# su - postgres
[postgres@Rocky8-2 ~]$ pg_ctl restart -mf
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-09-11 17:24:21.580 CST [1611] LOG: starting PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit
2023-09-11 17:24:21.581 CST [1611] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-09-11 17:24:21.581 CST [1611] LOG: listening on IPv6 address "::", port 5432
2023-09-11 17:24:21.583 CST [1611] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-09-11 17:24:21.588 CST [1614] LOG: database system was shut down at 2023-09-11 17:24:21 CST
2023-09-11 17:24:21.592 CST [1611] LOG: database system is ready to accept connections
done
server started

## 查看监听端口
[postgres@Rocky8-2 ~]$ ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 244 0.0.0.0:5432 0.0.0.0:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 244 [::]:5432 [::]:*

## 在远程机器测试连接
[root@Rocky8-1 ~]# psql -h 10.0.0.12 -p 5432 -U postgres
Password for user postgres:
psql (15.3)
Type "help" for help.

postgres=#

设置显示信息的格式

1
2
3
4
5
6
7
8
9
10
## 调整横向和纵向显示
postgres=# \x
Expanded display is on.

## 显示命令执行的时长
postgres=# timing on

## 显示详细信息,可打印出报出问题的源代码位置
postgres-# \set VERBOSITY verbose

查看和连接数据库

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
29
30
31
32
33
34
35
36
37
38
39
40
## 列出数据库,相当于Mysql的 show databases
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+---------+-------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | C | C | | libc |
template0 | postgres | UTF8 | C | C | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
testdb1 | postgres | UTF8 | C | C | | libc |
(4 rows)

## 显示数据为详细信息
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges | Size | Tablespace | Description

-----------+----------+----------+---------+-------+------------+-----------------+-----------------------+---------+------------+-------------------------------
-------------
postgres | postgres | UTF8 | C | C | | libc | | 7229 kB | pg_default | default administrative connect
ion database
template0 | postgres | UTF8 | C | C | | libc | =c/postgres +| 7073 kB | pg_default | unmodifiable empty database
| | | | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C | C | | libc | =c/postgres +| 7301 kB | pg_default | default template for new datab
ases
| | | | | | | postgres=CTc/postgres | | |
testdb1 | postgres | UTF8 | C | C | | libc | | 7145 kB | pg_default |
(4 rows)

## 查看当前连接
postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".

## 使用数据库
postgres=# \c testdb1
You are now connected to database "testdb1" as user "postgres".
testdb1=#

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
## 方法1
postgres=# create database testdb1;
CREATE DATABASE

## 方法2
[root@Rocky8-2 ~]# createdb -h 10.0.0.12 -p 5432 -U postgres testdb2
Password:

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+---------+-------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | C | C | | libc |
template0 | postgres | UTF8 | C | C | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
testdb1 | postgres | UTF8 | C | C | | libc |
testdb2 | postgres | UTF8 | C | C | | libc |
(5 rows)

删除数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
postgres=# drop database testdb2;
DROP DATABASE

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+---------+-------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | C | C | | libc |
template0 | postgres | UTF8 | C | C | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
testdb1 | postgres | UTF8 | C | C | | libc |
(4 rows)

创建表和表数据的CRUD操作

CRUD即:insert update delete select

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
postgres=# \c testdb1 
You are now connected to database "testdb1" as user "postgres".
testdb1=# create table tb2 (id serial primary key,name text);
CREATE TABLE

testdb1=# \d tb2;
Table "public.tb1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------------------------------
id | integer | | not null | nextval('tb1_id_seq'::regclass)
name | text | | |
Indexes:
"tb1_pkey" PRIMARY KEY, btree (id)

testdb1=# insert into tb2 (name) values ('wang');

testdb1=# select * from tb2;

testdb1=# update tb2 set name='zhang' where id=2;

testdb1=# delete from tb2 where id=2;

testdb1=# drop table tb1;

查看数据库用户

1
2
3
4
5
6
7
8
9
10
11
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

事务管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
## 开始事务
BEGIN
testdb1-# \h begin
Command: BEGIN
Description: start a transaction block
Syntax:
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

## 提交事务
COMMIT | END
## 取消事务 需要在关闭自动提交状态下:\set AUTOCOMMIT OFF
ROLLBACK
## 查看事务ID
select txid_current();

PostgreSQL体系架构
https://www.xcjyc.top/2023/07/22/PostgreSQL体系架构/
作者
XCJYC
发布于
2023年7月22日
许可协议