PostgreSQL

PG简介

PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。

PostgreSQL 开发者把它念作 post-gress-Q-L。

PostgreSQL 的 Slogan 是 “世界上最先进的开源关系型数据库”。

“开源界的Oracle”,去O首选。

PostgreSQL官网

https://www.postgresql.org/

PostgreSQL中文社区

http://www.postgres.cn/v2/home

全球数据库排行

https://db-engines.com/en/

国产数据库排行

https://www.modb.pro/dbRank

PG的历史

PostgreSQL最初设想于1986年，当时被叫做Berkley Postgres Project。

该项目一直到1994年都处于演进和修改中，直到开发人员Andrew Yu和Jolly Chen在Postgres中添加了一个SQL（Structured Query Language，结构化查询语言）翻译程序，该版本叫做Postgres95，在开放源代码社区发放。

开始以社区的形式运作。

1996年，再次对Postgres95做了较大的改动，并将其作为PostgresSQL6.0版发布。该版本的Postgres提高了后端的速度，包括增强型SQL92标准以及重要的后端特性（包括子选择、默认值、约束和触发器）。

2005年，发布8.0版本，开始支持windows系统环境

PostgreSQL 9.0 ：支持64位windows系统，异步流数据复制、Hot Standby；

PostgreSQL 9.1 ：支持数据同步复制，unlogged tabels、serializable snapshot isolation、FDW 外部表。

此版本后，PostgreSQL 开始得到中国多个行业用户的关注，开始有应用于电信、保险、制造业等边缘系统。

目前生产环境主流的版本是PostgreSQL 12。

2021-09-30，PostgreSQL全球开发组宣布，功能最为强大的开源数据库，PostgreSQL 14版本正式发布！

开源许可独特性

PG是纯社区的。

（Postgres遵守BSD许可证发行）却使开发者们得以获取源代码并进一步开发系统。

BSD许可协议（英语：Berkeley Software Distribution license）是自由软件中使用最广泛的许可协议之一。BSD就是遵照这个许可证来发布，也因此而得名 BSD许可协议。

众所周知，MySQL被Oracle所控制，MySQL同时使用了GPL和一种商业许可（称为双重许可）。

GPL(General Public license)是公共许可，遵循了GPL的软件是公共的。如果某软件使用了GPL软件，那么该软件也需要开源，如果不开源，就不能使用GPL软件，这和是否把该软件商用与否是没关系的。

如果无法满足GPL，就需要获得商业许可，通过与Oracle公司联系，制定解决方案，受Oracle公司约束。

同为开源软件，PostgreSQL源码使用自由友好、商业应用不受任何公司实体所控制，而MySQL则在一定程度上有所限制。

与MySQL对比

PostgreSQL相对于MySQL的优势

  • 在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨。
  • 对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强。
  • PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
  • PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
  • PostgreSQL支持JSON和其他NoSQL功能，如本机XML支持和使用HSTORE的键值对。它还支持索引JSON数据以加快访问速度，特别是10版本JSONB更是强大。
  • PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgreSQL数据库不会被其它公司控制。相反，MySQL现在主要是被Oracle公司控制。

MySQL相对于PG的优势

  • innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触 发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀。

  • MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束。

  • MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作。

  • MySQL相对于PG在国内的流行度更高，PG在国内显得就有些落寞了。

  • MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。

总结

从应用场景来说，PG更加适合严格的企业应用场景（比如金融、电信、ERP、CRM），但不仅仅限制于此，PostgreSQL的json，jsonb，hstore等数据格式，特别适用于一些大数据格式的分析；而MySQL更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场景（比如google、facebook、alibaba），当然现在MySQL的在innodb引擎的大力发展，功能表现良好。

安装PostgreSQL

下载安装

访问官网下载地址

https://www.postgresql.org/download/

![](https://lrwjz-1311278609.cos.ap-nanjing.myqcloud.com/postgre/assets/image (1).png)

可以看到第4步的安装步骤命令：

1
2
3
4
5
6
7
8
9
10
# 导入yum源
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装PostgreSQL服务
sudo yum install -y postgresql14-server
# 初始化数据库
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
# 设置为开机自启
sudo systemctl enable postgresql-14
# 启动PostgreSQL服务
sudo systemctl start postgresql-14

修改postgres账号密码

PostgreSQL安装成功之后，会默认创建一个名为postgres的Linux用户，初始化数据库后，会有名为postgres的数据库，来存储数据库的基础信息，例如用户信息等等，相当于MySQL中默认的名为mysql数据库。

postgres数据库中会初始化一名超级用户postgres

为了方便我们使用postgres账号进行管理，我们可以修改该账号的密码。

1
2
3
4
5
6
7
8
# 通过su命令切换linux用户为postgres会自动进入命令行
su postgres

# 启动SQL Shell
psql

# 修改密码
ALTER USER postgres WITH PASSWORD 'postgres';

配置远程访问

1、linux开放5432端口

1
2
sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

2、修改IP绑定

1
2
3
4
5
6
#修改配置文件
vi /var/lib/pgsql/14/data/postgresql.conf

#将监听地址修改为*
#默认listen_addresses配置是注释掉的，所以可以直接在配置文件开头加入该行
listen_addresses='*'

3、允许所有IP访问

1
2
3
4
5
#修改配置文件
vi /var/lib/pgsql/14/data/pg_hba.conf

#在问价尾部加入
host  all  all 0.0.0.0/0 md5

4、重启PostgreSQL服务

1
2
#重启PostgreSQL服务
sudo systemctl restart postgresql-14

PostgreSQL命令行

登录

1
2
3
4
5
6
7
# psql -h IP地址 -U 用户名  -d 数据库 -p 端口地址  // -U 是大写
psql -U username -d test -h 127.0.0.1 -p 5432


$ psql (连接数据库，默认用户和数据库都是postgres)
# 相当于系统用户postgres以同名数据库用户的身份，登录数据库，这是不用输入密码的。如果一切正常，系统提示符会变为"postgres=#"，表示这时已经进入了数据库控制台。

数据库操作

1
2
3
4
5
6
7
8
9
10
11
# 创建数据库
CREATE DATABASE mydb;

# 查看所有数据库
\l

# 切换当前数据库
\c mydb

# 删除数据库
drop database <dbname>

数据库表操作

数据类型

创建表格时每列都必须使用数据类型。PotgreSQL中主要有三类数据类型：

  • 数值数据类型
  • 字符串数据类型
  • 日期/时间数据类型

常见数值类型包括：

名字 存储长度 描述 范围
smallint 2 字节 小范围整数 -32768 到 +32767
integer 4 字节 常用的整数 -2147483648 到 +2147483647
bigint 8 字节 大范围整数 -9223372036854775808 到 +9223372036854775807
decimal 可变长 用户指定的精度，精确 小数点前 131072 位；小数点后 16383 位
numeric 可变长 用户指定的精度，精确 小数点前 131072 位；小数点后 16383 位
real 4 字节 可变精度，不精确 6 位十进制数字精度
double 8 字节 可变精度，不精确 15 位十进制数字精度

字符串字符串类型包括：

  • char(size)，character(size)：固定长度字符串，size 规定了需存储的字符数，由右边的空格补齐；
  • varchar(size)，character varying(size)：可变长度字符串，size 规定了需存储的字符数；
  • text：可变长度字符串。

日期/时间：

表示日期或时间的数据类型有：

  • timestamp：日期和时间；
  • date：日期，无时间；
  • time：时间；

其他数据类型类型还有布尔值 boolean （true 或 false），货币数额 money 和几何数据等。

表操作

1
2
3
4
5
6
7
8
9
10
11
# 创建表
CREATE TABLE test(id int,body varchar(100));

# 在表中插入数据
insert into test(id,body) values(1,'hello,postgresql');

# 查看当前数据库下所有表
\d

# 查看表结构，相当于desc
\d test

主键自增

PostgreSQL 使用序列来标识字段的自增长，数据类型有 smallserial、serial 和 bigserial 。这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性。

SMALLSERIAL、SERIAL 和 BIGSERIAL 范围：

伪类型 存储大小 范围
SMALLSERIAL 2字节 1 到 32,767
SERIAL 4字节 1 到 2,147,483,647
BIGSERIAL 8字节 1 到 922,337,2036,854,775,807

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 创建表
CREATE TABLE COMPANY(
   ID  SERIAL PRIMARY KEY,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

# 插入数据
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );

Schema模式

PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。

一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势：

  • 允许多个用户使用一个数据库并且不会互相干扰。

  • 将数据库对象组织成逻辑组以便更容易管理。

  • 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

模式类似于操作系统层的目录，但是模式不能嵌套。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 创建schema： 
create schema myschema;

# 创建表，并指定表所在的schema
create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

# 删除schema： 
drop schema myschema；

# 删除一个模式以及其中包含的所有对象：
DROP SCHEMA myschema CASCADE;

备份PostgreSQL

如果您在生产环境中使用PostgreSQL，请务必采取预防措施以确保用户的数据不会丢失。

备份和恢复单数据库

备份

PostgreSQL提供了pg_dump实用程序来简化备份单个数据库的过程。 必须以对要备份的数据库具有读取权限的用户身份运行此命令。

1、以postgres用户身份登录：sudo su - postgres

2、通过运行以下命令将数据库的内容转储到文件中。替换dbname为要备份的数据库的名称：

1
pg_dump dbname > dbname.bak

生成的备份文件dbname.bak可以使用scp传输到另一台主机，也可以存储在本地以供以后使用。

恢复

要演示恢复丢失的数据，请删除示例数据库并在其位置创建一个空数据库。

使用psql恢复数据库：

1
psql test < dbname.bak

备份格式有几种选择：

  • *.bak：压缩二进制格式

  • *.sql：明文转储

  • *.tar：tarball

注意：默认情况下，PostgreSQL将忽略备份过程中发生的任何错误。这可能导致备份不完整。要防止这种情况，您可以使用-1选项运行pg_dump命令。 这会将整个备份过程视为单个事务，这将在发生错误时阻止部分备份。

备份和恢复所有数据库

由于pg_dump一次只创建一个数据库的备份，因此它不会存储有关数据库角色或其他群集范围配置的信息。 要存储此信息并同时备份所有数据库，可以使用pg_dumpall。

创建备份文件：

1
pg_dumpall > pg_backup.bak

从备份还原所有数据库：

1
psql -f pg_backup.bak postgres

示例：

1
2
3
4
5
6
7
8
9
# 备份数据库
$ pg_dump -U postgres -f /tmp/postgres.sql postgres (导出postgres数据库保存为postgres.sql)
$ pg_dump -U postgres -f /tmp/postgres.sql -t test postgres (导出postgres数据库中表test的数据)
$ pg_dump -U postgres -F t -f /tmp/postgres.tar postgres (导出postgres数据库以tar形式压缩保存为postgres.tar)

# 恢复数据库
$ psql -U postgres -f /tmp/postgres.sql bk01 (恢复postgres.sql数据到bk01数据库)
# pg_restore --  从pg_dump创建的备份文件中恢复PostgreSQL数据库,用于恢复由pg_dump 转储的任何非纯文本格式中的PostgreSQL数据库。
$ pg_restore -U postgres -d bk01 /tmp/postgres.tar  (恢复postgres.tar数据到bk01数据库)

用户操作

命令行用户操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 创建用户并设置密码
CREATE USER 'username' WITH PASSWORD 'password';
CREATE USER test WITH PASSWORD 'test';

# 修改用户密码
$ ALTER USER 'username' WITH PASSWORD 'password';

# 数据库授权,赋予指定账户指定数据库所有权限
$ GRANT ALL PRIVILEGES ON DATABASE 'dbname' TO 'username';

# 将数据库 mydb 权限授权于 test
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;
# 但此时用户还是没有读写权限，需要继续授权表
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xxx;
# 注意，该sql语句必须在所要操作的数据库里执行

# 移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb from test

# 删除用户
drop user test

# 查看用户
\du

角色操作

在PostgreSQL 里没有区分用户和角色的概念，”CREATE USER” 为 “CREATE ROLE” 的别名，这两个命令几乎是完全相同的，唯一的区别是”CREATE USER” 命令创建的用户默认带有LOGIN属性，而”CREATE ROLE” 命令创建的用户默认不带LOGIN属性。

角色属性：

属性 说明
login 只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。
superuser 数据库超级用户
createdb 创建数据库权限
createrole 允许其创建或删除其他普通的用户角色(超级用户除外)
replication 做流复制的时候用到的一个用户属性，一般单独设定。
password 在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关
inherit 用户组对组员的一个继承标志，成员可以继承用户组的权限特性

创建用户时赋予角色属性

如果要在创建角色时就赋予角色一些属性，可以使用下面的方法。

首先切换到postgres 用户。

创建角色bella 并赋予其CREATEDB 的权限。

1
2
3
4
5
6
7
8
9
10
11
postgres=# CREATE ROLE bella CREATEDB ;
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB, Cannot login                        | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}

创建角色renee 并赋予其创建数据库及带有密码登录的属性。

1
2
3
4
5
6
7
8
9
10
11
12
postgres=# CREATE ROLE renee CREATEDB PASSWORD 'abc123' LOGIN;
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB, Cannot login                        | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create DB                                      | {}
 sandy     |                                                | {}

给已存在用户赋予各种权限

赋予登录权限

1
2
3
4
5
6
7
8
9
10
11
12
postgres=# ALTER ROLE bella WITH LOGIN;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create DB                                      | {}
 sandy     |                                                | {}

赋予renee 创建角色的权限

1
2
3
4
5
6
7
8
9
10
11
12
postgres=# ALTER ROLE renee WITH CREATEROLE;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}

控制台常用命令总结

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
# 设置密码
\password

# 退出
\q

# 查看SQL命令的解释，比如\h select
\h

# 查看psql命令列表
\?

# 列出所有数据库
\l

# 连接其他数据库
\c [database_name]

# 列出当前数据库的所有表格
\d

# 列出某一张表格的结构
\d [table_name]

# 列出所有用户
\du