You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Copy file name to clipboardExpand all lines: README.md
+56-38Lines changed: 56 additions & 38 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -63,9 +63,14 @@ run sqlserver2pgsql.pl from.
63
63
64
64
If you just want to convert this schema, run:
65
65
66
-
`sqlserver2pgsql.pl -f input_sql_dump -b output_before_script -a output_after_script -u output_unsure_script`
66
+
```
67
+
sqlserver2pgsql.pl -f input_sql_dump \
68
+
-b output_before_script\
69
+
-a output_after_script\
70
+
-u output_unsure_script
71
+
```
67
72
68
-
The sqlserver2pgsql Perl script processes your SQL raw dump "input_sql_dump" and producse these three scripts:
73
+
The sqlserver2pgsql Perl script processes your SQL raw dump "input_sql_dump" and produces these three scripts:
69
74
70
75
- output_before_script: contains what is needed to import data (types, tables and columns)
71
76
@@ -79,56 +84,31 @@ There is an example of such a conf file (example_conf_file)
79
84
You can also use the -i, -num and/or -nr options:
80
85
81
86
-i : Generate an "ignore case" schema, using citext, to emulate MSSQL's case insensitive collation.
82
-
It will create citext fields, with check constraints.
87
+
It will create citext fields, with check constraints. This type is slower on string comparison operations.
83
88
84
89
-nr : Don't convert the dbo schema to public. By default, this conversion is done, as it converts MSSQL's default
85
-
schema to PostgreSQL's default schema
90
+
schema (dbo) to PostgreSQL's default schema (public)
86
91
87
92
-relabel_schemas is a list of schemas to remap. The syntax is : 'source1=>dest1;source2=>dest2'. Don't forget to quote this option or the shell might alter it
88
93
there is a default dbo=>public remapping, that can be cancelled with -nr. Use double quotes instead of simple quotes on Windows.
89
94
90
95
-num : Converts numeric (xxx,0) to the appropriate smallint, integer or bigint. It won't keep the constraint on
91
-
the size of the scale of the numeric
96
+
the size of the scale of the numeric. But smallint, integer and bigint types are faster than numeric.
92
97
93
98
-keep_identifier_case: don't convert the dump to all lower case. This is not recommended, as you'll have to put every identifier (column, table…) in double quotes…
94
99
95
100
-validate_constraints=yes/after/no: for foreign keys, if yes: foreign keys are created as valid in the after script (default)
96
101
if no: they are created as not valid (enforced only for new rows)
97
102
if after: they are created as not valid, but the statements to validate them are put in the unsure file
98
103
99
-
-sort_size=100000: sort size to use for incremental jobs. Default is 10000, to try to be on the safe side (see below).
100
-
101
-
We don't sort in databases for two reasons: the sort order can be different between SQL Server
102
-
and PostgreSQL, and we don't want to stress the servers. But sorting a lot of data in Java can generate a Java Out of Heap Memory error.
103
-
If you get Out of Memory errors, raise the Java Heap memory (in the kitchen script) as much as you can. If you still have the problem, reduce
104
-
this sort size. You can also try reducing parallelism, having one on two sorts instead of 8 will of course consume less memory. The last problem is that
105
-
if the sort_size is small, kettle is going to generate a very large amount of temporary files, and then read them back sorted. So you may hit the
106
-
"too many open files" limit of your system (default 1024 on linux for instance). So you'll have to do some tuning here:
107
-
- First, use as much Java memory as you can: set the JAVAXMEM environment variable to 4096 (megabytes) or more if you can afford it. The more the better.
108
-
- If you still get Out Of Memory errors, put a smaller sort size, until you can do the sorts (decrease it tenfold each time for example). You'll obviously lose some performance
109
-
- If then you get the too many open files error, raise the maximum number of open files. In most Linux distributions, this is editing /etc/security/limits.conf and putting
110
-
@userName soft nofile 65535
111
-
@userName hard nofile 65535
112
-
(replace userName with your user name). Log in again, and verify with "ulimit -n" that you are now allowed to open 65535 files.
113
-
You may also have to raise the maximum number of open files on the system: echo the new value to /proc/sys/fs/file-max.
114
-
115
-
You'll need a lot of temporary space on disk to do these sorts...
116
-
117
-
You can also edit only the offending transformation with Spoon (Kettle's GUI), so that only this one is slowed down.
118
-
119
-
When Kettle crashed on one of these problems, the temporary files aren't removed. They are usually in /tmp (or in your temp directory in Windows), and start with out_. Don't forget to remove them.
120
-
121
-
-use_pk_if_possible=0/1/public.table1,myschema.table2: enable the generation of jobs doing sorts in the databases (order by in the select part of Kettle's table inputs).
122
-
123
-
1 will ask to try for all tables, or you can give a list of tables (if for example, you cannot make these tables work with a reasonable sort size). Anyway, sqlserver2sql will only accept
124
-
to do sorts in the database if the primary key can be guaranteed to be sorted the same way in PostgreSQL and SQL Server. That means that it only accepts if the key is made only of numeric
125
-
and date/timestamp types. If not, the standard incremental job will be generated.
-k is the directory where you want to store the kettle xml files (there will be
134
114
one for each table to copy, plus the one for the job)
@@ -147,9 +127,43 @@ cleartext, so don't make this directory public):
147
127
-pu : postgresql username
148
128
-pw : postgresql password
149
129
-f : the SQL Server structure dump file
130
+
131
+
150
132
-p : The parallelism used in kettle jobs: there will be this amount of sessions used to insert into PostgreSQL. Default to 8
133
+
-sort_size=100000: sort size to use for incremental jobs. Default is 10000, to try to be on the safe side (see below).
151
134
152
-
You've generated everything. Let's do the import:
135
+
We don't sort in databases for two reasons: the sort order (collation for strings for example) can be different between SQL Server
136
+
and PostgreSQL, and we don't want to stress the servers more than needed anyway. But sorting a lot of data in Java can generate a Java Out of Heap Memory error.
137
+
If you get Out of Memory errors, raise the Java Heap memory (in the kitchen script) as much as you can. If you still have the problem, reduce
138
+
this sort size. You can also try reducing parallelism, having one or two sorts instead of 8 will of course consume less memory. The last problem is that
139
+
if the sort_size is small, kettle is going to generate a very large amount of temporary files, and then read them back sorted. So you may hit the
140
+
"too many open files" limit of your system (default 1024 on linux for instance). So you'll have to do some tuning here:
141
+
142
+
- First, use as much Java memory as you can: set the JAVAXMEM environment variable to 4096 (megabytes) or more if you can afford it. The more the better.
143
+
- If you still get Out Of Memory errors, put a smaller sort size, until you can do the sorts (decrease it tenfold each time for example). You'll obviously lose some performance
144
+
- If then you get the too many open files error, raise the maximum number of open files. In most Linux distributions, this is editing /etc/security/limits.conf and putting
145
+
```
146
+
@userName soft nofile 65535
147
+
@userName hard nofile 65535
148
+
```
149
+
150
+
(replace userName with your user name). Log in again, and verify with "ulimit -n" that you are now allowed to open 65535 files.
151
+
You may also have to raise the maximum number of open files on the system: echo the new value to /proc/sys/fs/file-max.
152
+
153
+
You'll need a lot of temporary space on disk to do these sorts...
154
+
155
+
You can also edit only the offending transformation with Spoon (Kettle's GUI), so that only this one is slowed down.
156
+
157
+
When Kettle crashed on one of these problems, the temporary files aren't removed. They are usually in /tmp (or in your temp directory in Windows), and start with out_. Don't forget to remove them.
158
+
159
+
-use_pk_if_possible=0/1/public.table1,myschema.table2: enable the generation of jobs doing sorts in the databases (order by in the select part of Kettle's table inputs).
160
+
161
+
1 will ask to try for all tables, or you can give a list of tables (if for example, you cannot make these tables work with a reasonable sort size). Anyway, sqlserver2pgsql will only accept
162
+
to do sorts in the database if the primary key can be guaranteed to be sorted the same way in PostgreSQL and SQL Server. That means that it only accepts if the key is made only of numeric
163
+
and date/timestamp types. If not, the standard, kettle-sorting incremental job will be generated.
164
+
165
+
166
+
Now you've generated everything. Let's do the import:
153
167
154
168
```
155
169
# Run the before script (creates the tables)
@@ -163,20 +177,24 @@ You've generated everything. Let's do the import:
163
177
164
178
If you want to dig deeper into the kettle job, you can use kettle_report.pl to display the individual table's transfer performance. Then, if needed, you'll be able to modify the Kettle job to optimize it, using Spoon, Kettle's GUI
This one is highly experimental. I need your feedback ! :). You should only run an incremental job on an already loaded database.
171
187
172
188
It may fail for a variety of reasons, mainly out of memory errors. If you have other unique constraints beyond the primary key, the series of queries generated by sqlserver2pgsql may generate conflicting updates. So test it several times before the migration day, if you really want to try this method. The "normal" method is safer, but of course, you'll start from scratch, and have those long indexes builds at the end.
173
189
174
190
By the way, to be able to insert data into all tables, it deactivates triggers at the beginning and activates them back at the end of the job. So, if the job fails, those triggers won't be reactivated.
175
191
176
-
```
192
+
177
193
You can also use a configuration file if you like:
There is an example configuration file provided. You can also mix the configuration file with command line options. Command line options have the priority over values set in the configuration file.
0 commit comments