PostgreSql13 安装配置与远程连接

源码安装(Centos)

# 以下命令需要在root用户下执行
wget https://ftp.postgresql.org/pub/source/v13.0/postgresql-13.0.tar.bz2  # 源码下载
tar xjvf postgresql-13.0.tar.bz2 # 解压至当前文件夹
cd postgresql-13.0
./configure --prefix=/usr/local/pgsql13 --with-blocksize=32 --with-wal-blocksize=32
## 其中 --prefix 表示安装至对应目录。后面两个配置是提交较大数据库的I/O性能 指定数据块为32KB,WAL日志快为32KB

make && make install # 安装
# 系统中增加postgres 用户
adduser postgres
passwd postgres # 为postgres用户设置密码
mkdir /data/postgres-data #创建数据库目录
chown -R postgres:postgres /data/postgres-data

编译安装常见问题

默认情况下安安装会用到数据库压缩功能，这个功能需要依赖第三方zlib库的支持。在configure编译的时候如果出现configure: error: zlib library not found。需要先安装对应的zlib库

ubuntu20 安装zlib库
1. apt-cache search zlib | grep dev 查找对应的zlib库
2. apt-get install zlib1g zlib1g-dev 安装zlib库

psql中如果想要方面的使用上下键翻阅查找历史命令，需要安装readline开发包。编译的时候如果系统没有此开发包会出现configure: error: readline library not found

ubuntu20 安装readline开发包

apt-get install libreadline-dev

环境变量配置

将postgres 的安装目录添加至环境变量中。

如果需要系统中别的用户使用，则添加至 /etc/profile 中

vim /etc/profile

  export PATH=/usr/local/pgsql13/bin:$PATH
  export LD_LIBRARY_PATH=/usr/local/pgsql13/lib:$LD_LIBRARY_PATH
  # 保存退出

# 重载环境变量
source /etc/profile
# 切换至刚刚的postgres用户
su - postgres
# 为postgres用户添加相关环境变量
vim .bashrc

  # 增加PGDATA环境变量
  export PGDATA=/data/postgres-data
  export PGPORT=5432 # 如果修改了监听端口则需要同步、因为pgsql createdb 等命令会默认使用此环境变量
  # 保存退出 :wq

source .bashrc
initdb  # 初始化数据库 /usr/local/pgsql13/bin/initdb -D /data/postgres-data
pg_ctl start -D $PGDATA # 启动数据库
pg_ctl -D /data/postgres-data/ -l logfile.log start # 指定日志输出路径
pg_ctl stop -D $PGDATA # 关闭数据库 -m [smart,fast,immediate] smart:等所有连接终止后，关闭数据库; fast: 快速关闭数据库，断开客户端的连接，让已有的失误回滚。immediate: 立即关闭数据库，想当与进程停止，直接退出，下次启动数据库需要进行恢复。
createdb test_db # 创建test_db 数据库，默认当前系统登录的postgres用户
psql -p 54322 -h localhost # 连接端口号为54322的PostgreSQL 和mysql 差不多
psql -l # 查看数据库
psql test_db # 进入数据库 类似于 mysql进入数据库终端,并使用 use test_db
psql # 进入数据库

# 以下是使用psql之后的终端
postgres=# create database test2_db;
CREATE DATABASE
postgres=# \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
 test1_db  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 test2_db  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 test_db   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)

安全设置

PostgreSQL用户密码默认是使用md5方式，推荐使用scram-sha-256认证口令的方式，需要在$PGDATA/postgresql.conf文件中修改 password_encryption = scram-sha-256重启服务之后,通过psql命令进入客户端使用\password更新密码即可。

默认pg只允许本地访问的，如果需要开启远程访问需要修改$PGDATA/postgresql.conf文件中的listen_addresses='*'

安装完成之后，先使用psql已默认postgres用户进入数据库终端，设置postgres的密码，使用\password命令设置(更新)当前登录用户的密码。

postgres=# \password
Enter new password:
Enter it again:
postgres=#

超级用户postgres类似于mysql中的root用户密码设置完成之后，需要在 $PGDATA/pg_hba.conf中修改访问控制。

认证方法

方法	说明
trust	无条件的允许连接、此方法允许任何可以与Postgres数据库服务器连接的用户以任意Postgres数据库用户身份进行连接，不需要口令或其他认证
reject	无条件的拒绝连接。reject行可以阻止一个特定的主机连接，相当于设置了一个黑名单
scram-sha-256(推荐)	执行SCRAM-SHA-256认证来验证用户的口令
md5	要求客户端提供一个MD5加密的口令进行认证
password	要求客户端提供一个未加密的口令进行认证。(不推荐使用)
gss	用GSSAPI认证用户。只有在进行TCP/IP连接是才能使用
sspi	用SSPI认证用户，Windows专用
krb5	用Kerberos V5认证用户，只有在进行TCP/IP连接是才能使用
ident	通过练习客户端的ident服务器获取客户端操作系统的用户名，并检查是否匹配被请求的数据库用户名。
peer	数据库端允许客户端上的特定操作系统用户连接到数据库。
ladp	使用LDAP服务器认证
radius	RADIUS服务器认证
cert	用SSL客户端证书认证
pam	用操作系统提供的可插入认证模块服务(PAM)认证

`pg_hba.conf` 配置案列

默认PostgreSQL数据库只能本地连接，远程连接需要修改$PGDATA/postgresql.conf文件中的listen_addresses配置项

允许所有地址通过TCP/IP使用密码进行连接

# IPv4 local connections:
host    all             all             0.0.0.0/0                md5

允许 192.168.x.x 主机的用户通过口令连接到任意数据库。

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.0.0/16         scram-sha-256

允许来自任意具有 IP 地址192.168.93.x 的主机上任意用户以 ident 为该连接所报告的相同用户名连接到数据库 “postgres”（通常是操作系统用户名）。

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.93.0/24         ident

允许主机:192.168.5.213的任意用户使用密码连接到数据库postgres。

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.5.213/32        scram-sha-256               md5

允许主机68hub.com上的任意用户连接到任意数据库

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             .68hub.com            scram-sha-256

pg_hba.conf文件修改之后需要重启数据库实例：pg_ctl restart。

psql 相关命令

\? # 查看命令文档

\d # 列出当前数据库中所有表(默认public schema下的表)
\d [pattern] # 显示每个匹配“pattern”(表、视图、索引、序列)的信息
\d table_name  # 显示table_name 表的结构定义
\d table_pkey # 显示table_pkey的索引信息
\d table_* # 可以使用通配符 * ? 等匹配
\d+ # 命令显示更加详细的信息

\dn # 列出所有schema (模式)
\db # 显示所有的表空间
\du \dg  # 列出数据库中所有角色或用户
\dp \z # 显示表的权限分配情况

\timing on # 开启记录sql语句执行的时间

\c database_name # 切换数据库,类似于mysql use database_name;
\encoding utf8 # 指定客户端字符集
\pset border 2 # 格式化输出内容。输出带有外边框的查询数据
\pset border 0 # 输出不带边框的查询数据
\o blog.68hub.com # 在当前session中将SQL命令输出值test.txt(blog.68hub.com) 文件中
\x # 将结果从按行展示修改成按列展示
\i <文件名> # 执行外部文件中的SQL命令 psql -x -f text.sql 同理
\echo  #输出信息

\set AUTOCOMMIT off # 关闭当前session中的自动提交功能

\set ECHO_HIDDEN <on|off> # 将快捷命令实际执行的详细sql显示出来。 启动 psql -E 同理

连接远程PostgreSQL服务器： psql -h blog.68hub.com -U username -p 5432 -W -d dbname