3 ms·
Off-topic, but are FDW's a reasonable way to migrate data between databases? We have most of our data in a Postgres DB with a layout we like: multiple "core" o
by claytonjy 6y ago
Off-topic, but are FDW's a reasonable way to migrate data between databases?
We have most of our data in a Postgres DB with a layout we like: multiple "core" 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's no good reason all this data can't or shouldn't live together (it's really all one "service"), and plenty ways it would make our architecture and dev work simpler (we join across them all the time).
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's API schema. Once we settle on good API & 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's.
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.
- eska 6y agoThat'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're using is of good quality (since they're not officially part of Postgres).
- sradman 6y ago> are FDW's a reasonable way to migrate data between databases? As I was trying to find a reference to when SQL Anywhere introduced proxy tables, I came across a post [1] that discussed the speed improvement in proxy table bulk loads introduced in SQL Anywhere 11. It is reasonable to do the same with PostgreSQL's FDWs if the underlying query optimizer detects when a bulk load technique should be used. Sounds like a good experiment to run. [1] http://sqlanywhere.blogspot.com/2010/01/omigosh-proxy-tables-are-fast.html http://sqlanywhere.blogspot.com/2010/01/omigosh-proxy-tables...
- SOLAR_FIELDS 6y agoBack in my DBA days, I used the MSSQL equivalent, "Linked Servers", to set up regularly scheduled ETLs. It's a nice way to reason about tables since you can test your joins in real time. The only real implication here is performance, as someone else mentioned in another comment, joins can be quite slow. In my case my ETLs were usually once daily and the fact that they took 1 minute instead of 1 second was not a huge deal.
- michaelwda 6y agoI've also used this, combined with an agent job to keep data in sync between a primary system of record and a replica. In my case I was moving account number information between iSeries db2 and an ArcGIS instance.
- atwebb 6y agoSQL has a similar concept now called Polybase which can scale out as well.
- wenc 6y agoIf you want to continuously move data, there's an open-source tool called SymmetricDS that does db replication that sounds like it might fit your use case. https://www.symmetricds.org/ https://www.symmetricds.org/
- brightball 6y agoI don’t see why not. I use them all the time to restore a selection of deleted records from a linked backup copy.