Customize Conversion
In this guide, you will write conversion rules to adapt the PostgreSQL catalog to your needs. Please take a look on previous Get Started guide to initialize a project with a ready-to-go TOML configuration file.
1. Rename objects¶
To rename an object, such as a schema or a table,
you can define a conversion rule in your configuration file.
For example, to rename a schema from sakila to public,
add the following rule in your pg_migrate.toml file:
Path reflects the internal path of an object after the source database inspection.
You may use it as an unique identifier to choose the object to convert.
See Rules.Path reference for more details.
You must convert your catalog to apply the new conversion rule.
It scans the source catalog stored in Source.json file,
located under .pg_migrate folder,
finding the sakila schema and its objects.
The newly-converted tree is written to another file, Target.json.
$ pg_migrate --verbose convert
...
09:57:38 DEBUG Converting object identifier. from=sakila to=public path=Schemas/sakila
...
$
You should generate DDL of this catalog by using the dump command
with --target files options to control the wanted conversions.
Use --force to ignore all pending annotations.
$ pg_migrate dump --target files --schema-only --force
10:12:59 INFO Writing queries to dump/ directory.
10:12:59 INFO Create schema. name=public path=00001-Schemas-public-create.sql
10:12:59 INFO Create table. name=public.store path=00019-Tables-public.store-create.sql
10:12:59 INFO Create table. name=public.part_range path=00015-Tables-public.part_range-create.sql
10:12:59 INFO Create table. name=public.staff path=00018-Tables-public.staff-create.sql
...
10:12:59 INFO Create foreign key. name=public.rental fk=rental_staff_id_fkey path=20062-Tables-public.rental-ForeignKeys-rental_staff_id_fkey-create.sql
10:12:59 INFO Create foreign key. name=public.payment fk=payment_staff_id_fkey path=20059-Tables-public.payment-ForeignKeys-payment_staff_id_fkey-create.sql
10:12:59 INFO Databases connections closed. source=0 target=0
$
Files are located inside the dump/ folder.
For instance, the film table will be correcty created in a public schema.
--
-- Name: film; Type: TABLE; Schema: public; Owner: unknown
--
CREATE TABLE "public"."film" (
"film_id" integer GENERATED ALWAYS AS IDENTITY,
"title" varchar(255),
"description" text,
"release_year" smallint,
"language_id" smallint,
"original_language_id" smallint,
"rental_duration" smallint,
"rental_rate" numeric(4, 2),
"length" integer,
"replacement_cost" numeric(5, 2),
"rating" text,
"special_features" text[],
"last_update" timestamp
);
2. Change column types¶
Sometimes, you may need to change a column type,
such as a bigger integer or a date without time.
You can choose to change a specific column from its path and the Type attribute
or provide a global data type conversion rule with the Convert.DataTypes parameter.
For example, we can increase the email column size up to 255 characters in
customer and staff tables.
[[Convert.Rules]]
Path = "Tables/sakila.customer/Columns/email"
Type = "varchar(255)"
[[Convert.Rules]]
Path = "Tables/sakila.staff/Columns/email"
Type = "varchar(255)"
$ pg_migrate --verbose convert
...
16:06:12 DEBUG Converting column type. from=varchar(50) to=varchar(255) path=Tables/sakila.customer/Columns/email
16:06:12 DEBUG Converting column type. from=varchar(50) to=varchar(255) path=Tables/sakila.staff/Columns/email
...
$
The following configuration defines a rule
adding timezone to any column named timestamp.
$ pg_migrate --verbose convert
...
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/actor/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/address/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/category/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/city/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/country/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/customer/Columns/create_date
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/customer/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/film/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/film_actor/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/film_category/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/inventory/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/language/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/payment/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/payment/Columns/payment_date
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/rental/Columns/rental_date
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/rental/Columns/return_date
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/rental/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/staff/Columns/last_update
16:06:12 DEBUG Converting column type. from=timestamp to="timestamp with timezone" path=sakila/Tables/store/Columns/last_update
...
$
As a result,
tables like staff will respect conversion rules
during the dumping step.
--
-- Name: staff; Type: TABLE; Schema: public; Owner: unknown
--
CREATE TABLE "public"."staff" (
"staff_id" smallint GENERATED ALWAYS AS IDENTITY,
"first_name" varchar(45),
"last_name" varchar(45),
"address_id" integer,
"picture" bytea,
"email" varchar(255),
"store_id" smallint,
"active" boolean,
"username" varchar(16),
"password" varchar(40),
"last_update" timestamp with timezone
);
Congratulation! You performed the basic configuration of your migration project. You should read Convert reference to go deeper with conversion rules.
3. Select Objects to migrate¶
You may select which objects to migrate because some are useless in your new schema or to workaround PostgreSQL Migrator.
Do this using Dump.Objects pattern list.
This option selects target objects from dump.
You will always see excluded objects in Target catalog.
Dump.Objects receives catalog path and accepts LIKE pattern.
To find catalog path of an object,
open Web UI using ui command,
click PostgreSQL to browse Target catalog,
search object,
click the copy button in object details.
For example:
Dump.Objects = [
"!Roles", # exclude all roles.
"!Tables/sakila.address", # exclude a table.
"!Views/%.bi_%", # exclude all views prefixed with BI.
]
See TOML reference for Dump.Objects for details.