pgsql简介

1、pg和mysql的优劣势

1、PG相对于MYSQL的优势

• PostgreSQL 的稳定性极强， Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步，然而很多 MySQL 用户都遇到过Server级的数据库丢失的场景
• PostgreSQL的各种丰富的功能和对事务的支持，可以满足实际金融业务的交易需求;
• PostgreSQL的物理复制保证数据库零丢失，相对于MySQL基于binlog的逻辑复制。可以高效快速地将数据库可以跨机房,设置跨region同步，可以满足业务的在各region本地高效读的需求

2、MySQL相对于PG的优势

• innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触 发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀。
• MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束。
• MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作。
• MySQL相对于PG在国内的流行度更高，PG在国内显得就有些落寞了。
• MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。

2、yum下载安装

下载官网https://www.postgresql.org/download/，可选版本，复制脚本进行脚本一键安装即可，rocky8自带10版本，ubuntu2004自带12版本，端口使用5432

默认安装路径在/var/lib/postgresql下

2.1、启动初始化

ubuntu安装完成后需进行数据库初始化安装，即创建相关数据库文件

#12表示版本，其他版本按需选择
#方法1
#初始化并启动
pg_crearecluster 12 main --start

#方法2
su - postgres
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/data
#启动服务-l 指定日志文件logfile
/usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start
1
2
3
4
5
6
7
8
9
10

3、源码编译安装

#环境准备
#关闭防火墙和SELINUX
#内核参数优化 vim /etc/sysctl.conf   sysctl -p
#vim /etc/security/limits.conf

#安装依赖包
#yum
gcc make readline-devel zlib-devel
#apt
apt update
gcc make libreadline-dev zlib1g-dev

#源码准备,以14.2版本为例
wget https://ftp.postgresql.org/pub/source/v14.2/postgresql-14.2.tar.gz
tar xf postgresql-14.2.tar.gz
cd postgresql-14.2

#该文件可查看具体编译安装启动步骤帮助
cat postgresql-14.2/INSTALL
#查看编译选项
pg_config --configure

#编译安装
. /configure --prefis=/apps/pgsql      #指定程序安装路径
make -j 2 world                        #编译源码的同时也编译安装相关的帮助文档及其他模块
make install-world                     #编译源码的同时也编译安装相关的帮助文档及其他模块

#必须创建一个操作系统的普通账号来用于启动服务，且在进行初始化操作时使用该账号来默认作为管理员账号
#ubuntu默认不创建家目录，可-m来创建，且-s指定shell类型，centos和ubuntu通用指令
useradd -s /bin/bash -m -d /home/postgres postgres
echo -e '123456\n123456' | passwd postgres

#初始化数据库的目录
mkdir -pv /pgsql/data/
chown postgres. /pgsql/data/

#设置环境变量，免于写路径，减少麻烦
vim /etc/profile.d/pgsql.sh
export PGHOME=/apps/pgsql            #程序安装路径
export PATH=$PGHOME/bin/:$PATH       #二进制程序路径
export PGDATA=/pgsql/data            #初始化数据库的数据存放路径
export PGUSER=postgres               #启动账号
export MANPATH=/apps/pgsql/share/man:$MANPATH    #man帮助

which initdb         #检查

#初始化
su - postgres
initdb  

#启动，指定日志文件
pg_ctl -l logfile start

#添加自启动
#方法1
#将程序自带的启动脚本拷贝到操作系统的启动目录下
cp postgresql-14.2/contrib/start-scripts/linux /etc/init.d/postgresql
chmod +x /etc/init.d/postgresql
chkconfig --add postgresql
#需更改启动脚本中的选项
vim /etc/init.d/postgresql
prefix=/apps/pgsql
PGDATA="/pgsql/data"
#将其写进rc.local      二选一   
/etc/init.d/postgresql start
su - postgresql -c "/apps/pgsql/bin/pg_ctl -l logfile start"

#方法2
#创建新的service文件
cat > /lib/systemd/system/postgresql.service <<EOF
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
User=postgres
Group=postgres

Execstart=/apps/pgsql/bin/postmaster -D /pgsql/data
ExecReload=/bin/kill -HUP

[Install]
WantedBy=multi-user.target
EOF

systemct1 daemon-reload
systemctl enable --now postgresql.service
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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

4、pg_ctl管理工具

需切换到postgresql的专属账号，可跟选项start、stop、restart、reload、status、promote、kill…

• 初始化PostgreSQL数据库实例
• 启动、终止或重启PostgreSQL数据库服务
• 查看PostgreSQL数据库服务的状态
• 让数据库实例重新读取配置文件。允许给一个指定的PostgreSQL进程发送信号
• 控制standby服务器为可读写
• 在Windows平台下允许为数据库实例注册或取消一个系统服务

4.1、创建新的数据库，多实例

chown postgres. /pgsql/
su - postgres

initdb [数据库路径]             #数据库初始化方法1
pg_ctl init -D /pgsql/data2   #数据库初始化方法2

vim /pgsql/data2/postgresql.conf
port = 5433                         #需更改端口号，避免冲突

/apps/pgsql/bin/pg_ctl -D /pgsql/data2 start
1
2
3
4
5
6
7
8
9
10

5、常用选项

\?                      #列出psql命令帮助
\h                      #列出sql命令帮助
\l                      #列出数据库列表
\l+                     #增强版的\l。显示更多，如大小，表空间
\du                     #查看所有用户  也可用\dg
\dn                     #列出所分类的列表schemas模式
\df                     #显示用户自定义的函数
\dfS                    #显示所有系统函数
\dS                     #显示系统表
\db                     #显示表空间大小
\c                      #当前所在的数据库，也可当做是mysql的use，用于切换数据库
/conninfo               #增强版的\c，可列出通过什么方式连接或连接的哪里
\d                      #列出当前数据库中的所有值，视图、索引、表
\dt[+]                  #只列出当前数据库里的表
\i                      #登录状态下调用文件，相当于source
\z                      #显示表，视图，序列的权限分配情况
\x                      #查看的格式开关，打开之后所有的查询结果以竖向显示，相当于mysql的\G
\timing on              #开启执行命令所花费的时间显示，关闭用off
\set VERBOSITY verbose  #开启错误提示
\set                    #查看系统环境，变量
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

5.1、psql

#远程连接到10.0.0.200的postgresql数据库，连接端口5432，需指定连接的数据库testdb和以哪个用户postgres连接
psql -h 10.0.0.200 -p 5432 testdb -U postgres
#默认不支持远程登录

#
psql -f /opt/test.sql      #调用文件中的sql指令来执行
1
2
3
4
5
6

6、配置postgresql

6.1、配置文件

ll /pgsql/data/     #在初始化数据库的数据存放目录下
postgresql.conf     #数据库实例的主配置文件，基本上所有的配置参数都在此文件中。等价于/etc/mysql/my.conf
pg_hba.conf         #基于主机认证配置文件，配置了允许哪些IP的主机访问数据库，认证的方法是什么等信息。
pg_ident.conf       #认证方式ident的用户映射文件，主要就是ident的认证方式，可在pg_hba.conf文件中更改
1
2
3
4

6.1.1、开启远程登录

#添加密码，登录状态下更改
[root@rocky8 ~]#psql
postgres=# ALTER USER postgres with password '123456';

#更改配置文件，更改监听端口
vim /pgsql/data/postgresql.conf
listen_addresses = '0.0.0.0'           #取消注释，更改值

vim /pgsql/data/pg_hba.conf

#重启
pg_ctl restart
psql -h 10.0.0.200 -p 5432 testdb -U postgres
1
2
3
4
5
6
7
8
9
10
11
12
13

6.2pg_hba.conf文件

6.2.1、文件格式

cat /pgsql/data/pg_hba.conf
# TYPE   DATABASE      USER       ADDRESS     METHOD
#下面的每一行就是授权连接的每一条记录

# IPv4
# IPv6
host       all          all       10.0.0.0     md5    #远程10网段可登录连接
1
2
3
4
5
6
7

• TYPE类型
  • local ：这条记录匹配通过UNIX域套接字的连接认证。没有这种类型的记录，就不允许有UNIX域套接字的连接。当psq|后面不指定主机名或IP地址时，即用UNIX域套接字的方式连接数据库。本地连接
  • host：这条记录匹配通过TCP/IP进行的连接。包括了SSL和非SS的连接。综合远程连接
  • hostssl：这条记录匹配使用TCP/IP的SSL 连接。必须是使用SSL加密的连接，且要使用这个选项，编译服务器时必须打开SSL支持，启动服务器时必须打开SSL配置选项。远程加密连接
  • hostnossl ：这条记录与hostssI相反，它只匹配那些在TCP/IP上不使用SSL的连接请求。远程不加密连接
• 第2个字段用于设置：一个数据库名称，如果设置为all，表示可以匹配任何数据库，注意：如果设置为replication时比较特殊，表示允许流复制连接，而不是允许连接到一个名为"replication"的数据库上。指定连接到哪个数据库
• 第3个字段用于设置一个用户的名称， 如果设置为all,表示可以匹配任何用户。允许哪个用户
• 第4个字段<ip/masklen>表示允许哪些IP地址来访问此服务器，如192.168.1.10/32表示只允许192.168.1.10这台主机访问数据库，192.168.1.0/24表示IP地址前缀为192.168.1.X的主机都允许访问数据库服务器。允许访问的IP地址
• 第5个字段表示验证方法，PostgreSQL支持的认证配置方式很多，最常用的认证方法是trust信任、reject拒绝、md5用户密码验证和ident操作系统账号名与数据库账号名需相同才可登录，服务器本机默认不用密码登录，远程需要密码。

6.2.2、远程连接免密登录

#利用.pgpass文件
vim .pgpass
10.0.0.200:5432:db1:postgres:123456
#连接主机ip：端口号：所需连接的数据库：用户名：密码，，，，中间用：隔开
chmod 600 .pgpass

#再用户postgres远程连接10.0.0.200上的数据库db1时可不用再敲密码
1
2
3
4
5
6
7

6.3、数据的组织结构

可以分为五层：

实例：一个postsql对应安装的数据目录$PGDATA,即一个instance实例

数据库：一个postgresql数据库服务下可以管理多个数据库，当应用连接到一个数据库时，一般只能访问这个数据库中的数据

模式：一个数据库可以创建多个不同的名称空间Schema,用于分隔不同的业务数据

表和索引：一个数据库可以有多个表和索引，在postgressql中的术语为Relation,在其他数据库叫Table

行和列：每张表有很多列和行数据，在postgresql的术语Tuple,在其他数据库叫Row数据库管理

6.4、创建数据库

createdb    #相当于create database

#在主机200上创建数据库db2，需输入密码不用分号结尾
createdb -h 10.0.0.200 -U postgres db2    
1
2
3
4

6.5、删除数据库

drop database db2;
1

6.6、数据库文件路径

#列出全部的表
select * from pg_tables;

#路经在/var/lib/***/data下的base里，版本不同，***路径不同，图片为14版本
#查看存放目录下文件oid编号所对应的数据库
[postgres@centos8 ~]#psql
select oid,datname from pg_database;
#自带的pg_database表存放的是所有数据库的源数据信息
#oid是临时性的，只在生命周期内有效，例如表删除后该oid可给新创建的表来使用

#查看当前数据库中的所有表的信息
select * from pg_stat_all_tables;
select * from pg_stat_all_tables where relname='tb1';  #查看表tb1的信息

#可直接查看表tb1的文件路径
select * from pg_relation_filepath('tb1');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

6.7、创建schema模式分类

同一个表名或其他例如函数、数据类型等可以在不同的模式中使用而不会冲突，例如schema1和schema2可以都存在一个名为test的表

默认在public中

#创建名为wei的schema模式分类
create schema wei;

#列出所有schema模式分类
\dn

#新建表归类到wei类别中
create table wei.students(id int);
1
2
3
4
5
6
7
8

7、管理表

#serial自动增长，primary key主键，text文本形式
create table tb1 (id serial primary key,name text);

#在表tb1中的name列上添加9条随机字符，函数generate_series可生成数字序列2到10，select (md5(random()::text))生成随机数并进行md5运算然后识别成文本形式
insert into tb1 (name) select (md5(random()::text)) from generate_series (2,10);

#创建表tb2，用的是tb1的结构，不带tb1的数据
create table tb2 ( like tb1 );

#查看表结构
\d tb1

#删除表
drop table tb2;

postgres=# create table tb1 (id serial primary key,name text);
CREATE TABLE
postgres=# insert into tb1 (name) select (md5(random()::text)) from generate_series (2,10);
INSERT 0 9
postgres=# select * from tb1;
 id |               name               
----+----------------------------------
  1 | 1c07360f404f9fce29fa5ebe9ef4f7f0
  2 | 99cd31c83719d5bd046e91232155d2fe
  3 | de0bd26b70e2a54708dee02522479aab
  4 | af5178d1653fde1fe5f858477c1cf51c
  5 | 37e37fd08037c7dbadb9c22666e9a9a0
  6 | 7667fe3282810fac7de9fb76c036dc0d
  7 | 04a629789db088783b860e0568f7545d
  8 | 8eddab7438d2e039d3ef07f42518e821
  9 | 3a6e275e59952a6a150a9fed1483b06c
(9 rows)
postgres=# \d tb1
                            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)
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

7.1、函数

内置函数官方文档

#查看所有函数
\dfS+

#函数generate_series可生成数字序列
select generate_series (2,10);

#当前时间
select clock_timestamp();

#查看客户端哪个远程主机，哪个端口
inet_client_addr(),inet_client_port();
#服务器端
inet_server_addr(),inet_server_port();
1
2
3
4
5
6
7
8
9
10
11
12
13

7.2、增删改查

7.2.1、增加insert

into选项不可省略
1

7.2.2、删除delete

留意要加from选项
truncate tb1;      #清空tb1表 
1
2

7.3、索引

#在表tb1的id列上创建索引
create index idx_tb1_id on tb1(id);

#可查看索引信息
\d tb1

#查询是否使用索引explain
explain analyze select * from tb1 where id = 500;

#临时关闭索引
set enable_indexscan=off;
#关闭位图索引
set enable_bitmapscan=off;

#删除索引，不用指表名，因此留意索引名唯一
drop index idx_tb1_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16