In this guide, we will set up a Foreign Data Wrapper (FDW) in PostgreSQL 17. This will allow a local laptop (Laptop B) to access data stored on a remote laptop (Laptop A) using postgres_fdw. This approach makes data sharing simple and efficient, eliminating the need for manual data transfers between databases.
Network and Setup Overview
Laptop A (Remote Server)
* IP Address: 10.0.20.xx
* Role: Stores the PostgreSQL database with the data.
* Purpose: Acts as the primary database server that holds the records and tables.
Laptop B (Local Server)
* IP Address: 10.0.20.xxx
* Role: Connects to Laptop A using postgres_fdw to fetch data.
* Purpose: Serves as the client, pulling data from Laptop A without direct database duplication.
Both laptops are connected to the same network, ensuring smooth connectivity without requiring VPN or additional routing configurations.
Step 1: Setting Up Laptop A (Remote Server)
Allow External Connections
Edit the postgresql.conf file to enable connections from other devices:
listen_addresses = '10.0.20.xx' # Allow access from this IP or use '*' for all
port = 5432 # Default PostgreSQL port
This setting allows PostgreSQL to listen for connections from remote machines. If this step is skipped, Laptop B will not be able to communicate with the database.
Restart PostgreSQL to apply changes:
pg_ctl -D ~/postgres_code/postgresql/data restart
Enable Access for Laptop B
Modify pg_hba.conf to allow Laptop B to connect:
host all all 10.0.20.xxx/32 md5
This rule grants access to Laptop B using password authentication (md5). Without this step, connection attempts from Laptop B will be rejected.
Reload the PostgreSQL configuration to apply the changes:
psql -U postgres -c "SELECT pg_reload_conf();"
Create a Database and User
Creating a dedicated user and database ensures secure and isolated access.
CREATE USER remote_user WITH PASSWORD 'secure_password';
CREATE DATABASE remote_db;
GRANT ALL PRIVILEGES ON DATABASE remote_db TO remote_user;
Create a Sample Table
To verify the connection later, create a simple table with test data:
\c remote_db
CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, salary INT);
INSERT INTO employees (name, salary) VALUES ('Alice', 50000), ('Bob', 60000);
GRANT ALL ON employees TO remote_user;
Test Connection from Laptop B
On Laptop B, check if it can connect to Laptop A:
psql -h 10.0.20.xx -U remote_user -d remote_db
If the connection is successful, run a simple query to verify:
SELECT * FROM employees;
If you see the inserted employee records, Laptop A is set up correctly.
Step 2: Configuring Laptop B (Local Server)
Enable postgres_fdw
Before setting up the connection, enable the Foreign Data Wrapper extension:
CREATE DATABASE my_db;
\c my_db
CREATE EXTENSION postgres_fdw;
Verify that the extension was added successfully:
\dx
Set Up the Foreign Server
Create a foreign server that points to Laptop A:
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '10.0.20.xx', port '5432', dbname 'remote_db');
This setup establishes a remote link but does not yet authenticate the user.
Configure User Mapping
Provide credentials for connecting to Laptop A:
CREATE USER MAPPING FOR postgres
SERVER remote_server
OPTIONS (user 'remote_user', password 'secure_password');
This step allows PostgreSQL to authenticate with the remote database using the provided user credentials.
Create a Foreign Table
Define the table on Laptop B that will map to the employees table on Laptop A:
CREATE FOREIGN TABLE employees (
id INT,
name TEXT,
salary INT
)
SERVER remote_server
OPTIONS (schema_name 'public', table_name 'employees');
Now, the employees' table exists on Laptop B but fetches data dynamically from Laptop A.
Alternatively, import all tables from the remote database automatically:
IMPORT FOREIGN SCHEMA public
FROM SERVER remote_server
INTO public;
This method saves time if multiple tables need to be accessed.
Step 3: Querying the Data from Laptop B
Now, you can run a query on Laptop B just as if the table were local:
SELECT * FROM employees;
Expected result:
id | name | salary
----+-------+--------
1 | Alice | 50000
2 | Bob | 60000
This confirms that Laptop B successfully retrieves data from Laptop A.
Optimizing Query Performance
Run the following to confirm that queries are executed on the remote server efficiently:
EXPLAIN SELECT * FROM employees;
It should return a "Foreign Scan" execution plan, confirming remote query execution.
Summary of Configuration
* Laptop A (10.0.20.xx): Stores remote_db and allows connections.
* Laptop B (10.0.20.xxx): Uses postgres_fdw to access remote_db remotely.
Additional Tips
* Security: Always enable SSL in production to protect data in transit.
* Performance: Queries on foreign tables may be slower due to network overhead, so consider optimizing indexes and caching frequently accessed data.
By following these steps, you have successfully set up FDW in PostgreSQL 17.