How to Capture and Extract Data From PostgreSQL: 6 Methods

PostgreSQL data can be captured or extracted in six common ways: a managed change data capture platform, native logical replication, database triggers, pgAdmin, the psql command-line client, or a custom Python script.

The right method depends on whether you need a one-time export or continuous updates. Use pgAdmin or psql for occasional exports, Python for custom extraction workflows, native logical replication for PostgreSQL to PostgreSQL replication, and triggers for small auditing use cases. For production pipelines that continuously deliver PostgreSQL changes to warehouses, lakehouses, search platforms, or other heterogeneous destinations, log-based CDC is generally the most scalable approach.

This guide explains how each method works and compares its support for initial data loading, ongoing change capture, destination flexibility, and operational maintenance.

Comparison of PostgreSQL Data Capture Methods

What Is Change Data Capture (CDC) in PostgreSQL?

In modern data architectures, SQL databases like PostgreSQL are typically the most up-to-date storage systems because they handle transactional workloads. On the other hand, data warehouses are essential for analytical applications since they can process complex queries and large volumes of data.

Together, they form a complementary architecture that supports both transactional and analytical use cases. This creates a data integration challenge: businesses need to transfer the latest data from PostgreSQL to other systems promptly, reliably, and with minimal performance impact.

This is where Change Data Capture (CDC) comes in. CDC identifies changes in a source system, allowing downstream systems to act on these updates in real time.

Why Businesses Use CDC for Postgres

CDC ensures that the latest data from PostgreSQL can be replicated to data warehouses, BI tools, or applications without repeatedly running heavy batch jobs. This is especially important for:

• Keeping analytics dashboards updated in near real time.
• Avoiding performance issues from full table exports.
• Powering real-time applications like fraud detection or customer personalization.
• Maintaining data integrity across multiple systems.

Key Advantages of CDC in Postgres

PostgreSQL records database changes in its write-ahead log. When logical replication is enabled, a consumer can decode selected changes from the WAL through a replication slot and publication.

This provides several benefits:

• Incremental capture: Consumers read new changes instead of repeatedly scanning complete tables.
• Transactional ordering: Changes are emitted according to committed PostgreSQL transactions.
• Selective replication: Publications can restrict capture to specific tables and, in supported configurations, selected operations, rows, or columns.
• Recoverable positions: Replication slots track how far a consumer has processed the WAL.

These features also create operational responsibilities. Teams must monitor replication lag, long-running transactions, replication-slot health, and WAL retention. A stalled slot can cause PostgreSQL to retain large amounts of WAL, while an undersized WAL limit can invalidate the slot and require recovery.

How To Capture & Extract Data From PostgreSQL: 6 Proven Methods

When working with PostgreSQL, it’s often necessary to extract data for reporting, analytics, machine learning, or integrations with other systems. To do this effectively, you need reliable extraction methods that can ensure efficiency, accuracy, and minimal impact on your production database.

We’ll walk through six proven approaches to capturing data from PostgreSQL, ranging from real-time Change Data Capture (CDC) techniques to one-time exports. Each method comes with its own benefits and trade-offs, so you can choose the one that best fits your use case.

Method 1: Managed Real-Time PostgreSQL CDC With Estuary

Best for: Production PostgreSQL pipelines that require an initial backfill followed by continuous delivery to data warehouses, lakehouses, analytical databases, search platforms, or operational systems.

Capture method: Log-based change data capture using PostgreSQL logical replication.

Initial data load: Optional backfill of existing rows before ongoing change capture begins.

Estuary’s PostgreSQL CDC connector continuously captures committed inserts, updates, and deletes from the PostgreSQL write-ahead log and delivers them to one or more downstream systems.

When a capture starts, Estuary normally backfills the current contents of the selected tables and then transitions to ongoing change capture. Captured records are written to reusable Estuary collections, which can be transformed or delivered to multiple destinations without creating an independent source extraction pipeline for each destination.

Estuary supports public, private, and bring-your-own-cloud deployment options. The PostgreSQL connector works with self-hosted PostgreSQL and managed services including Amazon RDS, Amazon Aurora, Google Cloud SQL, Azure Database for PostgreSQL, Supabase, and Neon.

How PostgreSQL CDC Works in Estuary

A PostgreSQL data flow in Estuary has four main stages:

1. The capture connector reads selected tables and change events through PostgreSQL logical replication.
2. Estuary backfills the existing contents of each selected table unless backfilling has been disabled for that table.
3. Captured records are written to durable Estuary collections.
4. One or more materializations deliver those collections to downstream destinations.

Because collections are reusable, the same captured PostgreSQL data can feed multiple destinations, such as Snowflake for analytics, Kafka for event-driven applications, and Elasticsearch for search.

PostgreSQL CDC Prerequisites

Before creating a capture, confirm that the PostgreSQL database has:

• PostgreSQL 10 or later.
• Logical replication enabled with wal_level = logical.
• A user with the REPLICATION attribute and permission to read the captured tables.
• A publication containing the tables that should be captured.
• A logical replication slot. Estuary can create one automatically when the capture user has sufficient permissions.
• A watermarks table for the standard backfill process. Estuary can also create this automatically when permissions allow.
• Network connectivity through direct access, SSH tunnelling, or private cloud networking.

Each independent capture from the same PostgreSQL database must use a unique replication slot.

Version note: Estuary supports PostgreSQL 10 and later at the connector level. For production workloads, use a PostgreSQL release that is still supported by the PostgreSQL community and keep it on the latest available minor version.

When logical replication is unavailable, or when you need to capture database views, execute custom queries, or extract data from certain read replicas, use Estuary’s PostgreSQL Batch Query connector instead.

Configure a Self-Hosted PostgreSQL Database

Managed PostgreSQL services have provider-specific logical replication, authentication, and networking requirements. The following example applies to a self-hosted PostgreSQL database.

Step 1: Enable Logical Replication

Run the following command using an administrative account:

sql
ALTER SYSTEM SET wal_level = logical;

Restart PostgreSQL for the change to take effect.

Also confirm that max_replication_slots and max_wal_senders are high enough for the number of logical captures and other replicas running on the database.

Step 2: Create a Dedicated Capture User

Create a user with replication access:

sql
CREATE USER flow_capture
WITH PASSWORD 'replace_with_a_secure_password'
REPLICATION;

For PostgreSQL 14 and later, grant read access with:

sql
GRANT pg_read_all_data TO flow_capture;

For an earlier PostgreSQL release or a more granular least-privilege configuration, grant access to the schemas and tables that will be captured:

sql
GRANT USAGE ON SCHEMA public, <other_schema> TO flow_capture;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO flow_capture;

GRANT SELECT ON ALL TABLES
IN SCHEMA public, <other_schema>
TO flow_capture;

GRANT SELECT ON ALL TABLES
IN SCHEMA information_schema, pg_catalog
TO flow_capture;

Replace <other_schema> with any additional schemas containing tables you want to capture.

Step 3: Create the Watermarks Table and Publication

In a restricted setup, create the watermarks table and publication manually:

sql
CREATE TABLE IF NOT EXISTS public.flow_watermarks (
    slot TEXT PRIMARY KEY,
    watermark TEXT
);

GRANT ALL PRIVILEGES
ON TABLE public.flow_watermarks
TO flow_capture;

CREATE PUBLICATION flow_publication;

ALTER PUBLICATION flow_publication
SET (publish_via_partition_root = true);

ALTER PUBLICATION flow_publication
ADD TABLE public.flow_watermarks, <other_tables>;

Replace <other_tables> with the schema-qualified names of the tables you want to capture.

The publish_via_partition_root setting is useful when changes to partitioned tables should be captured under the root table name, but it is not required for every PostgreSQL deployment.

When the capture user has sufficient permissions, Estuary can create the publication, watermarks table, and replication slot automatically.

Step 4: Allow Network Access

Ensure that the Estuary data plane can connect to the PostgreSQL host and port.

Depending on your deployment, this may involve:

• Allowing the Estuary data-plane IP addresses through a firewall or security group.
• Configuring an SSH tunnel.
• Using private networking with a private or BYOC deployment.
• Connecting directly to the PostgreSQL database rather than through a transaction-mode connection pooler.

Create the PostgreSQL Capture in Estuary

In the Estuary web application:

1. Open Sources and select + New Capture.
2. Choose the PostgreSQL connector for your hosting environment.
3. Enter the database address, database name, and authentication details.
4. Enter the publication and replication-slot names when using custom values.
5. Test the connection and discover the available tables.
6. Select the tables you want to capture.
7. Review the generated collections and publish the capture.

By default, Estuary backfills each selected table and then begins ongoing change capture. You can disable the initial backfill for an individual table when you only need future changes or do not want to scan its existing contents.

Deliver PostgreSQL Data to a Destination

After publishing the capture:

1. Open Destinations and create a new materialization.
2. Choose one of Estuary’s supported destination connectors.
3. Select the collections created by the PostgreSQL capture.
4. Configure the destination resources and update behaviour.
5. Publish the materialization.

The same PostgreSQL collections can supply multiple materializations without creating a separate capture for every destination.

PostgreSQL CDC Production Considerations

Monitor the following when running PostgreSQL CDC in production:

• WAL retention: A replication slot that stops advancing can cause PostgreSQL to retain increasing amounts of WAL.
• WAL limits: Set max_slot_wal_keep_size high enough to account for the database change rate, expected interruptions, and long-running transactions. Setting it too low can invalidate the slot.
• Replication-slot recovery: A slot can be lost during a major PostgreSQL upgrade, failover, manual deletion, or after exceeding its WAL limit. Follow Estuary’s PostgreSQL replication-slot recovery guide if this occurs.
• Read-only capture mode: Estuary can operate without writing to a watermarks table, but the capture must still receive regular source changes so that the replication slot can advance.
• Read replicas: Logical decoding from a read-only standby requires a sufficiently recent PostgreSQL version and additional standby configuration.
• Multiple captures: Every independent capture from the same PostgreSQL database must have its own replication slot.

For provider-specific configuration and advanced connector settings, refer to the PostgreSQL connector documentation linked at the beginning of this section.

PostgreSQL CDC in Production

Estuary is used for PostgreSQL CDC across analytical and operational workloads.

Hayden AI used Estuary to backfill 5 TB of PostgreSQL data and continuously deliver changes to Amazon Redshift. The company reduced replication lag from 24 hours to approximately one hour and lowered monthly replication costs by 60%.

SocialHP built a PostgreSQL-to-Elasticsearch CDC pipeline in two days. The pipeline delivers changes with roughly second-level latency and supports the company’s real-time social analytics platform.

Ready to build a pipeline? Create a free Estuary account and connect PostgreSQL to your destination.

Method 2: PostgreSQL-to-PostgreSQL Logical Replication

PostgreSQL includes native logical replication for continuously replicating selected tables from one PostgreSQL database to another. It uses a publication on the source database and a subscription on the target database.

Native logical replication is suitable when both the source and destination are PostgreSQL. It does not replicate database schema changes, sequences, indexes, or other database objects, so those must be managed separately.

Step 1: Enable logical replication on the source

On the publisher, configure PostgreSQL with:

plaintext language-conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Restart PostgreSQL after changing these settings.

The values for max_replication_slots and max_wal_senders should reflect the number of subscriptions, synchronization workers, and physical replicas used by your environment rather than being copied blindly.

Step 2: Create a replication user

Create a login role with replication access:

sql
CREATE ROLE replication_user
WITH LOGIN REPLICATION PASSWORD 'replace_with_a_secure_password';

Grant access to the database, schema, and published tables:

sql
GRANT CONNECT ON DATABASE source_database TO replication_user;
GRANT USAGE ON SCHEMA public TO replication_user;
GRANT SELECT ON TABLE public.orders TO replication_user;

You must also configure pg_hba.conf, firewall rules, and network access so the subscriber can connect to the source database.

Step 3: Check the table’s replica identity

Tables that publish UPDATE or DELETE events normally need a primary key or another suitable replica identity.

Check the table definition before continuing. When no suitable key exists, PostgreSQL supports:

sql
ALTER TABLE public.orders REPLICA IDENTITY FULL;

However, REPLICA IDENTITY FULL increases the amount of data written to the WAL and should generally be used only when adding an appropriate key is not possible.

Step 4: Create the publication on the source

Create the publication before creating the subscription:

sql
CREATE PUBLICATION orders_publication
FOR TABLE public.orders;

A publication can contain one table, multiple tables, or all eligible tables. Publishing only the tables you need makes the replication scope easier to control.

Step 5: Create the target table

Native logical replication does not create tables or replicate schema changes. Create the corresponding table on the subscriber before starting replication. The target must use the expected schema and table name and have columns compatible with the data published by the source.

For example, you can export the source schema with:

bash
pg_dump --schema-only \
  --table=public.orders \
  source_database > orders_schema.sql

Apply that schema to the subscriber before creating the subscription.

Run this command using a role that is authorized to create subscriptions on the subscriber. On current PostgreSQL versions, the role needs permission to create subscriptions and CREATE privilege on the database. Older PostgreSQL releases may require a superuser.

Step 6: Create the subscription on the target

On the subscriber, run:

sql
CREATE SUBSCRIPTION orders_subscription
CONNECTION 'host=source_host port=5432 dbname=source_database user=replication_user password=replace_with_a_secure_password'
PUBLICATION orders_publication;

By default, PostgreSQL creates the replication slot, copies the existing table contents, and then begins applying new inserts, updates, deletes, and supported truncate operations.

Use copy_data = false only when the subscriber already contains the correct initial data and you intentionally want to replicate future changes only.

Step 7: Verify replication

On the subscriber, inspect the subscription:

sql
SELECT *
FROM pg_stat_subscription;

Then insert or update a row on the publisher and confirm that the change appears on the subscriber.

Native logical replication is a good option for PostgreSQL to PostgreSQL replication. For warehouses, lakehouses, search platforms, or multiple heterogeneous destinations, a CDC platform can manage the source capture, recovery, transformation, and downstream delivery.

Method 3: Using Triggers In PostgreSQL For CDC

PostgreSQL triggers provide a simple way to record INSERT, UPDATE, and DELETE events for small auditing or proof-of-concept workflows. However, every captured change creates an additional database write, and you must separately manage retention, delivery checkpoints, retries, monitoring, and schema changes. For high-write production pipelines, WAL-based CDC generally has less application-level coupling.

Here's how you can implement CDC with Triggers in PostgreSQL for extracting data:

Create A Dedicated Table For Change Records

The first step is to create a new table for storing the captured change data. To do this, use:

plaintext
CREATE TABLE change_records (
    id SERIAL PRIMARY KEY,
    operation CHAR(1),
    table_name TEXT,
    row_data JSONB,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

Create A Trigger Function

Next, create a trigger function that logs changes to the Change_Records Table.

plaintext
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO change_records (operation, table_name, row_data)
        VALUES ('D', TG_TABLE_NAME, row_to_json(OLD));
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO change_records (operation, table_name, row_data)
        VALUES ('U', TG_TABLE_NAME, row_to_json(NEW));
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO change_records (operation, table_name, row_data)
        VALUES ('I', TG_TABLE_NAME, row_to_json(NEW));
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Attach The Trigger Function To The Desired Table(s)

The trigger functions need to be attached to tables you want to monitor for changes.

plaintext
CREATE TRIGGER log_changes_trigger
AFTER INSERT OR UPDATE OR DELETE ON your_table
FOR EACH ROW EXECUTE FUNCTION log_changes();

Query The Change_Records Table

Finally, to extract the change data, run the following query on the Change_Records table.

plaintext
SELECT * FROM change_records ORDER BY changed_at;

Method 4: Export PostgreSQL Data With pgAdmin

pgAdmin is suitable for one-time manual exports of a table or query result. It is not designed for continuous synchronization or change data capture.

Export a complete table

1. Connect to the PostgreSQL server in pgAdmin.
2. Expand Databases, select the database, and open the relevant schema.
3. Expand Tables and right-click the table you want to export.
4. Select Import/Export Data.
5. Switch the dialog to Export.
6. Choose a target file and select CSV, text, or binary format.
7. Configure options such as headers, delimiter, encoding, quoting, and selected columns.
8. Click OK to run the export.

In pgAdmin server mode, the file may be created in server-side storage and may need to be downloaded through the Storage Manager.

Export filtered query results

To export a filtered dataset rather than a complete table, use Export Data Using Query and provide a query such as:

sql
SELECT id, email, created_at
FROM public.users
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days';

Choose the file format and export options, then run the export.

Method 5: Export PostgreSQL Data With psql

The psql command-line client can run interactive queries, execute scripts, and export query results.

Connect to PostgreSQL with:

bash
psql -h hostname -p 5432 -U username -d database_name

To display available tables:

plaintext
\dt

To export a table to a CSV file on the client machine:

plaintext
\copy public.users TO 'users.csv' WITH (FORMAT csv, HEADER true)

To export the result of a filtered query:

plaintext
\copy (SELECT * FROM public.users WHERE age > 18 ORDER BY age DESC) TO 'adult_users.csv' WITH (FORMAT csv, HEADER true)

Unlike the SQL COPY command, which normally reads or writes files accessible to the PostgreSQL server, \copy reads or writes files through the local psql client.

psql can also be automated from a shell script:

bash
psql \
  -h hostname \
  -p 5432 \
  -U username \
  -d database_name \
  -c "\copy (SELECT * FROM public.users) TO 'users.csv' WITH (FORMAT csv, HEADER true)"

This approach works well for occasional or scheduled exports, but it does not automatically track inserts, updates, and deletes between runs.

Method 6: Extract PostgreSQL Data With Python

For new Python projects, use Psycopg 3, the current generation of the PostgreSQL adapter.

Install the binary package:

bash
pip install "psycopg[binary]"

Connect to PostgreSQL and iterate over the query results:

python
import csv
import psycopg

connection_string = (
    "host=your_host "
    "port=5432 "
    "dbname=your_database "
    "user=your_user "
    "password=your_password"
)

with psycopg.connect(connection_string) as connection:
    with connection.cursor(name="users_export") as cursor:
        cursor.itersize = 1000

        cursor.execute("""
            SELECT id, email, created_at
            FROM public.users
            ORDER BY id
        """)

        with open("users.csv", "w", newline="", encoding="utf-8") as output_file:
            writer = csv.writer(output_file)
            writer.writerow(["id", "email", "created_at"])

            for row in cursor:
                writer.writerow(row)

The named server-side cursor retrieves the query result in batches instead of storing the complete result on the client. For larger bulk exports, PostgreSQL’s COPY protocol through Psycopg is generally more efficient than converting rows individually in Python.

Popular PostgreSQL Data Pipelines by Destination

After capturing data from PostgreSQL, the right destination depends on how that data will be used. Data warehouses and lakehouses are typically used for reporting, analytics, and machine learning. Analytical databases support low-latency queries, Kafka supports event-driven applications, and operational systems support migrations and application-serving use cases.

The following guides explain how to build common PostgreSQL pipelines, including the initial data load and ongoing change data capture.

Data Warehouses and Lakehouses

• PostgreSQL to Snowflake: Replicate operational data into Snowflake for business intelligence, reporting, data modelling, and AI workloads.
• PostgreSQL to BigQuery: Deliver PostgreSQL changes to BigQuery for serverless analytics and integration with Google Cloud services.
• PostgreSQL to Amazon Redshift: Move PostgreSQL data into an AWS-native warehouse for analytical queries and reporting.
• PostgreSQL to Databricks: Send transactional data to a lakehouse for analytics, machine learning, and Delta Lake workloads.
• PostgreSQL to Microsoft Fabric: Integrate PostgreSQL data with Fabric Warehouse, Power BI, and other Microsoft analytics services.
• PostgreSQL to Apache Iceberg: Capture PostgreSQL changes into open lakehouse tables that can be queried by multiple processing engines.

Real-Time Analytics and Streaming

• PostgreSQL to ClickHouse: Stream transactional changes into ClickHouse for real-time dashboards and low-latency analytical queries.
• PostgreSQL to Kafka: Publish PostgreSQL changes as event streams for microservices, stream processing, and event-driven applications.
• PostgreSQL to MotherDuck: Deliver PostgreSQL data to a managed DuckDB environment for lightweight cloud analytics.
• Search and Operational Databases
• PostgreSQL to Elasticsearch: Keep search indexes and operational search applications updated with PostgreSQL changes.
• PostgreSQL to MySQL: Migrate or continuously synchronize selected PostgreSQL tables with a MySQL database.
• PostgreSQL to SQL Server: Support migration, consolidation, or low-downtime synchronization with Microsoft SQL Server.
• PostgreSQL to MongoDB: Move relational PostgreSQL records into MongoDB for document-oriented application and serving workloads.

Object Storage and Data Lakes

• PostgreSQL to Amazon S3: Store PostgreSQL data in object storage for data-lake, archival, machine-learning, and downstream processing use cases.

Using a Managed PostgreSQL Service?

Managed PostgreSQL services have provider-specific settings for logical replication, authentication, networking, and connection pooling. Use the relevant connector documentation before configuring your pipeline:

• Amazon RDS for PostgreSQL connector
• Google Cloud SQL for PostgreSQL connector
• Supabase PostgreSQL connector
• Neon PostgreSQL connector

For another destination, browse Estuary’s complete connector catalogue.

Conclusion

The best way to capture data from PostgreSQL depends on the required latency, destination, data volume, and level of operational control.

Use pgAdmin or psql for small, one-time exports. A Python script is appropriate when you need custom extraction or application-specific processing. PostgreSQL triggers can support small auditing workflows, while native logical replication is a strong option for continuously replicating data between PostgreSQL databases.

For production pipelines that require an initial backfill followed by continuous delivery to warehouses, lakehouses, search platforms, or other heterogeneous destinations, a managed CDC platform reduces the work involved in checkpointing, recovery, monitoring, and downstream delivery.

Estuary provides:

• Log-based PostgreSQL CDC with optional backfills of existing table data.
• Reusable collections that can deliver the same captured data to multiple destinations.
• Managed checkpointing and recovery for continuous pipelines.
• Broad destination support across warehouses, lakehouses, analytical databases, operational systems, search platforms, and object storage.
• Flexible deployment options, including public SaaS, private deployments, and bring your own cloud.

Review the PostgreSQL CDC connector documentation for configuration requirements, or create a free Estuary account to build a PostgreSQL pipeline.

Explore PostgreSQL in depth and uncover its full range of capabilities with these essential insights.

• PostgreSQL Data Types Explained With Examples
• Postgres Schema Tutorial
• PostgreSQL Trigger Fundamentals: Examples & Syntax
• What is PostgreSQL DISTINCT Clause? + How To Use It
• Move Data From CSV to PostgreSQL
• Load Data into PostgreSQL