Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql-fdw]

Foreign Data Wrappers (FDW) enable access to remote data stores from PostgreSQL.

1
vote
1answer
44 views

Efficient group by on sorted CSV using PostgreSQL FDW

I have a large CSV file sorted on the first column. Here is an extremely simplified version): a 7 a 4 b 6 c 3 c 9 c 2 I want to group by the first column and sum the second. How do I tell the ...
4
votes
1answer
48 views

Postgres, RDW, WHERE condition not pushed to remote (JSONB attribute, using postgres_rdw)

I have a remote postgres table using RDW. It contains a JSONB column and I use values in that JSONB for the WHERE condition. The remote table has a GIN index on the JSONB column and an index on the ...
1
vote
1answer
265 views

Create foreign key on foreign table in postgresql [duplicate]

I need to link my table with another one which is in different database(say table logs in device db and table accounts in user db, both on same server). So using Foreign Data Wrapper I create a ...
1
vote
0answers
181 views

FDW-based Sharding with PostgreSQL 10: Multiple coordinator nodes?

Context I'm currently looking for and comparing different options for sharding data from a Postgres database into multiple ones. The end result may be something like having instances of a web ...
0
votes
1answer
574 views

How do I set the option use_remote_estimate?

Where do I set the "Cost Estimation Options" for postgres_fdw. Specifically I want to add use_remote_estimate. test=# SET use_remote_estimate=true; ERROR: unrecognized configuration parameter "...
1
vote
1answer
150 views

Postgres_fdw LEFT JOIN with foreign table slows query down massively

I have this base query and execution time around 400ms: SELECT s.parcelno FROM export.scans s INNER JOIN plnum_customerno cp ON cp.pl_number = s.parcelno WHERE s.type_of_scan = '05' AND ...
0
votes
0answers
412 views

Creating triggers on foreign data wrapper table in postgresql

I had a requirement to write a query that combined data from two PostgreSQL databases.I followed the steps in the following link and I was successfully able to join tables from local database and ...
1
vote
1answer
538 views

ORDER BY too slow in Foreign Table using postgres_fdw

PostgreSQL v9.6, postgres_fdw Foreign table CREATE FOREIGN TABLE user_info ( id bigint , info jsonb ) SERVER server_test_fdw OPTIONS(SCHEMA_NAME 'public', TABLE_NAME 'user_info_raw' ); -- ...
0
votes
1answer
302 views

postgres_fdw and postgres version upgrade

Case explained I'm facing difficulties concerning performance of queries using a Postgres 9.5.4 database set up on a different server connecting via Foreign Data Wrapper to another instance of ...
3
votes
1answer
47 views

naming a column differently then its original one in foreign data wrapper of PostgreSQL

I have two databases. In each database I have an employee table which differ from each other in the number of columns and column names. Here is what the table looks in the first database: Emp_ID ...
7
votes
1answer
4k views

Permission Denied for Foreign Server

I'm trying to set up a user with limited permissions that would be able to create foreign tables. I have two databases, hr_db and accounting_db. I have created an hr_user user for hr_db and a ...
10
votes
1answer
3k views

postgres_fdw performance is slow

The following query on a foreign takes about 5 seconds to execute on 3.2 million rows: SELECT x."IncidentTypeCode", COUNT(x."IncidentTypeCode") FROM "IntterraNearRealTimeUnitReflexes300sForeign" x ...
1
vote
1answer
47 views

Is there a way to connect a Postgres 9 database client to a Postgres 8 system?

I a now work mainly with Postgres 9.5, but I alsoo need to connect to a number Postgres 8 databases connect them probably as different schemas, if that is possible. Are foreign database wrappers the ...
3
votes
2answers
1k views

Foreign Key (references) constraint on PostgreSQL Foreign Data Wrapper?

It seems to be disallowed to add a Foreign Key constraint to a foreign table. Is there any other way to do this? My two tables have these constrains on the remote server. More specific details: I ...
1
vote
0answers
141 views

Postgresql FDW oracle out of memory

I have a linux box with centos 6.7 and postgres 9.3.9. I have a foreign data wrapper throught an oracle 11.2 box and a jdbc driver. It's working fine since several months, but yesterday I had to ...
1
vote
0answers
1k views

Foreign table not using index

I have a table on database temp_db named external_objects and an index on the field id, which is an MongoDB id (like 5458d717cd07870859000003). When I explain this select at this database, I got this ...
0
votes
0answers
46 views

doing a insert while selecting an specific user

I'm importing and legacy database to a new database, and changing data format. In this legacy database, I have data as following: Interaction id: 5458d717cd07870859000004 user_id: ...
5
votes
2answers
1k views

manual execution of specific remote query with postgresql_fdw

In 9.4b2, postgresql_fdw doesn't know how to "push down" aggregate queries on remote tables, e.g. > explain verbose select max(col1) from remote_tables.table1; ...