PostgreSQL FDW 安装与部署

#PostgreSQL #FDW #安装与部署

1 安装数据库

2 环境信息

主机名称	IP地址	FDW 角色
node1	192.168.10.159	server
node2	192.168.10.193	client

3 操作系统准备

3.1 node1

3.1.1 配置hosts

cat >>  /etc/hosts << EOF
192.168.10.159 node1
192.168.10.193 node2
EOF

3.2 node2

3.2.1 配置hosts

cat >>  /etc/hosts << EOF
192.168.10.159 node1
192.168.10.193 node2
EOF

3.2.2 Install postgres_fdw

postgres>
cd /soft/postgresql-12.12/contrib/postgres_fdw

编译

postgres> make

安装

make install

4 数据库准备

4.1 node1

4.1.1 创建用户

postgresql> psql -U postgres -d testdb
CREATE USER sakila PASSWORD 'sakila' SUPERUSER;

4.1.2 创建数据库

postgresql> psql -U sakila -d testdb 
create  database testdb;  #已创建可跳过。

4.1.3 准备测试数据

参考 PostgreSQL Sakila示例库。

上传postgres-sakila-db文件夹到/soft
更改脚本属主和属主

root> chown postgres:postgres /soft/  -R

导入数据

psql -U sakila -d testdb -f /soft/postgres-sakila-db/postgres-sakila-schema.sql 
psql -U sakila -d testdb -f /soft/postgres-sakila-db/postgres-sakila-insert-data.sql

删除数据

psql -U sakila -d testdb -f /soft/postgres-sakila-db/postgres-sakila-drop-objects.sql

4.1.4 配置pg_hba文件

pg fdw服务器端，需要配置hba文件，允许以scram-sha-256方式远程访问。

postgres>
vi $PGDATA/pg_hba.conf
--------------------input------------------------------
host    all             all             192.168.10.0/24         md5

4.1.5 查看建表语句

postgres>
pg_dump -U sakila -d testdb -s -t country -f /tmp/country.sql
cat /tmp/country.sql
--------------------------------------output:-------------------------------
CREATE TABLE public.country (
    country_id integer ,
    country character varying(50),
    last_update timestamp
);
postgres>
pg_dump -U sakila -d testdb -s -t city -f /tmp/city.sql
cat /tmp/city.sql
--------------------------------------output:-------------------------------
CREATE TABLE public.city (
    city_id integer DEFAULT,
    city character varying(50) 
    country_id smallint ,
    last_update timestamp
);

4.1.6 配置数据库日志

cat >> $PGDATA/postgresql.conf  << EOF
log_destination = 'csvlog'         
logging_collector = on
log_directory = 'pg_log' ##$PGDATA/pg_log           
log_filename = 'postgresql-%Y-%m-%d'  
log_truncate_on_rotation = off
log_rotation_age = 1d               
log_rotation_size = 0   
log_error_verbosity = verbose 
log_statement = all
EOF

4.1.7 重启数据库生效

pg_ctl restart -D $PGDATA

4.2 node2

4.2.1 配置数据库日志

cat >> $PGDATA/postgresql.conf  << EOF
log_destination = 'csvlog'         
logging_collector = on
log_directory = 'pg_log' ##$PGDATA/pg_log           
log_filename = 'postgresql-%Y-%m-%d'  
log_truncate_on_rotation = off
log_rotation_age = 1d               
log_rotation_size = 0   
log_error_verbosity = verbose 
log_statement = all
EOF

4.2.2 安装postgres_fdw 扩展

psqlgres>psql -U postgres -d testdb
CREATE EXTENSION postgres_fdw;

4.2.3 重启数据库生效

pg_ctl restart -D $PGDATA

5 配置FDW（nod2）

5.1 创建用户并且授权

postgres> psql -U postgres -d testdb
testdb=# CREATE USER sakila_fdw PASSWORD 'sakila' SUPERUSER;

5.2 创建fdw服务器

postgres> psql -U sakila_fdw -d testdb
testdb=# 
CREATE SERVER pg_fdw_server FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'node1',port '1922',dbname 'testdb');
GRANT USAGE ON FOREIGN SERVER pg_fdw_server TO sakila_fdw;

5.3 配置远程执行计划

PostgreSQL的FDW支持获取外部表的统计信息来估计查询的计划树的功能，一些FDW扩展使用了这些功能，如postgres_FDW、mysql_FDW、tds_FDW和jdbc2_FDW。
如果使用ALTER SERVER命令将use_remote_estimate选项设置为on，则计划器将通过执行EXPLAIN命令向远程服务器查询计划的成本；
否则，远程多表连接时，不会把连接下推到远程，而是在本地进行连接操作；同时在日志中看不到为每一条sql语句前面加上explain命令。

 ALTER SERVER pg_fdw_server OPTIONS (use_remote_estimate 'on'); #这个属性只允许修改一次

5.4 创建用户映射

testdb=#  
CREATE USER MAPPING FOR sakila_fdw SERVER pg_fdw_server
OPTIONS (user 'sakila', password 'sakila');

5.5 创建外部表

postgresql> psql -U sakila_fdw -d testdb
CREATE FOREIGN TABLE public.country_fdw (
    country_id integer ,
    country character varying(50),
    last_update timestamp
) SERVER pg_fdw_server OPTIONS (schema_name 'public', table_name 'country');  

CREATE FOREIGN TABLE city_fdw (
    city_id integer ,
    city character varying(50),
    country_id smallint ,
    last_update timestamp
)SERVER pg_fdw_server OPTIONS (schema_name 'public', table_name 'city');

5.6 测试表是否能够正常查询

select * from city_fdw ;
select * from country_fdw;

5.7 批量导入表

IMPORT FOREIGN SCHEMA public LIMIT TO (actor, store) 
FROM SERVER pg_fdw_server INTO public;

5.8 导入模式

IMPORT FOREIGN SCHEMA public FROM SERVER pg_fdw_server INTO public（;

Warning

缺少自定义类型year ， mpaa_rating ,需要手工同步
CREATE TYPE mpaa_rating AS ENUM (
'G',
'PG',
'PG-13',
'R',
'NC-17'
);
CREATE DOMAIN year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

6 理论验证和测试

6.1.1 单表查询

select * from city_fdw ;
select * from country_fdw;