pg_lake_copy

Overview

pg_extension_base auto-loads pg_lake_engine, pg_lake_iceberg, pg_lake_table, and pg_lake_copy in dependency order. Extension SQL/control version is 3.4; source and DEB/RPM package version is 3.4.0.

Version

Build

You can build the RPM / DEB packages for pg_lake using pig build:

pig build pkg pg_lake         # build RPM / DEB packages

Install

You can install pg_lake directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

pig install pg_lake;          # Install for current active PG version

pig ext install -y pg_lake -v 18  # PG 18
pig ext install -y pg_lake -v 17  # PG 17
pig ext install -y pg_lake -v 16  # PG 16

dnf install -y pg_lake_18       # PG 18
dnf install -y pg_lake_17       # PG 17
dnf install -y pg_lake_16       # PG 16

apt install -y postgresql-18-pg-lake   # PG 18
apt install -y postgresql-17-pg-lake   # PG 17
apt install -y postgresql-16-pg-lake   # PG 16

Preload:

shared_preload_libraries = 'pg_extension_base';

Create Extension:

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.