PostgreSQL中替代COPY/pg_dump实现类Greenplum可写外部表的SQL方案

Great question! You’ve already mapped Greenplum’s readable external tables to PostgreSQL’s file_fdw, so let’s dive straight into the write-side solutions that fit your requirement: embeddable in SQL functions, working just like a writable external table.

1. Use file_fdw for Writable External Tables (Yes, It Supports Writing!) #

Most folks only know file_fdw for reading external files, but PostgreSQL 9.3+ includes built-in support for INSERT operations with this FDW. This is the closest match to Greenplum’s writable external tables.

Step-by-Step Setup: #

1. Ensure the extension is installed:

   CREATE EXTENSION IF NOT EXISTS file_fdw;
   

2. Create an FDW server for file operations:

   CREATE SERVER file_export_server FOREIGN DATA WRAPPER file_fdw;
   

3. Define a writable foreign table that maps to your target file:

   CREATE FOREIGN TABLE customer_export (
       customer_id INT,
       full_name TEXT,
       signup_date DATE
   )
   SERVER file_export_server
   OPTIONS (
       filename '/var/lib/postgresql/exports/customer_data.csv',
       format 'csv',
       delimiter ',',
       header 'true',
       append 'false' -- Set to 'true' to add to an existing file instead of overwriting
   );
   

4. Embed this in a SQL function just like you would with Greenplum:

   CREATE OR REPLACE FUNCTION export_recent_customers() RETURNS VOID AS $$
   BEGIN
       INSERT INTO customer_export
       SELECT customer_id, full_name, signup_date
       FROM customers
       WHERE signup_date >= NOW() - INTERVAL '30 days';
   END;
   $$ LANGUAGE plpgsql;
   

   Call it with:

   SELECT export_recent_customers();
   

Key Notes: #

• The PostgreSQL runtime user (usually postgres) needs write permissions to the target file path.
• Supports all the same formats as the COPY command (CSV, text, binary) with matching options.

2. Custom Foreign Data Wrapper (For Advanced Use Cases) #

If file_fdw doesn’t cover your needs (e.g., exporting to compressed files, custom formats like Parquet), you can use a community-maintained FDW or build your own:

• parquet_fdw: For exporting directly to Parquet files (great for big data workflows).
• Custom PL/Python FDW: If you need custom logic (like dynamic file naming or post-processing), you can wrap file-writing logic in a PL/Python function and expose it as a foreign table (requires the plpythonu extension).

This is more work than using file_fdw, but it’s flexible for edge cases.

3. postgres_fdw Hack (For Restricted Environments) #

If file system permissions block file_fdw, you can use postgres_fdw to connect to your local PostgreSQL instance, then create a foreign table that uses a COPY ... TO PROGRAM statement under the hood. This is a workaround, though—file_fdw is still the cleaner option.

内容的提问来源于stack exchange，提问作者Anuraag Veerapaneni