Getting the management to let go of their death grip on MSSQL was the first (big!) hurdle, but I succeeded.
Now I need to prove that PostgreSQL will work for us, but I can't seem to find a painless way to migrate my binary data. I've searched the MS docs, Postgres docs, pg TechDocs, List archives, Google, you name it. I have a table in MSSQL 2000 with 20 million rows, about 60 columns. One of the columns is of type VARBINARY.
** pgAdminII Migration Wizard (1.4.12): ignores the binary column when importing. To be fair, this possibility is mentioned in the pgAdminII docs. However, it is totally painless for all my other non-binary tables and I love it!
** MS DTS Transformation: Migrates the data (including varbinary) perfectly. However, the migration fails at about 400,000 rows because it runs out of memory (1.5 GB physical memory on the MSSQL machine). I'm trying to just import small chunks of the table using a query for the source, but it is very slow and tedious even over Gigabit Ethernet.
** bcp: I'd like to use bcp to do a text import, but I can't figure out how to make the binary data work on either end. It appears to me that Postgres COPY requires literal binary data to be in octal format (e.g. '\\047'). bcp outputs the binary data as an non-escaped ASCII string of hex values (e.g. DF9B52A3). I guess I could write a Java program to convert the hex to escaped octal, then run COPY. This would probably be pretty slow (both from a 20-million row performance standpoint and my personal productivity).
Is there a better way to do this?
Source machine:
Dual P3 1.1 GHz, 1.5 GB RAM, single 18GB SCSI disk
Win2k Server SP3, MS SQL 2000 SP3, pgAdmin 1.4.12, pgODBC 7.02.00.05
Destination machine:
Dual Xeon 2.0 GHz, 4.0 GB RAM, 104GB 6-disk RAID-10 dedicated to /usr/local/pgsql/data
RedHat 8.0
PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903
Thanks!!!
Roman Fail
Sr. Web Application Developer
POS Portal, Inc.