简介

PostgreSQL 可以说是目前功能最强大、特性最丰富和结构最复杂的开源数据库管理系统，其中有些特性甚至连商业数据库都不具备。这个起源于加州大学伯克利分校的数据库，现已成为一项国际开发项目，并且拥有广泛的用户群，尤其是在海外，目前国内使用者也越来越多。

PostgreSQL 基本上算是见证了整个数据库理论和技术的发展历程，由 UCB 计算机教授 Michael Stonebraker 于 1986 年创建。在此之前，Stonebraker 教授主导了关系数据库 Ingres 研究项目，88 年，提出了 Postgres 的第一个原型设计。

MySQL 号称是使用最广泛的开源数据库，而 PG 则被称为功能最强大的开源数据库。

安装与配置

安装

以安装 PostgreSQL 9.6 为例做为演示。

有2个方向，一是手工安装，打开 PostgreSQL Database Server 9.6 PGDG ，将4个包都下载下来

postgresql96-9.6.15-1PGDG.rhel7.x86_64.rpm			#PG客户端工具
postgresql96-contrib-9.6.15-1PGDG.rhel7.x86_64.rpm	#PG库文件
postgresql96-libs-9.6.15-1PGDG.rhel7.x86_64.rpm		#PG运行库
postgresql96-server-9.6.15-1PGDG.rhel7.x86_64.rpm	#PG服务器程序

下载完成之后，直接使用yum localinstall postgresql96-*进行安装，安装工具大部分是放置在 /usr/pgsql-9.6/ 目录下。

二是yum源安装，运行以下命令即可。

1 2	yum install -y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum install -y postgresql96

使用这2个方法安装，都会自动建一个名为postgres的系统账号，用于执行PostgreSQL；同时数据库中也会生成一个名为postgres的数据库用户，且密码已自动生成，需要进入数据库后修改。

数据库初始化设置

默认情况下，数据库是保存在 /var/lib/pgsql/9.6/data/ 目录下，我们可以修改 /usr/lib/systemd/system/postgresql-9.6.service这里面的配置：

mkdir -p /data/PostgreSQL/data

#数据库目录属主必须是postgres，以及权限是700
chown postgres.postgres /data/PostgreSQL/data
chmod 700 /data/PostgreSQL/data

#更改启动脚本中的数据目录
sed -i 's:^Environment=PGDATA.*:Environment=PGDATA=/data/PostgreSQL/data:' /usr/lib/systemd/system/postgresql-9.6.service

#进行数据库初始化，完成之后，就可以看到/data/PostgreSQL/data下载生成了很多文件以及配置文件
/usr/pgsql-9.6/bin/postgresql96-setup initdb

#启动服务
systemctl daemon-reload
systemctl enable postgresql-9.6.service
systemctl start postgresql-9.6.service

#加入环境变量
echo "export PATH=/usr/pgsql-9.6/bin:$PATH" >/etc/profile.d/postgresql.sh

数据库初始化完成之后，就可以看到/data/PostgreSQL/data下载生成了很多文件以及配置文件，如 postgresql.conf 配置文件控制pg数据库， pg_hba.conf 是控制允许哪些用户可以登陆数据库。

配置使用

修改密码

PostgreSQL在数据库用户同名的系统账号下登录是免密码的，所以先切到 postgres ，然后使用 psql -U postgres 进行登陆。之后就可以修改密码了。

[root@localhost ~]# su - postgres
Last login: Mon Oct 21 03:11:40 EDT 2019 on pts/2
-bash-4.2$ psql -U postgres
psql (9.6.15)
Type "help" for help.

postgres=# alter user postgres with password '123456';
postgres=# q
-bash-4.2$

默认情况下，只允许本机才能访问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这个段可以登陆数据库

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
host    all             all           	192.168.137.0/24        md5
# IPv6 local connections:
host    all             all             ::1/128                 ident

这样设置好了之后，就可以很愉快地登陆服务器了。

[root@remote src]# psql -h 192.168.137.66 -p 5432 -U postgres -W
Password for user postgres:
psql (9.2.24, server 9.6.15)
WARNING: psql version 9.2, server version 9.6.
         Some psql features might not work.
Type "help" for help.

postgres=#

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 的配置目录下。

配置文件的格式如下：

# cat pg_hba.conf
...
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            ident
host    all             all             ::1/128                 ident

从内容可以看出，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 认证，我们需要在操作系统中创建对应的用户。

实例：

local   all             all                                     peer
# 表示本机上的所有用户可以以 Unix socket 的方式连接数据库。

host    all             all             127.0.0.1/32            ident
# 同上，但只允许 127.0.0.1/32 (IPv4) 这个 IP 连接数据库。

host    all             all             ::1/128                 ident
# 同上，但只允许 ::1/128 (IPv6) 这个 IP 连接数据库。

登陆方法

如果在本机登陆的话，有开启unix socket，可以直接 psql -U postgres ；如果是需要远程登陆，可以使用 psql -h 192.168.137.66 -p 5432 -U postgres 输入密码来登陆。而如果想不输入密码，pg也提供了一个方式，用touch一个文件，~/.pgpass，具体操作如下：

touch ~/.pgpass
chmod 0600 ~/.pgpass
##输入以下内容到 cat ~/.pgpass
# hostname:port:database:username:password
192.168.137.66:5432:*:postgres:123456

这个文件记录着连接数据库需要的所有信息，按下面数据格式，每行一条记录信息，格式如上，该文件中可以有注释内容，注释符号是 # 。前四个字段可以是确定的字面值，也可以使用通配符 * 匹配所有。连接数据库的时候，系统自动从前到后遍历该文件，使用最先匹配到的记录，因此，当你在文件中使用了通配符 * 的时候，应该优先把最具体的信息放在文件的最前面。

PG数据库操作指南

常用命令

password           设置密码。
q                  退出。
h                  查看SQL命令的解释，比如h select。
?                  查看psql命令列表。
l                  列出所有数据库。
c database username  以username去连接数据库，数据库可以用-代替，表示不切换数据库；username也可以省略，表示不切换用户。
d                  列出当前数据库的所有表格。
d [table_name]     列出某一张表格的结构。
du                 列出所有用户。
dn					查看schema模式名称
dt					查看schema模式关系
e                  打开文本编辑器。
conninfo           列出当前数据库和连接的信息。

基础操作

创建数据库与表

create databse test;
# 切换数据库
c test;

CREATE TABLE films (
     code        char(5) CONSTRAINT firstkey PRIMARY KEY,
     title       varchar(40) NOT NULL,
     did         integer NOT NULL,
     date_prod   date,
     kind        varchar(10),
     len         interval hour to minute
 );

插入表数据

1	insert into films values ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

创建索引

1 2	test=# create unique index title_index on films (title); CREATE INDEX

查看相关的命令

-----  查看数据库
test-# l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)


-----  列出数据库中所有的表
test=# dt
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | films | table | postgres
(1 row)

-----  查看表结构
test=# d films
              Table "public.films"
  Column   |          Type           | Modifiers
-----------+-------------------------+-----------
 code      | character(5)            | not null
 title     | character varying(40)   | not null
 did       | integer                 | not null
 date_prod | date                    |
 kind      | character varying(10)   |
 len       | interval hour to minute |
Indexes:
    "firstkey" PRIMARY KEY, btree (code)
    "title_index" UNIQUE, btree (title)

test=# select * from films ;
 code  |  title  | did | date_prod  |  kind  |   len
-------+---------+-----+------------+--------+----------
 UA502 | Bananas | 105 | 1971-07-13 | Comedy | 01:22:00
(1 row)

sequece序列对象

序列对象，也被称为序列生成器，实际上就是用 CREATE SEQUENCE 创建的特殊的单行表，通常用来表生成唯一的标识符。可以理解为mysql的自增长字段一样，用法如下：

----- 直接在建表时使用serial类型，默认生成为tblname+colname+'seq'，如下的名字为 t2_id_seq
postgres=# create table t2(id serial ,name text);
CREATE TABLE
postgres=# d t2
                         Table "public.t2"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('t2_id_seq'::regclass)
 name   | text    |
----- 查看 t2_id_seq 的属性，max_value为最大值
postgres=# d t2_id_seq
          Sequence "public.t2_id_seq"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | t2_id_seq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f
Owned by: public.t2.id
----- 这里加名字时，不能使用双引号，只能使用单引号
postgres=# insert into t2(name) values("aaa");
ERROR:  column "aaa" does not exist
LINE 1: insert into t2(name) values("aaa");
                                    ^
postgres=# insert into t2(name) values('aaa');
INSERT 0 1
postgres=# insert into t2(name) values('bbb');
INSERT 0 1
postgres=# select * from t2;
 id | name
----+------
  1 | aaa
  2 | bbb
(2 rows)

权限控制

角色与用户的区别

角色就相当于岗位：角色可以是经理，助理。用户就是具体的人：比如陈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

创建的方法如下：

-----  查看schema，默认就有public
postgres=# dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

postgres=#
postgres=#
postgres=# create schema s01;
CREATE SCHEMA
postgres=# dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
 s01    | postgres
(2 rows)

实例应用，以下创建了2个t1表，属于2个不同的schema，一个是s01，另一个是默认的public：

postgres=# create table s01.t1(id int);
CREATE TABLE
postgres=# insert into s01.t1 values (1) ;
INSERT 0 1
postgres=# select * from s01.t1 ;
 id
----
  1
(1 row)

postgres=# create table t1(id int);
CREATE TABLE
postgres=# insert into t1 values (88);
INSERT 0 1
----- 默认是schema为public
postgres=# select * from t1 ;
 id
----
 88
(1 row)
postgres=# select * from public.t1 ;
 id
----
 88
(1 row)

删除schema

使用 drop schema 来删除，如果里面还有对象的话，不能直接删除，会报错，可以加上 cascade 关键词，这会把属于这个schema里面的东西全部删除掉。相当于linux下面的rm -rf一样的效果。

postgres=# select * from pg_tables where tablename = 't1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | t1        | postgres   |            | f          | f        | f           | f
 s01        | t1        | postgres   |            | f          | f        | f           | f
(2 rows)

postgres=# drop schema s01 ;
ERROR:  cannot drop schema s01 because other objects depend on it
DETAIL:  table s01.t1 depends on schema s01
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
postgres=# drop schema s01 cascade;
NOTICE:  drop cascades to table s01.t1
DROP SCHEMA
postgres=# select * from pg_tables where tablename = 't1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | t1        | postgres   |            | f          | f        | f           | f

创建schema指定owner

用户登陆的数据库之后，默认是谁创建的schema，owner就是谁，这边owner就是用户名的意思，在创建schema时是可以指定的。

----- 指定s01模式的所有者为fdm这个用户，但由于没有创建，所以会失败。
postgres=# create schema s01 authorization fdm;
ERROR:  role "fdm" does not exist
postgres=# create user fdm with password '123456';
CREATE ROLE
postgres=# create schema s01 authorization fdm;
CREATE SCHEMA
postgres=#
----- 也可以不写schema的名字，默认为owner的名称
postgres=# create schema authorization fdm;
CREATE SCHEMA
postgres=# dn
  List of schemas
  Name  |  Owner
--------+----------
 fdm    | fdm
 public | postgres
 s01    | fdm
(3 rows)

指定了owner，不指定schema，则schema名字与owner一致。这是什么意思呢？以上面第一条记录为例，schema的name为fdm，其owner是fdm，那意思是说创建fdm.t1表格时，这张表的owner就属于fdm这个用户，有点类似默认权限的意思。

具体实例如下，

postgres=# select * from pg_tables where tablename = 't1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | t1        | postgres   |            | f          | f        | f           | f
(1 row)
----- 切换到fdm这个用户，那这个用户的schema为fdm -----
postgres=# c - fdm
Password for user fdm:
You are now connected to database "postgres" as user "fdm".
----- 这个没有加schema，但由于fdm用户的schema为fdm，所以创建后数据库的schema就是fdm -----
postgres=> create table t1 (id int);
CREATE TABLE
postgres=> create table s01.t1(id int);
CREATE TABLE
----- 由于pg_tables表就可以看到表的相关的信息，注意schemaname与tableowner -----
postgres=> select * from pg_tables where tablename = 't1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | t1        | postgres   |            | f          | f        | f           | f
 s01        | t1        | fdm        |            | f          | f        | f           | f
 fdm        | t1        | fdm        |            | f          | f        | f           | f

设置schema搜索路径

所谓schema搜索路径，指的是schema的优先级，类似linux的PATH这个环境变量的意思，默认为 "$user", public，当user存在schema时，就先以这个优先，如果不存在，则默认为public这个模式。越靠前，其优先级最高。

----- 当前登陆用户为fdm，所以$user的值为fdm
postgres=> show search_path ;
   search_path
-----------------
 "$user", public
(1 row)
----- 当前登陆用户为fdm，那这个用户的schema为fdm -----
postgres=> insert into t1 values (333);
INSERT 0 1
postgres=> insert into s01.t1 values (555);
INSERT 0 1
postgres=> select * from t1;
 id
-----
 333
(1 row)

----- public.t1 是postgres这个用户创建的，所以fdm没有权限 -----
postgres=> select * from public.t1 ;
ERROR:  permission denied for relation t1
----- 将search_path切换到s01 -----==
postgres=> set search_path = 's01' ;
SET
postgres=> show search_path ;
 search_path
-------------
 s01
(1 row)
postgres=> select * from t1;
 id
-----
 555
(1 row)

schema与权限

schema跟权限是息息相关的，只有login权限的用户，对于owner不是自己的schema是没有任何权限的。先看以下实例，先重新登陆pg，然后运行以下命令：

----- 先用超管账号进行登陆
postgres=# conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.137.66" at port "5432".
----- 先创建schema以及User、table -----
postgres=# create schema s02;
CREATE SCHEMA
postgres=# create user u02 with password '123456';
CREATE ROLE
postgres=# create table s02.t1 (id int);
CREATE TABLE
----- 切换用u02来登陆 -----
postgres=# c - u02;
----- 没有权限查看 -----
postgres=> select * from s02.t1 ;
ERROR:  permission denied for schema s02
LINE 1: select * from s02.t1 ;
postgres=> dt s02.t1
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 s02    | t1   | table | postgres
(1 row)

由上，可以得知使用s02这个模式的owner是postgres，所以fdm这个用户是没有办法查看s02.t1这个表格的。

我们可以通过授权的方式来让fdm这个用户有权限去查看s02.t1这个表格：

----- 将s02授权给U02 -----==
postgres=> c - postgres
postgres=# grant all on SCHEMA s02 to u02;
GRANT
postgres=> c - u02;
----- 提示没有查询权限 -----==
postgres=> select * from s02.t1 ;
ERROR:  permission denied for relation t1
postgres=> c - postgres
----- 将所有表的schema为s02的owner修改为u02 -----==
postgres=# grant all on all tables in schema s02 to u02;
GRANT
postgres=> select * from s02.t1 ;
 id
----
(0 rows)

总结

postgresql在权限控制方面比mysql严格多了，初学者很容易在这边打圈，尤其是之前有接触过Mysql的同学。在默认情况下，所有新建的数据库、表都属于public这个schema，同时还需要关注schema所属的owner。

postgres=# create user dbuser with password 'dbuser';
CREATE ROLE
postgres=# create database exampledb OWNER dbuser;
postgres=# grant all on DATABASE exampledb to dbuser;
GRANT
postgres=# c exampledb
psql (9.2.24, server 9.6.15)
WARNING: psql version 9.2, server version 9.6.
         Some psql features might not work.
You are now connected to database "exampledb" as user "postgres".
exampledb=# dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)
exampledb=# alter schema public owner to dbuser ;
ALTER SCHEMA
exampledb=# dn
 List of schemas
  Name  | Owner
--------+--------
 public | dbuser
(1 row)

exampledb=# grant ALL ON all tables in schema public to dbuser;
GRANT
exampledb=# grant ALL ON all sequences in schema public to dbuser;
GRANT

简言之，新建数据库以及赋权的方法如下：

--- 在新建数据库时，就先创建一个schema
create database test_db;
create user test_user with password '123456';
create schema test_schema authorization test_user;
grant all on schema test_schema to test_user;
grant all privileges on database test_db to test_user;
alter database test_db set search_path to test_user;

--- 或者使用public，但是修改owner
CREATE DATABASE exampledb OWNER dbuser;
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;
c exampledb;
ALTER SCHEMA public OWNER to dbuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dbuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dbuser;

相比mysql来说，复杂了很多。

备份与恢复

数据库的备份有多种分类方式。按照备份后的文件类型，可以分为物理备份（文件系统级别的备份）和逻辑备份（备份后的文件是sql文件或特定格式的导出文件）；按照备份过程中是否停止数据库服务，可分为冷备份（备份过程中停止数据库服务）和热备份（备份过程中数据库服务开启并可供用户访问）；按照备份是否是完整的数据库，可分为全量备份(备份是完整的数据库)和增量备份（备份是上一次全量备份后数据库改变的内容）。

文件系统级别的冷备份

这种备份方式需要关闭数据库，然后拷贝数据文件的完整目录。恢复数据库时，只需将数据目录复制到原来的位置。该方式实际工作中很少使用。

pg_dump备份

这种方式可以在数据库正在使用的时候进行完整一致的备份，并不阻塞其它用户对数据库的访问。它会产生一个脚本文件，里面包含备份开始时，已创建的各种数据库对象的SQL语句和每个表中的数据。可以使用数据库提供的工具pg_dumpall和pg_dump来进行备份。pg_dump只备份数据库集群中的某个数据库的数据，它不会导出角色和表空间相关的信息，因为这些信息是整个数据库集群共用的，不属于某个单独的数据库。pg_dumpall，对集簇中的每个数据库调用pg_dump来完成该工作,还会还转储对所有数据库公用的全局对象（pg_dump不保存这些对象）。目前这包括适数据库用户和组、表空间以及适合所有数据库的访问权限等属性。

pg_dump的选项如下：

-f, --file=FILENAME          输出文件或目录名
-F, --format=c|d|t|p         输出文件格式 (定制, 目录, tar)
                             明文 (默认值))
-j, --jobs=NUM               执行多个并行任务进行备份转储工作
-v, --verbose                详细模式
-V, --version                输出版本信息，然后退出
-Z, --compress=0-9           被压缩格式的压缩级别
--lock-wait-timeout=TIMEOUT  在等待表锁超时后操作失败
--no-sync                    do not wait for changes to be written safely to disk
-?, --help                   显示此帮助, 然后退出

控制输出内容选项:

-a, --data-only              只转储数据,不包括模式
-b, --blobs                  在转储中包括大对象
-B, --no-blobs               exclude large objects in dump
-c, --clean                  在重新创建之前，先清除（删除）数据库对象
-C, --create                 在转储中包括命令,以便创建数据库
-E, --encoding=ENCODING      转储以ENCODING形式编码的数据
-n, --schema=SCHEMA          只转储指定名称的模式
-N, --exclude-schema=SCHEMA  不转储已命名的模式
-o, --oids                   在转储中包括 OID
-O, --no-owner               在明文格式中, 忽略恢复对象所属者

-s, --schema-only            只转储模式, 不包括数据
-S, --superuser=NAME         在明文格式中使用指定的超级用户名
-t, --table=TABLE            只转储指定名称的表
-T, --exclude-table=TABLE    不转储指定名称的表
-x, --no-privileges          不要转储权限 (grant/revoke)
--binary-upgrade             只能由升级工具使用
--column-inserts             以带有列名的INSERT命令形式转储数据
--disable-dollar-quoting     取消美元 (符号) 引号, 使用 SQL 标准引号
--disable-triggers           在只恢复数据的过程中禁用触发器
--enable-row-security        启用行安全性（只转储用户能够访问的内容）
--exclude-table-data=TABLE   不转储指定名称的表中的数据
--if-exists              当删除对象时使用IF EXISTS
--inserts                    以INSERT命令，而不是COPY命令的形式转储数据
--no-publications            do not dump publications
--no-security-labels         不转储安全标签的分配
--no-subscriptions           do not dump subscriptions
--no-synchronized-snapshots  在并行工作集中不使用同步快照
--no-tablespaces             不转储表空间分配信息
--no-unlogged-table-data     不转储没有日志的表数据
--quote-all-identifiers      所有标识符加引号，即使不是关键字
--section=SECTION            备份命名的节 (数据前, 数据, 及 数据后)
--serializable-deferrable   等到备份可以无异常运行
--snapshot=SNAPSHOT          为转储使用给定的快照
--strict-names               要求每个表和/或schema包括模式以匹配至少一个实体
--use-set-session-authorization
                             使用 SESSION AUTHORIZATION 命令代替
              ALTER OWNER 命令来设置所有权

联接选项:

-d, --dbname=DBNAME       对数据库 DBNAME备份
-h, --host=主机名        数据库服务器的主机名或套接字目录
-p, --port=端口号        数据库服务器的端口号
-U, --username=名字      以指定的数据库用户联接
-w, --no-password        永远不提示输入口令
-W, --password           强制口令提示 (自动)
--role=ROLENAME          在转储前运行SET ROLE

实例如下：

[root@localhost ~]# pg_dump -h 192.168.137.66 -U postgres -C -c -d postgres -f postgres.sql
Password:
# 备份所有的数据库
[root@localhost ~]# pg_dumpall -U postgres -c -f postgres_all.sql
[root@localhost ~]# ll -h postgres*
-rw-r--r--. 1 root root 4.4K Nov  3 08:10 postgres.sql
-rw-r--r--. 1 root root 7.0K Nov  3 08:19 postgres_all.sql

#恢复数据库
psql -U postgres -f postgres.sql

连续归档

这种方式的策略是把一个文件系统级别的全量备份和WAL(预写式日志)级别的增量备份结合起来。当需要恢复时，我们先恢复文件系统级别的备份，然后重放备份的WAL文件，把系统恢复到之前的某个状态。这种备份有显著的优点：

不需要一个完美的一致的文件系统备份作为开始点。备份中的任何内部不一致性将通过日志重放来修正。
可以结合一个无穷长的WAL文件序列用于重放，可以通过简单地归档WAL文件来达到连续备份。
不需要重放WAL项一直到最后。可以在任何点停止重放，并使数据库恢复到当时的一致状态。
可以连续地将一系列WAL文件输送给另一台已经载入了相同基础备份文件的机器，得到一个实时的热备份系统。

创建测试数据

先创建表，插入100条数据，并查看数据库的大小：

postgres=# create table foo(id integer);
CREATE TABLE
postgres=# insert into foo values(generate_series(1,100));
INSERT 0 100
postgres=# select pg_size_pretty(pg_database_size('postgres'));
 pg_size_pretty
----------------
 7359 kB
(1 row)

------或者先查出oid值，再看查数据库的大小
postgres=# select oid from pg_database where datname='postgres';
  oid
-------
 13325
(1 row)
postgres=# q
[root@localhost ~]# du -sh /data/PostgreSQL/data/base/13325
7.3M	/data/PostgreSQL/data/base/13325

修改配置

修改postgresql.conf，将这三项的配置修改如下：

/cache1/PostgreSQL/data/postgresql.con

1
2
3

wal_level = replica			# minimal, replica, or logical
archive_mode = on		# enables archiving; off, on, or always
archive_command = 'cp %p /data/PostgreSQL/archive/%f'		# command to use to archive a logfile segment

其中archive_command中 %p 表示将要被归档的WAL文件的完整路径，本例为路径 /data/PostgreSQL/data/pg_xlog/ ，用 %f 代表要被归档的日志文件的文件名，都不需要管，pg会自动识别填充。修改完成之后，由于备份的目录权限需要设置为数据库启动的用户权限，所以需要再运行以下命令：

1
2
3

mkdir -p /data/PostgreSQL/archive/
chown postgres:postgres /data/PostgreSQL/archive/
systemctl restart postgresql-9.6.service

这个archive_command在什么时候执行呢，即PostgreSQL在每次WAL日志16MB段满的时候才执行以及archive_timeout。可以使用以下命令查看：

postgres=# show archive_timeout;
 archive_timeout
-----------------
 0
(1 row)
postgres=# show wal_segment_size;
 wal_segment_size
------------------
 16MB
(1 row)

修改wal_level和archive_mode参数都需要重新启动数据库才可以生效，修改archive_command不需要重启，只需要reload即可

创建用户

使用专属的用户进行备份数据。

1 2	postgres=# CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '123456'; CREATE ROLE

再修改以下配置：

[root@localhost data]# grep max_wal_senders postgresql.conf
max_wal_senders = 10		# max number of walsender processes
[root@localhost data]# cat pg_hba.conf |grep '^local   replication'
local   replication             repuser                                     md5

建立基础备份

pg_basebackup工具是对数据库实例级进行的物理备份，可以进行打包，也可以进行。默认在安装好PG之后，运行pg_basebackup建立基础备份，-Ft 为打包为tar，-z -Z5 指定压缩等级，-Pv 显示打包的过程，-Xf 是指 --xlog-method=fetch|stream，在备份末尾收集预写日志文件。

[root@localhost PostgreSQL]# pg_basebackup -Ft -Pv -Xf -z -Z5 -U repuser -D /data/PostgreSQL/base/
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
transaction log start point: 0/2000028 on timeline 1
39245/39245 kB (100%), 1/1 tablespace
transaction log end point: 0/20000F8
pg_basebackup: base backup completed
[root@localhost PostgreSQL]# ll -h /data/PostgreSQL/base/base.tar.gz
-rw-r--r--. 1 root root 2.7M Nov  5 22:03 /data/PostgreSQL/base/base.tar.gz

手工归档备份

由于WAL文件是写满16MB才会进行归档，测试阶段可能写入会非常少，可以在执行完基础备份之后，手动进行一次WAL切换。在PG9.6的版本上面使用pg_switch_xlog()来切换，而PG 10版本则是使用pg_switch_wal()来切换。

postgres=# select pg_switch_xlog();
 pg_switch_xlog
----------------
 0/100001C0
(1 row)

或者通过设置archive_timeout参数，在达到timeout阈值时强行切换到新的WAL段。

这样 /data/PostgreSQL/archive/ 就能看到备份的数据了

恢复数据

为了演示方便，可以先手工建立一个还原点，如下操作：

postgres=# select pg_create_restore_point('201911052224');
 pg_create_restore_point
-------------------------
 0/3000138
(1 row)

postgres=# delete from foo ;
DELETE 100
postgres=# select count(*) from foo;
 count
-------
     0
(1 row)

还原点建立好了之后，直接删除foo文件。然后再进行恢复数据：

systemctl stop postgresql-9.6.service
cd /data/PostgreSQL
mv data/ data.bak
mkdir data
cp /usr/pgsql-9.6/share/recovery.conf.sample data/recovery.conf
tar zxf /data/PostgreSQL/base/base.tar.gz -C data  # 恢复基础的备份
chown postgres:postgres data -R
chmod 700 data -R

然后在data/recovery.conf处修改配置如下：

/data/PostgreSQL/data/recovery.conf

1
2
3

restore_command = 'cp /data/PostgreSQL/archive/%f %p'		# e.g. 'cp /mnt/server/archivedir/%f %p'
recovery_target_name = '201911052224'	# e.g. 'daily backup 2011-01-26'
recovery_target_timeline = 'latest'

最后再重启一下pg即可。

[root@localhost pg_log]# systemctl restart postgresql-9.6.service
[root@localhost pg_log]# cd /data/PostgreSQL/data/pg_log
[root@localhost pg_log]# ll /data/PostgreSQL/data/recovery.done
-rwx------. 1 postgres postgres 5726 Nov  4 20:46 /data/PostgreSQL/data/recovery.done

重启完成之后，recovery.conf会自动变成recovery.done，同时在pg_log目录下面可以查到看具体的日志。

/cache1/PostgreSQL/data/pg_log/postgresql-Tue.log

< 2019-11-05 22:29:44.298 CST > LOG:  database system was interrupted; last known up at 2019-11-05 22:03:28 CST
cp: cannot stat ??data/PostgreSQL/archive/00000002.history?? No such file or directory
< 2019-11-05 22:29:44.471 CST > LOG:  starting point-in-time recovery to "201911052224"
< 2019-11-05 22:29:44.482 CST > LOG:  restored log file "000000010000000000000002" from archive
< 2019-11-05 22:29:44.531 CST > LOG:  redo starts at 0/2000028
< 2019-11-05 22:29:44.536 CST > LOG:  consistent recovery state reached at 0/20000F8
< 2019-11-05 22:29:44.537 CST > LOG:  database system is ready to accept read only connections
< 2019-11-05 22:29:44.559 CST > LOG:  restored log file "000000010000000000000003" from archive
< 2019-11-05 22:29:44.598 CST > LOG:  recovery stopping at restore point "201911052224", time 2019-11-05 22:25:10.646955+08
< 2019-11-05 22:29:44.598 CST > LOG:  recovery has paused
< 2019-11-05 22:29:44.598 CST > HINT:  Execute pg_xlog_replay_resume() to continue.

这时登陆数据库查看，就可以看到删除的数据就会回来了。

恢复具体时间点的数据

以上为了演示方便，使用了还原点的方法，但我们还是可以使用还原到指定时间：

postgres=# select current_timestamp;
              now
-------------------------------
 2019-11-05 10:24:40.932538+08
(1 row)

相应的，recovery.conf需要做一定的修改。

$ vim /data/app_pg/recovery.conf
restore_command='cp /backup/wal/%f %p'
recovery_target_time='2019-11-05 10:21:55.794813+08'
recovery_target_timeline='latest'

配置流复制

所谓流复制，就是从库通过tcp流从主库中同步相应的数据。postgres的主从主称之为primary，从称为stand_by。实现的原理跟上面说的连续归档是一样的。

主服务配置

主服务器的IP为192.168.137.66，安装和配置方法参考第一节。主要是配置有点不一样，具体如下：

postgresql.conf配置如下：

/data/PostgreSQL/data/postgresql.conf

listen_addresses = '*'
wal_level = replica             
archive_mode = on            
archive_command = 'cp %p /cache/PostgreSQL/pg_archive/%f'  
max_wal_senders = 10         # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 512		 ＃ 设置流复制保留的最多的xlog数目
hot_standby = on
max_connections = 100 		# 从库的max_connections必须要大于主库的

修改pg_hba.conf，增加replica用户，进行同步。

/data/PostgreSQL/data/pg_hba.conf

1
2
3

local   all             all                                     trust
host    all             all           	192.168.137.0/24        md5
host    replication     repuser           	192.168.137.0/24    md5

新增repuser用户

1	postgres=# CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '123456';

从服务配置

从服务器的IP为192.168.137.55，只需要安装好postgresql，不需要初始化Initdb。注意这个区别，学过mysql的同学对比很难理解。

从主节点拷贝数据到从节点，如果之前有启动了postgresql，那需要停止服务，然后把data目录删除掉。

cd /data/PostgreSQL/
rm -rf data/
pg_basebackup -h 192.168.137.66 -U repuser -D /data/PostgreSQL/data -X stream -P	#从主服务器上面同步基础备份的数据

#同步完成之后要确保权限以及属主
chown postgres.postgres /data/PostgreSQL/data -R
chmod 700 /data/PostgreSQL/data -R

配置recovery.conf，从 cp /usr/pgsql-9.6/share/recovery.conf.sample data/recovery.conf 之后，修改配置如下：

/data/PostgreSQL/data/recovery.conf

1
2
3

recovery_target_timeline = 'latest'
standby_mode = on   # 说明该节点是从服务器
primary_conninfo = 'host=192.168.137.66 port=5432 user=repuser password=123456'  # 主服务器的信息以及连接的用户

postgresql.conf也需要修改一些配置：

max_connections = 1000 ＃ 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on  ＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 1s  # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈

这样就可以启动pg服务了。

验证是否成功

查看进程，主库所在的机器中会看到sender进程:

1
2
3

[root@localhost ~]# ps aux |grep wal |grep -v grep
postgres  61583  0.0  0.1 362580  5864 ?        Ss   14:59   0:00 postgres: wal writer process
postgres  82987  0.0  0.0 362984  3036 ?        Ss   15:31   0:00 postgres: wal sender process repuser 192.168.137.55(53912) streaming 0/7009848

从库所在的机器中会看到receiver进程:

1 2	[root@remote data]# ps aux \|grep wal \|grep -v grep postgres 8490 0.0 0.2 410908 3252 ? Ss Nov05 0:15 postgres: wal receiver process streaming 0/7009848

查看复制状态

在主服务器上面执行：

postgres=# select client_addr,sync_state from pg_stat_replication;
  client_addr   | sync_state
----------------+------------
 192.168.137.55 | async
(1 row)

--- 如果返回 f 说明是主库，返回 t 说明是备库
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

sync_state有三个值，async: 异步、sync: 同步、potential: 虽然现在是异步模式，但是有可能升级到同步模式。PG在默认情况下是使用异步模式，即主库上提交事务时不需要等待备库接收WAL日志流并写入到备库WAL日志文件时便返回成功，因此异步流复制的TPS会相对同步流复制要高，延迟更低。

实测效果

还是以以前的案列来验证，在主库上面创建一张表，增加数据：

postgres=# create table foo(id integer);
CREATE TABLE
postgres=# insert into foo values(generate_series(1,100));
INSERT 0 100

然后登陆从库，就可以看到foo也有数据了。

主备互切

使用 pg_ctl promote 方法来做切换。

先停止主库 systemctl stop postgresql

在备库上执行pg_ctl promote命令激活备库，如果recovery.conf变成recovery.done表示备库已切换成主库

# 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
3

recovery_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 目录下面的日志：

< 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

以上是我初学postgresql的笔记，可以看到，同为关系型数据库，跟mysql相差还是很大，相对而言，在用户权限方面，比mysql更复杂一些。以上部署是通过rpm包的方法部署的，有兴趣的同学可以使用编译来安装，会更加折腾一些。另外，PG数据库不允许root权限，所以在报错时，请确认用户属主是否是postgres，切记！！！

夜河资源网提供的所有内容仅供学习与交流。通过使用本站内容随之而来的风险以及法律责任与本站无关，所承担的法律责任由使用者承担。
一、如果您发现本站侵害了相关版权，请附上本站侵权链接和您的版权证明一并发送至邮箱：yehes#qq.com（#替换为@）我们将会在五天内处理并断开该文章下载地址。
二、本站所有资源来自互联网整理收集，全部内容采用撰写共用版权协议，要求署名、非商业用途和相同方式共享，如转载请也遵循撰写共用协议。
三、根据署名-非商业性使用-相同方式共享 (by-nc-sa) 许可协议规定，只要他人在以原作品为基础创作的新作品上适用同一类型的许可协议，并且在新作品发布的显著位置，注明原作者的姓名、来源及其采用的知识共享协议，与该作品在本网站的原发地址建立链接，他人就可基于非商业目的对原作品重新编排、修改、节选或者本人的作品为基础进行创作和发布。
四、基于原作品创作的所有新作品都要适用同一类型的许可协议，因此适用该项协议，对任何以他人原作为基础创作的作品自然同样都不得商业性用途。
五、根据二〇〇二年一月一日《计算机软件保护条例》规定：为了学习和研究软件内含的设计思想和原理，通过安装、显示、传输或者存储软件等方式使用软件的，可不经软件著作权人许可，无需向其支付报酬！
六、鉴此，也望大家按此说明转载和分享资源！本站提供的所有信息、教程、软件版权归原公司所有，仅供日常使用，不得用于任何商业用途，下载试用后请24小时内删除，因下载本站资源造成的损失，全部由使用者本人承担！

Centos7安装与配置PostgreSQL

简介

安装与配置

安装

数据库初始化设置

配置使用

PG客户端认证

psql

pg_hba.conf

登陆方法

PG数据库操作指南

常用命令

基础操作

创建数据库与表

插入表数据

创建索引

查看相关的命令

sequece序列对象

权限控制

角色与用户的区别

schema模式的用法

创建schema

删除schema

创建schema指定owner

设置schema搜索路径

schema与权限

总结

备份与恢复

文件系统级别的冷备份

pg_dump备份

连续归档

创建测试数据

修改配置

创建用户

建立基础备份

手工归档备份

恢复数据

恢复具体时间点的数据

配置流复制

主服务配置

从服务配置

验证是否成功

查看复制状态

实测效果

主备互切

相关文章

亲自演示linux删库跑路命令sudo rm -rf删除全部文件

Linux：Shell Script脚本简单化入门教学

适用于Linux/Windows一键网络重装系统,一键DD魔改版

PHP简单混淆加密解密

支付宝亚运会大水，扫一扫最高领99红包！

扫一扫领取饿了么美团外卖红包，每天都可领。

注意