Re:Foreign data wrappers: PostgreSQL's secret weapon?
by wenc on Wednesday July 08, 2020 @01:03PM
(#23771817)
If 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.
Re:Foreign data wrappers: PostgreSQL's secret weapon?
by SOLAR_FIELDS on Wednesday July 08, 2020 @12:10PM
(#23771258)
Back 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.
Re:Foreign data wrappers: PostgreSQL's secret weapon?
by michaelwda on Wednesday July 08, 2020 @01:01PM
(#23771798)
I'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.
Re:Foreign data wrappers: PostgreSQL's secret weapon?
by atwebb on Wednesday July 08, 2020 @01:25PM
(#23772022)
SQL has a similar concept now called Polybase which can scale out as well.
Re:Foreign data wrappers: PostgreSQL's secret weapon?
by eska on Wednesday July 08, 2020 @11:23AM
(#23770791)
That'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).
Re:Foreign data wrappers: PostgreSQL's secret weapon?
by sradman on Wednesday July 08, 2020 @11:28AM
(#23770837)
> 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...
Re:Foreign data wrappers: PostgreSQL's secret weapon?
by brightball on Wednesday July 08, 2020 @06:25PM
(#23774879)
I don’t see why not. I use them all the time to restore a selection of deleted records from a linked backup copy.
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.