FDW for PostgreSQL

1. Prepare the PostgreSQL environment

PostgreSQL environment:

Host: 127.0.0.1
Port: 5432
User: pg
Database: postgres

Now, create a test table:

postgres=# CREATE TABLE test(c1 int, c2 int);
CREATE TABLE

Then, insert several pieces of test data:

postgres=# INSERT INTO test VALUES(0,0),(1,1),(2,2);
INSERT 0 3

2. Create FDW in YMatrix

First, create the postgres_fdw extension:

mxadmin=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

Create a PostgreSQL server definition:

mxadmin=# CREATE SERVER server_pg FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5432', dbname 'postgres');
CREATE SERVER

Create a user map:

mxadmin=# CREATE USER MAPPING FOR mxadmin SERVER server_pg OPTIONS (user 'pg');
CREATE USER MAPPING

Create an external table:

mxadmin=# CREATE FOREIGN TABLE ext_pg (c1 int, c2 int) SERVER server_pg OPTIONS (table_name 'test');
CREATE FOREIGN TABLE

3. Use postgres_fdw to read and write data tables

After the external table is successfully created, you can directly read and write the external table to implement the operation of the test table in PostgreSQL.

3.1 Query data

Through the following query, you can see that the external table data is the same as the original table:

mxadmin=# SELECT * FROM ext_pg;
 c1 | c2
----+----
  0 |  0
  1 |  1
  2 |  2
(3 rows)

3.2 Write data

Insert data into external tables:

mxadmin=# INSERT INTO ext_pg VALUES(3,3);
INSERT 0 1

After inserting the data, connect to PostgreSQL to view the data:

postgres=# SELECT * FROM test;
 c1 | c2
----+----
  0 |  0
  1 |  1
  2 |  2
  3 |  3
(4 rows)

It can be seen that the data has been written to the test table of PostgreSQL.