简介
PostgreSQL 可以说是目前功能最强大、特性最丰富和结构最复杂的开源数据库管理系统,其中有些特性甚至连商业数据库都不具备。这个起源于加州大学伯克利分校的数据库,现已成为一项国际开发项目,并且拥有广泛的用户群,尤其是在海外,目前国内使用者也越来越多。
PostgreSQL 基本上算是见证了整个数据库理论和技术的发展历程,由 UCB 计算机教授 Michael Stonebraker 于 1986 年创建。在此之前,Stonebraker 教授主导了关系数据库 Ingres 研究项目,88 年,提出了 Postgres 的第一个原型设计。
MySQL 号称是使用最广泛的开源数据库,而 PG 则被称为功能最强大的开源数据库。
安装与配置
安装
以安装 PostgreSQL 9.6 为例做为演示。
有2个方向,一是手工安装,打开 PostgreSQL Database Server 9.6 PGDG ,将4个包都下载下来
1 | postgresql96-9.6.15-1PGDG.rhel7.x86_64.rpm #PG客户端工具 |
下载完成之后,直接使用yum localinstall postgresql96-*
进行安装,安装工具大部分是放置在 /usr/pgsql-9.6/
目录下。
二是yum源安装,运行以下命令即可。
1 | yum install -y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm |
使用这2个方法安装,都会自动建一个名为postgres的系统账号,用于执行PostgreSQL;同时数据库中也会生成一个名为postgres的数据库用户,且密码已自动生成,需要进入数据库后修改。
数据库初始化设置
默认情况下,数据库是保存在 /var/lib/pgsql/9.6/data/
目录下,我们可以修改 /usr/lib/systemd/system/postgresql-9.6.service
这里面的配置:
1 | mkdir -p /data/PostgreSQL/data |
数据库初始化完成之后,就可以看到/data/PostgreSQL/data下载生成了很多文件以及配置文件,如 postgresql.conf
配置文件控制pg数据库, pg_hba.conf
是控制允许哪些用户可以登陆数据库。
配置使用
- 修改密码
PostgreSQL在数据库用户同名的系统账号下登录是免密码的,所以先切到 postgres
,然后使用 psql -U postgres
进行登陆。之后就可以修改密码了。
1 | [root@localhost ~]# su - postgres |
- 默认情况下,只允许本机才能访问pg数据库,修改为允许所有的主机都可以访问。
1 | sed -i "s|#listen_addresses = 'localhost'|listen_addresses = '*'|g" /data/PostgreSQL/data/postgresql.conf |
- pg_hba.conf是控制哪些IP可以登陆数据库的,在IPv4处,新增一条记录,允许192.168.137.0/24这个段可以登陆数据库
1 | # TYPE DATABASE USER ADDRESS METHOD |
这样设置好了之后,就可以很愉快地登陆服务器了。
1 | [root@remote src]# psql -h 192.168.137.66 -p 5432 -U postgres -W |
PG客户端认证
当客户端与数据库服务器连接时,它需要指定用哪个数据库用户的身份来连接。 PostgreSQL为我们提供了很多种客户端认证的方式,我们可以根据自己的需要来选择认证方式。
psql
psql 是 PostgreSQL 的客户端程序,要连接 PostgreSQL 数据库,我们需要指定以下内容:
-d or --dbname 数据库名
- 默认情况下是连接与当前操作系统用户名字相同的数据库。
- 如果该数据库不存在,会报 psql: FATAL: database “root” does not exist。
-h or --host 主机名
- 默认情况下 psql 会通过 Unix socket 连接数据库。
- 如果没有 Unix socket,那么会以 TCP/IP 连接到 localhost。
- 如果需要通过 TCP/IP 连接到数据库,那么就需要指定主机名。
-p or --port 端口号
:默认情况下是 5432 端口。-U or --username 用户名
:默认情况下是用当前操作系统用户名去连接数据库。如果该用户不存在,会报 psql: FATAL: role “root” does not exist。
我们也可以用 URI 的方式连接数据库:psql postgresql://dbmaster:5433/mydb?sslmode=require
pg_hba.conf
pg_hba.conf
是 PostgreSQL 客户端认证的配置文件 (hba 是 host-based authentication 的缩写),它位于 PostgreSQL 的配置目录下。
配置文件的格式如下:
1 | # cat pg_hba.conf |
从内容可以看出,pg_hba.conf
是以行为单位来配置的,第一行的TYPE表示连接类型,第二行表示database,第三行表示USER,第四行是IP地址,第五行表示认证的方法。
- TYPE连接类型,表示允许用哪些方式连接数据库,它允许以下几个值:
local
通过 Unix socket 的方式连接。host
通过 TCP/IP 的方式连接,它能匹配 SSL 和 non-SSL 连接。hostssl
只允许 SSL 连接。hostnossl
只允许 non-SSL 连接。
DATABASE
可连接的数据库,它有以下几个特殊值:all
匹配所有数据库。sameuser
可连接和用户名相同的数据库。samerole
可连接和角色名相同的数据库。replication
允许复制连接,用于集群环境下的数据库同步。 除了上面这些特殊值之外,我们可以写特定的数据库,可以用逗号 (,) 来分割多个数据库。
USER
可连接数据库的用户,值有三种写法:all
匹配所有用户。- 特定数据库用户名。
- 特定数据库用户组,需要在前面加上
+
(如:+admin
)。
ADDRESS
可连接数据库的地址,有以下几种形式:all
匹配所有 IP 地址。samehost
匹配该服务器的 IP 地址。samenet
匹配该服务器子网下的 IP 地址。- ipaddress/netmask (如:172.20.143.32/32),支持 IPv4 与 IPv6。
- 如果上面几种形式都匹配不上,就会被当成是 hostname。 注意: 只有 host, hostssl, hostnossl 会应用个字段。
METHOD
连接数据库时的认证方式,常见的有几个特殊值:trust
无条件通过认证。reject
无条件拒绝认证。md5
用 md5 加密密码进行认证。password
用明文密码进行认证,不建议在不信任的网络中使用。ident
从一个 ident 服务器 (RFC1413) 获得客户端的操作系统用户名并且用它作为被允许的数据库用户名来认证,只能用在 TCP/IP 的类型中 (即 host, hostssl, hostnossl)。peer
从内核获得客户端的操作系统用户名并把它用作被允许的数据库用户名来认证,只能用于本地连接 (即 local)。- 其他特殊值可以在 官方文档 中查阅。 简单来说,ident 和 peer 都要求客户端操作系统中存在对应的用户。 注意: 上面列举的只有 md5 和 password 是需要密码的,其他方式都不需要输入密码认证。
PostgreSQL数据库默认是使用unix套接字登陆的,加上-h就成了host登陆。同时要让用户通过 peer 和 ident 认证,我们需要在操作系统中创建对应的用户。
实例:
1 | local all all peer |
登陆方法
如果在本机登陆的话,有开启unix socket,可以直接 psql -U postgres
;如果是需要远程登陆,可以使用 psql -h 192.168.137.66 -p 5432 -U postgres
输入密码来登陆。而如果想不输入密码,pg也提供了一个方式,用touch一个文件,~/.pgpass
,具体操作如下:
1 | touch ~/.pgpass |
这个文件记录着连接数据库需要的所有信息,按下面数据格式,每行一条记录信息,格式如上,该文件中可以有注释内容,注释符号是 #
。前四个字段可以是确定的字面值,也可以使用通配符 *
匹配所有。连接数据库的时候,系统自动从前到后遍历该文件,使用最先匹配到的记录,因此,当你在文件中使用了通配符 * 的时候,应该优先把最具体的信息放在文件的最前面。
PG数据库操作指南
常用命令
1 | \password 设置密码。 |
基础操作
创建数据库与表
1 | create databse test; |
插入表数据
1 | insert into films values ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes'); |
创建索引
1 | test=# create unique index title_index on films (title); |
查看相关的命令
1 | ----- 查看数据库 |
sequece序列对象
序列对象,也被称为序列生成器,实际上就是用 CREATE SEQUENCE
创建的特殊的单行表,通常用来表生成唯一的标识符。可以理解为mysql的自增长字段一样,用法如下:
1 | ----- 直接在建表时使用serial类型,默认生成为tblname+colname+'seq',如下的名字为 t2_id_seq |
权限控制
角色与用户的区别
角色就相当于岗位:角色可以是经理,助理。用户就是具体的人:比如陈XX经理,朱XX助理,王XX助理。
在PostgreSQL 里没有区分用户和角色的概念,”CREATE USER” 为 “CREATE ROLE” 的别名,这两个命令几乎是完全相同的,唯一的区别是”CREATE USER” 命令创建的用户默认带有LOGIN属性,而”CREATE ROLE” 命令创建的用户默认不带LOGIN属性。
创建角色使用 create role
命令,创建用户使用 create user
命令,可以使用 \h create role
来查看具体的选项。具体有以下的属性。
属性 | 说明 |
---|---|
login | 只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。 |
superuser | 数据库超级用户 |
createdb | 创建数据库权限 |
createrole | 允许其创建或删除其他普通的用户角色(超级用户除外) |
replication | 做流复制的时候用到的一个用户属性,一般单独设定。 |
password | 在登录时要求指定密码时才会起作用,比如md5或者password模式,跟客户端的连接认证方式有关 |
inherit | 用户组对组员的一个继承标志,成员可以继承用户组的权限特性 |
说了这么多,简单一点,直接一条命令解决:create user test_user with password '123456';
schema模式的用法
schema有点像命名空间的作用,其作用主要是在同一个数据库下,允许出现在不同schema下,可以重复表、函数等,但在同一个schema下不能有重复的对象名字。使用schema的作用如下:
- 方便管理多个用户共享一个数据库,但是又可以互相独立.
- 方便管理众多对象,更有逻辑性
- 方便兼容某些第三方应用程序,创建对象时是有schema的
在数据库创建的时候,默认的schema是 public 模式,在此数据库中创建的对象,如表、函数、试图、索引、序列等都保存在这个模式中。
创建schema
创建的方法如下:
1 | ----- 查看schema,默认就有public |
实例应用,以下创建了2个t1表,属于2个不同的schema,一个是s01,另一个是默认的public:
1 | postgres=# create table s01.t1(id int); |
删除schema
使用 drop schema
来删除,如果里面还有对象的话,不能直接删除,会报错,可以加上 cascade
关键词,这会把属于这个schema里面的东西全部删除掉。相当于linux下面的rm -rf一样的效果。
1 | postgres=# select * from pg_tables where tablename = 't1'; |
创建schema指定owner
用户登陆的数据库之后,默认是谁创建的schema,owner就是谁,这边owner就是用户名的意思,在创建schema时是可以指定的。
1 | ----- 指定s01模式的所有者为fdm这个用户,但由于没有创建,所以会失败。 |
指定了owner,不指定schema,则schema名字与owner一致。这是什么意思呢?以上面第一条记录为例,schema的name为fdm,其owner是fdm,那意思是说创建fdm.t1表格时,这张表的owner就属于fdm这个用户,有点类似默认权限的意思。
具体实例如下,
1 | postgres=> select * from pg_tables where tablename = 't1'; |
在上面的那个例子中,我们没有指定schema,fdm用户的schema为fdm,所以创建后数据库的schema就是fdm。
设置schema搜索路径
所谓schema搜索路径,指的是schema的优先级,类似linux的PATH这个环境变量的意思,默认为 "$user", public
,当user存在schema时,就先以这个优先,如果不存在,则默认为public这个模式。越靠前,其优先级最高。
1 | ----- 当前登陆用户为fdm,所以$user的值为fdm |
schema与权限
schema跟权限是息息相关的,只有login权限的用户,对于owner不是自己的schema是没有任何权限的。先看以下实例,先重新登陆pg,然后运行以下命令:
1 | ----- 先用超管账号进行登陆 |
由上,可以得知使用s02这个模式的owner是postgres,所以fdm这个用户是没有办法查看s02.t1这个表格的。
我们可以通过授权的方式来让fdm这个用户有权限去查看s02.t1这个表格:
1 | ----- 将s02授权给U02 -----== |
总结
postgresql在权限控制方面比mysql严格多了,初学者很容易在这边打圈,尤其是之前有接触过Mysql的同学。在默认情况下,所有新建的数据库、表都属于public这个schema,同时还需要关注schema所属的owner。
- 在创建数据库时,默认就会自动创建public这个schema;所以每个数据库都默认就会有public的schema。
- 每个数据库都可以创建相同的schema;即可以在postgres这个数据库创建wumingx这个schema,也可以在database_name这个数据库上面创建名为wumingx的schema,所以创建schema就必须先切换对应的数据库上。
- 默认情况下,创建表默认的schema是public,可以使用search_path来修改默认的schema
- 用户名是全局唯一的,不能同名;但schema是可以同名的。
1 | CREATE USER dbuser WITH PASSWORD 'user_password'; |
相比mysql来说,复杂了很多。
备份与恢复
数据库的备份有多种分类方式。按照备份后的文件类型,可以分为物理备份(文件系统级别的备份)和逻辑备份(备份后的文件是sql文件或特定格式的导出文件);按照备份过程中是否停止数据库服务,可分为冷备份(备份过程中停止数据库服务)和热备份(备份过程中数据库服务开启并可供用户访问);按照备份是否是完整的数据库,可分为全量备份(备份是完整的数据库)和增量备份(备份是上一次全量备份后数据库改变的内容)。
文件系统级别的冷备份
这种备份方式需要关闭数据库,然后拷贝数据文件的完整目录。恢复数据库时,只需将数据目录复制到原来的位置。该方式实际工作中很少使用。
pg_dump备份
这种方式可以在数据库正在使用的时候进行完整一致的备份,并不阻塞其它用户对数据库的访问。它会产生一个脚本文件,里面包含备份开始时,已创建的各种数据库对象的SQL语句和每个表中的数据。可以使用数据库提供的工具pg_dumpall和pg_dump来进行备份。pg_dump只备份数据库集群中的某个数据库的数据,它不会导出角色和表空间相关的信息,因为这些信息是整个数据库集群共用的,不属于某个单独的数据库。pg_dumpall,对集簇中的每个数据库调用pg_dump来完成该工作,还会还转储对所有数据库公用的全局对象(pg_dump不保存这些对象)。 目前这包括适数据库用户和组、表空间以及适合所有数据库的访问权限等属性。
pg_dump的选项如下 :
1 | -f, --file=FILENAME 输出文件或目录名 |
控制输出内容选项:
1 | -a, --data-only 只转储数据,不包括模式 |
联接选项:
1 | -d, --dbname=DBNAME 对数据库 DBNAME备份 |
实例如下:
1 | [root@localhost ~]# pg_dump -h 192.168.137.66 -U postgres -C -c -d postgres -f postgres.sql |
连续归档
这种方式的策略是把一个文件系统级别的全量备份和WAL(预写式日志)级别的增量备份结合起来。当需要恢复时,我们先恢复文件系统级别的备份,然后重放备份的WAL文件,把系统恢复到之前的某个状态。这种备份有显著的优点:
- 不需要一个完美的一致的文件系统备份作为开始点。备份中的任何内部不一致性将通过日志重放来修正。
- 可以结合一个无穷长的WAL文件序列用于重放,可以通过简单地归档WAL文件来达到连续备份。
- 不需要重放WAL项一直到最后。可以在任何点停止重放,并使数据库恢复到当时的一致状态。
- 可以连续地将一系列WAL文件输送给另一台已经载入了相同基础备份文件的机器,得到一个实时的热备份系统。
创建测试数据
先创建表,插入100条数据,并查看数据库的大小:
1 | postgres=# create table foo(id integer); |
修改配置
修改postgresql.conf,将这三项的配置修改如下:
1 | wal_level = replica # minimal, replica, or logical |
其中archive_command中 %p
表示将要被归档的WAL文件的完整路径,本例为路径 /data/PostgreSQL/data/pg_xlog/
,用 %f
代表要被归档的日志文件的文件名,都不需要管,pg会自动识别填充。修改完成之后,由于备份的目录权限需要设置为数据库启动的用户权限,所以需要再运行以下命令:
1 | mkdir -p /data/PostgreSQL/archive/ |
这个archive_command在什么时候执行呢,即PostgreSQL在每次WAL日志16MB段满的时候才执行以及archive_timeout。可以使用以下命令查看:
1 | postgres=# show archive_timeout; |
修改wal_level和archive_mode参数都需要重新启动数据库才可以生效,修改archive_command不需要重启,只需要reload即可
创建用户
使用专属的用户进行备份数据。
1 | postgres=# CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '123456'; |
再修改以下配置:
1 | [root@localhost data]# grep max_wal_senders postgresql.conf |
建立基础备份
pg_basebackup工具是对数据库实例级进行的物理备份,可以进行打包,也可以进行。默认在安装好PG之后,运行pg_basebackup建立基础备份,-Ft
为打包为tar,-z -Z5
指定压缩等级,-Pv
显示打包的过程,-Xf
是指 --xlog-method=fetch|stream
,在备份末尾收集预写日志文件。
1 | [root@localhost PostgreSQL]# pg_basebackup -Ft -Pv -Xf -z -Z5 -U repuser -D /data/PostgreSQL/base/ |
手工归档备份
由于WAL文件是写满16MB才会进行归档,测试阶段可能写入会非常少,可以在执行完基础备份之后,手动进行一次WAL切换。在PG9.6的版本上面使用pg_switch_xlog()
来切换,而PG 10版本则是使用pg_switch_wal()
来切换。
1 | postgres=# select pg_switch_xlog(); |
或者通过设置archive_timeout参数,在达到timeout阈值时强行切换到新的WAL段。
这样 /data/PostgreSQL/archive/
就能看到备份的数据了
恢复数据
为了演示方便,可以先手工建立一个还原点,如下操作:
1 | postgres=# select pg_create_restore_point('201911052224'); |
还原点建立好了之后,直接删除foo文件。然后再进行恢复数据:
1 | systemctl stop postgresql-9.6.service |
然后在data/recovery.conf处修改配置如下:
1 | restore_command = 'cp /data/PostgreSQL/archive/%f %p' # e.g. 'cp /mnt/server/archivedir/%f %p' |
最后再重启一下pg即可。
1 | [root@localhost pg_log]# systemctl restart postgresql-9.6.service |
重启完成之后,recovery.conf会自动变成recovery.done,同时在pg_log目录下面可以查到看具体的日志。
1 | < 2019-11-05 22:29:44.298 CST > LOG: database system was interrupted; last known up at 2019-11-05 22:03:28 CST |
这时登陆数据库查看,就可以看到删除的数据就会回来了。
恢复具体时间点的数据
以上为了演示方便,使用了还原点的方法,但我们还是可以使用还原到指定时间:
1 | postgres=# select current_timestamp; |
相应的,recovery.conf需要做一定的修改。
1 | $ vim /data/app_pg/recovery.conf |
配置流复制
所谓流复制,就是从库通过tcp流从主库中同步相应的数据。postgres的主从主称之为primary,从称为stand_by。实现的原理跟上面说的连续归档是一样的。
主服务配置
主服务器的IP为192.168.137.66,安装和配置方法参考第一节。主要是配置有点不一样,具体如下:
- postgresql.conf配置如下:
1 | listen_addresses = '*' |
- 修改pg_hba.conf,增加replica用户,进行同步。
1 | local all all trust |
- 新增repuser用户
1 | postgres=# CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '123456'; |
从服务配置
从服务器的IP为192.168.137.55,只需要安装好postgresql,不需要初始化Initdb。注意这个区别,学过mysql的同学对比很难理解。
- 从主节点拷贝数据到从节点,如果之前有启动了postgresql,那需要停止服务,然后把data目录删除掉。
1 | cd /data/PostgreSQL/ |
- 配置recovery.conf,从
cp /usr/pgsql-9.6/share/recovery.conf.sample data/recovery.conf
之后,修改配置如下:
1 | recovery_target_timeline = 'latest' |
- postgresql.conf也需要修改一些配置:
1 | max_connections = 1000 # 一般查多于写的应用从库的最大连接数要比较大 |
这样就可以启动pg服务了。
验证是否成功
查看进程,主库所在的机器中会看到sender进程:
1 | [root@localhost ~]# ps aux |grep wal |grep -v grep |
从库所在的机器中会看到receiver进程:
1 | [root@remote data]# ps aux |grep wal |grep -v grep |
查看复制状态
在主服务器上面执行:
1 | postgres=# select client_addr,sync_state from pg_stat_replication; |
sync_state有三个值,async: 异步、sync: 同步、potential: 虽然现在是异步模式,但是有可能升级到同步模式。PG在默认情况下是使用异步模式,即主库上提交事务时不需要等待备库接收WAL日志流并写入到备库WAL日志文件时便返回成功,因此异步流复制的TPS会相对同步流复制要高,延迟更低。
实测效果
还是以以前的案列来验证,在主库上面创建一张表,增加数据:
1 | postgres=# create table foo(id integer); |
然后登陆从库,就可以看到foo也有数据了。
主备切换方法
主备互切
使用 pg_ctl promote
方法来做切换。
先停止主库
systemctl stop postgresql
在备库上执行pg_ctl promote命令激活备库,如果recovery.conf变成recovery.done表示备库已切换成主库
1
2
3
4
5
6
7
8
9
10
11
12
13# pg_ctl必须使用postgres权限运行
[root@remote data]# /usr/pgsql-9.6/bin/pg_ctl promote
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
[root@remote data]# su - postgres
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl promote
pg_ctl: directory "/var/lib/pgsql/9.6/data" is not a database cluster directory
# 需要人工指定数据库目录
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl promote -D /data/PostgreSQL/data
server promoting
-bash-4.2$ ls /data/PostgreSQL/data/recovery.done
/data/PostgreSQL/data/recovery.done命令执行后,如果原来的 recovery.conf 更名为 recovery.done, 表示切换成功。
这时需要将老的主库切换成备库,同样的方法,建立recovery.conf文件,配置如下:
1
2
3recovery_target_timeline = 'latest'
standby_mode = on # 说明该节点是从服务器
primary_conninfo = 'host=192.168.137.55 port=5432 user=repuser password=123456' # 主服务器的信息以及连接的用户可以看到,并没有什么区别,只是需要将host修改一下即可。
修改max_connections值,因为从库的值一定要大于主备的值,如果不修改,在启动时就会报错,可以查看
data/pg_log
目录下面的日志:1
2
3
4< 2019-11-06 22:10:09.722 CST > FATAL: hot standby is not possible because max_connections = 100 is a lower setting than on the master server (its value was 1000)
< 2019-11-06 22:10:09.723 CST > LOG: startup process (PID 90620) exited with exit code 1
< 2019-11-06 22:10:09.723 CST > LOG: aborting startup due to startup process failure
< 2019-11-06 22:10:09.725 CST > LOG: database system is shut down
备切换成主
在从库生成一个触发文件,就是在配置从库中recovery.conf中配置的trigger_file,就可以实现
1 | touch /data/PostgreSQL/data/postgresql.trigger |
此时日志会提示找到触发文件,自动切换为新的主库
1 | LOG: trigger file found: /var/lib/pgsql/9.3/data/postgresql.trigger.5432 |
以上是我初学postgresql的笔记,可以看到,同为关系型数据库,跟mysql相差还是很大,相对而言,在用户权限方面,比mysql更复杂一些。以上部署是通过rpm包的方法部署的,有兴趣的同学可以使用编译来安装,会更加折腾一些。另外,PG数据库不允许root权限,所以在报错时,请确认用户属主是否是postgres,切记!!!
附1:流复制脚本,注意使用流复制的用户名。
1 |
|
备份脚本:
1 |
|