学习大纲：
postgresql考级进阶路线图（PCA+CPC）.xls

1.PG历史

点击了解详情： 时间线 版本特性

1. 为什么学习pg

   a. 高性能，功能完善。
   b. 完全开源，众多国产数据库基于PG开发。

   八卦😀： 为什么PG没有MySQL流行

2. 安装

2.1 yum 安装

急速体验：
系统版本： centos7.6
软件版本： Postgresql 13.2

一： 安装
#配置yum源
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#安装
yum install postgresql13-contrib.x86_64 postgresql13-server.x86_64 -y
二： 配置(暂时可忽略)
/var/lib/pgsql/data/pg_hba.conf   #修改监听地址0.0.0.0/0 
/var/lib/pgsql/data/postgresql.conf   #修改允许连接地址 '*' 
三： 启动
#初始化数据库
postgresql-13-setup initdb
systemctl start postgresql
systemctl status postgresql
四：  进入数据库
# 切换自动创建的用户postgres 
-bash-4.2$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres)
# 简单体验
postgres=# psql
postgres=# 
postgres=# create user pgsql with password '******';
CREATE ROLE
postgres=# create database dbtest owner pgsql;
CREATE DATABASE
postgres=# grant all on database dbtest to pgsql;
GRANT
postgres=# \q
五： 卸载
yum remove postgresql13-contrib.x86_64 postgresql13-server.x86_64 -y
rm -rf /etc/init.d/postgresql-13
rm -rf /var/lib/pgsql/*

注意事项：

#安装包说明： 
postgresql13.x86_64                postgresql的client端程序和库文件，不会安装数据库服务器。
postgresql13-contrib.x86_64        postgresq的附加模块，包括常用的扩展等
postgresql13-devel.x86_64         postgresql的c和c++头文件，如果开发libpq程序，它是必需的。
postgresql13-server.x86_64        postgresql server端程序，作为数据库服务器，它是最核心的包。

2.2 源码安装

一： 下载postgresql源码：
# 进入主页：
https://www.postgresql.org/ftp/latest
# 下载并解压：
wget https://ftp.postgresql.org/pub/source/v13.2/postgresql-13.2.tar.gz
tar xf postgresql-13.2.tar.gz
# 安装依赖：
yum install -y bison flex readline-devel zlib-deve1 zlib zlib-devel gcc
二： 编译安装
# 准备：
useradd postgres && echo '123456' | passwd --stdin postgres
mkdir /usr/local/postgresql/data -p
chown  -R  postgres. Postgres  /usr/local/postgresql
# 进行编译：
./configure --prefix=/usr/local/postgresql
make && make install
-- gmake world && gmake install-world 一次性安装全部附加组键
# 配置环境变量：
vim /etc/profile
export PATH=/usr/local/postgresql/bin:$PATH
# 初始化：
su - postgres
initdb -D /usr/local/postgresql/data
三： 启动数据库
① 使用pg_ctl进行启动：
pg_ctl -D /usr/local/postgresql/data start|stop
② 使用system进行管理 (root用户管理，冲突)：
vim /usr/lib/systemd/system/postgresql-13.service
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/usr/local/postgresql/data/
OOMScoreAdjust=-1000
ExecStart=/usr/local/postgresql/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/usr/local/postgresql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/local/postgresql/bin/pg_ctl reload -D ${PGDATA} -s
TimeoutSec=300
[Install]
WantedBy=multi-user.target

说明：

--prefix=PREFIX    指定安装目录，默认的安装目录为/usr/local/pgsql
--includedir=DIR    指定c和c++的头文件目录，默认的安装目录为prefix/include
--with-pgport=PORTNUM    指定初始化数据目录时的端口
--with-blocksize=BLOCKSIZE    指定数据文件的块大小，默认为8KB，如果在OLAP场景下可以适当增加这个值到32kb，以提高OLAP的性能，但在PLTP场景下建议使用8KB。
--with-segsize=SEGSIZE    指定单个数据文件的大小，默认为1GB
--with-wal-blocksize=BLOCKSIZE    指定WAL文件的块大小，默认为8KB
--with-segsize=SEGSIZE    指定单个WAL文件的大小，默认为16MB

3. 远程访问

3.1 远程访问配置

find / -name pg_hba.conf # vim修改监听地址为任意0.0.0.0/0

连接种类  目标库控制   访问用户控制     访问地址控制          表示客户端认证方法             
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

TYPE 说明：

METHOD 说明：

find / -name postgresql.conf #vim修改允许连接地址 ‘*’

# - Connection Settings -
listen_addresses = '*'          # what IP address(es) to listen on;
#port = 5432                    # (change requires restart)
其他： 
max_connections    最大连接数
superuser_reserved_connections    给超级用户预留连接数
unix_socket_directories    本地套接字文件所指定的目录
hot_standby = on    在接收主库同步数据的同时又能提供读服务

4. PG数据库的功能及应用

4.1 连接数据库

一： 创建一个测试库mydb，归属用户为pguser，同时为mydb库分配一个新的表空间tbs_mydb:
# 创建表空间目录：
[root@pg-test ~]#  mkdir /usr/local/postgresql/pg_tbs/tbs_mydb -p
# 创建用户：
[root@pg-test ~]#  psql
postgres=# create role pguser with encrypted password '123456';
# 创建表空间：
postgres=# create tablespace tbs_mydb owner pguser location '/usr/local/postgresql/pg_tbs/tbs_mydb';
# 创建数据库：
postgres=# create database mydb with owner = pguser template = template0 encoding = 'utf8' tablespace = tbs_mydb;
# 【赋权】
postgres=# grant all on database mydb to pguser with grant option;  
postgres=# grant all on tablespace tbs_mydb to pguser;
postgres=# alter role pguser login;
二： 使用pguser用户远程连接mydb数据库：
[root@db01 ~]# psql -h 10.0.0.71  -p 5432 mydb pguse
说明：
# owner      数据库属主
# template   数据库模板，默认有template0和template1模板，也可以自定义模板
# encoding   表示数据库字符集，这里设置的是utf8字符集
# tablespace 表示数据库的默认表空间

表空间作用说明：

# 表空间说明：
表空间是指其物理存储的地方；表是指他的表现形式；物理存储的全是实际数据，是不能直接拿过来用的，可以通过表名，字段名这种接口来读取数据，数据库会到表空间去找到实际的数据，再按照具体的命令显示给出需要的数据。
其中pg_default和pg_global是在PG集群initdb之后默认创建的。
简言之： 表空间是告诉PG服务器将数据库对象的物理文件放在哪里  
# 作用：
1.表空间允许管理员根据数据库对象的使用模式来优化性能。
例如，一个很频繁使用的索引可以被放在非常快并且非常可靠的磁盘上，如一种非常贵的固态设备。同时，一个很少使用的或者对性能要求不高的存储归档数据的表可以存储在一个便宜但比较慢的磁盘系统上。
2.如果初始化集簇所在的分区或者卷用光了空间，而又不能在逻辑上扩展或者做别的什么操作，那么表空间可以被创建在一个不同的分区上，直到系统可以被重新配置。
3.表空间位置
- 默认表空间：pg_default ，是用户表、用户表index、和临时表、临时表index、内部临时表的默认空间。对应文件系统目录$PADATA/base/。
- 系统字典表表空间：pg_global，存放系统字典表，如pg_database、pg_authid、pg_tablespace等表以及它们的索引。对应文件系统目录$PADATA/global/。
- 自定义表空间：

表空间： 详情

4.2 plsql快捷操作

PostgreSQL 对应 1.简单常用SQL 2.常用SQL语句

元数据查看练习：

\d 实践：
# 创建一张测试表：
create table test_1(id int4,name text,creeate_time timestamp without time zone default clock_timestamp());
alter table test_1 add primary key (id);
# 查看表的定义：
\d test_1
\dt实践：
# 插入5000000数据：
insert into test_1(id,name) select n,n || '_francs' from generate_series(1,5000000) n;
# 查看表的大小：
\dt+ test_1
-E实践：
psql -E mydb pguser
\db
copy命令演示：
# 创建文件：
[root@pg-test ~]# cat >test_copy_in.txt <<'EOF'
1     a
2     b
3     c
EOF
# 创建表：
create table test_copy(id int4,name text);
# 命令行导入数据：
postgres=# copy test_copy from '/home/postgres/test_copy_in.txt'  with(format text, delimiter ' ');
# 导出数据：
copy test_copy to '/home/postgres/test.txt';
# 将数据以csv的格式导出：
copy test_copy to '/home/postgres/test_csv.txt' with csv header;
\copy实践：
# 导出数据：
\copy test_copy to '/home/postgres/test.txt';
\set实践：
# test_copy表有四条记录，设置变量v_id值为2，查询id值等于2的记录：
\set v_id 2
select * from test_copy where id=:v_id;
# 取消变量设置的值：
\set v_id
\watch实践：
# 例如每隔1秒执行一次sql语句：
# 首先执行一条想要重复执行的sql语句：
select now();
# 设置重复执行间隔：
\watch 1

4.3 psql执行SQL脚本

4.3.1 执行脚本参数

练习

-q参数实践：
# 创建test_q.sql脚本：
[postgres@pg01 ~]$ cat > test_q.sql<<'EOF'
create table test_q(id int4);
truncate table test_q;
insert into test_q values (1);
insert into test_q values (2);
EOF
# 加上-q参数之前：
[postgres@pg01 ~]$ psql -f test_q.sql 
CREATE TABLE
TRUNCATE TABLE
INSERT 0 1
INSERT 0 1
# 加上-q参数之后：（此时发现什么都没有显示）
[postgres@pg01 ~]$ psql -q -f test_q.sql 
-v参数演示：
# 编辑脚本文件：
cat select_1.sql 
select * from test_copy where id=:v_id;
# 使用-v传递变量：
psql -v v_id=1 -f select_1.sql

4.3.2 日常维护脚本：

一： 定制维护脚本：查询活动会话：
 # 编写配置文件：
[postgres@pg01 ~]$ cat >psqlrc <<'EOF' 
\set active_session 'select pid,usename,datname,query,client_addr,state from pg_stat_activity where pid <> pg_backend_pid() and state='active' order by query;'
EOF
 # 重新链接数据库，执行变量即可：
postgres=# :active_session;  
 # 以上参数说明：
     · pg_stat_activity视图显示postgresql进程信息
    · pid进程号
    · username数据库用户名称
    · datname 数据库名称
  · query 进程最近执行的sql
    · client_addr 进程客户端IP
    · state 进程状态说明：
   · active 后台进程正在执行的sql
   · idle   后台进程为空闲状态，等待后续客户端发出命令
   · idle in transaction 后台进程正在事务中，并不是执行的sql
   · idle in transaction (aborted) 和idle in transaction状态类似，只是事务中的部分sql异常。
二： 定制维护脚本：查询等待事件：
# 编写配置文件：
[postgres@pg01 ~]$ cat  >psqlrc <<'EOF'
\set wait_event 'select pid,usename,datname,query,client_addr,wait_event_type,wait_event from pg_stat_activity where pid <> pg_backend_pid() and wait_event is not null order by wait_event_type;'
EOF
三： 查看数据库连接数脚本：
\set connections 'select datname,usename,client_addr,count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1,2,3 order by 1,2,4 desc;'

4.4 客户端界面提示符

提示符的设置：

一： 查看默认的提示符：
\echo :PROMPT1
二： 提示符号的设置：
# 例如设置PROMPT1值为%M%R%#
\set PROMPT1 '%M%R%#'
# 以上设置如果想永久生效可以写入.psqlrc文件中

4.5 创建普通用户：

创建一个DB,不指定owner,默认的owner就是当前用户.

1. 创建用户（此时无法连接到数据库）
create user max with password '123456';
2. 创建数据库：
create database db_max;
3. 将数据库授权给heng用户，以下任选其一：（此时就可以远程连接了）
alter database db_max owner to max;
grant all privileges on database db_max to max;
4.  撤销用户数据库权限：
revoke all on database db_max from max;
5. 给用户添加创建数据库的权限：（此时就可以创建用户了）
alter user max createdb;
6. 回收此用户权限：
alter user max nocreatedb;

4.6 创建超级用户：

1. 创建超级用户并设置密码：
create user hsping superuser password '123';
2. 创建数据库：
create database db_hsping;
3. 将数据库授权给heng用户：
grant all privileges on database db_hsping to hsping;
4. 修改用户密码：
alter user hsping with password '123456';

4.7 角色增删查改：

增删查改

1. 创建角色：
create role juese;
2. 查看当前角色，可查看系统表pg_roles
select rolname from pg_roles;
3. 删除角色：
drop role juese;

3.8 角色属性：

权限说明：

权限赋予与撤销： 【 查看与schema操作请点击】

一： 用户-角色 的赋予：
# 将schema进行赋予：
alter schema s1 owner to juese;
# 将s1这个schema下的所有表授权给max用户：
grant select,insert,update,delete on all tables in schema s1 to max;
# 将s1这个schema下a表授权max用户：
grant select,insert,update,delete on s1.a  to max;
# 将t1表的查询权限赋予给juese
grant select on t1 to juese;
# 将赋予给juese对t1表的查询权限进行回收：
revoke select on t1 from juese;
二：创建数据库：
# 创建一个oldboy库：
create database oldboy;
# 基于oldboy库为模板创建另一个库：
create database oldboy1 template oldboy;
# 删除一个库：
drop database oldboy1;
# 基于oldboy库为模板，创建一个数据库同时指定它的所有者
create database zhang owner zhang template oldboy;
三： 权限的打包：(未解决)
# 创建角色：
create role juese login superuser createdb createrole;
# 创建用户：
create user zhang;
create database zhang owner zhang;
# 将角色的权限赋予给zhang用户：
grant juese to zhang;
# 回收角色权限：
revoke juese from zhang;

5. PostgreSQL 如何修改配置变量：

在 PostgreSQL 数据库的 查询代价计算中，会看到有如下的变量： seq_page_cost; //顺序读页代价 random_page_cost; //随机读页代价

等等。这些变量在数据库安装后我们可以通过两种方式来修改并生效：

5.1 通过 SET修改配置参数：

1. seq_page_cost 默认值为 1: 
# su - postgres
# psql postgres 
postgres > SHOW seq_page_cost;
postgres=# SHOW seq_page_cost;
 seq_page_cost 
---------------
 1
(1 row)
postgres=# SET seq_page_cost = 2.5;
SET
postgres=# SHOW seq_page_cost;
 seq_page_cost 
---------------
 2.5
(1 row)

SET 设置的值只在当前 session 中有效。

5.2 通过配置文件修改配置参数：

修改 PG安装路径/data 目录下的 postgresql.conf 的配置文件
如： #seq_page_cost = 1 改为2

如果当前数据库已经启动，再执行 _PG安装路径/bin/pg_ctl -D PG 安装路径/data reload_， 让运行中的数据库重新加载配置。否则的话，启动数据库，启动中自动加载配置。从而配置生效。在 psql 中执行 SHOW 命令验证。