Alexander Kuznetsov on SQLblog.com
has an interesting series going entitled with Learning PostgreSQL. In the series he focuses on what it takes to move
a SQL Server database and App to PostgreSQL and highlights some of the key differences between the two platforms that you should watch out for.
I recommend it to any SQL Server developer planning to make the switch to PostgreSQL or any PostgreSQL consultant tasked with the job
and not familiar with the intricacies of SQL Server.
In PostgreSQL 9.3 Postgres_FDW: A Test Drive
we talked about taking Postgres Foreign Data Wrapper for a test drive. One downside of the Postgres FDW and actually most PostgreSQL FDWs is that
the foreign table structure can't be inspected from the source and the fields have to be explicitly stated in the foreign table definition. If you have a lot of tables to script, this can
quickly become tedious. For our planned used cases, we plan to script Foreign tables from a source database for tables that are bulky and rarely change
and then build materialized views against those for faster performance where needed. To help in this end, we wrote a quick
SQL function that you install on the source database and when run will generate foreign table creation statements to install on a target database.
One thing that was important to us was that the function properly handle views and typmod types since PostGIS now uses typmod heavily and many of our databases have spatial data
and complex views we'd like to link in as foreign tables.