Configuration
PostgreSQL Migrator requires a TOML configuration file named pg_migrate.toml located in the project root directory.
pg_migrate init command generates an initial configuration file.
If pg_migrate.auto.toml exists, PostgreSQL Migrator reads it too.
About TOML¶
TOML is a file format that is easy to read and write thanks to its obvious semantics. See the TOML specification for more information.
Schemas¶
A list of case-sensitive LIKE patterns to match Oracle or MySQL schema names.
Defined patterns are applied during inspection.
Any objects of unmatched schemas will be excluded from the project.
May improve inspection performance by scanning fewer rows from the source catalog.
Default value is ["%"].
pg_migrate always excludes:
- all System Schemas for Oracle.
information_schema,mysql,performance_schema,sysfor MySQL.
Scores¶
A mapping of string to floating number.
pg_migrate uses scores to evaluate the complexity of a migration.
The project score is the sum score of all objects.
Scores maps migration issue id to complexity score.
An issue id can be matched by a contextual prefix.
[Scores]
# increase column score to 0.5
"type: Column" = 0.5
# any transqlate issue. like transplate: parse error.
"transqlate" = 50
See Audit reference for details on how PostgreSQL migrator search score in Scores map.
Convert section¶
The convert section configures the conversion of the catalog and data.
Convert.DataTypes¶
String-to-string mapping. Map a source SQL data type expression to a PostgreSQL data type expression. SQL data type expression is case-insensitive.
[Convert.DataTypes]
"char(1)" = "boolean" # Drops length
"varchar2(n)" = "varchar(n)" # Preserves length
To match any length or precision, use an identifier as a placeholder. To preserve length or precision of the type, reuse the placeholder in the target data type. If the target type does not have a corresponding placeholder, PostgreSQL Migrator drops the parameter. The same behaviour applies to scale, only the position changes.
To match one or more type parameters, use a single ... placeholder.
PostgreSQL Migrator tests the most specific rules first.
For example: RAW(16) is tested before RAW(n),
which is tested before RAW(...).
See Conversion for default data type conversions.
In case of ambiguity, you may resolve this by using a path based rule.
Convert.PreserveCase¶
Boolean disabling identifier lowering. Does not applied to mixed-case identifiers. Useful when statements use quote-protected on uppercased identifiers. Default to false.
Convert.RenameConstraints¶
Boolean enabling renaming of all constraints.
Defaults to false.
When false, convert regenerates only generated constraint name like SYS_....
When true, all constraints get a new name.
Convert.RenameIndexes¶
Boolean enabling renaming of all indexes.
Defaults to false.
When false, convert regenerates only generated index name like SYS_....
When true, all indexes get a new name as generated by PostgreSQL.
See Convert.RenameConstraints.
Convert.TrueValues¶
List of string values.
When converting strings to boolean, consider these values as TRUE.
Other values are implicitly false.
Default values are ["y", "Y", "1"].
Convert.Rules¶
Holds conversion overrides for a specific database object.
[[Convert.Rules]] # in TOML, double brackets append rule to the list of rules
Path = "Tables/SAKILA.CUSTOMER/Columns/ACTIVE"
Type = "boolean"
TrueValues = ["1", "Y"]
Path field is resolved in Source catalog.
Missing Path field is an error.
A path not found in source catalog fails conversion.
Rules.Path¶
Absolute catalog path targeting the object to convert. The path applies to the source catalog.
A conversion rule references an object using a catalog path formatted as Tables/<schema>.<name>/Columns/<name>.
e.g. Tables/SAKILA.CUSTOMER/Columns/ACTIVE.
Rules.Expression¶
SQL expression string to use as the default or generated value for a column, functionnal index column expression, or check expression.
[[Convert.Rules]]
Path = "Tables/SAKILA.RENTAL/Columns/LAST_UPDATE"
Expression = "current_timestamp"
[[Convert.Rules]]
Path = "Tables/SAKILA.FILM/Columns/CHECK_SPECIAL_RATING"
Expression = "rating in ('G','PG','PG-13','R','NC-17')"
Rules.Name¶
String used to rename the object.
Propagates changes to underlined relations (tables, columns, check expressions) in best effort.
Convert.PreserveCase does not apply here: the new name is used as is, with case preserved.
[[Convert.Rules]]
Path = "Schemas/SAKILA"
Name = "public"
[[Convert.Rules]]
Path = "Tables/SAKILA.STAFF/Columns/ACTIVE"
Name = "is_active"
Rules.Type¶
String containing the name of a PostgreSQL data type. Overrides the object type. Applies only to columns. The type definition can include length, precision and scale:
Like Convert.DataTypes, you can use placeholder to reuse source type parameters.
See Conversion for default data type conversions.
Rules.TrueValues¶
Overrides Convert.TrueValues for the targeted object.
Dump section¶
The dump section configures dumping of the catalog and data.
Dump.Objects¶
List of pattern to exclude or include object paths. This parameter applies only to target objects. When set to true, PostgreSQL Migrator excludes the converted object from audit and dump. Columns cannot be excluded.
Excluding a schema cascades to all object belonging to it.
Use % to match full or partial object name.
If no include pattern is defined, all objects except those explicitly excluded are included.
Order of patterns matters, last matching pattern wins.
[Dump]
Objects = [
# Include all public schema. e.g. public.order
"Schemas/public",
# Exclude all procedures. e.g. public.get_actor
"!Procedures/",
# Except get_film procedure.
"Procedures/sakila.get_film(%)",
# Include sakila.account table.
"Tables/sakila.account",
# Include all tables starting with film. e.g. sakila.film, sakila.film_actor
"Tables/sakila.film%",
# Exclude public.audit schema.
"!Schemas/public.audit",
# Exclude all unique indexes. e.g. public.manager/Indexes/uniq_manager
"!Tables/%/Indexes/uniq_%",
]
Dump.Force¶
Boolean enabling partial dump. Ignores pending annotations. Permanent option for dump –force CLI flag.
Dump.StripZeros¶
Boolean enabling silent drop of zero bytes from text data. Default to false. Oracle accepts zero bytes in strings. PostgreSQL is stricter. Usually application drops zero, especially trailing zero. This applies only to text data type, not binary.