安装postgresql¶

二进制安装¶

# ubuntu/debian package install 

sudo apt update
sudo apt -y upgrade
sudo apt -y install vim bash-completion wget

# 添加PostgreSQL的APT源
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# 添加公钥签名
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# 更新包列表
sudo apt update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt install postgresql-13 postgresql-client-13

systemctl status postgresql.service 

Ver  Cluster    Port   Status   Owner      Data directory                    Log file
13    main      5432   down     postgres   /var/lib/postgresql/13/main       /var/log/postgresql/postgresql-13-main.log

pg_ctlcluster 13 main start

编译安装¶

编译安装，需要的相关依赖：

GNU make version 3.80 or newer;
ISO/ANSI C compiler (at least C99-compliant). Recent versions of GCC are recom-mended
The GNU Readline library is used by default

# https://wiki.postgresql.org/wiki/Compile_and_Install_from_source_code
apt-get install build-essential  zlib1g-dev   libxml2-dev libxslt-dev libssl-dev  xsltproc ccache  bison flex libxml2-utils libreadline-dev  libsystemd-dev libacl1  libacl1-dev libicu-dev pkg-config

# git clone源代码
git clone https://git.postgresql.org/git/postgresql.git 


# 官网下载二进制，一般下比较新的版本
wget https://ftp.postgresql.org/pub/source/v13.2/postgresql-13.2.tar.gz -P /usr/local/src/
wget https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v14.4/postgresql-14.4.tar.gz -P /usr/local/src/
wget https://mirrors.ustc.edu.cn/postgresql/source/v14.4/postgresql-14.4.tar.gz -P /usr/local/src/
cd /usr/local/src/  && tar -zxvf postgresql-14.4.tar.gz


# 创建用于运行postgre的操作系统账号，并设置强密码
groupadd postgres &&  useradd -g postgres postgres
echo "postgres@123" | passwd --stdin postgres

# 创建安装目录
mkdir -p /data/postgres/data
chown -R postgres:postgres /data/postgres/


cd /usr/local/src/postgresql-14.4
# 如果不自定义目录，后面的安装默认会安到/usr/local/pgsql/bin中
./configure --prefix=/data/postgres/14.4/  --with-systemd
make world
make install


su - postgres

# 初始化，初始化必须要切到postgres用户，不能用root用户执行初始化命令
# 在初始化数据库系统时有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。


# 参数
/data/postgres/14.4/bin/initdb -D /data/postgres/14.4/data



# 启动
# bin目录中的postgres就是postgre server进程的二进制可执行文件

# 直接前台启动，-D指定数据目录，如果没有-D，则用PGDATA环境变量。
postgres -D /usr/local/pgsql/data

# pg_ctl是一个包装命令，可以直接后台启动postgre进程
/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data

# 登录
./psql  -h localhost -p 54321 -U postgres -w password -d postgres

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



# 注册成系统服务，/etc/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=infinity
[Install]
WantedBy=multi-user.target

配置¶

PostgreSQL配置，主要分为三类配置文件，

主配置文件¶

对于PostgreSQL 9.4或之后的版本，当使用initdb程序命令初始化数据库集簇之后，会在PG_DATA目录下同时存在两个与PostgreSQL服务相关的配置文件。

它们分别是postgresql.auto.conf和postgresql.conf。对于postgresql.conf文件，我们比较熟悉，该文件中存储着与PostgreSQL服务相关的所有默认参数。

在PostgreSQL 9.4版本之前，如果需要对PostgreSQL服务的某些功能进行优化、或是调整默认配置参数，则修改postgresql.conf配置文件，然后重启PostgreSQL（对于大多数配置参数的修改，均需要重启以生效）服务。

参数动态修改

但是从PostgreSQL 9.4版本开始，新引入了postgresql.auto.conf配置文件，它作为postgresql.conf文件的补充，在参数配置格式上，它和postgresql.conf保持一致。

configuration_parameter = value 或 configuration_parameter = 'value'

postgresql.conf文件创建成功之后，里面有PostgreSQL依赖的默认配置文件参数，比如"最大连接数、共享缓冲区、时区"等等

对于postgresql.auto.conf配置文件，一开始除了两行文本注释说明之外，没有其他的配置参数。正如postgresql.auto.conf配置文件中初始化时的文本字符串提示一样：

不要手动修改此文件, 因为它会被ALTER SYSTEM命令给覆盖。

该文件主要用于存储有ALTER SYSTEM命令设置的参数值。所以它不需要像postgresql.conf文件一样，每当调整配置参数时，都手动去打开修改、保存。

ALTER SYSTEM语句是PostgreSQL数据库的一个扩展，它用于在PostgreSQL数据库集群中修改服务器的配置参数。然后修改后的参数将保存在postgresql.auto.conf配置文件中。

当使用ALTER SYSTEM语句修改了某配置参数之后，该文件中存在的这个参数将覆盖解析该文件之前存在的参数值。通俗点说，就是该文件中的这个参数将覆盖掉postgresql.conf文件中的该参数（但是postgresql.conf文件中这个参数值不会被修改，只是对于PostgreSQL服务，postgresql.auto.conf文件中的该参数具有更高的优先级）。

客户端认证配置文件¶

客户端认证是由一个配置文件（通常名为pg_hba.conf并被存放在数据库集簇目录中）控制（HBA表示基于主机的认证，host-based authentication）。

在initdb初始化数据目录时，它会安装一个默认的pg_hba.conf文件。不过我们也可以把认证配置文件放在其它地方。

pg_hba.conf的路径由postgresql.conf中的hba_file = 'ConfigDir/pg_hba.conf'来控制。

pg_hba.conf文件的常用格式是一组记录，每行一条。空白行将被忽略，记录不能跨行。注释行以#号开头。

一条记录由若干用空格和/或制表符分隔的域组成。如果域值用双引号包围，那么它可以包含空白。在数据库、用户或地址域中引用一个关键字（例如，all或replication）将使该词失去其特殊含义，并且只是匹配一个有该名字的数据库、用户或主机。

每条记录指定一种连接类型、一个客户端 IP 地址范围（如果和连接类型相关）、一个数据库名、一个用户名以及对匹配这些参数的连接使用的认证方法。

第一条匹配连接类型、客户端地址、连接请求的数据库和用户名的记录将被用于执行认证。

这个过程没有"落空"或者"后备"的说法：如果选择了一条记录而且认证失败，那么将不再考虑后面的记录。如果没有匹配的记录，那么访问将被拒绝。

# pg_hba.conf文件的常用格式是一组记录，每行一条。空白行将被忽略， #注释字符后面的任何文本也被忽略。记录不能跨行。
# 一条记录由若干用空格 和/或制表符分隔的域组成。如果域值用双引号包围，那么它可以包含空白。
# 在数据库、用户或地址域中 引用一个关键字（例如，all或replication）将使该词失去其特殊 含义，并且只是匹配一个有该名字的数据库、用户或主机。
# 每条记录指定一种连接类型、一个客户端 IP 地址范围（如果和连接类型相关）、一个数据库名、一个用户名以及对匹配这些参数的连接使用的认证方法。
# 第一条匹配连接类型、客户端地址、连接请求的数据库和用户名的记录将被用于执行认证。

# PostgreSQL自上而下逐行检查规则，一旦匹配到一条规则，就停止检查。如果前面的规则过于宽松，后面的规则可能永远不会被匹配。有点像防火墙的概念。
# 将限制性更强的规则放在前面，将开放性规则放在后面。例如，先限制特定IP地址的访问，再允许本地连接。
# 这个过程没有“落空”或者“后备”的说法：如果选择了一条记录而且认证失败，那么将不再考虑后面的记录。如果没有匹配的记录，那么访问将被拒绝。

# TYPE     DATABASE USER   ADDRESS                METHOD

local      database  user                       auth-method     [auth-options]
host       database  user  address              auth-method     [auth-options]
hostssl    database  user  address              auth-method     [auth-options]
hostnossl  database  user  address              auth-method     [auth-options]
host       database  user  IP-address  IP-mask  auth-method     [auth-options]
hostssl    database  user  IP-address  IP-mask  auth-method     [auth-options]
hostnossl  database  user  IP-address  IP-mask  auth-method     [auth-options]

# "local" is for Unix domain socket connections only
local       all              all                     trust

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust




# 第一个字段的取值如下：
# local     本地连接，这条记录匹配企图使用 Unix 域套接字的连接。如果没有这种类型的记录，就不允许 Unix 域套接字连接。
# host      这条记录匹配企图使用 TCP/IP 建立的连接。host记录匹配SSL和非SSL的连接尝试。
# hostssl   这条记录匹配企图使用 TCP/IP 建立的连接，但必须是使用SSL加密的连接。要使用这个选项，编译服务器的时候必须打开SSL支持。
# 
host all all 0.0.0.0/0        md5 # 允许所有机器登录，使用md5密码。

# 第二个字段取值：
# database 声明记录所匹配的数据库名称。值 all表明该记录匹配所有数据库，可以通过用逗号分隔的方法声明多个数据库，也可以通过前缀@来声明一个包含数据库名的文件。

# 第三个字段取值：
# user 为这条记录声明所匹配的数据库用户。值all表明它匹配于所有用户。否则， 它就是特定数据库用户的名字或者是一个前缀+的组名称。

命令行交互¶

在UNIX平台中安装PostgreSQL之后，PostgreSQL会在UNIX系统中创建一个名为"postgres"的系统用户。PostgreSQL的默认用户名和数据库也是"postgres"。

如果是包管理器安装，一般会自动创建这个用户，如果是源代码编译安装，一般也建议创建一个这样的用户。

Linux服务器中的postgresql进程通常是由操作系统的postgres用户来启动的。

如果你连接到一个数据库时，你可以选择以何种PostgreSQL用户名进行连接，如果你不选择，那么默认就是你的当前操作系统账号。如果这样，那么总有一个与操作系统用户同名的PostgreSQL用户账号用于启动服务器，并且通常这个用户都有创建数据库的权限。如果你不想以该用户身份登录，那么你也可以在任何地方声明一个-U选项以选择一个用于连接的PostgreSQL用户名。

角色就相当于岗位：角色可以是经理，助理。

用户就是具体的人：比如陈XX经理，朱XX助理，王XX助理。

在PostgreSQL 里没有区分用户和角色的概念，"CREATE USER" 为 "CREATE ROLE" 的别名这两个命令几乎是完全相同的，唯一的区别是"CREATE USER" 命令创建的用户默认带有LOGIN属性，而"CREATE ROLE" 命令创建的用户默认不带LOGIN属性 (CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not)

PostgreSQL安装后默认用户名和数据库也是"postgres"，默认没有密码。在安装PostgreSQL之后可以以默认用户登录，也可以创建新当用户名。

安装成功后，PostgreSQL 会自动创建一个默认用户（这个属于最高权限的 superuser），名称为 postgres，密码为空。

但是不支持远程登录（报错：psql: fe_sendauth: no password supplied），必须设置密码后才行（普通用户也适用这个规则）。

# 切换到Linux操作系统上的postgre用户
su postgres 

# 登陆: 主机，端口，用户名，密码，使用的数据库
psql  -h localhost -p 54321 -U postgres -w password -d postgres
#
password postgres

-- 创建普通用户
CREATE ROLE david   WITH PASSWORD '123456';　   -- 默认不带LOGIN属性
CREATE USER fengzhao WITH PASSWORD '123456';　　-- 默认具有LOGIN属性

-- 在PostgreSQL 里没有区分用户和角色的概念，"CREATE USER" 为 "CREATE ROLE" 的别名
-- 这两个命令几乎是完全相同的，唯一的区别是"CREATE USER"命令创建的用户默认带有LOGIN属性，而"CREATE ROLE"命令创建的用户默认不带LOGIN属性
-- (CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not)

-- 用户sandy可以登录，角色david不可以登录。
-- 角色就相当于岗位：角色可以是经理，助理。
-- 用户就是具体的人：比如陈XX经理，朱XX助理，王XX助理。

-- PostgreSQL安装后默认用户名和数据库也是"postgres"，默认没有密码。在安装PostgreSQL之后可以以默认用户登录，也可以创建新当用户名。
-- 强烈建议安装后第一件事就是给默认的postgres用户创建一个

-- 官方建议是这样的：

-- 在管理员创建一个具体数据库后，应该为所有可以连接到该数据库的用户分别创建一个与用户名相同的模式，然后，将search_path设置为"$user"(即缺省模式为与用户名相同的模式)。
-- 这样任何当某个用户连接上来后，会默认将查找或者定义的对象都定位到与之同名的模式中。这是一个好的设计架构。

-- 不建议业务项目的表存放在public下：
--  1、是数据安全；
--  2、表存放混乱；不利于后期维护等等。


SELECT rolname from pg_roles ;
SELECT usename from pg_user;  -- 角色david创建时没有分配login权限，所以没有创建用户



create database dbtest;

schema_element
CREATE SCHEMA schema_name [AUTHORIZATION username]   [schema_element [ ... ]]
-- schema_name，创建方案的名称，如果省略，则当前用户名被用作方案名。该名字不能以 pg_ 开头，因为这些名字是为系统目录方案保存的。
-- rolename，拥有该方案的角色的名称。如果省略，默认为执行该命令的角色。只有超级用户可以创建属于其他角色的方案，除了他们自己。
-- schema_element ，定义在方案中创建对象的 SQL 语句。比如 CREATE TABLE、CREATE VIEW、CREATE INDEX 等语句

PostgreSQL 提供了强大的命令行交互功能，我们可以使用 + 关键词来进行操作。我们可以通过查阅文档或 \? 与 help 命令来查看命令详情与帮助信息。其他常用命令如下：

# 查看帮助
help

# 查看 psql 命令详情
\?

# 查看数据库（全部）
\l

# 查看数据库（指定）
\l <database-name>

# 连接数据库
\c <database-name>

# 查看方法
\df

# 查看表（全部）
\d

# 查看表（只看表）
\dt

# 查看表（指定）
\d <table-name>

# 执行 sql 命令
\i <filepath>/<filename>

# 打开拓展视图
\x

# 导出至 CSV
\copy (SELECT * FROM person LEFT JOIN car ON person.car_id = car.id) TO 'path/to/output.csv' DELIMITER ',' CSV HEADER;

# 退出
\q

角色管理¶

PostgreSQL 的权限管理模型，是一种典型的 RBAC（基于角色的权限控制）的实现。其将用户、角色、权限通过此模型管理起来。

前面讲到过，PostgreSQL 中的"用户"概念和"角色"概念实际是等同的，唯一的区别在于"用户"拥有 login 权限，而"角色"拥有 nologin 权限。

PostgreSQL 的权限可以分为两类，"系统权限"和"数据库对象权限"。PostgreSQL 通过角色来进行权限管理，角色可以拥有系统权限和数据库对象权限。并且也可以作为一组权限集合，授予给其他的角色或者用户。

可以通过授予角色/用户相应的系统权限和对象权限来对数据库进行管理。

角色可以拥有属性，属性确定了角色拥有的特权，并且在登录时与客户端认证系统进行交互。常见的角色属性包括：

登录特权，只有具有 LOGIN 属性的角色才能连接数据库。具有 LOGIN 角色的用户可以被看作一个"数据库用户"。
```
CREATE ROLE name LOGIN;
CREATE USER name;
```
超级用户，数据的超级用户可以避开所有的权限检查，只验证登录权限。因此，这是一个很危险的特权，使用时需要特别小心；最好在日常的操作中避免使用超级用户。
```
CREATE ROLE name SUPERUSER;
```
创建数据库，只有明确授权的角色才能够创建数据库（超级用户除外，因为他们可以避开权限检查）。
```
CREATE ROLE name CREATEDB;
```
创建角色，只有明确授权的角色才能够创建其他角色（超级用户除外，因为他们可以避开权限检查）。
```
CREATE ROLE name CREATEROLE;
```

CREATE ROLE  david 
    SUPERUSER|NOSUPERUSER       /*是否超级用户，默认否*/     
    CREATEDB|NOCREATEDB         /*是否能建DB，默认否*/     
    CREATEROLE|NOCREATEROLE     /*是否能建ROLE，默认否*/     
    INHERIT|INHERIT             /*继承*/
    LOGIN|NOLOGIN               /*是否能登录，默认否*/    /*能登陆的role等价于user*/
    REPLICATION|NOREPLICATION   
ENCRYPTED PASSWORD 'david@123';



CREATE ROLE david;　　/**默认不带LOGIN属性**/
CREATE USER sandy;　　/**默认具有LOGIN属性**/
-- 用户sandy可以登录，角色david不可以登录。

SELECT rolname from pg_roles ;
SELECT usename from pg_user;  --角色david创建时没有分配login权限，所以没有创建用户

对象授权¶

-- 其中，privilege_list 权限列表可以是 SELECT、INSERT、UPDATE、DELETE、TRUNCATE等，ALL表示表上的所有权限。
GRANT privilege_list | ALL  ON [ TABLE ] table_name   TO role_name;

-- ALL TABLES IN SCHEMA 表示某个模式中的所有表，可以方便批量授权操作。
GRANT privilege_list | ALL  ON ALL TABLES IN SCHEMA schema_name   TO role_name;

角色成员¶

在现实的环境中，管理员通常需要管理大量的用户和对象权限。为了便于权限管理，减少复杂度，可以将用户进行分组，然后以组为单位进行权限的授予和撤销操作。

为此，PostgreSQL 引入了组（group）角色的概念。具体来说，就是创建一个代表组的角色，然后将该组的成员资格授予其他用户，让其成为该组的成员。

-- 创建一个组角色，按照习惯，组角色通常不具有 LOGIN 特权，也就是不能作为一个用户登录。
CREATE ROLE group_name;


-- 使用与对象授权操作相同的 GRANT 和 REVOKE 语句为组添加和删除成员

#/bin/bash

OPT_BASE=/opt
PGVERSION=15.0
SOURCE_BINARY_FILE=https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v${PGVERSION}/postgresql-${PGVERSION}.tar.gz 
PGBASE=$OPT_BASE/local/pgsql
PGHOME=$OPT_BASE/local/pgsql/$PGVERSION
PGDATA=$OPT_BASE/var/pgsql/$PGVERSION

### 检查是否 root 用户
[ $(id -u) != "0" ] && { echo "${CFAILURE}Error: You must be root to run this script${CEND}"; exit 1; }

### 检查是否存在某个文件，是否存在可执行的二进制文件
if  [ ! -e '/usr/bin/wget' ]; then
    echo "Error: wget command not found. You must be install wget command at first."
    exit 1
fi

if [ -d $PGHOME ]; then
  rm -rf $PGHOME
elif [ ! -d $PGBASE ]; then
  mkdir -p $PGBASE
fi

if [ ! -d $PGDATA ]; then
  mkdir -p $PGDATA
fi

echo "DownLoading PostgreSQL"
wget -P ${OPT_BASE}  ${SOURCE_BINARY_FILE}
md5_sum=$(curl -s https://ftp.postgresql.org/pub/source/v${PGVERSION}/postgresql-${PGVERSION}.tar.gz.md5)


echo "Install PostgreSQL"
cd /opt/
tar zxf $PG_SOFT_TAR -C $PGBASE
mv $PGBASE/pgsql $PGHOME
cp pg-pwfile $PGHOME

echo "Init PostgreSQL"
pushd $PGHOME
./bin/initdb --pgdata="$PGDATA" --auth=ident --auth-host=md5 --encoding=UTF-8 --locale=zh_CN.UTF-8 --username=postgres --pwfile=pg-pwfile
rm -f pg-pwfile
popd

cp pg_hba.conf $PGDATA
cp postgresql.conf $PGDATA
chmod 600 $PGDATA/*.conf

echo "Start PostgreSQL"
$PGHOME/bin/pg_ctl -D $PGDATA -l logfile start
sleep 5
#cp .pgpass ~/
$PGHOME/bin/psql -h localhost -U postgres -d postgres -f pg_init.sql