pg_lake_copy

pg_lake : Copy to/from data lake files

Overview

Packages

Source

pig build pkg pg_lake;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_lake;		# install via package name, for the active PG version
pig install pg_lake_copy;		# install by extension name, for the current active PG version

pig install pg_lake_copy -v 18;   # install for PG 18
pig install pg_lake_copy -v 17;   # install for PG 17
pig install pg_lake_copy -v 16;   # install for PG 16

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_extension_base';

Create this extension with:

CREATE EXTENSION pg_lake_copy CASCADE; -- requires pg_lake_engine, pg_lake_iceberg, pg_lake_table

Usage

Sources:

• Version 3.4 control file
• Official data-lake import and export guide
• Official file-format reference
• Version 3.4 SQL file

pg_lake_copy extends PostgreSQL COPY so queries, heap tables, external lake tables, and Iceberg tables can exchange Parquet, CSV, or newline-delimited JSON files with local paths, HTTP endpoints, and configured object stores. It adds behavior through hooks and has no standalone SQL function API.

Enable the Component

The normal entry point installs pg_lake_copy and its exact dependencies together:

CREATE EXTENSION pg_lake CASCADE;

Its control file requires pg_lake_engine, pg_lake_iceberg, and pg_lake_table. The deployment also needs pg_extension_base in shared_preload_libraries and a running pgduck_server.

Export and Import

Format is inferred from the path suffix or selected explicitly:

COPY (
    SELECT event_id, event_time, payload
    FROM events
    WHERE event_time >= DATE '2026-07-01'
)
TO 's3://analytics-bucket/events/july.parquet'
WITH (format 'parquet');

COPY events_archive
FROM 's3://analytics-bucket/events/july.parquet'
WITH (format 'parquet');

CSV and compressed output use standard-looking COPY options extended for the lake writer:

COPY (SELECT * FROM daily_summary)
TO 's3://analytics-bucket/summary/daily.csv.gz'
WITH (format 'csv', header true, compression 'gzip');

The destination can be a PostgreSQL heap table or an Iceberg table; the source can likewise be any query supported by the installed pg_lake stack.

Format and Runtime Boundaries

• Parquet is columnar and preserves supported typed values; CSV and newline-delimited JSON have format-specific inference and conversion options documented upstream.
• Object-store access runs through pgduck_server. Its credential chain, network access, and bucket permissions must permit the requested read or write.
• COPY is one statement and participates in the surrounding PostgreSQL transaction, but remote files and cleanup also depend on the pg_lake transaction/queue machinery. Inspect failed operations and orphan cleanup before retrying large exports.
• Version 3.4 adds no user-visible SQL objects in pg_lake_copy; its 3.3 to 3.4 upgrade script is empty.