I think the best for your situation is to use the
COPY ... TO with one file for each table, and use
pg_dump only for schema.
COPY you can do change the output format (including
COPY foo TO '/path/to/foo.output' WITH NULL '<your null>';
You can even use CSV or other format (I don't know what MSSQL recognizes).
To automatize, you can easily check all the tables with a shell script. To achieve that, first edit as needed the following and save in
SELECT 'COPY '||c.oid::regclass||' TO ''/path/to/'||c.oid::regclass||''' WITH NULL '''';'
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind='r' AND n.nspname !~ '^(information_schema$|pg_)';
With that, you use the following to execute the copys (you can even use
xargs to parallelize it):
psql <conn_info> -XAtf getcommand.sql | psql <conn_info>
It will generate and execute the
COPY commands to export all tables data.
regclass to filename and corrected a syntax error on the generated COPY command (the
WITH NULL goes at the end, not begining). Added -X to
psql to avoid unexpected input through pipe.
I have made the test with
xargs to parallelize it, and the result is the following:
psql -XAtf getcommand.sql | xargs -d '\n' -L 1 -P <num jobs> psql -c
On a simple test I did, with a small database I use for tests (~800MB), with no parallelism it took 6 seconds to complete, and with 4 jobs it took little more than 1 second. I imagine that for a 500GB database on a good server, the difference will be bigger. If you want to compare:
time ( psql -XAtf getcommand.sql | xargs -d '\n' -L 1 -P 4 psql -c )
time psql -XAtf getcommand.sql | psql
NOTICE: For those thinking in use it for parallel backups (e.g. to substitute
pg_dump): don't do it! Because this solution uses different sessions (and transactions) for each table, so it can't provide consistency on the result if there are other users working on the database. On PG 9.2+ we can use transaction snapshot to guarantee consistency, but PG 9.3 (still beta) already has parallelism on