pg_lake_copy

Overview

Lake COPY component. Package 3.5.3; SQL 3.5.

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.5 control file
• Official data-lake import and export guide
• Official file-format reference
• 3.4 to 3.5 SQL migration

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.

The pg_lake release and package version is 3.5.3; the SQL extension version is 3.5. Keep the libraries and query server from the same release.

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.5 adds no standalone SQL API in pg_lake_copy; the 3.4 to 3.5 migration is empty. COPY behavior still depends on matching lake libraries and query-server binaries.