pg配置基础

pg目录结构

[postgres@base ~]$ tree -L 1 /soft/opt/pg12

/soft/opt/pg12

├── bin

├── include

├── lib

└── share

share 目录存放着 PostgreSQL 文档、 man 、示 件以及一些扩展， include 目录是PostgreSQL ＋＋的 头文文件  bin 目录就 PostgreSQL 的应用程序

PostgreSQL本身是一 构的程序 ，这些应用程序可以分为两类 ：客户端程序和服务器程序，

 

客户端程序

1. 封装 SQL 命令的害户端程序

clusterdb 是SQL CLUSTER 命令的一个封装。 PostgreSQL 是堆表存储的， clusterdb通过索引对数据库中基于堆表的物理文件重新排序 ，它在一定场景下可以节省磁盘访问，加快查询速度。

如：／opt/pgsql/bin/clusterdb -h pghostl -p 1921 -d mydb

reindexdb SQL REINDEX 命令的一个封装 .索引物理文件发生损坏或索引膨胀发生时，可以使用 reindexdb 对指定的表或者数据库重建索引并且删除旧的索引。

如：/opt / pgsql / bin/reindexdb - e - h pghostl -p 1 92 1 - d mydb

vacuumdb 是 PostgreSQL 数据库独有的VACUUM VACUUM FREEZE VACUUM FULL, VACUUM ANALYZE 这几个 SQL 的封 VACUUM 系列命令的封装。VACUUM系列命令的主要职责是对数据的物理文件的垃圾回收， 是PostgreSQL 中非常重要的一系列命令。

如：/opt/pgsql/bin/vacuurndb -h pghostl - p 1921 rnydb

 

/opt/pgsql/bin/createdb - h pghostl -p 1921 newdb ” New database .”

 

/opt/pgsql /bin/dropdb-h pghostl -p 1921 newdb

 

/opt/pgsql/ bin/createuser -h pghostl - p 1921 - c 1 -g pgmonitor --interactive - e - P newuser

 

/opt/pgsql/b n/dropuser -h pghostl  -p 1921 newuser

 

2、备份与恢复的害户端程序

pg_basebackup 取得一个正在运行中的 PostgreSQL 实例的基础备份

pg_dump pg_dumpall 都是以数据库转储方式进行备份的工具。

pg_restore 用来从 pg dump 令创建的非文本格式的备份中恢复数据

 

3、其他客户端程序

oid2name 解析一个 PostgreSQL 数据目录中的 OID 和文件结点

pgbench 是运行基准测试的工具，平常我 可以用它模拟简单的压力测试

pg_config 获取当前安装的 stgreSQL 应用程序的配置参数。

PostgreSQL 包装了 pg_isready 工具用来检测数据库服务器是否已经允许接受连接

pg_receivexlog 可以从一个运行中的实例获取事务日志的流

pg_recvlogical 控制逻辑解码复制槽 已经来自这种复制槽的流数据

psql 是连接 PostgreSQL 数据库的客户端命令行工具

 

服务器程序

服务器程序包括

 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 目录中看到，是指向 postgres 服务器程序的一个软链接

 

初始化数据库

/soft/opt/pg12 /bin/initdb -D /opt/data5555 -X /opt/wal5555 -E UTF8 -U postgres -W

-W：为数据库超级用户创建密码

 

启动和停止数据库服务器

 

/opt / pgsql/bin/pg ctl - D /pgdata/10/data start

或/opt/pgsql/bin/postgres - D /pgdata/10/data/

 

/opt/pgsql/bin/pg ctl - D /pgdata/10/data status

 

opt/pgsql/bin/pg_isready - p 1921

/tmp : l921 - accepting connections 或 /tmp :l 921 - no response

 

smart fast immediate  -ms -mf -mi

I opt/pgsql /bin pg_ctl - D /pgdata/10/data - ms stop

或

kill -sigterm `head- 1 /pgdata/10/data/postmaster.pid`

PostgreSQL 的守护进程 postmaster 的人口函数中注册了信号处理程序，对 SIGINT、SIGTERM、SIGQUIT 的处理方式分别对应 PostgreSQL 种关闭方式 smart、fast、immediate

 

数据库配置基础

 

配置文件的位置

在实例化数据目录之后，在数据目录的根目录下会有 postgresql. conf、postgresql. auto.conf、pg_hba.conf、pg_ident.conf 个配置文件 ，除身份认证以外的数据库系统行为都由postgresqI. conf 配置。

 

pg_hba.conf 是它所在数据库实例的"防火墙” ，文件格式如下：

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]

METHOD

host datab ase 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 opt ons]

 

这些配置看起来复杂， 实际上简单来说每一行的作用就是 ：允许哪些主机可以通过什么连接方式和认证方式通过哪个数据库用户连接哪个数据库。也就是允许 ADDRESS 列的主机通过 TYPE 方式以 METHOD 认证方式通过 USER 用户连接 DATABASE 数据库。

 

1. 连接方式

TYPE 列标识允许的连接方式，可用的值有： local host hostssl hostnossl 说明如下：

 local 配使用 Unix 域套接字的连接 如果没有 TYPE为ocal 的条目则不允许通过Unix 套接字连接

host 匹配使用 TCP/IP 建立的连接，同时匹配 SSL 和非 SSL 连接。默认安装只监听本地环回地址 localhost 的连接，不允许使用 TCP/IP 远程连接，启用远程连接需要修改postgresql.conf中的 listen_addresses 参数

hostssl 匹配必须是使用 SSL的TCP/IP 连接。配置hostssl 有三个前提条件：

1. 客户端和服务端都安装 OpenSSL;

编译 PostgreSQL 的时候指定 configure 数…with-openssl 打开 SSL 支持；

3. postgresql.conf 中配置 ssl =on

hostnossl和 hostssl相反，它只匹配使用非 SSL 的TCP/IP 连接

目标数据库

DATABASE 列标识该行设置对哪个数据库生效

3. 目标用户

USER 列标识该行设置对哪个数据库用户生效；

访问来源

ADDRESS 列标识该行设置对哪个 IP 地址或 IP 地址段生效；

认证方法

METHOD 列标识 客户端 的认证方法，常见的认证方法有 trust、reject、md5、password等

reject 认证方式主要应用在这样的场景中：允许某一网段的大多数主机访问数据库，但拒绝这个网段的少数特定主

md5 和password 认证方式的区别在于 md5 认证方式为双 md5 加密， password 指明文密码，所以不要在非信任网络使用 password 认证方式。

 

 

postgresql.conf 配置文件的文件结构很简单，由多个 configparameter = value 的行组成，“＃”开头的行为注释 value 支持的数据类型有布尔 、整数、浮点数 字符串 枚举，value 的值还支持各种单位，例如 MB GB ms min等， postgresql.conf 文件还支持include和 include_if_exists 指令，并且允许嵌套。

在配置项末尾标记了 “＃ (change requires restart 的配置项是需要重启数据库实例才可以生效的，其他没有标记的配置项只需要 reload 即可生效。

 

1. 全局配置的修改方法

修改全局配置的方法有：

修改 postgresqI. conf 配置文件

使用 vim、nano 类的文本编辑器或者 sed 命令编辑它们

通过 ALTER SYSTEM 命令修改全局配置 例如：

mydb=# ALTER SYSTEM SET listen_addresses ＝‘*’；

通过 ALTER SYSTEM SQL 令修改的全局配置参数，会自动编辑 postgresql.auto.conf文件，在数据库启动时会加载 postgresql.auto .conf 文件，并用它的配置覆盖 postgresql.conf中已有的配置。 这个文件不要手动修改它

启动数据库时进行设置，例如：[postgres@pghostl opt pgsql /b in postgres - D / pgdata /10/data - c port= l922

 

2. 非全局配置的修改方法

设置和重置 Database 级别的配置，例如：

ALTER DATABASE name SET configparameter { TO I = } { value I DEFAULT }

ALTER DATABASE name RESET configuration

设置和重置 Session 级别的配置

通过 SET 设置当前 Session 的配置，例如：

SET configparameter { TO I = } { value | ’ value ’I DEFAULT }

SET con gparameter TO DEFAULT;

更新 pg_settings 视图，例如：

UPDATE `pg_settings` SET setting = new value WHERE name = ’ configparameter’ 

UPDATE `pg_settings` SET setting = reset val WHERE name = ’ configparameter ' 

使用 set_config 函数更新会话配置， 例如

SELECT set_config ( ’ configparameter'， new_value , false) ;

设置和重置 Role 级别的配置，例如

ALTER ROLE name IN DATABASE database_name SET configparameter{ TO I= } { valueI DEFAULT }

ALTER ROLE name IN DATABASE database_name RESET configparameter

 

3、如何查看配置

查询pg_settings 系统表，例如：

SELECT name , setting FROM pg_settings where name ~'xxxx';

SELECT current setting(name) ;

通过 show (show all ）命令查看。

 

4、使配置生效的方法

如果是不需要重启的参数， reload 一次就可以生效

mydb=# SELECT pg_reload_conf();

也可以使用 pg ctl reload 置，命令如下所示：

[root@pghostl ~]# su - postgres

[postgres@pghostl ~]$ /opt/pgsql/bin/pg ctl -D /pgdata/10/data reload

 

允许远程访问数据库

在默认情况下， Pos tgreSQL 实例是不允许通过远程访问数据库的，从其他主机访问数据库端口，将会被拒绝。

netstat -nlt I grep 1921

 

通过以下配置方法，允许从远程访问数据库

1. 修改监昕地址

PostgreSQL 管理监昕地址的配置项为 postgresql.conf 文件中的 listen_addresses=ip或'*'。

2. 修改 pg_hba.conf 文件

[postgres@pghostl ～］ echo “host rnydb pguser 0.0.0.0/0 rnd5”>/pgdata/10/data/pg hba.conf

 

opt/pgsql/bin/pg_ctl - D /pgdata/1 0/data/ reload