您当前的位置：首页 > 学海无涯 > 应用搭建网站首页应用搭建
Centos7安装Postgresql并安装FDW、PostGIS组件
发布时间：2021-04-13作者：♂逸風★淩軒
primary_conninfo = 'host=192.168.100.103 port=5432 user=replica password=123456' recovery_target_timeline = latest # 默认 max_connections = 120 # 大于等于主节点，正式环境应当重新考虑此值的大小 hot_standby = on max_standby_streaming_delay = 30s wal_receiver_status_interval = 10s hot_standby_feedback = on
⑦重新启动从节点
systemctl start postgresql-12
4、验证主从
方法一：
登录主节点数据库执行如下命令
su postgres psql select client_addr,sync_state from pg_stat_replication;
结果如下：
postgres=# select client_addr,sync_state from pg_stat_replication; client_addr | sync_state ---------------+------------ 192.168.100.104 | async (1 row)
说明104服务器是从节点，在接收流，而且是异步流复制
方法二：
在主节点与从节点分别执行如下命令
ps -ef | grep postgres
可以看到主节点有wal receiver进程
postgres 2638 2606 0 17:12 ? 00:00:00 postgres: walsender replica 192.168.100.104(51504) streaming 0/7000660
可以看到从节点有wal receiver进程
postgres 1960 1953 0 17:12 ? 00:00:02 postgres: walreceiver streaming 0/7000660
5、主备切换（主库出现故障时）
当主库出现故障时，我们需要将备库提升为主库进行读写操作。
0、在pg12之前我们一般使用2种方式：
1）pg_ctl 方式: 在备库主机执行 pg_ctl promote shell 脚本
2）触发器文件方式: 备库配置 recovery.conf 文件的 trigger_file 参数，之后在备库主机上创建触发器文件
1、pg12开始新增了一个pg_promote()函数，让我们可以通过SQL命令激活备库。
pg_promote()语法：
pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60)
两个参数：
wait: 表示是否等待备库的 promotion 完成或者 wait_seconds 秒之后返回成功，默认值为 true。
wait_seconds: 等待时间，单位秒，默认 60
2、切换举例：
1）主库操作：关闭主库，模拟主库故障：
systemctl stop postgresql-12
2）备库操作：激活备库：
su postgres psql select pg_promote(true,60);
4、验证：
/usr/pgsql-12/bin/pg_controldata /app/pgsql/data
主备库英文显示如下：
Database cluster state: in production
Database cluster state: in archive recovery
主备库中文显示如下：
数据库簇状态: 在运行中
数据库簇状态: 正在归档恢复
6、原主库修复后降为备库（其实大部分操作与建立备库时一样，只是修改几个IP地址）
1、新主库(104)：
# vi /app/pgsql/data/pg_hba.conf
host replication replica 192.168.100.103/32 trust
# systemctl restart postgresql-12
2、新备库(103)
# systemctl stop postgresql-12
# su - postgres
# rm -rf /app/pgsql/data/* # cd /app/pgsql/data # pg_basebackup -h 192.168.100.104 -p 5432 -U replica -Fp -Xs -Pv -R -D /app/pgsql/data # vi standby.signal
# vi standby.signal
# 添加
standby_mode = 'on'
vi /app/pgsql/data/postgresql.conf
primary_conninfo = 'host=192.168.100.104 port=5432 user=replica password=123456'
# systemctl start postgresql-12
7、再次将老主库切回主库
1、当前备库：
> 停止服务
> 删除“/app/pgsql/data/standby.signal”文件
> 启动服务
2、当前主库：
> 停止服务
> 以“postgres”用户创建“/app/pgsql/data/standby.signal”文件，添加内容：standby_mode = 'on'
> 启动服务
8、备份文件的定时清理
比如你配置了archive_mode=on，但是没有配置archive_command，那么xlog文件会一直堆积（pg_wal写完后，会写.ready，但是由于没有配置archive_command，也就是说不会触发归档命令，所以一直都不会写.done）。
要配置备用服务器使用pg_archivecleanup，将下列代码放入recovery.conf配置文件中：
archive_cleanup_command = 'pg_archivecleanup archivelocation %r'
主服务器则为以下：
当用作独立程序时，所有逻辑上在oldestkeptwalfile之前的WAL文件都将从archivelocation中移除。 在这个模式中，如果你声明一个.backup文件名， 那么只有文件前缀将被用作oldestkeptwalfile。 这允许你无误的删除所有在一个特定基础备份之前归档的WAL文件。
ARCHIVEDIR='/data/pg_archive' PGDATA='/var/lib/pgsql/12/data' #3 1. 读取控制文件，找到哪个文件是可以被清理的 #/usr/pgsql-12/bin/pg_controldata $PGDATA Latest checkpoint location: 1/E000028 Prior checkpoint location: 1/D18C068 Latest checkpoint's REDO location: 1/E000028 Latest checkpoint's REDO WAL file: 00000001000000010000000E ## 表示00000001000000010000000E之前的pg_wal文件可以删除 (pg10以前的叫做pg_xlog) #/usr/pgsql-12/bin/pg_archivecleanup -d $PGDATA/pg_wal 00000001000000010000000E
三、安装插件
1、安装PostGIS
(1)安装yum包
yum install -y postgis30_12.x86_64 postgis30_12-client.x86_64 postgis30_12-debuginfo.x86_64 postgis30_12-devel.x86_64 postgis30_12-docs.x86_64 postgis30_12-gui.x86_64 postgis30_12-utils.x86_64
(2)执行sql
---- Enable PostGIS (as of 3.0 contains just geometry/geography) CREATE EXTENSION postgis; ---- enable raster support (for 3+) CREATE EXTENSION postgis_raster; ---- Enable Topology CREATE EXTENSION postgis_topology; ---- Enable PostGIS Advanced 3D ---- and other geoprocessing algorithms ---- sfcgal not available with all distributions CREATE EXTENSION postgis_sfcgal; ---- fuzzy matching needed for Tiger CREATE EXTENSION fuzzystrmatch; ---- rule based standardizer CREATE EXTENSION address_standardizer; ---- example rule data set CREATE EXTENSION address_standardizer_data_us; ---- Enable US Tiger Geocoder CREATE EXTENSION postgis_tiger_geocoder;
2、安装FDW
---- 1. 安装 postgres_fdw 扩展 create extension postgres_fdw; ---- 2. 创建 foreign server 外部服务器 ----定义一个名为 foreign_server 的外部服务， options 设置远程pg数据源连接选项，用户名、密码、端口 CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', dbname 'postgres', port '5432'); SELECT * FROM pg_foreign_server; ALTER SERVER foreign_server OPTIONS (SET host '127.0.0.1'); ---- 3. 创建外部服务器用户映射 CREATE USER MAPPING FOR postgres SERVER foreign_server OPTIONS (user 'gold_fdw', password '密码'); ---- 4. 创建外部表 CREATE foreign TABLE user_operator_permission ( "user_id" int8 NOT NULL, "terminl_code" text COLLATE "pg_catalog"."default" NOT NULL, "disabled" bool NOT NULL, "created_time" int8 NOT NULL ) server foreign_server options (schema_name 'public',table_name 'user_operator_permission');
3、修改默认模式
ALTER ROLE 用户名(例如postgres) SET search_path= gold_cluster；
四、设置只读权限
alter user readonly set default_transaction_read_only=on; GRANT USAGE ON SCHEMA public to readonly; GRANT USAGE ON SCHEMA member to readonly; GRANT USAGE ON SCHEMA merchant to readonly; GRANT USAGE ON SCHEMA orders to readonly; grant select on all tables in schema public to gold_cx;
关键字词：centos,Postgresql
下一篇：rook-ceph部署使用说明