Load PostgreSQL data to Local Filesystem

PostgreSQL is an open-source object-relational database system with a Python DBAPI adapter ecosystem. This page has everything needed to load PostgreSQL tables into Local Filesystem with dlt's sql_database source: the SQLAlchemy driver, the connection string, how to choose tables and load only new rows, plus the steps that hand the job to your coding agent.

---

Build your PostgreSQL to Local Filesystem pipeline

Set up a project, install the dltHub AI harness and its sql-database-pipeline toolkit, and let your coding agent build the pipeline:

Copy
uv init
uv add "dlt[hub,sql_database]" pyarrow pandas psycopg2-binary
uv run dlthub ai init --agent <your-agent> # <agent>: claude | cursor | codex
uv run dlthub ai toolkit install sql-database-pipeline

Then paste this prompt into Claude, Codex, or Cursor:

The toolkit lists the schemas and tables in your database, asks which tables to load, then scaffolds a one-table pipeline and runs it with a load limit before scaling up. Credentials are inspected through MCP tools, so your agent never reads secrets.toml itself. About the toolkit →

Prefer to write it yourself? Every fact the agent uses is below.

---

PostgreSQL at a glance

---

How do I connect to PostgreSQL?

dlt needs a database user with read access to the tables you want to load. Add its connection details to .dlt/secrets.toml:

Copy
[sources.sql_database.credentials]
drivername = "postgresql+psycopg2"
host = "localhost"
port = 5432
username = "my_user"
password = "my_password"
database = "my_database"

Or as a single connection string:

Copy
[sources.sql_database]
credentials = "postgresql+psycopg2://my_user:my_password@localhost:5432/my_database"

SQLAlchemy’s PostgreSQL dialect uses psycopg2 as its default DBAPI; use an explicit driver such as postgresql+psycopg2 when needed. The connection must have a usable PostgreSQL search_path: SQLAlchemy generally expects the default public path, but a username matching a schema or a customized search_path can change the effective default schema. psycopg2-binary avoids compiler and external-library requirements and is mainly recommended for development and testing; production deployments should generally use the source-built psycopg2 package.

Local Filesystem credentials go in the same file; the Local Filesystem destination docs list the fields it needs. dlt reads .dlt/secrets.toml automatically at runtime — never hardcode credentials in your pipeline script. For production, see setting up credentials with dlt.

---

Which PostgreSQL tables can I load into Local Filesystem?

Any table or view the database user can read. The sql_database source reads each table's schema from PostgreSQL and creates a matching table in Local Filesystem. These options narrow a load down to what you need:

Always pass table_names=[...] to sql_database(): without it, dlt reflects every table in the schema before loading anything.

---

How do I load only new PostgreSQL rows?

Give each table a cursor column and a primary key, and dlt loads only rows changed since the last run and merges them into Local Filesystem:

Copy
source.orders.apply_hints(
    incremental=dlt.sources.incremental("updated_at"),
    write_disposition="merge",
    primary_key="id",
)

Use a timestamp such as updated_at for tables whose rows change, and an increasing id for append-only tables. On the first run dlt loads everything; after that it reads only rows past the last cursor value. See incremental loading.

---

What does the PostgreSQL to Local Filesystem pipeline look like?

This loads the customers and orders tables from the public schema into Local Filesystem. Replace the table names with your own:

Copy
import dlt
from dlt.sources.sql_database import sql_database


def load_postgresql_to_local_filesystem() -> None:
    source = sql_database(
        schema="public",
        table_names=["customers", "orders"],
        backend="pyarrow",
    )
    source.orders.apply_hints(
        incremental=dlt.sources.incremental("updated_at"),
        write_disposition="merge",
        primary_key="id",
    )

    pipeline = dlt.pipeline(
        pipeline_name="postgresql_pipeline",
        destination="filesystem",
        dataset_name="postgresql_data",
    )
    load_info = pipeline.run(source)
    print(load_info)


if __name__ == "__main__":
    load_postgresql_to_local_filesystem()

Credentials are not passed in the code: sql_database() reads them from [sources.sql_database] in .dlt/secrets.toml. Run it with uv run python postgresql_pipeline.py.

---

How do I configure Local Filesystem as the destination?

dlt writes to Local Filesystem through its filesystem destination; a file:// URL or a plain path in bucket_url selects it. No extra is needed, and there are no credentials, so this can live in .dlt/config.toml:

Copy
# .dlt/config.toml
[destination.filesystem]
bucket_url = "file:///absolute/path/to/files"

dlt writes JSONL by default; pass loader_file_format="parquet" to pipeline.run() for Parquet. Delta and Iceberg table formats are supported too. See the dlt filesystem destination docs.

Changing bucket_url is all it takes to write the same data to Amazon S3, Google Cloud Storage or Azure Blob Storage instead; see dlt's filesystem destination for all four.

---

Which backend should I use?

The backend decides how rows are read from PostgreSQL. The example uses pyarrow; switch if your data calls for it:

The toolkit's /optimize-sql-performance skill measures where time goes and picks a backend, chunk size and parallelism for you. See configuring the backend.

---

How do I query PostgreSQL data in Local Filesystem?

After a run, pipeline.dataset() holds the loaded tables. A Relation is a query on that dataset, either a whole table or a SQL string, and reading it gives you the data as a Pandas DataFrame, a PyArrow table or Python tuples. Nothing is read until you ask for it. A filesystem destination holds files rather than tables, so dlt reads them through DuckDB views and the interface is the same as for a SQL destination; for frequent reads load Delta or Iceberg tables, which DuckDB reads only as far as the query needs (see reading data from filesystem).

Copy
import dlt

pipeline = dlt.pipeline("postgresql_pipeline")

# Step 1: Get the dataset from the pipeline
dataset = pipeline.dataset()

# Step 2: Get a Relation, either a whole table...
orders = dataset.table("orders")
# ...or any SQL query against Local Filesystem
recent = dataset("SELECT * FROM orders ORDER BY updated_at DESC LIMIT 100")

# Step 3: Read the data
df = orders.df()  # or orders.df(chunk_size=50) for a large table
arrow_table = recent.arrow()  # a PyArrow Table
rows = recent.fetchall()  # a list of Python tuples

The columns are the ones of your PostgreSQL tables; dataset.row_counts().df() lists every table with its row count. Your agent can run the same queries through the MCP server's execute_sql_query tool, and uv run dlthub show opens the Pipeline Dashboard with the schema, load metrics and a query box. See access loaded data in Python and exploring it in marimo notebooks.

---

How do I deploy the PostgreSQL to Local Filesystem pipeline in production?

The pipeline runs locally, which is ideal for prototyping and one-off loads. When you need it on a schedule, monitored on every load, and shared with your team, deploy the same dlt code on the dltHub platform — no infrastructure to maintain. The harness's dlthub-platform toolkit takes your agent there:

Copy
uv run dlthub ai toolkit install dlthub-platform

• Deploy & schedule — run the pipeline as a managed job with automatic retries.
• Monitor — observable job queues, alerting, and load metrics for every run.
• Transform — promote raw PostgreSQL loads into governed, documented models.
• Visualize & share — explore data in notebooks and publish live dashboards instead of static screenshots.

Book a demo →

---

What other destinations can I load PostgreSQL data to?

dlt loads into any of these — only the destination argument changes:

Set dlt.pipeline(destination="snowflake") and add that destination's credentials in .dlt/secrets.toml. See the full destinations list.

---

Next steps

• Ready for production? Book a demo of the dltHub platform →
• SQL database source reference
• The sql-database-pipeline toolkit
• Join the dlt community on Slack