English

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 some 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 server definition for PostgreSQL:

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 mapping:

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

Create the foreign table:

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

3. Read and Write Data Using postgres_fdw

After successfully creating the foreign table, you can directly read from and write to the foreign table to access and modify the test table in PostgreSQL.

3.1 Query Data

The following query shows that the data in the foreign table matches the original table:

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

3.2 Insert Data

Insert data into the foreign table:

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

After insertion, connect to PostgreSQL and check the data:

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

The data has been successfully written to the test table in PostgreSQL.

← Previous

Overview

FDW for MySQL