PostgreSQL之Foreign Data Wrappers使用指南

[root@hdp06 ~]# yum -y install postgresql10-contrib.x86_64

postgres=# CREATE USER rhnadmin WITH password 'RedHat' CREATEDB SUPERUSER replication bypa***ls createrole login;

postgres=# create extension postgres_fdw;
CREATE EXTENSION
postgres=# \dx postgres_fdw

postgres=# grant usage on FOREIGN DATA WRAPPER postgres_fdw to rhnadmin;

postgres=# CREATE SERVER rhnsrv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'rhnschema', host 'oln', port '5432');
postgres=# GRANT USAGE ON FOREIGN SERVER rhnsrv TO rhnadmin;

rhnschema=# CREATE USER MAPPING for rhnadmin SERVER rhnsrv OPTIONS (user 'rhnuser', password 'redhat');

rhnschema=# IMPORT FOREIGN SCHEMA "public" FROM SERVER rhnsrv INTO public;
ERROR:  type "public.evr_t" does not exist
--首次导入报错了，因为目标端没有创建evr_t类型，使用下面的语句创建类型再次导入。
rhnschema=# CREATE TYPE evr_t AS (epoch character varying(16),version character varying(512),release character varying(512));
rhnschema=# ALTER TYPE public.evr_t OWNER TO rhnadmin;
rhnschema=# IMPORT FOREIGN SCHEMA public FROM SERVER rhnsrv INTO public;
IMPORT FOREIGN SCHEMA

rhnschema=# IMPORT FOREIGN SCHEMA "public" limit to (rhnpackagefile) FROM SERVER rhnsrv INTO public;

rhnschema=# select *from pg_foreign_table;
rhnschema=# select *from log limit 20;