postgres extension postgres_fdw file_fdw - ghdrako/doc_snipets GitHub Wiki

Foreign data wrappers allow us to access data that is hosted on an external database as if it were kept in a normal local table. We can connect PostgreSQL to various data sources, we can connect PostgreSQL to another PostgreSQL server, or we can connect PostgreSQL to another data source that can be relational or non-relational. Once the foreign data wrapper is connected, PostgreSQL is able to read the remote table as if it were local. A complete list of foreign data wrappers available for PostgreSQL is available at https://wiki.postgresql.org/wiki/Foreign_data_wrappers.

POSTGRES_FDW - modern option to postgres dblink

CREATE EXTENSION postgres_fdw;
-- Create a foreign server:
CREATE SERVER salesinfo_bridge
	FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'p.2gdmzr2pcbadzcstrkuolxvtpq.db.postgresbridge.com', dbname 'sales');
-- Set up a user mapping to authenticate:
CREATE USER MAPPING FOR postgres
	SERVER salesinfo_bridge
    OPTIONS (user 'fdw_user', password 'password');

set up foreign tables that correspond to the tables I want to query on the foreign server. This is done in two different ways:

  • Run CREATE FOREIGN TABLE, which is pretty similar to CREATE TABLE in that you have to define column names, data types, constraints etc.
  • Run IMPORT FOREIGN SCHEMA, which imports tables and views from a schema, and creates foreign tables that match the definitions for the external tables. You even have the option to include/exclude specific tables only, which makes it even more convenient:
test=# IMPORT FOREIGN SCHEMA public LIMIT TO (payment_methods, accounts)
FROM SERVER salesinfo_bridge INTO public;

test=# SELECT c.id, pm.type, acct.balance
FROM contacts c
LEFT JOIN accounts acct ON c.id = acct.contact_id
LEFT JOIN payment_methods pm ON acct.id = pm.acct_id
WHERE acct.balance > 0;

psql commands that provide information related to foreign data wrappers:

  • \des - list foreign servers
  • \deu - list uses mappings
  • \det - list foreign tables
  • \dtE - list both local and foreign tables
  • \d <name of foreign table> - show columns, data types, and other table metadata
CREATE EXTENSION if not exists file_fdw;

CREATE SERVER if not exists aoc2022 FOREIGN DATA WRAPPER file_fdw;

DROP SCHEMA if exists aoc_signal CASCADE;
CREATE SCHEMA aoc_signal;
SET search_path = aoc_signal;

DROP FOREIGN TABLE if exists aoc_day13;

CREATE FOREIGN TABLE aoc_day13 (line text)
  SERVER aoc2022 options(filename '/tmp/aoc2022.day13.input');

DROP SEQUENCE if exists aoc;
CREATE SEQUENCE aoc;

FILE_FDW

mysql_fdw

# Verify the installed extension in PostgreSQL database
 select * from pg_extension
# Create mysql_fdw extension I PostgreSQL database
 CREATE EXTENSION mysql_fdw
 # Verify the above installed extension
 select * from pg_extension
# Create Foreign Data Wrapper server on PostgreSQL database server
 CREATE SERVER SOURCE_MYSQL_SRV FOREIGN DATA WRAPPER mysql_fdw options (host 'mysql_srv.local', port '3306');
 # Verify Foreign Data Wrapper server
 select * from pg_foreign_server;
# Configure User mapping for Foreign Data Wrapper server on the PostgreSQL database server
 CREATE USER MAPPING FOR pgfdwusr server source_mysql_srv OPTIONS (username 'myfdwusr', password 'Welcome@2023');
 # Verify user mapping for Foreign Data Wrapper server
 select * from pg_user_mappings; 
# Authorize user for Foreign server access
 GRANT USAGE ON FOREIGN SERVER source_mysql_src TO pgfdwusr;
# Import the target schema from source schema
 IMPORT FOREIGN SCHEMA workorder FROM SERVER source_mysql_srv INTO workorder_src;
# Insert sample data in the source MySQL database system
 INSERT INTO workorder.action(action_id, first_name, last_name) VALUES(201, 'Bob', 'Builder');

Note: Any new object, for example a table created under a source scheme, will not be reflected in the target scheme unless the scheme is re-imported.

# Create table action_mod on source MySql Database system
CREATE TABLE `action_mod` ( `action_id` smallint unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`action_id`), KEY `idx_action_mod_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


# Verify the table action_mod on the source MySQL Database system
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from   information_schema.tables  where TABLE_SCHEMA='WORKORDER';

# Import the target schema from source schema
IMPORT FOREIGN SCHEMA workorder LIMIT TO (action_mod) FROM SERVER source_mysql_srv INTO workorder_src;

# Verify the table action_mod on the Target system post reimport operation
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from   information_schema.tables  where TABLE_SCHEMA='workorder_src';

in addition to IMPORT FOREIGN SCHEMA, we can also use CREATE FOREIGN TABLE to further granularize the source and target import based on the specific tables.