Dump¶

PostgreSQL Migrator names dump the process of exporting schema and data from a source database. Export lands in stdout, files or directly in target PostgreSQL server.

pg_migrate dump tries to have a semantic similar to pg_dump with flags like --data-only, --section= and more. When dumping directly to PostgreSQL, pg_migrate dump crosses the line to reach pg_restore features.

Table priority¶

Table priority is the average row size of the table. As soon as a table is ready to receive data, PostgreSQL Migrator queue COPY task.

Streamed COPY¶

PostgreSQL Migrator implements data transfer using COPY statements. Unlike Foreign Data Wrapper, COPY has absolutely no dependency, making it the most universal data transfer method. PostgreSQL Migrator COPY implementation is very fast and efficient.

When a batch reaches 8MB of data, PostgreSQL Migrator rotates the batch: a new COPY is executed. The actual size of the batch varies depending on the size of the last row of the batch. e.g. a huge row of 9MB fills a batch of one single row. Three rows of 5MB each will produce two batchs: one of 10MB and another for the last row. PostgreSQL driver sends data by messages of 64k to PostgreSQL server.

To summarize: PostgreSQL Migrator sends data to PostgreSQL by chunks of 64k. Once the batch hits 8MB of data, PostgreSQL Migrator finishes last row and issues a new COPY statement for next rows.

No Foreign Data Wrapper¶

Foreign Data Wrappers have a few limitations:

often requires non-free software.
installation is often painful.
intrusive: require creating temporary objects in PostgreSQL.
slow: no bulk copy.
memory hungry: entire batch of row is in memory.

Until FDW implements a streamed bulk copy, PostgreSQL Migrator won’t consider it. This is very unlikely.

For now, PostgreSQL Migrator implements plain text COPY which uses a lot of CPU for encoding. We consider RAW COPY for faster througput. Please reach us or contribute if you need this.

Large Object¶

PostgreSQL Migrator chunks LOBs and long columns to prevent OOM. Chunk size varies seconding source DBMS, see table below.

OracleMySQL

Column type	Chunk size
LONG	Not chunked
LONG RAW	Not chunked
CLOB	128kB
BLOB	128kB

Column type	Chunk size
mediumtext	Not chunked
mediumblob	Not chunked
longtext	128kB
longblob	128kB

PostgreSQL Migrator inlines long columns for tables with average row size below 64KiB.

Table constraints¶

PostgreSQL Migrator serializes creation of table constraints in the following order:

primary key
unique keys, one by one.
indexes, one by one.
foreign keys, one by one.

The dump scheduler parallelize constraints of different tables, but not constraints on the same table. This prevents deadlocks. Each constraint runs in its own transaction.

Excluding columns¶

It’s frequent to drop a legacy column while migrating to PostgreSQL. Do this with Dump.Objects parameter:

[Dump]
Objects = [
  "!Tables/sakila.film/Columns/legacy_column",
]

Column legacy_column will appear in web interface but wont be dumped in CREATE TABLE and COPY statements.

Excluding a column cascades to constraints. Column is removed from primary and unique key. If all columns of a primary on unique key are excluded, the constraint is excluded as well.

Excluding a column constrained by a foreign key removes the column from the constraint. If all columns of the foreign key are excluded, the foreign key constraint is excluded as well. However, excluding a column does not propagate to referencing constraint. You must either exclude the referencing column or exclude the foreign key constraint pointing to excluded column. The above configuration exclude constraints on table sakila.film, but not on table referencing sakila.film.legacy_column.

Excluding an indexed column removes it from index, including functionnal index. If all columns of the index ar excluded, the index is excluded as well.

If all columns referenced by a check constraint are excluded, then the check is excluded as well. Dumping a check referencing both excluded and dumped columns raises an error.

Override statements ¶

Warning

This feature is experimental. Its final behaviour may change, so use it with care.

PostgreSQL Migrator provides an experimental way to override DDL statements in dump by adding an overrides folder in the project.

This follows the same tree structure as in dump tree option.

The faster way to initialize overrides is to use dump sub-command:

$ pg_migrate dump --format=tree --file=overrides Tables/sakila.film

overrides/
└── Tables/
    └── <schema>.<tableName>/
        └── create.sql -- overrides <schema>.<tableName> creation
        └── data.sql -- overrides the copy query in each data_xxx.sql file
        └── drop.sql -- overrides <schema>.<tableName> drop
        └── Checks/
            └── <checkName>.sql -- overrides <checkName> creation
        └── ForeignKeys/
            └── <foreignKeyName>.sql -- overrides <foreignKeyName> creation
        └── Indexes/
            └── <IndexName>.sql -- overrides <IndexName> creation
        └── Keys/
            └── <KeyName>.sql -- overrides <KeyName> creation

A log message in dump.log confirms override.