mirror of
https://github.com/dimitri/pgloader.git
synced 2026-01-26 01:21:02 +01:00
a86a606d55
When loading data into an existing PostgreSQL catalog, we DROP the indexes for better performance of the data loading. Some of the indexes are UNIQUE or even PRIMARY KEYS, and some FOREIGN KEYS might depend on them in the PostgreSQL dependency tracking of the catalog. We used to use the CASCADE option when dropping the indexes, which hides a bug: if we exclude from the load tables with foreign keys pointing to tables we target, then we would DROP those foreign keys because of the CASCADE option, but fail to install them again at the end of the load. To prevent that from happening, pgloader now query the PostgreSQL pg_depend system catalog to list the “missing” foreign keys and add them to our internal catalog representation, from which we know to DROP then CREATE the SQL object at the proper times. See #400 as this was an oversight in fixing this issue.
45 lines
1.5 KiB
Fish
45 lines
1.5 KiB
Fish
load database
|
|
from mysql://root@localhost/sakila
|
|
into postgresql:///sakila
|
|
|
|
-- WITH include drop, create tables, no truncate,
|
|
-- create indexes, reset sequences, foreign keys
|
|
|
|
-- WITH batch rows = 10000
|
|
|
|
WITH concurrency = 1, workers = 6,
|
|
max parallel create index = 4
|
|
|
|
-- uncomment the following line to test loading into an already
|
|
-- existing schema, and make sure the schema actually is ready by
|
|
-- having done a first migration without those options:
|
|
, create no tables, include drop, truncate
|
|
|
|
SET maintenance_work_mem to '128MB',
|
|
work_mem to '12MB',
|
|
search_path to 'sakila, public, "$user"'
|
|
|
|
CAST type date drop not null drop default using zero-dates-to-null,
|
|
type datetime to timestamp drop default drop not null using zero-dates-to-null
|
|
|
|
-- type tinyint to boolean using tinyint-to-boolean,
|
|
-- type year to integer drop typemod -- now a default
|
|
|
|
-- MATERIALIZE VIEWS film_list, staff_list
|
|
MATERIALIZE ALL VIEWS
|
|
|
|
ALTER TABLE NAMES MATCHING ~/_list$/, 'sales_by_store', ~/sales_by/
|
|
SET SCHEMA 'mv'
|
|
|
|
ALTER TABLE NAMES MATCHING 'sales_by_store' RENAME TO 'sales_by_store_list'
|
|
ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films'
|
|
|
|
-- INCLUDING ONLY TABLE NAMES MATCHING ~/film/, 'actor'
|
|
EXCLUDING TABLE NAMES MATCHING ~<ory>
|
|
|
|
BEFORE LOAD DO
|
|
$$ create schema if not exists sakila; $$,
|
|
$$ create schema if not exists mv; $$,
|
|
$$ alter database sakila set search_path to sakila, mv, public; $$;
|
|
|