Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [postgresql-fdw]

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

For more information please see this page.

50 questions
15
votes
1 answer

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 accounting_user user for accounting_db. I only want the…
Shaun
  • 251
  • 1
  • 2
  • 4
13
votes
1 answer

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 WHERE x."IncidentDateTime" >= '05/01/2016' GROUP…
J-DawG
  • 283
  • 3
  • 11
9
votes
2 answers

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 only really need it as an annotation because some…
odinho - Velmont
  • 221
  • 3
  • 7
8
votes
1 answer

Create foreign key on foreign table in postgresql

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 foreign table(I check it with running select * from…
Bonje Fir
  • 183
  • 1
  • 4
6
votes
2 answers

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; QUERY PLAN …
zwol
  • 251
  • 1
  • 9
5
votes
2 answers

Create shared user mapping in PostgreSQL foreign data wrapper server

We are using a foreign data wrapper to query across databases on a single PostgreSQL RDS. The foreign data wrapper server needs a user mapping for each user who will query against the remote server. However, adding the user mapping for each user may…
Brylie Christopher Oxley
  • 267
  • 1
  • 4
  • 10
5
votes
1 answer

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 hostname-attribute that I use for filtering.…
Pascal
  • 153
  • 5
4
votes
2 answers

IMPORT FOREIGN SCHEMA fails with Password error for 127.0.0.1 but not for remote IP (of same server)

I have a Postgres server in Google SQL Cloud with multiple databases. I want to create a FOREIGN DATA WRAPPER between them. If I use the server's remote IP it works: CREATE SERVER "some_db_fdw" FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host…
WillyC
  • 143
  • 1
  • 5
4
votes
1 answer

How do I get PostgreSQL FDW to push down the LIMIT to the (single) backend server?

I've set up a PostgreSQL FDW server with the following table, sharded by user_id over four servers: CREATE TABLE my_big_table ( user_id bigint NOT NULL, serial bigint NOT NULL, -- external, incrementing only some_object_id bigint…
gertvdijk
  • 227
  • 1
  • 4
  • 14
4
votes
1 answer

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 "use_remote_estimate"
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
3
votes
1 answer

Error: SSL connection closed unexpectedly. Error while fetching large data using postgres FDW

I am using postgres 13 and created a foreign server with use_remote_estimate: on and fetch_size: 10000. tableA here is a partition table by created_date. The query is running fine if the number of records is around 3M but throws an error for more…
Sushma Yadav
  • 31
  • 1
  • 2
3
votes
1 answer

Execute foreign functions using FDWs in Postgres

Is it possible to execute functions that are located on a foreign server using postgres_fdw? If not, is there any available workaround?
Lev
  • 233
  • 1
  • 9
3
votes
0 answers

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 application in different regions (one in Europe, one in…
ginmrt
  • 31
  • 1
3
votes
2 answers

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' ); -- user_info_raw is a large table (100 million records,…
Luan Huynh
  • 2,010
  • 7
  • 27
  • 37
2
votes
1 answer

postgres_fdw passes/does not pass the LIMIT clause to the remote destination

I'm making a FDW (Foreign Data Wrapper) in PostgreSQL for myself using C. And in the test environment below, myFDW receives the query with/without LIMIT clause case by case. Test environment: psql → PC1(Postgresql + postgres_fdw) → PC2(Postgresql +…
toratora7964
  • 21
  • 2
1
2 3 4