0

I'm trying to setup a new server with foreign tables (using postgres_fdw) that weren't foreign tables previously, and I have some OneToOneFields pointing to these tables. This doesn't work out of the box - OneToOneFields use foreign keys, and postgres_fdw does not support foreign keys for foreign tables.

The foreign tables are in a read-only database on the same server.

Is there an easy way to get this working?

Crag
  • 1,723
  • 17
  • 35
  • Not sure what you think doesn't work. All a OneToOneField needs is an id pointing at the other table. – Daniel Roseman Oct 27 '17 at 17:41
  • I'd like to be able to create my data schema, but the foreign keys can't be created, so the initial migration fails. I was wondering if there was some project that managed this already - maybe through some type of proxy tables, views, or creating triggers that would handle this somehow... – Crag Oct 28 '17 at 13:12

1 Answers1

0

After a little more research, I think the answer is 'don't do that'. Handling foreign keys for foreign tables isn't implemented, and any solution that tried to bridge that gap would have serious performance and/or consistency issues.

pglogical looks like a better solution - instead of pulling the data in through postgres_fdw, replicate the tables into the new database so they're local. There are other projects for replicating just specific tables, but pglogical is included with PostgreSQL 10.

Crag
  • 1,723
  • 17
  • 35