Several problems in the process of sqlserver2postgresql migration

1. PostgreSQL Cross-Platform Migration Tool Migration Toolkit Guide to use: http://www.enterprisedb.com/docs/en/8.4/mtkguide/Table%20of% 20contents.htm#topofpage

2, may be the problem of the program, the Migration tool will be the Jtds drive connection string is considered Oracle to connect, Google later: http://my.oschina.net/congqian/blog/106518, find this instruction specify the type of data source to do the operation:

Java-dprop=toolkit.properties-jar Lib/edb-migrationtoolkit.jar-sourcedbtype Sqlserver-targetdbtype PostgreSQL- Alltables-targetschema public dbo

3, in the process of migration with the above tools, I found that can hinder the entire table migration problem is the type matching problem, the MSSQL bit type is built as a Boolean field during the PostgreSQL table, However, the data loading error: The bit data can not be converted to Boolean data. I was just beginning to write a Python script to migrate these tables, and later found that the problem is relatively single, directly to the source table bit type into the Smartint type, and then migrated over, ready to again PostgreSQL inside again back 。 (plus) It's weird that the bit type of one of the tables has passed, but the bit in the database is incompatible with the byte type in the Java code, or the field type is changed to Smallini

4, then the above problem said, 3 done after the data migration, modified the Web application data source configuration file, start the Web application, do a few previously changed the type of table read and write test, found that the normal available, the original bit type in SQL Server automatically generated hibernate The type of bit in the ORM configuration file is Java.lang.byte,byte is 8 bytes and smallint is exactly 8 bits, which is compatible with the available. It's an accidental luck event.

5, another problem, before the tool data migration, the system often reminds of UTF-8 coding problem, I think the migration process all the string data will be converted to UTF-8 data automatically, and the Web application is GBK encoding, so guess is not the migration also need to do the encoding set of all converted to UTF-8, But the actual discovery is not so, even before using Python in SQL Server query out the string with the U-word, in PostgreSQL found that it seems to have no U-head.

6, RHEL6.0 postgressql binary version of the installation directory is/var/lib/pgsql, and other platforms, the data files and configuration files are within the subdirectory of this directory.

7, after data migration processing, PostgreSQL primary key ID generation mechanism and SQL Server implementation mechanism slightly different, from the identity of SQL Server to the sequence in PostgreSQL, the process of tool migration, only copy data, The primary key needs to be set manually (or by script), the primary key field is set, and the current value of each sequence is set once for the current table's ID maximum value. My Web Application project is hibernate-based, because hibernate's configuration file is also modified: 1, is to modify the main configuration file of the SQL dialect type, 2, is to change the primary key generation mechanism of each table from identity to sequence ( The current version of this setting is usually done by the @ annotation mechanism)

SQL Server is like this:

<name= "id"  column= "id"  type= "Java.lang.Integer ">       <class=" Identity "/>  </ ID >  

This is the case when you change to PostgreSQL:

<IDname= "id"column= "id"type= "Java.lang.Integer">      <Generatorclass= "sequence">             <paramname= "sequence">Here is the sequence name corresponding to the table ID, you can find the table corresponding sequence name in the ADMINIII sequence directory, the name of the general sequence is tablename_id_seq</param>         </Generator>   

Several problems in the process of sqlserver2postgresql migration