Skip to content

📚 postgres_fdw enabled

🔍 Prepare：

## OS and Software environment：

Environment name Version
OS Rocky Linux 9.x
DB PostgreSQL 16.X

### 1. Create postgres_fdw Extension On postgres DB（Corresponding DB Creation，Example On postgres）

su - postgres
psql -p 5432

\dx
CREATE EXTENSION postgres_fdw; 
\dx

alt text

### 2. Create Foreign Server

\des
CREATE SERVER "databasename_schemaname" FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'IP', port 'PORT', dbname 'DB');
ALTER SERVER "databasename_schemaname" OWNER TO postgres;
GRANT USAGE ON FOREIGN SERVER "databasename_schemaname" TO username;
\des
select * from pg_foreign_server;

### 3. Create User Mapping

\deu
CREATE USER MAPPING FOR "public" SERVER "databasename_schemaname" OPTIONS ("user" 'test123', password '123456');
\deu
select * from pg_user_mappings;

### 4. Create Foreign Table

  • 4.1 Manually Specifying Field Properties
\det
create foreign table "tablename" (METRIC_ID int,STATUS varchar(16), CATEGORY varchar(16)) server "databasename_schemaname" options (schema 'test123', table 'tablename’);
\det
select ftrelid::regclass,* from pg_foreign_table;
  • 4.2 Import Foreign Table（Automatically Specifying Field Properties）

alt text

  • 4.2.1 Import Remote_Schema one or more Table SFCS Execute on Their User Account

    \det
IMPORT FOREIGN SCHEMA "DBSNMP" LIMIT TO (BSLN_METRIC_DEFAULTS,BSLN_METRIC_DEFAULTS2) FROM SERVER "databasename_schemaname" INTO schemaname;
\det
select ftrelid::regclass,* from pg_foreign_table;

  • 4.2.2 Import Remote_Schema All Table

    \det
IMPORT FOREIGN SCHEMA "DBSNMP" FROM SERVER "databasename_schemaname" INTO schemaname;
\det
select ftrelid::regclass,* from pg_foreign_table;

### 5. Drop Foreign Server/user mapping/foreign table

  • 5.1 Drop foreign Server

    Drop Server "databasename_schemaname";

  • 5.2 Drop user mapping

    DROP USER MAPPING IF EXISTS FOR public SERVER "databasename_schemaname";

  • 5.3 Drop foreign table

    Drop Foreign Table BSLN_METRIC_DEFAULTS;

Drop foreign table（Drop External Tables Will Not Drop Remote Tables, If There Is A Drop Or Rename In The Remote Table, The Local PG tables Update Will Not Be Synchronized And External Table Queries Will Be Made, Indicating That It Does Not Exist）