DOCSEARCHbyAlgolia
Submit Docs

pgloader

repository·main·Indexed 27 days ago

https://github.com/dimitri/pgloader

A data loading tool for PostgreSQL that utilizes the COPY command for bulk loads. It supports migrations from MySQL, SQLite, DBF, MS SQL Server, and other PostgreSQL instances, as well as CSV and fixed-width files. The tool handles erroneous data by logging rejected rows and provides automatic reformatting, such as converting MySQL zero-dates to PostgreSQL NULLs. Available as a v3 version via package managers and a v4 Clojure/JVM rewrite distributed as a JAR.

Tokens
37.8K
Snippets
88
Records
185
Agent score
90%

What's inside pgloader

  1. Overview of pgloader capabilities

    main

    pgloader is an open-source tool designed to load data from various sources into PostgreSQL. It uses the PostgreSQL COPY protocol to stream data efficiently and manages errors by generating reject.dat and reject.log files.

    Key capabilities include:

    • Loading from files: Supports CSV, fixed columns, dBase (db3), and IBM IXF formats. It can also read from archives (zip, tar, gzip) and download via HTTP(S).
    • Database migrations: Performs full schema and data migrations from other databases (like SQLite or MySQL) to PostgreSQL in a single, unattended command.
    • Data transformation: Supports on-the-fly data cleaning, field projections, and user-defined type casting rules.
    • Continuous Migration: Enables repeatable, automated migration processes, including DROP+CREATE workflows.

  2. Targeting Amazon Redshift with pgloader

    main
    Because Amazon Redshift is a fork of PostgreSQL 8.0, pgloader uses specific catalog queries designed for that version to ensure compatibility. When using pgloader to migrate data to or from Redshift, be aware that some logic typically handled by SQL in other PostgreSQL versions is implemented directly in the pgloader source code to accommodate the limitations of the Redshift/PostgreSQL 8.0 catalog.

  3. Understand pgloader v4 Error Handling and Batching

    main

    pgloader v4 uses a batch-based error handling mechanism to ensure that a few bad rows do not abort an entire table copy.

    How it works:

    1. Data is read and grouped into batches.
    2. Each batch is sent to PostgreSQL via a single COPY transaction.
    3. If a PSQLException occurs, the batch is rolled back.
    4. pgloader performs a binary search within the failed batch to isolate the specific bad rows.
    5. Valid rows from the failed batch are retried in smaller sub-batches to ensure they are committed.
    6. Isolated bad rows are written to reject files, while the rest of the data succeeds.

  4. Understand the pgloader v4 development roadmap

    main

    The v4 rewrite is being developed in phases:

    • Phase 1: Prototype supporting CSV and MySQL loads, featuring batch error handling (binary search retry), prefetch queues, and reject files.
    • Phase 2: Schema DDL execution (Tables, Indexes, Foreign Keys) and sequence resetting.
    • Phase 3: Additional JDBC sources including SQLite and MSSQL.
    • Phase 4: Full .load file DSL compatibility (e.g., ALTER TABLE, BEFORE/AFTER LOAD EXECUTE, truncate).
    • Phase 5: File format sources like DBF and Fixed-width, plus archive support (zip/tar/gzip).
    • Phase 6: Oracle support and GraalVM native-image builds.

  5. Load data from a CSV file

    main

    You can load data from a CSV file into a pre-existing PostgreSQL table using command-line switches. When using this method, you bypass the pgloader command syntax and must provide all necessary configuration via --type, --field, and --with switches. Note that the PostgreSQL URI must include the target tablename as a query parameter.

    pgloader --type csv                                   \
         --field id --field field                     \
         --with truncate                              \
         --with "fields terminated by ','"            \
         ./test/data/matching-1.csv                   \
         postgres:///pgloader?tablename=matching

  7. Migrate SQLite to PostgreSQL using default settings

    main

    To perform a simple migration from a SQLite file to a PostgreSQL database using default settings, use the following command line syntax:

    pgloader sqlite:///path/to/file.db pgsql://pguser@pghost/dbname

    This command supports automatic schema discovery and index building.

    $ pgloader sqlite:///path/to/file.db pgsql://pguser@pghost/dbname

  8. Run pgloader via Command Line

    main

    You can run pgloader in two ways:

    1. Using a command file: Read instructions from a file.

      pgloader commands.load

    2. Using direct arguments: Provide the source and target directly on the command line.

      pgloader SOURCE TARGET
    pgloader commands.load

pgloader SOURCE TARGET

  9. Load data from a ZIP archive or HTTP URL

    main

    Use the LOAD ARCHIVE command to instruct pgloader to extract and load data from one or more files contained within a ZIP archive. The archive can be a local file path or an HTTP URL. If an HTTP URL is provided, pgloader will download the file locally before processing.

    When processing a ZIP file, pgloader uses the unzip command-line utility to expand the archive into $TMPDIR (or /tmp if $TMPDIR is unset or invalid).

  10. Migrate PostgreSQL to Citus

    main

    You can migrate data from a standard PostgreSQL server to a Citus cluster using pgloader. This process supports automatic schema discovery (including indexes, primary keys, and foreign keys) and automatic distribution column backfilling.

    When migrating to Citus, pgloader uses the distribute command to manage distribution keys. It automatically handles:

    • Adding the distribution column to tables if it doesn't exist.
    • Adding the distribution column to primary keys.
    • Adding the distribution column to foreign keys.
    • Executing Citus-specific create_reference_table() and create_distributed_table() commands.
    • Backfilling the new distribution columns by joining with referenced tables during the data load phase.
    load database
   from pgsql:///hackathon
   into pgsql://localhost:9700/dim

   with include drop, reset no sequences

   cast column impressions.seen_at to "timestamp with time zone"

   distribute companies using id
   -- distribute campaigns using company_id
   -- distribute ads using company_id from campaigns
   -- distribute clicks using company_id from ads, campaigns
   -- distribute impressions using company_id from ads, campaigns
   ;

  12. Load data from fixed-size column text files

    main
    Use the LOAD FIXED command to instruct pgloader to parse text files where columns are defined by specific character positions (fixed-width format) rather than delimiters. You can specify the source using a filename, stdin, or inline (to embed data directly in the command file).