通过源码安装PostgresSQL
1.1 下载源码包环境： Centos6.8 64位
yum -y install bison flex readline-devel zlib-devel yum -y groupinstall "Development tools" cd /opt/ wget https://ftp.postgresql.org/pub/source/v10.0/postgresql-10.0.tar.gz tar -xvf postgresql-10.0.tar.gz cd postgresql-10.0 ./configure --prefix=/opt/pg10 --with-pgport=1921 gmake world gmake install world
# 查看版本
/opt/pg10/bin/postgres --version
1.2 设置一个软连接
有时候为了方便工作，会自己写一些shell或者python脚本处理一些定时任务，经常会通过类似/opt/pg9.x这样的全路径调用
一些工具，使用环境也会有一些其他的问题存在，如何尽可能地避免这种麻烦？很简单。
创建一个/opt/pgsql的软连接指向当前版本即可。
[root@fudao_db_cluster_003 ~]# ln -s /opt/pg10 /opt/pgsql [root@fudao_db_cluster_003 ~]# ll /opt/pgsql/ total 16 drwxrwxr-x 2 root root 4096 Jun 24 17:31 bin drwxrwxr-x 4 root root 4096 Jun 24 17:31 include drwxrwxr-x 4 root root 4096 Jun 24 17:31 lib drwxrwxr-x 3 root root 4096 Jun 24 17:31 share [root@fudao_db_cluster_003 ~]#
当版本变更之后，不需要调整大量的脚本，只需要修改这个软连接即可。
1.3 客户端程序和服务端程序
经过上面的安装步骤，已经成功安装了PostgreSQL数据库。
[root@fudao_db_cluster_003 ~]# ll /opt/pgsql/ total 16 drwxrwxr-x 2 root root 4096 Jun 24 17:31 bin drwxrwxr-x 4 root root 4096 Jun 24 17:31 include drwxrwxr-x 4 root root 4096 Jun 24 17:31 lib drwxrwxr-x 3 root root 4096 Jun 24 17:31 share [root@fudao_db_cluster_003 ~]#
share目录存放着PostgreSQL的文档、man、示例文件以及一些扩展。
include目录是PostgreSQL的C、C++的头文件，bin目录就是PistgreSQL的应用程序了。
PostgreSQL本身就是一个C/S架构的程序，这些应用程序可以分为两类：客户端程序和服务器程序，本章先介绍这些应用程序的功能。
并讲解其中比较基础的一部分，其他的会在后续章节详细讲解。
1.3.1 客户端程序
客户端程序也可以分为几大类，下面分别介绍。
1. 封装SQL命令的客户端程序
clusterdb
clusterdb是 SQL CLUSTER命令的一个封装。PostgreSQL是堆表存储的，clusterdb通过索引对数据库基于堆表的物理文件重新排序，
它在一定场景下可以节省磁盘访问，加快查询速度。
举例如下：
[root@fudao_db_cluster_003 ~]# /opt/pgsql/bin/clusterdb -h pghost1 -p 1921 -d mydb
reindexdb
reindexdb是SQL REINDEX命令的一个封装。在索引物理文件发生损坏或者索引膨胀等情况发生时，可以使用redindex命令对指定的表或者
数据库重建索引并且删除旧的索引。
举例如下：
[root@fudao_db_cluster_003 ~]# /opt/pgsql/bin/reindexdb -e -h pghost1 -p 1921 -d mydb
vacuumdb
vacuumdb是PostgreSQL数据库独有的VACUUM、VACUUM FREEZE和VACUUMA FULL， VACUUM ANALYZE 这几个SQL命令的封装。VACUUMA系列命令的主要职责
是对数据库物理文件等的垃圾回收，是PostgreSQL中非常重要的一系列命令。
举例如下：
[root@fudao_db_cluster_003 ~]# /opt/pgsql/bin/vacuumdb -h pghost1 -p 1921 mydb
vacuumlo
vacuumlo用来清理数据库中未引用的大对象。
举例如下：
[root@fudao_db_cluster_003 ~]# /opt/pgsql/bin/vacuumlo -h pghost1 -p 1921 mydb # 笔者没有找到这个命令，可能原因是版本差异
createdb 和 dropdb
它们分别是SQL命令 CREATE DATABASE 和 DROP DATABASE的封装。
例如在名为pghost1的主机，端口为1921的实例中创建一个名为newdb的数据库，并且加上注释，命令如下：
[root@fudao_db_cluster_003 ~]# /opt/pgsql/bin/createdb -h pghost1 -p 1921 newdb "New database"
删除名为newdb的数据库的命令如下所示：
[root@fudao_db_cluster_003 ~]# /opt/pgsql/bin/dropdb -h pghost1 -p 1921 newdb
createuser 和 dropuser
它们分别是SQL命令 CREATE USER 和 DROP USER 的封装。可以通过帮助查看它们的参数说明。
例如创建一个名为newuser的非超级用户，newuser继承自pg_minitor系统角色，只能有一个连接，没有创建数据库的权限，
没有创建用户的权限，并且立即给它设置密码，命令如下：
[root@fudao_db_cluster_003 ~]# /opt/pgsql/bin/createuser -h pghost1 -p 1921 -c 1 -g pg_monitor -D -R -S -P -e newuser
是否为超级用户、是否允许创建数据库、是否允许创建用户这三个权限可以使用--interactive参数提供交互界面，使用更简单，举例如下：
[root@fudao_db_cluster_003 ~]# /opt/pgsql/bin/createuser -h pghost1 -p 1921 -c 1 -g pg_monitor --interactive -D -R -S -P -e newuser
删除名为newuser的用户的命令如下所示：
[root@fudao_db_cluster_003 ~]# /opt/pgsql/bin/dropuser -h pghost1 -p 1921 newuser
2. 备份与恢复的客户端程序
pg_basebackup取得一个正在运行中的PostgreSQL实例的基础备份。
pg_dump 和 pg_dumpall 都是以数据库转储方式进行备份的工具。
pg_restore用来从pg_dump命令创建的非文本格式的备份中恢复数据。
3. 其他客户端程序
ecpg是用于C程序的PostgreSQL嵌入式SQL预处理器。它将SQL调用替换为特殊函数调用，把带有嵌入式SQL语句的C程序转化为普通C代码，输出文件可以被
任何C编译器工具处理。
oid2name:解析一个PostgreSQL数据目录中的OID和文件特点，在文件系统章节会详细介绍。 pgbench:是运行基准测试的工具，平常我们可以用它模拟简单的压力测试。 pg_config:获取当前安装的PostgreSQL应用程序的配置参数。 pg_isready:PostgreSQL包装了pg_isready工具来检测数据库服务器是否已经允许接收连接。 pg_receivewal:可以从一个运行中的实例获取事务日志的流。 pg_recvlogical:控制逻辑解码复制以及来自这种复制曹的流数据。 psql:是连接PostgreSQL数据库的客户端命令行工具，是使用频率非常高的工具。使用psql客户端工具连接数据库的命令如下所示： [root@fudao_db_cluster_003 ~]# /opt/pgsql/bin/psql -h pghost1 -p 1921 -d mydb
其中的参数含义如下：
-h:参数指定需要连接的主机。
-p:参数指定数据库实例的端口。
-d:参数指定连接哪一个库，默认是和连接所使用的用户的用户名同名的数据库。
连接到数据库之后，就进入PostgreSQL的shell界面，如果是用数据库超级用户连接，提示符由数据库名称和"=#"组成，如果是普通用户的数据库用户，提示符则
由数据库名称和"=>"组成。使用"q"或者CRTL+D退出。
1.3.2 服务器程序
服务器程序包括：
initdb:用来创建新的数据库目录。
pg_archivecleanup:是清理PostgreSQL WAL归档文件的工具。 pg_controldata:显示数据库服务器的控制信息，例如目录版本、预写日志和检查点的信息。 pg_ctl:是初始化、启动、停止、控制数据库服务器的工具。 pg_resetwal:可以清除预写日志并且有选择地重复存储在pg_control文件中的一些控制信息。当服务器由于控制文件损坏，pg_resetwal可以作为最后的手段。 pg_rewind:是在master、slave角色发生切换时，将原master通过同步模式恢复，避免重做基础备份的工具。 pg_test_fsync:可以通过一个快速的测试，了解系统使用哪一种预写日志的同步方法(wal_sync_method)最快，还可以在发生I/O问题时提供诊断信息。 pg_test_timing:是一种度量系统记时开销以及确认系统时间绝不会回退的工具。 pg_upgrade:是PostgreSQL的升级工具，在版本升级的章节中会详细介绍。 pg_waldump:用来将预写日志解析为可读的格式。 postgres:是PostgreSQL的服务器程序。 postmaster:可以从bin目录中看到，是指向postgreSQL服务器程序的一个软连接。
1.4 创建数据库实例
在PostgreSQL中一个数据库实例和一组使用相同配置文件和监听端口的数据库集关联，它由数据目录组成，数据目录中包含了所有的数据文件和配置文件。
一台数据库服务器可以管理多个数据库实例，PostgreSQL通过数据目录的位置和这个数据集合实例的端口号引用它。
1.4.1 创建操作系统用户
在创建数据库实例之前要做的第一件事是首先创建一个独立的操作系统用户，也可以称之为本地用户。创建这个账号的目的是为了防止因为应用软件的BUG被攻击这个利用，
对系统造成破坏。它拥有该数据库实例管理的所有数据，是这个数据库实例的超级用户。你可以使用你喜欢的用户名称作为这个数据库实例超级用户，例如：pger等，但通常
我们使用postgres作为这个操作系统超级用户的名字，这个用户将被用来对数据库实例进行start、stop、restart操作。如果使用yum安装，且操作系统中不存在postgres本地用户，
安装程序会自动创建名为postgres的操作系统用户和名为postgres的数据库超级用户，尽管如此，仍然建议在yum安装之前预先手动创建postgres用户。
当一个黑客利用一个软件的BUG进入一台计算机时，他就获得了这个软件运行所使用的用户账号的权限。目前我们不知道PostgreSQL是否有这个的BUG,我们坚持使用非管理员账号运行PostgreSQL
的目的就是为了减少(万一)黑客利用在PostgresSQL发现的BUG对系统造成的可能损害。
[root@fudao_db_cluster_003 ~]# groupadd -g 10000 postgres [root@fudao_db_cluster_003 ~]# useradd -g 10000 -u 10000 postgres
注意事项：
1) 出于安全考虑，这个操作系统用户不能是root或者具有操作系统管理员权限的账号，例如拥有sudo权限的用户。
2) 如果是部署集群，建议配置NTP服务，统一集群中每个节点的操作系统用户的uid个gid，如果集群中某些节点的数据库操作系统用户的uid和gid与其他节点不一致，可以通过groupmod 和 usermod命令
进行修改。例如：
[root@fudao_db_cluster_003 ~]# groupmod -g 20000 postgres You have new mail in /var/spool/mail/root [root@fudao_db_cluster_003 ~]# usermod -g 20000 -u 20000 postgres [root@fudao_db_cluster_003 ~]#
1.4.2 创建数据目录
[root@fudao_db_cluster_003 ~]# mkdir -p /data01/pgdata/10/{data,backups,scripts,archive_wals}
将数据目录的属主修改为我们创建的操作系统用户，并且修改数据目录的权限为0700。修改这一步其实并不需要，因为initdb会回收除PostgreSQL用户外所有用户的访问权限。
但我们应该明确知道数据目录包含所有存储在数据库里的数据，保护这个目录不受未授权的访问非常重要。修改权限的命令如下所示:
[root@fudao_db_cluster_003 ~]# chown -R postgres.postgres /data01/pgdata/10 You have new mail in /var/spool/mail/root [root@fudao_db_cluster_003 ~]# chmod 0700 /data01/pgdata/10/data/ [root@fudao_db_cluster_003 ~]#
1.4.3 初始化数据目录
实例化数据目录使用initdb工具，initdb工具将创建一个新的数据库目录(这个目录包括存放数据库数据的目录)，创建template1和postgres数据库，初始化修改数据库实例的
默认区域和字符集编码。initdb命令的语法如下：
[root@fudao_db_cluster_003 ~]# /opt/pgsql/bin/initdb --help initdb initializes a PostgreSQL database cluster. Usage: initdb [OPTION]... [DATADIR] Options: -A, --auth=METHOD default authentication method for local connections --auth-host=METHOD default authentication method for local TCP/IP connections --auth-local=METHOD default authentication method for local-socket connections [-D, --pgdata=]DATADIR location for this database cluster -E, --encoding=ENCODING set default encoding for new databases --locale=LOCALE set default locale for new databases --lc-collate=, --lc-ctype=, --lc-messages=LOCALE --lc-monetary=, --lc-numeric=, --lc-time=LOCALE set default locale in the respective category for new databases (default taken from environment) --no-locale equivalent to --locale=C --pwfile=FILE read password for the new superuser from file -T, --text-search-config=CFG default text search configuration -U, --username=NAME database superuser name -W, --pwprompt prompt for a password for the new superuser -X, --waldir=WALDIR location for the write-ahead log directory
知道了这些选项的意义，我们开始初始化上一步创建好的数据目录，如下所示：
[root@fudao_db_cluster_003 ~]# su - postgres [postgres@fudao_db_cluster_003 ~]$ /opt/pgsql/bin/initdb -D /data01/pgdata/10/data -W The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. Enter new superuser password: Enter it again: fixing permissions on existing directory /data01/pgdata/10/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: /opt/pgsql/bin/pg_ctl -D /data01/pgdata/10/data -l logfile start [postgres@fudao_db_cluster_003 ~]$
因为我们制定了-W参数，所以在初始化的过程中，initdb工具会要求为数据库超级用户创建密码。在initdb的输出中可以看到系统自动创建了 template1数据库和postgres
数据库，template1是生成其他数据库的模板，postgres数据库是一个默认数据库，用于给用户、工具或者第三方应用提供默认数据库。输出的最后一行还告诉你了如何启动
刚才初始化的数据库。
需要注意一点的是：不要在将要初始化的数据目录中手动创建任何文件，如果数据目录中已经有文件，会有提示错误信息。这样做的目的是防止无意中覆盖已经存在的数据目录。
除了使用initdb来初始化数据目录，还可以使用pg_ctl工具进行数据库目录的初始化。用法如下：
[postgres@fudao_db_cluster_003 ~]$ /opt/pgsql/bin/pg_ctl init -D /data01/pgdata/10/data -c "-W"
1.5 启动和停止数据库服务器
在使用数据库服务器之前，必须先启动数据库服务器。可以通过service方式、PostgreSQL的命令行工具启动或者停止数据库。
pg_ctl是Postgres中初始化数据目录，启动、停止、重启、重载数据库服务，或者查看数据库服务状态的工具。执行pg_ctl需要操作系统用户su - postgres。
1、启动数据库
[root@fudao_db_cluster_003 ~]# su - postgres [postgres@fudao_db_cluster_003 ~]$ /opt/pgsql/bin/pg_ctl -D /data01/pgdata/10/data start waiting for server to start....2019-06-25 18:34:23.558 CST [17548] LOG: listening on IPv6 address "::1", port 1921 2019-06-25 18:34:23.558 CST [17548] LOG: listening on IPv4 address "127.0.0.1", port 1921 2019-06-25 18:34:23.625 CST [17548] LOG: listening on Unix socket "/tmp/.s.PGSQL.1921" 2019-06-25 18:34:23.733 CST [17549] LOG: database system was shut down at 2019-06-25 18:18:17 CST 2019-06-25 18:34:23.766 CST [17548] LOG: database system is ready to accept connections done server started [postgres@fudao_db_cluster_003 ~]$
2、查看数据库状态
[postgres@fudao_db_cluster_003 ~]$ /opt/pgsql/bin/pg_ctl -D /data01/pgdata/10/data status pg_ctl: server is running (PID: 17548) /opt/pg10/bin/postgres "-D" "/data01/pgdata/10/data" [postgres@fudao_db_cluster_003 ~]$ 还可以使用pg_isready工具来验证数据库服务器是否已经接受连接： [postgres@fudao_db_cluster_003 ~]$ /opt/pgsql/bin/pg_isready -p 1921 /tmp:1921 - accepting connections [postgres@fudao_db_cluster_003 ~]$
3、停止数据库
使用pg_ctl停止数据库的命令为：
[postgres@fudao_db_cluster_003 ~]$ /opt/pgsql/bin/pg_ctl -D /data01/pgdata/10/data/ stop 2019-06-25 18:40:10.989 CST [17548] LOG: received fast shutdown request waiting for server to shut down....2019-06-25 18:40:11.026 CST [17548] LOG: aborting any active transactions 2019-06-25 18:40:11.027 CST [17548] LOG: worker process: logical replication launcher (PID 17555) exited with exit code 1 2019-06-25 18:40:11.027 CST [17550] LOG: shutting down 2019-06-25 18:40:11.153 CST [17548] LOG: database system is shut down done server stopped [postgres@fudao_db_cluster_003 ~]$
1.6 数据库配置基础
在一个数据库实例中，有些配置会影响到整个实例，我们称之为全局配置；有些配置只对一个数据中的单个Database生效，或只对当前会话或者某个
数据库用户生效，这一类的配置我们成为非全局配置。
postgreSQL有两个重要的全局配置文件：postgresql.conf和 pg_hba.conf。它们提供了很多可配置的参数，这些参数从不同层面影响着数据库系统的行为，
postgresql.conf配置文件主要负责配置文件位置、资源限制、复制集群等，pg_hba.conf文件则负责客户端的连接和认证。这两个文件都位于初始化数据目录中。
1.6.1 配置文件的位置
在实例初始化数据目录后，在数据目录的根目录下会有postgresql.conf、postgresql.auto.conf、pg_hba.conf和pg_ident.conf这几个配置文件。
除去身份认证以外的数据系统行为都由postgresql.conf文件配置。
[postgres@fudao_db_cluster_003 ~]$ ll /data01/pgdata/10/data/ total 56 drwx------ 5 postgres postgres 38 Jun 25 18:18 base drwx------ 2 postgres postgres 4096 Jun 26 09:58 global drwx------ 2 postgres postgres 6 Jun 25 18:18 pg_commit_ts drwx------ 2 postgres postgres 6 Jun 25 18:18 pg_dynshmem -rw------- 1 postgres postgres 4513 Jun 25 18:18 pg_hba.conf -rw------- 1 postgres postgres 1636 Jun 25 18:18 pg_ident.conf drwx------ 4 postgres postgres 65 Jun 26 10:02 pg_logical drwx------ 4 postgres postgres 34 Jun 25 18:18 pg_multixact drwx------ 2 postgres postgres 17 Jun 26 09:57 pg_notify drwx------ 2 postgres postgres 6 Jun 25 18:18 pg_replslot drwx------ 2 postgres postgres 6 Jun 25 18:18 pg_serial drwx------ 2 postgres postgres 6 Jun 25 18:18 pg_snapshots drwx------ 2 postgres postgres 6 Jun 26 09:57 pg_stat drwx------ 2 postgres postgres 60 Jun 26 10:19 pg_stat_tmp drwx------ 2 postgres postgres 17 Jun 25 18:18 pg_subtrans drwx------ 2 postgres postgres 6 Jun 25 18:18 pg_tblspc drwx------ 2 postgres postgres 6 Jun 25 18:18 pg_twophase -rw------- 1 postgres postgres 3 Jun 25 18:18 PG_VERSION drwx------ 3 postgres postgres 58 Jun 25 18:18 pg_wal drwx------ 2 postgres postgres 17 Jun 25 18:18 pg_xact -rw------- 1 postgres postgres 88 Jun 25 18:18 postgresql.auto.conf -rw------- 1 postgres postgres 22764 Jun 25 18:18 postgresql.conf -rw------- 1 postgres postgres 53 Jun 26 09:57 postmaster.opts -rw------- 1 postgres postgres 89 Jun 26 09:57 postmaster.pid [postgres@fudao_db_cluster_003 ~]$
1.6.2 pg_hba.conf
pg_hba.conf是它所在数据实例的"防火墙"，文件格式如下:
[postgres@fudao_db_cluster_003 ~]$ cat /data01/pgdata/10/data/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 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust [postgres@fudao_db_cluster_003
这些配置看起来复杂，实际上简单来说每一行的作用就是:允许哪些主机可以通过什么方式和认证通过哪个数据库用户连接哪个数据库。
也就是允许ADDRESS列的主机通过TYPE方式以METHOD认证方式通过USER用户连接DATABASE数据库。
1. 连接方式
TYPE 列标识允许的连接方式，可用的值有：local、host、hostssl、hostnossl,说明如下： local: 匹配使用Unix域套接字的连接。如果没有TYPE为local的条目则不允许通过Unix域套接字连接。 host: 匹配使用TCP/IP建立的连接，同时匹配SSL和非SSL连接。默认安装只监听本地换回地址localhost的连接，不允许使用TCP/IP远程连接，启用远程连接需要修改 postgresql.conf中的listen_address参数 hostssl: 匹配必须是使用SSL的TCP/IP连接。配置hosts1有三个前提条件： 1)、客户端和服务端都安装了OpenSSL； 2)、编译PostgreSQL的时候，指定了configure --with-openssl打开SSL支持； 3)、在postgresql.conf中配置ssl = on。
hostnossl：和hostssl相反，它只匹配使用非SSL的TCP/IP连接。
2. 目标数据库
DATABASE 列标识改行设置对那个数据库生效；
3. 目标用户
USER列标识该行设置对哪个IP地址或IP地址段生效；
4. 访问来源
ADDRESS列标识该行设置对哪个IP地址或IP地址段生效；
5. 认证方法
METHOD列标识客户端的认证方法，常见的认证方法有trust、reject、md5和password等。
reject认证方式主要应用在这样的场景中: 允许某一网段的大多数主机访问数据库，但是拒绝这一网段的少数特定主机。
md5和password认证方式的区别在于md5认证方式为双重md5加密，password指明文密码，所以不要在非信用网络使用password认证方式。
scram-sha-256是PostgreSQL 10中新增的基于SASL的认证方式，是PostgreSQL目前提的最安全的认证方式。使用scram-sha-256认证方式
不支持旧版本的客户端。如果使用PostgreSQL 10 以前的客户端连接数据库，会提示错误。
1.6.3 postgresql.conf
posgtesql.conf配置文件的文件结构很简单，由多个configparameter = value 形式的行组成，"#"开头的行为注释。value支持的数据类型有
布尔、整数、浮点数、字符串、枚举，value的值还支持各种单位，例如:MB、GB、和ms、min、d等。postgres.conf文件还支持include和include_if_exists指令，并且允许嵌套。
1. 全局配置的修改方法
修改全局配置的方法有：
1) 修改postgresql.conf配置文件
2) 使用vim命令编辑
3) 通过alter system 命令修改全局配置。
[postgres@fudao_db_cluster_003 ~]$ /opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -w postgres psql (10.0) Type "help" for help. postgres=# alter system set listen_addresses = '*'; ALTER SYSTEM postgres=# q [postgres@fudao_db_cluster_003 ~]$
通过alter system SQL命令修改的全局配置参数，会自动编辑postgres.auto.conf文件，在数据库启动时会加载postgresql.auto.conf文件，并用它的配置覆盖postgresql.conf
中已经有的配置。这个文件不要手工修改他。
2. 非全局配置的修改方法
1) 设置和重置Database级别的配置，例如：
alter database name set configparameter {TO | = } {value | DEFAULT}
alter database name reset configureation
2) 设置和重置Session级别的配置。
3) 通过SET 命令设置当前Session的配置，例如：
set configparameter {TO | = } {value | 'value' | DEFAULT} set configparameter TO DEFAULT ;
4). 更新pg_settings视图,例如：
update `pg_settings` set setting = new_value where name = 'configparameter'; update `pg_settings` set setting = reset_val where name = 'configparameter';
5). 使用set_config函数更新会话配置，例如：
select set_config('configparameter', new_value, false);
6). 设置和重置Role级别的配置，例如：
ALTER ROLE name IN DATABASE database_name SET configparameter {TO | = } {value| DEFAULT } ALTER ROLE name IN DATABASE database_name RESET configparameter;
3. 如何查看配置
查询pg_settings系统表，例如：
select name,setting from pg_settings where name = 'xxx'; select current_setting(name);
通过show all命令查看。
4. 使配置生效的方法
如果是不需要重启的参数，reload一次就可以生效，命令如下所示：
select pg_reload_conf(); [postgres@fudao_db_cluster_003 ~]$ /opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 psql (10.0) Type "help" for help. postgres=# select pg_reload_conf(); 2019-06-26 14:19:17.379 CST [19199] LOG: received SIGHUP, reloading configuration files pg_reload_conf ---------------- t (1 row) postgres=#
也可以使用pg_ctl命令reload配置，命令如下所示：
[postgres@fudao_db_cluster_003 ~]$ /opt/pgsql/bin/pg_ctl -D /data01/pgdata/10/data reload 2019-06-26 14:20:43.709 CST [19199] LOG: received SIGHUP, reloading configuration files server signaled [postgres@fudao_db_cluster_003 ~]$
1.6.4 允许远程访问数据库
1. 修改监听地址
PostgreSQL管理监听地址的配置项为postgresql.conf文件中的listen_addresses。默认安装只监听本地环回地址: localhost的连接，不允许使用TCP/IP建立远程连接。
启用远程连接需要修改postgresql.conf中的listen_addresses参数。用文本编辑器编辑这个文件。
找到名为listen_addresses的配置项，如下所示：
[postgres@fudao_db_cluster_003 ~]$ vim /data01/pgdata/10/data/postgresql.conf #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) #port = 1921 # (change requires restart) max_connections = 100 # (change requires restart) 关于listen_addresses参数的4行注释的含义如下： 1) what IP address(es) to listen on : 监听什么IP地址？也就是允许哪些IP地址访问，可以是一个IP，也可以是多个IP地址。 2) comma-separated list of addresses ：以逗号分割地址列表。 3) defaults to 'localhost' ; use '*' for all : 使用'*' 允许所有IP地址监听。 4) change requires restart：修改这个参数需要重启数据库。 去掉listen_addresses这一行的注释"#"，并把它替换为"*"，即允许所有地址访问数据库，如下所示： listen_addresses = '*'
修改完毕后，重启数据库生效。
[postgres@fudao_db_cluster_003 ~]$ /opt/pgsql/bin/pg_ctl -D /data01/pgdata/10/data restart waiting for server to shut down....2019-06-26 14:44:00.145 CST [19619] LOG: received fast shutdown request 2019-06-26 14:44:00.182 CST [19619] LOG: aborting any active transactions 2019-06-26 14:44:00.183 CST [19619] LOG: worker process: logical replication launcher (PID 19626) exited with exit code 1 2019-06-26 14:44:00.183 CST [19621] LOG: shutting down 2019-06-26 14:44:00.292 CST [19619] LOG: database system is shut down done server stopped waiting for server to start....2019-06-26 14:44:00.382 CST [19687] LOG: listening on IPv4 address "0.0.0.0", port 1921 2019-06-26 14:44:00.382 CST [19687] LOG: listening on IPv6 address "::", port 1921 2019-06-26 14:44:00.448 CST [19687] LOG: listening on Unix socket "/tmp/.s.PGSQL.1921" 2019-06-26 14:44:00.549 CST [19688] LOG: database system was shut down at 2019-06-26 14:44:00 CST 2019-06-26 14:44:00.583 CST [19687] LOG: database system is ready to accept connections done server started [postgres@fudao_db_cluster_003 ~]$
2. 修改完pg_hba.conf文件
修改监听地址之后，还需要修改pg_hba.conf文件，回答pg_hba.conf的问题：允许哪些主机可以通过什么连接方式和认证方式通过哪个数据库用户连接那个数据库？
假设我们允许所有主机通过TCP/IP建立的连接，同时匹配SSL和非SSL连接，通过md5口令认证，使用pguser用户，连接mydb数据库,那么我们只需要在pg_hba.conf文件中添加一行，如下：
[postgres@fudao_db_cluster_003 ~]$ echo "host mydb pguser 0.0.0.0/0 md5" >>/data01/pgdata/10/data/pg_hba.conf
修改pg_hba.conf文件之后需要reload使他生效，如下所示：
[postgres@fudao_db_cluster_003 ~]$ /opt/pgsql/bin/pg_ctl -D /data01/pgdata/10/data reload server signaled 2019-06-26 14:49:12.139 CST [19687] LOG: received SIGHUP, reloading configuration files [postgres@fudao_db_cluster_003 ~]$