Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to convert a large-ish Postgres DB (500 GB) to SQL Server 2012. After investigating a few third-party tools and being disappointed in features, performance, or both, I started pursuing a simple pg_dump/bulk import solution. Things looked promising until I realized that pg_dump represents NULLs in plaintext as "\N", which causes the bulk insert to vomit in cases of type mismatch. Even if I were to automate the pg_dump process to produce a single file per table, some of the individual tables involved are very large (20-50 GB) and performing a comprehensive find-replace using even fast file editing options in linux, or a Perl script, add too much overhead to the time required for the import/export.

I'm hoping there's a way to modify the NULL representation in the pg_dump output that I'm not aware of, or failing that, to get some recommendations for alternative approaches to this process in terms of tools or strategies. Thanks in advance for your help.

share|improve this question
1  
Did you check the tools in the Postgres wiki: wiki.postgresql.org/wiki/… some of them can easily export text files where you can configure NULL representaion. –  a_horse_with_no_name Jun 21 '13 at 20:28
    
Could you sastify my curiosity? What is the reason for you to convert from PostgreSQL to SQL Server? –  MatheusOl Jun 22 '13 at 15:39
add comment

4 Answers

up vote 2 down vote accepted

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.

With COPY you can do change the output format (including NULL), example:

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 getcommand.sql:

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.

EDIT1: Added 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.


EDIT2: Parallelism

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 pg_dump.

share|improve this answer
    
This is absolutely perfect. I had completely ignored COPY, as I assumed it was destined to be slower than pg_dump, but after a little bit of testing, I stand corrected. It handles the NULLs, and it even strips the header and footer rows produced by pg_dump (I had an efficient solution for those, but now I don't need to worry about them at all!). Thank you so much for your help - this looks like exactly what I needed. –  speedofsound Jun 21 '13 at 20:37
    
I'm glad I could help. There is actually no reason to think that COPY could be slower than pg_dump, because pg_dump does use COPY internally (it just append the result on the output file). –  MatheusOl Jun 22 '13 at 15:36
add comment

Well, there is no way to change NULL representation in pg_dump output (aside from changing sources), but please rememebr that you can modify the dump file on the fly, and not when it's done. This usually makes the change overhead negligible.

Example:

pg_dump ... | perl -pe 's/(^|\t)\\N(\t|$)//g if s/(^|\t)\\N(\t|$)//g' > dump.file
share|improve this answer
    
Thanks - I was just discussing this possibility with our linux admin, actually. I just wasn't sure if this would be efficient enough (though obviously more efficient than saving to disk and modifying after-the-fact, of course). But it seems like the best available option, regardless. And thank you very much for providing a specific command for this purpose so I didn't have to dig up a regex reference! :) –  speedofsound Jun 21 '13 at 17:09
    
Upon further review, this method still adds considerable overhead (a 20 minute test dump was increased to about 35 minutes). It's better so far than other available options that I know of, but I'm wondering if there's still a superior method available. –  speedofsound Jun 21 '13 at 19:23
add comment

@MatheusOl provided a solution with COPY. I propose a couple of improvements:

CREATE OR REPLACE FUNCTION f_copy_all() RETURNS void AS
$func$
DECLARE
  _sql text;
BEGIN
   FOR _sql IN
      SELECT format($$COPY %s WITH NULL '' TO '/path/to/%1$s'$$, c.oid::regclass)
      FROM    pg_class c
      JOIN    pg_namespace n ON c.relnamespace = n.oid
      WHERE   c.relkind='r'
      AND     n.nspname !~~ ALL ('{information_schema,pg_%}')
      AND     n.nspname = 'oeti'
   LOOP
      RETURN QUERY EXECUTE _sql;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT f_copy_all()
  • Instead of calling psql twice, create a function to call, that's cleaner and faster.

  • Execute dynamic SQL in a PL/pgSQL function with EXECUTE.

  • Use format() (Postgres 9.1+) for safe (against SQLi and typos), short and clean text concatenation.

  • Use c.oid::regclass as file name, since the table name alone may or may not be unique and you may be overwriting files. The object identifier type regclass is automatically schema-qualified where needed to make the identifier unique when coerced to text.

  • You easily extend this function to pass table or schema names or patterns to be processed.

  • If you'd want to parallelize, call the function once per schema (for instance) in separate sessions.

share|improve this answer
add comment

Another option would be to handle the data conversion within an SSIS package when loading the data. You could handle the value change back to NULL in process, then convert the data type back to what it should be.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.