<story><title>Foreign data wrappers: PostgreSQL&apos;s secret weapon?</title><url>https://www.splitgraph.com/blog/foreign-data-wrappers</url></story><parent_chain><item><author>claytonjy</author><text>Off-topic, but are FDW&amp;#x27;s a reasonable way to migrate data between databases?&lt;p&gt;We have most of our data in a Postgres DB with a layout we like: multiple &amp;quot;core&amp;quot; or low-level schemas and a higher-level API schema which is how all other services interact with it (reads and writes). We also have a few much smaller but very important additional SQL DBs (Postgres and MySQL). There&amp;#x27;s no good reason all this data can&amp;#x27;t or shouldn&amp;#x27;t live together (it&amp;#x27;s really all one &amp;quot;service&amp;quot;), and plenty ways it would make our architecture and dev work simpler (we join across them all the time).&lt;p&gt;So, I figure for each smaller DB, I could expose it with an FDW from the DB we like, and use those tables in that DB&amp;#x27;s API schema. Once we settle on good API &amp;amp; usage patterns there, I would then copy data from the FDW tables into local tables, update references in the API schema, and finally drop the FDW and decom the other DB&amp;#x27;s.&lt;p&gt;Is that sane? I know the usual caveats about order-of-operations, potentially writing to two places for some time to ensure no data is lost, etc. and am not worried about those.</text></item></parent_chain><comment><author>wenc</author><text>If you want to continuously move data, there&amp;#x27;s an open-source tool called SymmetricDS that does db replication that sounds like it might fit your use case.&lt;p&gt;&lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.symmetricds.org&amp;#x2F;&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.symmetricds.org&amp;#x2F;&lt;/a&gt;</text></comment>
<story><title>Foreign data wrappers: PostgreSQL&apos;s secret weapon?</title><url>https://www.splitgraph.com/blog/foreign-data-wrappers</url></story><parent_chain><item><author>claytonjy</author><text>Off-topic, but are FDW&amp;#x27;s a reasonable way to migrate data between databases?&lt;p&gt;We have most of our data in a Postgres DB with a layout we like: multiple &amp;quot;core&amp;quot; or low-level schemas and a higher-level API schema which is how all other services interact with it (reads and writes). We also have a few much smaller but very important additional SQL DBs (Postgres and MySQL). There&amp;#x27;s no good reason all this data can&amp;#x27;t or shouldn&amp;#x27;t live together (it&amp;#x27;s really all one &amp;quot;service&amp;quot;), and plenty ways it would make our architecture and dev work simpler (we join across them all the time).&lt;p&gt;So, I figure for each smaller DB, I could expose it with an FDW from the DB we like, and use those tables in that DB&amp;#x27;s API schema. Once we settle on good API &amp;amp; usage patterns there, I would then copy data from the FDW tables into local tables, update references in the API schema, and finally drop the FDW and decom the other DB&amp;#x27;s.&lt;p&gt;Is that sane? I know the usual caveats about order-of-operations, potentially writing to two places for some time to ensure no data is lost, etc. and am not worried about those.</text></item></parent_chain><comment><author>eska</author><text>That&amp;#x27;s exactly what I have done before and it worked well. You just have to ensure that you can map the data from the other database properly, and that the FDW you&amp;#x27;re using is of good quality (since they&amp;#x27;re not officially part of Postgres).</text></comment>