## File: README.md # pgloader [](https://github.com/dimitri/pgloader/actions/workflows/clojure-integration-tests.yml) [](http://pgloader.readthedocs.io/en/latest/) pgloader is a data loading tool for PostgreSQL, using the `COPY` command. Its main advantage over just using `COPY` or `\copy`, and over using a *Foreign Data Wrapper*, is its transaction behaviour, where *pgloader* will keep a separate file of rejected data, but continue trying to `copy` good data in your database. The default PostgreSQL behaviour is transactional, which means that *any* erroneous line in the input data (file or remote database) will stop the entire bulk load for the table. pgloader also implements data reformatting, a typical example of that being the transformation of MySQL datestamps `0000-00-00` and `0000-00-00 00:00:00` to PostgreSQL `NULL` value (because our calendar never had a *year zero*). ## Version 4 — Clojure/JVM rewrite pgloader v4 is a full rewrite in Clojure, distributed as a single self-contained JAR requiring **Java 21 or later**. It is a drop-in replacement for v3 and accepts the same `.load` file syntax and command-line flags. Key improvements over v3: - **Single JAR** — no native dependencies, no SBCL, no shared libraries - **JDBC connection strings** — use `jdbc:mysql://`, `jdbc:postgresql://`, `jdbc:sqlserver://` etc. everywhere; driver-specific parameters pass through unchanged - **Java heap** — no more Common Lisp heap exhaustion on large migrations; tune with standard `-Xmx` JVM flag - **SSL via URI** — use `?sslmode=require` / `?sslmode=disable` in the connection string instead of `--no-ssl-cert-verification` ## Documentation Full documentation is available at [https://pgloader.readthedocs.io/](https://pgloader.readthedocs.io/en/latest/). ``` $ java -jar pgloader.jar --help pgloader [ option ... ] SOURCE TARGET --help Show usage and exit. --version Display pgloader version and exit. --quiet Be quiet. --verbose Be verbose. --debug Display debug level information. --client-min-messages Filter logs seen at the console (default: info) --log-min-messages Filter logs seen in the logfile (default: info) --logfile Filename where to send the logs. --summary Filename where to copy the summary. --root-dir Output root directory (default: /tmp/pgloader/). --list-encodings List pgloader known encodings and exit. --dry-run Check connections only, don't load anything. --on-error-stop Stop on first error instead of continuing. ``` ## Usage You can either give a command file to pgloader or run it all from the command line, see the [pgloader quick start](https://pgloader.readthedocs.io/en/latest/tutorial/tutorial.html#pgloader-quick-start) for more details. $ java -jar pgloader.jar --help $ java -jar pgloader.jar For example, for a full migration from SQLite: $ createdb newdb $ java -jar pgloader.jar ./test/sqlite/sqlite.db postgresql:///newdb Or for a full migration from MySQL, including schema definition (tables, indexes, foreign keys, comments) and parallel loading of the corrected data: $ createdb pagila $ java -jar pgloader.jar mysql://user@localhost/sakila postgresql:///pagila ## INSTALL ### Java (v4 — recommended) pgloader v4 requires **Java 21 or later** ([Temurin](https://adoptium.net/), OpenJDK, or any compatible distribution). Download the latest pre-release JAR — this URL always points to the most recent build from the `v4-clojure-rewrite` branch: $ curl -L -o pgloader.jar \ https://github.com/dimitri/pgloader/releases/download/v4-dev/pgloader.jar $ java -jar pgloader.jar --version To make it available system-wide: $ sudo install -m 755 pgloader.jar /usr/local/lib/pgloader.jar $ echo '#!/bin/sh\nexec java -jar /usr/local/lib/pgloader.jar "$@"' \ | sudo tee /usr/local/bin/pgloader $ sudo chmod 755 /usr/local/bin/pgloader $ pgloader --version ### Docker The latest image is built by CI on every push to `v4-clojure-rewrite`: $ docker pull ghcr.io/dimitri/pgloader:latest $ docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader --version ### Debian / Ubuntu (v3) The v3 package is available in the Debian and Ubuntu repositories: $ apt-get install pgloader A v4 Debian package is planned. ## LICENCE pgloader is available under [The PostgreSQL Licence](http://www.postgresql.org/about/licence/). --- ## File: bundle/README.md # pgloader source bundle In order to ease building pgloader for non-lisp users, the *bundle* distribution is a tarball containing pgloader and its build dependencies. See the the following documentation for more details: The *bundle* comes with a specific `Makefile` so that building it is as simple as the following (which includes testing the resulting binary): make LANG=en_US.UTF-8 make test The compilation might takes a while, it's because SBCL is trying hard to generate run-time binary code that is fast and efficient. Yes you need to be in a unicide environment to run the test suite, so that it matches with the encoding of the test *.load files. You can then package or use the pgloader binary: ./bin/pgloader --version ./bin/pgloader --help Note that the SQLite test files are not included in the bundle, for weithing too much here. --- ## File: clojure/README.md # pgloader v4 (Clojure) Clojure-based reimplementation of pgloader, a data loading tool for PostgreSQL. Supports MySQL, MariaDB, SQLite, CSV, COPY, DBF, Fixed-width, MSSQL, and PostgreSQL-as-source migration. ## Status This is an **in-progress** rewrite. Core migration paths (CSV, MySQL, MariaDB, SQLite, DBF, PostgreSQL) are fully implemented and covered by integration tests that run against both v3 and v4 in CI. Known gaps: - **MSSQL**: source introspection is implemented; full end-to-end coverage is limited. - **IXF / Redshift**: not planned for v4 at this time. - **Built-in transforms**: all 19 v3 transforms are ported to `pgloader.transforms`. ## Intentional differences from v3 ### INI config files — not supported v3 supported two INI-related features that are **not implemented in v4** and will not be: 1. **`--upgrade-config FILE.conf`** — converted pgloader v2.x INI-format configuration files to v3 load file syntax. pgloader v2 has been unsupported for many years; this migration path is no longer needed. 2. **`--context FILE.ini`** — fed an INI file as a mustache template context into a `.load` file (e.g. `{{DBPATH}}`). v4 replaces this with standard OS environment variables: set `DBPATH` in the shell environment and reference it in the load file as `$DBPATH` or via `${DBPATH}`. If you relied on `--context`, move your INI values to environment variables and reference them in your `.load` file. No load-file syntax changes are needed for simple key=value substitution. ## Directory structure ``` /* Detailed source-code truncated for AI context efficiency. */ ``` ## Requirements - JDK 21+ - Clojure CLI 1.11+ - Docker + Docker Compose v2 (for integration tests) ## Build ```sh clojure -T:build uber # produces target/pgloader-v4-*.jar make build # same, via Makefile ``` ## How to run tests All integration tests run entirely inside Docker Compose — no local database needed. ### Unit tests (no Docker) ```sh make test-unit # or directly: clojure -M:test -m cognitect.test-runner \ -n pgloader.cast-test -n pgloader.batch-test \ -n pgloader.ddl-test -n pgloader.load-file.parser-test ``` ### Integration tests (Docker Compose) ```sh # From clojure/tests/: make csv # CSV + COPY + Fixed-width (v4) make csv-v3 # same tests with pgloader v3 make mysql # MySQL 8.0 → PostgreSQL make sqlite # SQLite → PostgreSQL make dbf # DBF → PostgreSQL make pgsql # PostgreSQL → PostgreSQL make mssql # SQL Server → PostgreSQL make citus # Citus distributed tables make all # all suites with v4 make all-v3 # all suites with v3 # Regenerate expected baselines (uses v3 as source of truth): make update-expected-csv make update-expected-mysql ``` Each target spins up the required database services, waits for health-checks, runs tests inside the `test-runner` container, then cleans up. ## Run migrations ```sh # From LOAD file (after build): java -jar target/pgloader-v4-*.jar path/to/file.load # Direct URI migration: java -jar target/pgloader-v4-*.jar mysql://user:pass@host/db pgsql://user:pass@host/db # Pipe data via stdin: cat data.csv | java -jar target/pgloader-v4-*.jar /path/to/file.load ``` --- ## File: docs/ref/archive.rst Archive (http, zip) =================== This command instructs pgloader to load data from one or more files contained in an archive. Currently the only supported archive format is *ZIP*, and the archive might be downloaded from an *HTTP* URL. Using advanced options and a load command file ---------------------------------------------- The command then would be: :: $ pgloader archive.load And the contents of the ``archive.load`` file could be inspired from the following: :: LOAD ARCHIVE FROM /Users/dim/Downloads/GeoLiteCity-latest.zip INTO postgresql:///ip4r BEFORE LOAD DO $$ create extension if not exists ip4r; $$, $$ create schema if not exists geolite; $$, EXECUTE 'geolite.sql' LOAD CSV FROM FILENAME MATCHING ~/GeoLiteCity-Location.csv/ WITH ENCODING iso-8859-1 ( locId, country, region null if blanks, city null if blanks, postalCode null if blanks, latitude, longitude, metroCode null if blanks, areaCode null if blanks ) INTO postgresql:///ip4r?geolite.location ( locid,country,region,city,postalCode, location point using (format nil "(~a,~a)" longitude latitude), metroCode,areaCode ) WITH skip header = 2, fields optionally enclosed by '"', fields escaped by double-quote, fields terminated by ',' AND LOAD CSV FROM FILENAME MATCHING ~/GeoLiteCity-Blocks.csv/ WITH ENCODING iso-8859-1 ( startIpNum, endIpNum, locId ) INTO postgresql:///ip4r?geolite.blocks ( iprange ip4r using (ip-range startIpNum endIpNum), locId ) WITH skip header = 2, fields optionally enclosed by '"', fields escaped by double-quote, fields terminated by ',' FINALLY DO $$ create index blocks_ip4r_idx on geolite.blocks using gist(iprange); $$; Common Clauses -------------- Please refer to :ref:`common_clauses` for documentation about common clauses. Archive Source Specification: FROM ---------------------------------- Filename or HTTP URI where to load the data from. When given an HTTP URL the linked file will get downloaded locally before processing. If the file is a `zip` file, the command line utility `unzip` is used to expand the archive into files in `$TMPDIR`, or `/tmp` if `$TMPDIR` is unset or set to a non-existing directory. Then the following commands are used from the top level directory where the archive has been expanded. Archive Sub Commands -------------------- - command [ *AND* command ... ] A series of commands against the contents of the archive, at the moment only `CSV`,`'FIXED` and `DBF` commands are supported. Note that commands are supporting the clause *FROM FILENAME MATCHING* which allows the pgloader command not to depend on the exact names of the archive directories. The same clause can also be applied to several files with using the spelling *FROM ALL FILENAMES MATCHING* and a regular expression. The whole *matching* clause must follow the following rule:: FROM [ ALL FILENAMES | [ FIRST ] FILENAME ] MATCHING Archive Final SQL Commands -------------------------- - *FINALLY DO* SQL Queries to run once the data is loaded, such as `CREATE INDEX`. --- ## File: docs/ref/copy.rst COPY ==== This commands instructs pgloader to load from a file containing COPY TEXT data as described in the PostgreSQL documentation. Using advanced options and a load command file ---------------------------------------------- The command then would be: :: $ pgloader copy.load And the contents of the ``copy.load`` file could be inspired from the following: :: LOAD COPY FROM copy://./data/track.copy ( trackid, track, album, media, genre, composer, milliseconds, bytes, unitprice ) INTO postgresql:///pgloader TARGET TABLE track_full WITH truncate SET work_mem to '14MB', standard_conforming_strings to 'on' BEFORE LOAD DO $$ drop table if exists track_full; $$, $$ create table track_full ( trackid bigserial, track text, album text, media text, genre text, composer text, milliseconds bigint, bytes bigint, unitprice numeric ); $$; Common Clauses -------------- Please refer to :ref:`common_clauses` for documentation about common clauses. COPY Formatted Files Source Specification: FROM ----------------------------------------------- Filename where to load the data from. This support local files, HTTP URLs and zip files containing a single dbf file of the same name. Fetch such a zip file from an HTTP address is of course supported. - *inline* The data is found after the end of the parsed commands. Any number of empty lines between the end of the commands and the beginning of the data is accepted. - *stdin* Reads the data from the standard input stream. - *FILENAMES MATCHING* The whole *matching* clause must follow the following rule:: [ ALL FILENAMES | [ FIRST ] FILENAME ] MATCHING regexp [ IN DIRECTORY '...' ] The *matching* clause applies given *regular expression* (see above for exact syntax, several options can be used here) to filenames. It's then possible to load data from only the first match of all of them. The optional *IN DIRECTORY* clause allows specifying which directory to walk for finding the data files, and can be either relative to where the command file is read from, or absolute. The given directory must exists. COPY Formatted File Options: WITH --------------------------------- When loading from a `COPY` file, the following options are supported: - *delimiter* Takes a single character as argument, which must be found inside single quotes, and might be given as the printable character itself, the special value \t to denote a tabulation character, or `0x` then an hexadecimal value read as the ASCII code for the character. This character is used as the *delimiter* when reading the data, in a similar way to the PostgreSQL `COPY` option. - *null* Takes a quoted string as an argument (quotes can be either double quotes or single quotes) and uses that string as the `NULL` representation in the data. This is similar to the *null* `COPY` option in PostgreSQL. - *truncate* When this option is listed, pgloader issues a `TRUNCATE` command against the PostgreSQL target table before reading the data file. - *disable triggers* When this option is listed, pgloader issues an `ALTER TABLE ... DISABLE TRIGGER ALL` command against the PostgreSQL target table before copying the data, then the command `ALTER TABLE ... ENABLE TRIGGER ALL` once the `COPY` is done. This option allows loading data into a pre-existing table ignoring the *foreign key constraints* and user defined triggers and may result in invalid *foreign key constraints* once the data is loaded. Use with care. - *skip header* Takes a numeric value as argument. Instruct pgloader to skip that many lines at the beginning of the input file. --- ## File: docs/ref/csv.rst CSV === This command instructs pgloader to load data from a `CSV` file. Because of the complexity of guessing the parameters of a CSV file, it's simpler to instruct pgloader with how to parse the data in there, using the full pgloader command syntax and CSV specifications as in the following example. Using advanced options and a load command file ---------------------------------------------- The command then would be: :: $ pgloader csv.load And the contents of the ``csv.load`` file could be inspired from the following: :: LOAD CSV FROM 'GeoLiteCity-Blocks.csv' WITH ENCODING iso-646-us HAVING FIELDS ( startIpNum, endIpNum, locId ) INTO postgresql://user@localhost:54393/dbname TARGET TABLE geolite.blocks TARGET COLUMNS ( iprange ip4r using (ip-range startIpNum endIpNum), locId ) WITH truncate, skip header = 2, fields optionally enclosed by '"', fields escaped by backslash-quote, fields terminated by '\t' SET work_mem to '32 MB', maintenance_work_mem to '64 MB'; Common Clauses -------------- Please refer to :ref:`common_clauses` for documentation about common clauses. CSV Source Specification: FROM ------------------------------ Filename where to load the data from. Accepts an *ENCODING* option. Use the `--list-encodings` option to know which encoding names are supported. The filename may be enclosed by single quotes, and could be one of the following special values: - *inline* The data is found after the end of the parsed commands. Any number of empty lines between the end of the commands and the beginning of the data is accepted. - *stdin* Reads the data from the standard input stream. - *FILENAME MATCHING* The whole *matching* clause must follow the following rule:: [ ALL FILENAMES | [ FIRST ] FILENAME ] MATCHING regexp [ IN DIRECTORY '...' ] The *matching* clause applies given *regular expression* (see above for exact syntax, several options can be used here) to filenames. It's then possible to load data from only the first match of all of them. The optional *IN DIRECTORY* clause allows specifying which directory to walk for finding the data files, and can be either relative to where the command file is read from, or absolute. The given directory must exists. Fields Specifications --------------------- The *FROM* option also supports an optional comma separated list of *field* names describing what is expected in the `CSV` data file, optionally introduced by the clause `HAVING FIELDS`. Each field name can be either only one name or a name following with specific reader options for that field, enclosed in square brackets and comma-separated. Supported per-field reader options are: - *terminated by* See the description of *field terminated by* below. The processing of this option is not currently implemented. - *date format* When the field is expected of the date type, then this option allows to specify the date format used in the file. Date format string are template strings modeled against the PostgreSQL `to_char` template strings support, limited to the following patterns: - YYYY, YYY, YY for the year part - MM for the numeric month part - DD for the numeric day part - HH, HH12, HH24 for the hour part - am, AM, a.m., A.M. - pm, PM, p.m., P.M. - MI for the minutes part - SS for the seconds part - MS for the milliseconds part (4 digits) - US for the microseconds part (6 digits) - unparsed punctuation signs: - . * # @ T / \ and space Here's an example of a *date format* specification:: column-name [date format 'YYYY-MM-DD HH24-MI-SS.US'] - *null if* This option takes an argument which is either the keyword *blanks* or a double-quoted string. When *blanks* is used and the field value that is read contains only space characters, then it's automatically converted to an SQL `NULL` value. When a double-quoted string is used and that string is read as the field value, then the field value is automatically converted to an SQL `NULL` value. - *trim both whitespace*, *trim left whitespace*, *trim right whitespace* This option allows to trim whitespaces in the read data, either from both sides of the data, or only the whitespace characters found on the left of the streaing, or only those on the right of the string. CSV Loading Options: WITH ------------------------- When loading from a `CSV` file, the following options are supported: - *truncate* When this option is listed, pgloader issues a `TRUNCATE` command against the PostgreSQL target table before reading the data file. - *drop indexes* When this option is listed, pgloader issues `DROP INDEX` commands against all the indexes defined on the target table before copying the data, then `CREATE INDEX` commands once the `COPY` is done. In order to get the best performance possible, all the indexes are created in parallel and when done the primary keys are built again from the unique indexes just created. This two step process allows creating the primary key index in parallel with the other indexes, as only the `ALTER TABLE` command needs an *access exclusive lock* on the target table. - *disable triggers* When this option is listed, pgloader issues an `ALTER TABLE ... DISABLE TRIGGER ALL` command against the PostgreSQL target table before copying the data, then the command `ALTER TABLE ... ENABLE TRIGGER ALL` once the `COPY` is done. This option allows loading data into a pre-existing table ignoring the *foreign key constraints* and user defined triggers and may result in invalid *foreign key constraints* once the data is loaded. Use with care. - *skip header* Takes a numeric value as argument. Instruct pgloader to skip that many lines at the beginning of the input file. - *csv header* Use the first line read after *skip header* as the list of csv field names to be found in the CSV file, using the same CSV parameters as for the CSV data. - *date format* Takes a date format string as argument. When target columns are of a PostgreSQL date/time type, this option applies the format to those fields by default. A per-field *date format* specification still takes precedence for that field. Here's an example of a *WITH* date format specification:: WITH date format 'YYYY-MM-DD HH24-MI-SS.US' - *trim unquoted blanks* When reading unquoted values in the `CSV` file, remove the blanks found in between the separator and the value. That behaviour is the default. - *keep unquoted blanks* When reading unquoted values in the `CSV` file, keep blanks found in between the separator and the value. - *fields optionally enclosed by* Takes a single character as argument, which must be found inside single quotes, and might be given as the printable character itself, the special value \t to denote a tabulation character, the special value \' to denote a single-quote, or `0x` then an hexadecimal value read as the ASCII code for the character. The following options specify the same enclosing character, a single quote:: fields optionally enclosed by '\'' fields optionally enclosed by '0x27' This character is used as the quoting character in the `CSV` file, and defaults to double-quote. - *fields not enclosed* By default, pgloader will use the double-quote character as the enclosing character. If you have a CSV file where fields are not enclosed and are using double-quote as an expected ordinary character, then use the option *fields not enclosed* for the CSV parser to accept those values. - *fields escaped by* Takes either the special value *backslash-quote* or *double-quote*, or any value supported by the *fields terminated by* option (see below). This value is used to recognize escaped field separators when they are to be found within the data fields themselves. Defaults to *double-quote*. - *csv escape mode* Takes either the special value *quote* (the default) or *following* and allows the CSV parser to parse either only escaped field separator or any character (including CSV data) when using the *following* value. - *fields terminated by* Takes a single character as argument, which must be found inside single quotes, and might be given as the printable character itself, the special value \t to denote a tabulation character, or `0x` then an hexadecimal value read as the ASCII code for the character. This character is used as the *field separator* when reading the `CSV` data. - *lines terminated by* Takes a single character as argument, which must be found inside single quotes, and might be given as the printable character itself, the special value \t to denote a tabulation character, or `0x` then an hexadecimal value read as the ASCII code for the character. This character is used to recognize *end-of-line* condition when reading the `CSV` data. --- ## File: docs/ref/dbf.rst DBF === This command instructs pgloader to load data from a `DBF` file. A default set of casting rules are provided and might be overloaded and appended to by the command. Using advanced options and a load command file ---------------------------------------------- Here's an example with a remote HTTP source and some user defined casting rules. The command then would be: :: $ pgloader dbf.load And the contents of the ``dbf.load`` file could be inspired from the following: :: LOAD DBF FROM http://www.insee.fr/fr/methodes/nomenclatures/cog/telechargement/2013/dbf/reg2013.dbf INTO postgresql://user@localhost/dbname WITH truncate, create table CAST column reg2013.region to integer, column reg2013.tncc to smallint; Common Clauses -------------- Please refer to :ref:`common_clauses` for documentation about common clauses. DBF Source Specification: FROM ------------------------------ Filename where to load the data from. This support local files, HTTP URLs and zip files containing a single dbf file of the same name. Fetch such a zip file from an HTTP address is of course supported. DBF Loading Options: WITH ------------------------- When loading from a `DBF` file, the following options are supported: - *truncate* When this option is listed, pgloader issues a `TRUNCATE` command against the PostgreSQL target table before reading the data file. - *disable triggers* When this option is listed, pgloader issues an `ALTER TABLE ... DISABLE TRIGGER ALL` command against the PostgreSQL target table before copying the data, then the command `ALTER TABLE ... ENABLE TRIGGER ALL` once the `COPY` is done. This option allows loading data into a pre-existing table ignoring the *foreign key constraints* and user defined triggers and may result in invalid *foreign key constraints* once the data is loaded. Use with care. - *create table* When this option is listed, pgloader creates the table using the meta data found in the `DBF` file, which must contain a list of fields with their data type. A standard data type conversion from DBF to PostgreSQL is done. - *table name* This options expects as its value the possibly qualified name of the table to create. Default DB3 Casting Rules ------------------------- When migrating from DB3 the following Casting Rules are provided:: type C to text using db3-trim-string type M to text using db3-trim-string type N to numeric using db3-numeric-to-pgsql-integer type I to numeric using db3-numeric-to-pgsql-numeric type L to boolean using logical-to-boolean type D to date using db3-date-to-pgsql-date --- ## File: docs/ref/fixed.rst Fixed Columns ============= This command instructs pgloader to load data from a text file containing columns arranged in a *fixed size* manner. Using advanced options and a load command file ---------------------------------------------- The command then would be: :: $ pgloader fixed.load And the contents of the ``fixed.load`` file could be inspired from the following: :: LOAD FIXED FROM inline ( a from 0 for 10, b from 10 for 8, c from 18 for 8, d from 26 for 17 [null if blanks, trim right whitespace] ) INTO postgresql:///pgloader TARGET TABLE fixed ( a, b, c time using (time-with-no-separator c), d ) WITH truncate SET work_mem to '14MB', standard_conforming_strings to 'on' BEFORE LOAD DO $$ drop table if exists fixed; $$, $$ create table fixed ( a integer, b date, c time, d text ); $$; 01234567892008052011431250firstline 01234562008052115182300left blank-padded 12345678902008052208231560another line 2345609872014092914371500 2345678902014092914371520 Note that the example comes from the test suite of pgloader, where we use the advanced feature ``FROM inline`` that allows embedding the source data within the command file. In most cases a more classic FROM clause loading the data from a separate file would be used. Common Clauses -------------- Please refer to :ref:`common_clauses` for documentation about common clauses. Fixed File Format Source Specification: FROM -------------------------------------------- Filename where to load the data from. Accepts an *ENCODING* option. Use the `--list-encodings` option to know which encoding names are supported. The filename may be enclosed by single quotes, and could be one of the following special values: - *inline* The data is found after the end of the parsed commands. Any number of empty lines between the end of the commands and the beginning of the data is accepted. - *stdin* Reads the data from the standard input stream. - *FILENAMES MATCHING* The whole *matching* clause must follow the following rule:: [ ALL FILENAMES | [ FIRST ] FILENAME ] MATCHING regexp [ IN DIRECTORY '...' ] The *matching* clause applies given *regular expression* (see above for exact syntax, several options can be used here) to filenames. It's then possible to load data from only the first match of all of them. The optional *IN DIRECTORY* clause allows specifying which directory to walk for finding the data files, and can be either relative to where the command file is read from, or absolute. The given directory must exists. Fields Specifications --------------------- The *FROM* option also supports an optional comma separated list of *field* names describing what is expected in the `FIXED` data file. Each field name is composed of the field name followed with specific reader options for that field. Supported per-field reader options are the following, where only *start* and *length* are required. - *start* Position in the line where to start reading that field's value. Can be entered with decimal digits or `0x` then hexadecimal digits. - *length* How many bytes to read from the *start* position to read that field's value. Same format as *start*. Those optional parameters must be enclosed in square brackets and comma-separated: - *terminated by* See the description of *field terminated by* below. The processing of this option is not currently implemented. - *date format* When the field is expected of the date type, then this option allows to specify the date format used in the file. Date format string are template strings modeled against the PostgreSQL `to_char` template strings support, limited to the following patterns: - YYYY, YYY, YY for the year part - MM for the numeric month part - DD for the numeric day part - HH, HH12, HH24 for the hour part - am, AM, a.m., A.M. - pm, PM, p.m., P.M. - MI for the minutes part - SS for the seconds part - MS for the milliseconds part (4 digits) - US for the microseconds part (6 digits) - unparsed punctuation signs: - . * # @ T / \ and space Here's an example of a *date format* specification:: column-name [date format 'YYYY-MM-DD HH24-MI-SS.US'] - *null if* This option takes an argument which is either the keyword *blanks* or a double-quoted string. When *blanks* is used and the field value that is read contains only space characters, then it's automatically converted to an SQL `NULL` value. When a double-quoted string is used and that string is read as the field value, then the field value is automatically converted to an SQL `NULL` value. - *trim both whitespace*, *trim left whitespace*, *trim right whitespace* This option allows to trim whitespaces in the read data, either from both sides of the data, or only the whitespace characters found on the left of the streaing, or only those on the right of the string. Fixed File Format Loading Options: WITH --------------------------------------- When loading from a `FIXED` file, the following options are supported: - *date format* Takes a date format string as argument. When target columns are of a PostgreSQL date/time type, this option applies the format to those fields by default. A per-field *date format* specification still takes precedence for that field. Here's an example of a *WITH* date format specification:: WITH date format 'YYYY-MM-DD HH24-MI-SS.US' - *truncate* When this option is listed, pgloader issues a `TRUNCATE` command against the PostgreSQL target table before reading the data file. - *disable triggers* When this option is listed, pgloader issues an `ALTER TABLE ... DISABLE TRIGGER ALL` command against the PostgreSQL target table before copying the data, then the command `ALTER TABLE ... ENABLE TRIGGER ALL` once the `COPY` is done. This option allows loading data into a pre-existing table ignoring the *foreign key constraints* and user defined triggers and may result in invalid *foreign key constraints* once the data is loaded. Use with care. - *skip header* Takes a numeric value as argument. Instruct pgloader to skip that many lines at the beginning of the input file. --- ## File: docs/ref/ixf.rst IXF === This command instructs pgloader to load data from an IBM `IXF` file. Using advanced options and a load command file ---------------------------------------------- The command then would be: :: $ pgloader ixf.load And the contents of the ``ixf.load`` file could be inspired from the following: :: LOAD IXF FROM data/nsitra.test1.ixf INTO postgresql:///pgloader TARGET TABLE nsitra.test1 WITH truncate, create table, timezone UTC BEFORE LOAD DO $$ create schema if not exists nsitra; $$, $$ drop table if exists nsitra.test1; $$; Common Clauses -------------- Please refer to :ref:`common_clauses` for documentation about common clauses. IXF Source Specification: FROM ------------------------------ Filename where to load the data from. This support local files, HTTP URLs and zip files containing a single ixf file of the same name. Fetch such a zip file from an HTTP address is of course supported. IXF Loading Options: WITH ------------------------- When loading from a `IXF` file, the following options are supported: - *truncate* When this option is listed, pgloader issues a `TRUNCATE` command against the PostgreSQL target table before reading the data file. - *disable triggers* When this option is listed, pgloader issues an `ALTER TABLE ... DISABLE TRIGGER ALL` command against the PostgreSQL target table before copying the data, then the command `ALTER TABLE ... ENABLE TRIGGER ALL` once the `COPY` is done. This option allows loading data into a pre-existing table ignoring the *foreign key constraints* and user defined triggers and may result in invalid *foreign key constraints* once the data is loaded. Use with care. - *create table* When this option is listed, pgloader creates the table using the meta data found in the `DBF` file, which must contain a list of fields with their data type. A standard data type conversion from DBF to PostgreSQL is done. - *table name* This options expects as its value the possibly qualified name of the table to create. - *timezone* This options allows to specify which timezone is used when parsing timestamps from an IXF file, and defaults to *UTC*. Expected values are either `UTC`, `GMT` or a single quoted location name such as `'Universal'` or `'Europe/Paris'`. --- ## File: docs/ref/mssql.rst MS SQL to Postgres ================== This command instructs pgloader to load data from a MS SQL database. Automatic discovery of the schema is supported, including build of the indexes, primary and foreign keys constraints. Using default settings ---------------------- Here is the simplest command line example, which might be all you need: :: $ pgloader mssql://user@mshost/dbname pgsql://pguser@pghost/dbname Using advanced options and a load command file ---------------------------------------------- The command then would be: :: $ pgloader ms.load And the contents of the command file ``ms.load`` could be inspired from the following: :: load database from mssql://user@host/dbname into postgresql:///dbname including only table names like 'GlobalAccount' in schema 'dbo' set work_mem to '16MB', maintenance_work_mem to '512 MB' before load do $$ drop schema if exists dbo cascade; $$; Common Clauses -------------- Please refer to :ref:`common_clauses` for documentation about common clauses. MS SQL Database Source Specification: FROM ------------------------------------------ Connection string to an existing MS SQL database server that accepts TCP/IP connections. pgloader v4 uses the official Microsoft JDBC driver (mssql-jdbc) so no FreeTDS or ODBC installation is required. Both pgloader-native and JDBC URL formats are accepted:: mssql://[user[:password]@][host][:port][/dbname] jdbc:sqlserver://host[:port][;param=value;...] The JDBC URL format is particularly useful when you need to pass driver-specific parameters such as ``encrypt``, ``trustServerCertificate``, or ``applicationIntent``: .. code-block:: sql -- Native pgloader URI (encrypt=false by default) FROM mssql://sa:password@mssql:1433/mydb -- JDBC URL — no URL-encoding needed for special chars in password, -- and all MSSQL JDBC properties are available: FROM jdbc:sqlserver://mssql:1433;databaseName=mydb;user=sa;password=P@$$w0rd;encrypt=false -- Azure SQL / SQL Server with SSL: FROM jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;user=admin;password=secret;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net When using the native ``mssql://`` URI, ``encrypt=false`` is set automatically (matching the previous FreeTDS behaviour). Override it by using a JDBC URL. MS SQL Database Migration Options: WITH --------------------------------------- When loading from a `MS SQL` database, the same options as when loading a `MYSQL` database are supported. Please refer to the MYSQL section. The following options are added: - *create schemas* When this option is listed, pgloader creates the same schemas as found on the MS SQL instance. This is the default. - *create no schemas* When this option is listed, pgloader refrains from creating any schemas at all, you must then ensure that the target schema do exist. MS SQL Database Casting Rules ----------------------------- CAST ^^^^ The ``CAST`` clause overrides or extends the built-in type mapping rules that pgloader applies when converting an MS SQL schema to PostgreSQL. The built-in defaults map all character types (``char``, ``nchar``, ``varchar``, ``nvarchar``, ``ntext``) to ``text`` and discard the length modifier. Use ``CAST`` when you want to preserve types and/or length modifiers. User-defined rules are evaluated before the built-in defaults, and the first matching rule wins. Synopsis """""""" :: CAST cast_rule [, ...] where *cast_rule* is: .. code-block:: text { type source_type | column table_name.column_name } [ guard ... ] [ to target_type ] [ option ... ] [ using function_name ] where *guard* is one of: .. code-block:: text when default 'string' when ( typemod_expression ) and not null and *option* is one of: .. code-block:: text { drop | keep } default { drop | keep } typemod { drop | keep | set } not null { drop | keep } extra Parameters """""""""" ``type`` *source_type* Match all columns whose MS SQL data type is *source_type* (case-insensitive). This is the most common form. ``column`` *table_name*.*column_name* Match a single column by its qualified name. This form accepts the same *option* list as the ``type`` form but no *guard*. ``to`` *target_type* The PostgreSQL type to emit in ``CREATE TABLE``. When omitted, the type from the first matching built-in rule is used and only the *options* take effect. Guards """""" Guards narrow the set of columns a rule applies to. Multiple guards on the same rule are combined with AND semantics. ``when default '``\ *string*\ ``'`` Match only columns whose default value equals *string*. ``when (`` *typemod_expression* ``)`` Match only columns whose type modifier satisfies *typemod_expression*, a Common Lisp s-expression where ``precision`` and ``scale`` are bound to the modifier components. For example, ``nvarchar(40)`` has ``precision = 40`` and ``scale = 0``; ``nvarchar(max)`` has ``precision = -1``. Supported operators: ``=``, ``<``, ``>``, ``<=``, ``>=``; combine with ``and`` and ``or``. ``and not null`` Match only columns declared ``NOT NULL`` in the source. Options """"""" ``drop typemod`` / ``keep typemod`` Controls whether the length modifier is carried into the PostgreSQL type definition. * **Default for all built-in MSSQL rules**: ``drop typemod`` — the modifier is discarded. ``nvarchar(40)`` with ``type nvarchar to varchar`` (or the built-in rule) produces a plain ``varchar``. * ``keep typemod`` preserves it. ``nvarchar(40)`` with ``type nvarchar to varchar keep typemod`` produces ``varchar(40)``. ``drop default`` / ``keep default`` Controls whether the source column's ``DEFAULT`` expression is copied to the ``CREATE TABLE`` statement. The default behaviour is ``drop default`` because MS SQL default expressions are often not valid PostgreSQL syntax. ``drop not null`` / ``keep not null`` / ``set not null`` Controls the ``NOT NULL`` constraint on the target column. ``set not null`` adds the constraint even when the source column is nullable. Examples """""""" Preserve character-type length modifiers instead of mapping everything to ``text``:: CAST type nvarchar to varchar keep typemod, type nchar to char keep typemod, type varchar to varchar keep typemod, type char to char keep typemod With these rules, ``nvarchar(40)`` becomes ``varchar(40)``, ``nchar(10)`` becomes ``char(10)``, and so on. Without them the built-in defaults would produce plain ``text`` for all four types. Handle ``nvarchar(max)`` (the driver reports ``precision = -1``) separately, keeping it as ``text``, while using a fixed-length type for all other ``nvarchar`` columns:: CAST type nvarchar when (= precision -1) to text drop typemod, type nvarchar to varchar keep typemod Override a single column while keeping the general rule:: CAST type nvarchar to varchar keep typemod, column dbo.product.code to char(8) drop typemod MS SQL Views Support -------------------- MS SQL views support allows pgloader to migrate view as if they were base tables. This feature then allows for on-the-fly transformation from MS SQL to PostgreSQL, as the view definition is used rather than the base data. MATERIALIZE VIEWS ^^^^^^^^^^^^^^^^^ This clause allows you to implement custom data processing at the data source by providing a *view definition* against which pgloader will query the data. It's not possible to just allow for plain `SQL` because we want to know a lot about the exact data types of each column involved in the query output. This clause expect a comma separated list of view definitions, each one being either the name of an existing view in your database or the following expression:: *name* `AS` `$$` *sql query* `$$` The *name* and the *sql query* will be used in a `CREATE VIEW` statement at the beginning of the data loading, and the resulting view will then be dropped at the end of the data loading. MATERIALIZE ALL VIEWS ^^^^^^^^^^^^^^^^^^^^^ Same behaviour as *MATERIALIZE VIEWS* using the dynamic list of views as returned by MS SQL rather than asking the user to specify the list. MS SQL Partial Migration ------------------------ INCLUDING ONLY TABLE NAMES LIKE ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Introduce a comma separated list of table name patterns used to limit the tables to migrate to a sublist. More than one such clause may be used, they will be accumulated together. The patterns use SQL Server's native ``LIKE`` syntax, meaning ``%`` matches any sequence of characters and ``_`` matches any single character. Regular expressions are not supported for MS SQL sources because SQL Server has no native regex operator; the filter is applied directly in the ``WHERE`` clause of the introspection query sent to the source database. Example:: including only table names like 'GlobalAccount' in schema 'dbo' EXCLUDING TABLE NAMES LIKE ^^^^^^^^^^^^^^^^^^^^^^^^^^ Introduce a comma separated list of table name patterns used to exclude table names from the migration. This filter only applies to the result of the *INCLUDING* filter. :: excluding table names like 'LocalAccount' in schema 'dbo' MS SQL Schema Transformations ----------------------------- ALTER SCHEMA '...' RENAME TO '...' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Allows to rename a schema on the flight, so that for instance the tables found in the schema 'dbo' in your source database will get migrated into the schema 'public' in the target database with this command:: alter schema 'dbo' rename to 'public' ALTER TABLE NAMES MATCHING ... IN SCHEMA '...' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Introduce a comma separated list of table names or *regular expressions* that you want to target in the pgloader *ALTER TABLE* command. Available actions are *SET SCHEMA*, *RENAME TO*, and *SET*:: ALTER TABLE NAMES MATCHING ~/_list$/, 'sales_by_store', ~/sales_by/ IN SCHEMA 'dbo' SET SCHEMA 'mv' ALTER TABLE NAMES MATCHING 'film' IN SCHEMA 'dbo' RENAME TO 'films' ALTER TABLE NAMES MATCHING ~/./ IN SCHEMA 'dbo' SET (fillfactor='40') ALTER TABLE NAMES MATCHING ~/./ IN SCHEMA 'dbo' SET TABLESPACE 'tlbspc' You can use as many such rules as you need. The list of tables to be migrated is searched in pgloader memory against the *ALTER TABLE* matching rules, and for each command pgloader stops at the first matching criteria (regexp or string). No *ALTER TABLE* command is sent to PostgreSQL, the modification happens at the level of the pgloader in-memory representation of your source database schema. In case of a name change, the mapping is kept and reused in the *foreign key* and *index* support. The *SET ()* action takes effect as a *WITH* clause for the `CREATE TABLE` command that pgloader will run when it has to create a table. The *SET TABLESPACE* action takes effect as a *TABLESPACE* clause for the `CREATE TABLE` command that pgloader will run when it has to create a table. The matching is done in pgloader itself, with a Common Lisp regular expression lib, so doesn't depend on the *LIKE* implementation of MS SQL, nor on the lack of support for regular expressions in the engine. Azure SQL / Azure Active Directory Authentication ------------------------------------------------- pgloader v4 uses the Microsoft JDBC driver (``mssql-jdbc``) and bundles both MSAL4J and the Azure Identity library (``azure-identity``), so all non-browser-dependent Entra ID authentication modes work out of the box. Always use a ``jdbc:sqlserver://`` URL for Azure SQL so that ``encrypt=true`` and ``authentication=`` reach the driver exactly as written. Quick reference: +--------------------------------------------+------------------------------------------------+ | Mode | Typical use case | +============================================+================================================+ | ``ActiveDirectoryServicePrincipal`` | CI/CD — app registration (client ID + secret) | +--------------------------------------------+------------------------------------------------+ | ``ActiveDirectoryServicePrincipalCertif.`` | CI/CD — app registration with certificate | +--------------------------------------------+------------------------------------------------+ | ``ActiveDirectoryManagedIdentity`` | Azure VM / App Service / AKS — no credentials | +--------------------------------------------+------------------------------------------------+ | ``ActiveDirectoryDefault`` | Developer laptop: ``az login`` fallback chain | +--------------------------------------------+------------------------------------------------+ | ``ActiveDirectoryIntegrated`` | Windows / Kerberos domain-joined machine | +--------------------------------------------+------------------------------------------------+ | ``ActiveDirectoryPassword`` | **Deprecated** — blocked by MFA enforcement | +--------------------------------------------+------------------------------------------------+ ``ActiveDirectoryServicePrincipal`` Authenticate as an Azure AD application (client ID + client secret) — the recommended mode for CI/CD pipelines and automated migrations:: FROM "jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;\ authentication=ActiveDirectoryServicePrincipal;\ AADSecurePrincipalId=;AADSecurePrincipalSecret=;\ encrypt=true" ``ActiveDirectoryManagedIdentity`` Authenticate using the managed identity of the Azure VM, App Service, AKS pod, or container that pgloader runs in — no credentials in the URL:: FROM "jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;\ authentication=ActiveDirectoryManagedIdentity;encrypt=true" ``ActiveDirectoryDefault`` The recommended mode for developer workstations. Uses the Azure Identity ``DefaultAzureCredential`` chain: environment variables → workload identity → managed identity → **Azure CLI** → Azure PowerShell. After a one-time ``az login``, no credentials need to appear in the URL:: FROM "jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;\ authentication=ActiveDirectoryDefault;encrypt=true;trustServerCertificate=false" This also works headlessly in WSL2: ``DefaultAzureCredential`` falls back to ``AzureCliCredential``, which shells out to ``az account get-access-token`` using your existing ``az login`` session — no browser popup is needed. ``ActiveDirectoryIntegrated`` Authenticate via Kerberos on a domain-joined machine. Requires a valid Kerberos ticket (``kinit`` on Linux/macOS, automatic on Windows):: FROM "jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;\ authentication=ActiveDirectoryIntegrated;encrypt=true" ``ActiveDirectoryPassword`` .. note:: **Deprecated by Microsoft.** This mode is incompatible with mandatory Entra ID multifactor authentication (MFA), which Microsoft now enforces by default across most tenants. Attempting it in an MFA-required tenant returns ``AADSTS50076``. Use ``ActiveDirectoryDefault`` or ``ActiveDirectoryServicePrincipal`` instead. :: FROM "jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;\ authentication=ActiveDirectoryPassword;user=user@tenant.onmicrosoft.com;\ password=secret;encrypt=true;trustServerCertificate=false" .. note:: ``ActiveDirectoryInteractive`` makes MSAL4J open a system browser via ``xdg-open`` for an OAuth2 interactive login. It works on native Windows, native macOS, and Linux desktops with a display server. It **fails** in WSL2 (``linux_xdg_open_failed`` — WSL2 has no JVM-accessible browser integration), headless Linux, Docker/Podman containers, and CI pipelines. Use ``ActiveDirectoryDefault`` with an existing ``az login`` session instead. The native ``mssql://`` scheme defaults to ``encrypt=false`` to match on-premises SQL Server behaviour. For Azure SQL use the ``jdbc:sqlserver://`` form shown above so that ``encrypt=true`` is set explicitly. MS SQL Driver setup and encoding -------------------------------- pgloader is using the `FreeTDS` driver, and internally expects the data to be sent in utf-8. To achieve that, you can configure the FreeTDS driver with those defaults, in the file `~/.freetds.conf`:: [global] tds version = 7.4 client charset = UTF-8 Default MS SQL Casting Rules ---------------------------- When migrating from MS SQL the following Casting Rules are provided: Numbers:: type tinyint to smallint type float to float using float-to-string type real to real using float-to-string type double to double precision using float-to-string type numeric to numeric using float-to-string type decimal to numeric using float-to-string type money to numeric using float-to-string type smallmoney to numeric using float-to-string Texts:: type char to text drop typemod type nchar to text drop typemod type varchar to text drop typemod type nvarchar to text drop typemod type xml to text drop typemod Binary:: type binary to bytea using byte-vector-to-bytea type varbinary to bytea using byte-vector-to-bytea Date:: type datetime to timestamptz type datetime2 to timestamptz Others:: type bit to boolean type hierarchyid to bytea type geography to bytea type uniqueidentifier to uuid using sql-server-uniqueidentifier-to-uuid