Postgresql – Duplicating a postgresql database

Short answer - there's no such solution yet for PostgreSQL if you need online readonly slaves.

There're two major development projects currently going on in this area which are included in PostgreSQL 9.0 (Spring/Summer 2010), namely:

• Synchronous Replication:

http://wiki.postgresql.org/wiki/NTT's_Development_Projects

• Read only hot standby slaves:

http://wiki.postgresql.org/wiki/Hot_Standby

which in combination aim to achieve the ease of use of MySQL-style replication minus the bugs/issues MySQL has plus the reliability users know from PostgreSQL.

All of this was kicked off by a manifest from the PostgreSQL Core Team in 2008:

http://archives.postgresql.org/pgsql-hackers/2008-05/msg00913.php

The PostgreSQL replication solutions to this day with the largest user base are Slony-I (more expensive for writes, makes schema changes fiddly), WAL shipping/walmgr (Slaves can't be used online) and pgQ/londiste from Skype/Skytools (more tools/building blocks than a finished solution).

I've written a few things on Log Shipping, walmgr and Slony-I, see

http://blogs.amd.co.at/mt/mt-search.cgi?blog_id=1&tag=pgrep&limit=20 for more information.

I ended up not using any third-party tool for the data as none of the ones I've tried worked for the large tables. Even SSIS failed. I did use a commercial tool for the schema, though. So my conversion process was as follows:

1. Full Convert Enterprise to copy the schema (no data).
2. pg_dump to export the data from Postgres in "plain text" format, which is basically a tab-separated values (TSV) file.
3. Python scripts to transform the exported files into a format bcp would understand.
4. bcp to import the data into MSSQL.

The transformation step took care of some differences in the formats used by pg_dump and bcp, such as:

• pg_dump puts some Postgres-specific stuff at the start of the file and ends the data with ".", while bcp expects the entire file to contain data
• pg_dump stores NULL values as "\N", while bcp expects nothing in place of a NULL (ie. no data in-between column separators)
• pg_dump encodes tabs as "\t" and newlines as "\n", while bcp treats those literally
• pg_dump always uses tabs and newlines as separators, while bcp allows the user to specify separators. This becomes necessary if the data contains any tabs or newlines, since they're not encoded.

I also found that some unique constraints that were fine in Postgres were violated in MSSQL, so I had to drop them. This was because NULL=NULL in MSSQL (ie. NULL is treated as a unique value), but not in Postgres.