3

I've got a postgres database which relies on the use of foreign tables to support the loading of large data sets based on user-provided (system sanitized) tables. I'd use COPY, but the tables loaded on a recurring (but non-daily) cycle and have complex load parameters which we've built into functions (using a combination of UPDATE WHERE, DELETE WHERE, TRUNCATE AND INSERT, INSERT, ON CONFLICT)

The process works well, and is stable. While I'm generally happy with it, I realize that it opens up the possibility that at some point the file paths specified in the CREATE FOREIGN TABLE command will change (e.g. from /home/postgres/path_primary/table.csv to /home/postgres/path_secondary/table.csv).

My question: Is there a way to point the base of the file path to a non-static value to abstract that change? Or would I be better off writing a script to collect the list of foreign tables, drop them, and rebuild the DDL with new filepath arguments?

1

My question: Is there a way to point the base of the file path to a non-static value to abstract that change? Or would I be better off writing a script to collect the list of foreign tables, drop them, and rebuild the DDL with new filepath arguments?

You can point to a symlink. However, I wouldn't. I would keep things running just the way you're doing it now. What's the reason to do that? I imagine under any reasonable load the DDL is not an issue. But, it may be worth experimenting and reporting back.

  • Thank you! This makes sense to me, I appreciate the feedback. – Adam Bethke Oct 19 '17 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.