The function of PostgreSQL's fdw implementation is the cross database operation between each PostgreSQL database and the remote database. The function is the same as oracle's dblink. The environment in this article is shown below:

1.1. Target installation software package

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

1.2 creating users at the target end

The source side here does not need to create users. Because there is already a rhnuser user on the source side, use it directly. According to the actual situation, give the user permission, which is generally select, update, delete and insert. The users here already have these permissions, so they don't need to be given any more permissions.

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

1.3. Install Postgres? FDW at the target end

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

1.4. Give users permission

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

1.5 create server definition

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;

1.6 create user mapping

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

1.7. Modify PG ﹣ HB file

As shown in the following figure, machine access is allowed for a specific network segment. Let go of it all here.

1.8 import source schema

rhnschema=# IMPORT FOREIGN SCHEMA "public" FROM SERVER rhnsrv INTO public;
ERROR:  type "public.evr_t" does not exist
--The first import reported an error because the target side did not create evr_t Type, use the following statement to create the type for import again.
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

If you want to import only individual tables, you need to use the following syntax:

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

1.9 query verification

After importing, you can query the PG ﹣ foreign ﹣ table table to get the imported table name information:

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