1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
load database
from mysql://root@localhost/sakila?sslmode=disable
into postgresql:///sakila
-- WITH include drop, create tables, no truncate,
-- create indexes, reset sequences, foreign keys
-- WITH batch rows = 10000
WITH on error stop, concurrency = 2, workers = 6,
prefetch rows = 25000,
multiple readers per thread, rows per range = 50000,
max parallel create index = 4-- ,
-- quote identifiers
SET PostgreSQL PARAMETERS
maintenance_work_mem to '128MB',
work_mem to '12MB',
search_path to 'sakila, public, "$user"'
SET MySQL PARAMETERS
net_read_timeout = '120',
net_write_timeout = '120'
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'
ALTER TABLE NAMES MATCHING ~/./ SET (fillfactor='40')
ALTER SCHEMA 'sakila' RENAME TO 'pagila'
-- INCLUDING ONLY TABLE NAMES MATCHING ~/film/, 'actor'
-- EXCLUDING TABLE NAMES MATCHING ~<ory>
BEFORE LOAD DO
$$ create schema if not exists pagila; $$,
$$ create schema if not exists mv; $$,
$$ alter database sakila set search_path to pagila, mv, public; $$;
|
