The Stack Overflow podcast is back! Listen to an interview with our new CEO.
10

To grant permissions for the foreign server: GRANT USAGE ON FOREIGN SERVER hr_db TO accounting_user; More details available in the example on official page https://www.postgresql.org/docs/9.6/static/contrib-dblink-connect.html


7

If you use use_remote_estimate be sure to run ANALYZE the foreign table (I see estimations pretty close with the returned, you'd probably did it). Also, the pushdown improvements are not available in <9.5 version. I also assume that you have the same table structure on the remote server either (including indexes). If a bitmap is needed due to the low ...


6

You can't create a FOREIGN KEY constraint that references a table in either a different database or through a foreign data wrapper. A foreign key must reference a (base) table* in the same database (a base table: not a view, not a foreign data wrapper table). If you can change your design to have these 2 databases as 2 schemas in the same database then you ...


3

postgres_fdw doesn't use GUC variables. The settings for postgres_fdw are actually set with options under CREATE SERVER, CREATE SERVER [IF NOT EXISTS] server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ] FOREIGN DATA WRAPPER fdw_name [ OPTIONS ( option 'value' [, ... ] ) ]; Or with ALTER SERVER Change options for the server. ADD, SET, ...


3

I assume that the oid in question is the Oid of a large object, and you are wondering why the large object isn't copied when the oid field is copied. Large objects cannot be copied via postgres_fdw. You'd have to use the large object API (or pg_dump) to move them from one database to the other. If the Oids are already taken on the second database, you'll ...


2

You can name the columns of your foreign table whatever you want. For example, after an IMPORT FOREIGN SCHEMA ... you can do ALTER FOREIGN TABLE myschema.mytable RENAME a_column TO another_column; (For CREATE FOREIGN TABLE, you have to specify the different column name in the OPTIONS clause to the column: CREATE FOREIGN TABLE foreign.employee (emp_id ...


2

For the time being, it seems that the best available option is to create a view on the remote server that encapsulates the query needing to be "pushed down". postgres_fdw is happy to define and use foreign tables backed by views on the remote, and regular old query optimization within the view does the Right Thing. For instance, given CREATE VIEW ...


2

I believe the reason for this is that in details->>'hostname', 'hostname' is of a text type, and PostgreSQL cannot prove to itself either that the same default collation is in use on both sides of the FDW, or that the ->> operator will always return the same answer regardless of collation of its right argument. So it won't push it over. There has ...


2

PostgreSQL lacks the insight into the "mod" function that it would need to declare that user_id=97 also implies that mod(user_id,4)=1. If you provide that insight manually, it would likely honor it: WHERE user_id=$1 and mod(user_id,4)=mod($1,4) This has nothing to do with FDW. If all partitions/tables were local, the answer would remain the same. You ...


1

This works as expected since version 12 with commit d50d172e51 indeed, but only for non-partitioned (non-sharded) tables. Running the query directly against the foreign table name (my_big_table_mod4_s0), the LIMIT is pushed down correctly. Reported as a bug ; I don't see a technical reason why this should not work with partitioning (partition pruning) ...


1

I'm afraid not. There is no such thing as a "sorted csv" file format, all you can have is a csv file that just happens to be sorted. I could take your example file and move the top line to the bottom and it would still be a valid csv. If the fdw made that trusted your assertion and you made a mistake it would return incorrect results which is obviously ...


1

Sort operation is performed on local postgres server, not on remote. If your code always sort the results, you can just create a view on remote postgres with order by clause and then create a foreign table pointing to the view. On remote server, adding a view CREATE VIEW vw_user_info_raw SELECT id, info FROM user_info_raw ORDER BY id On local server,...


1

Join and aggregate pushdowns are supposed to go live on Postgres 10, not Postgres 9.6 which is why remote queries are not being sent the way I expected it to.


1

Via PostgreSQL FDW you can connect to any other PostgreSQL (as well as other databases), regardless of the version of the location (remote, local, or same) or version (older, same, or newer) of that other PostgreSQL.


1

The PostgREST team gave a potential solution: faking the foreign key constraints. They read the pg_catalog.pg_constraint table to get this information. The idea was that I could manipulate the search_path and create my own pg_constraint table where I could make the fake foreign keys: CREATE TABLE pg_constraint (LIKE pg_catalog.pg_constraint); ALTER ROLE ...


1

You could create local copies of the remote tables, and run periodic processes that would add new records, remove deleted records, and update changed records. How large are the remote tables? I guess not too big since you were planning on creating local materialized views from them. Normal tables that you keep synchronised would have a similar impact. Once ...


Only top voted, non community-wiki answers of a minimum length are eligible