Questions tagged [postgresql-fdw]
Foreign Data Wrappers (FDW) enable access to remote data stores from PostgreSQL.
50 questions
0
votes
0
answers
24
views
Exporting a whole iSeries database to reinject it in PostgreSQL
I am trying to recover a client's data hosted on an iSeries server. It is very probably a DB2. There are 2000+ tables to transfer.
I have access with jdbc and tried with DBeaver (which gives the best ...
- 197
2
votes
1
answer
71
views
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 → ...
- 21
1
vote
1
answer
751
views
Set a column value using the (remote) default when inserting a row into a foreign table
I have a database with a table whose primary key is a serial column, or a column with a locally-computed default value that prevents conflicts, for instance:
CREATE TABLE foo (
foo_id serial ...
- 223
1
vote
1
answer
2k
views
permission denied for view my_view in postgres_fdw
I have 2 databases db1 & db2,
I created postgres_fdw into db1 & db 2 with:
CREATE EXTENSION postgres_fdw;
I configured server and user mapping into db2 :
CREATE SERVER remote_server FOREIGN ...
1
vote
1
answer
856
views
Create user mapping without password how to configure authentication
I am trying to create a user mapping in PostgreSQL without a password, but I am encountering an error that says.
local_db=> select * from employee;
ERROR: could not connect to server "...
- 11
0
votes
1
answer
174
views
postgres_fdw: from where, is the resource utilized? remote or local?
I have an OLTP database and ETL jobs are running in the same database in the background.
I was thinking of separating the OLTP and ETL instances so that resource utilization would be distributed. ...
- 312
0
votes
1
answer
96
views
Can Postgresql FDW avoid n+1 when planning joins?
Does the PostgreSQL executor / planner have the ability to avoid n+1 querying of FDW tables? If so, what conditions have to be in place for this to happen, e.g. does the FDW need to emit a specific ...
- 719
4
votes
1
answer
5k
views
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 ...
- 41
0
votes
1
answer
145
views
WALs increase for some reason
This is new to me. I have been searching the net but no result.
I have a server, Server A, that has been running for a few weeks, it is a new server. I set up FDW for this server to access all tables ...
- 327
0
votes
2
answers
752
views
Grouping user into Role to access FDW table
Recently I setup Forward Data Wrapper. Here is what I do:
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.125.1', port '5432', dbname 'template1 ');
IMPORT ...
- 327
0
votes
2
answers
3k
views
How can I get list of foreign server's tables in postgresql?
I create a foreign server in postgresql. Before adding foreign table, I want to get list of tables in this foreign server.
Is there any way I can do this?
This sql query show the created foreign ...
- 123
0
votes
1
answer
918
views
Postgres FDW: Is it possible to execute a foreign function in a foreign server after importing the foreign schema?
I imported a foreign schema using IMPORT FOREIGN SCHEMA.
Now I'm able to access all the table content in that schema, but still unable to access any of the functions.
Is it possible to execute a ...
0
votes
1
answer
3k
views
PostgreSQL Foreign Data Wrappers - Simultaneous queries won't finish
We're using foreign data wrappers in a database which points to another server (which is a read-only replica). We run scheduled jobs using python ( more on this here: https://github.com/sqlalchemy/...
- 13
0
votes
1
answer
3k
views
How to give Read_write users access to foreign tables imported through foreign data wrapper?
I had a question regarding the extension postgres_fdw. I am trying to use postgres_fdw to import foreign tables from DB A to DB B. These databases are on the same host.
In Database B, I have a set of ...
- 11
0
votes
1
answer
1k
views
POSTGRESQL table replication between 2 servers
I want to to replicate A TABLE from one server A to another B so that A and B always in-sync.
This table on server A has like 4 - 5 million rows, whereas the table B is empty.
What I want to do is ...
- 327
0
votes
1
answer
48
views
how to add a query to postgres and have it show up as a table [closed]
I'm not sure what technology I need because I'm not a DBA, but I'm sure there's a good way to do this.
I have a postgres database with this schema (top two tables exist):
So I'm trying to add the 3rd ...
- 103
3
votes
1
answer
2k
views
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?
- 243
1
vote
0
answers
315
views
Spatial database architecture with Apache Parquet, PostgreSQL, and PostGIS on on-premises bare-metal S3/MinIO cluster
Designing storage architecture for Petabyte-scale geospatial data; starting from scratch. Creating a MinIo cluster to store the objects in S3 buckets. To store the metadata, I’m considering the Apache ...
- 111
1
vote
1
answer
446
views
Postgres timezone issue when using postgres_fdw
I have a view stored on a foreign Postgres server (v 9.3) which pulls all of the data that was posted for the day.
I have foreign tables (using postgres_fdw) stored on my local Postgres servers (...
- 73
4
votes
2
answers
3k
views
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&...
- 143
6
votes
2
answers
3k
views
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. ...
1
vote
1
answer
391
views
PostgreSQL FDW: is it necessary to specify sever and user for every link to a database on the same server (with identical user privileges)?
I have a server with roughly 7000 databases that all have a practically identical schema. I've been querying them and collecting information about their contents in a separate database on the server. ...
- 117
0
votes
1
answer
217
views
How to have different server config on replication server for postgres_fdw?
I have a postgres sharding based on postgres_fdw and postgres partitioning, lets say 3 machine (1 coordinator and 2 Shard machines). What I want to do is have read replica version of same? How should ...
- 93
0
votes
1
answer
608
views
Slow insert in Postgresql when sharding using declarative partitioning and postgres_fdw?
We have been trying to partition a Postgres database on google cloud using the built-in Postgres declarative partitioning and postgres_fdw as explained here.
We are running commands as follow:
Shard ...
- 93
0
votes
1
answer
450
views
Feasible to have thousands of foreign data wrappers in Postgres?
We are creating enterprise-grade SaaS where we will have many 1,000s of customers. We are considering creating a database in the Postgres server for every customer to ensure enterprise-grade security/...
- 115
0
votes
1
answer
1k
views
Postgres foreign data wrappers weird join behavior
So I have tables in two different databases that I need to analyze together. I am using fdw to do so. However I am running in to weird behavior with my joins.
Keep in mind the following snippets are ...
- 3
0
votes
1
answer
168
views
How often is Postgres parsing a foreign table based on file_fdw?
I have a foreign table defined with file_fdw, which reads the data from a CSV file. Postgresql parses the CSV file and converts the date for example from YYYYMMDD to YYYY-MM-DD. This works fine.
My ...
- 379
0
votes
1
answer
1k
views
FDW use in postgres
I need to create a sequence and need to use the sequence for auto increment id for more than 2 databases on 2 separate servers. I'm referring below doc for that(for test purpose): https://paquier.xyz/...
- 380
1
vote
1
answer
454
views
Are subqueries pushed down to a remote data source by postgres_fdw?
Given this query:
select *
from fdw_schema.customer c
where exists
( select *
from fdw_schema.purchase p
where c.id = p.customer_id
and p.purchase_date > now() - interval '30 days');
...
- 386
0
votes
2
answers
408
views
Why does PostgreSQL FDW talk to multiple backend servers for this point query?
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, -- ...
- 227
4
votes
1
answer
5k
views
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, -- ...
- 227
0
votes
1
answer
1k
views
Copy large object field into remote PostgreSQL DB [closed]
We have configured postgres_fdw to move data from a DB to remote DB. It works fine but the large object field's data in source db not getting copied to remote DB.
This is the statement used to copy:
...
2
votes
1
answer
855
views
PostgreSQL: Check the structure of foreign tables
How can I check if the structure of a foreign table was changed in the source database in order to know when to re import it. Foreign table was imported by foreign-data wrapper postgres_fdw (...
- 437
1
vote
1
answer
295
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 ...
- 111
5
votes
1
answer
339
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 ...
- 153
9
votes
1
answer
9k
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 ...
- 193
3
votes
0
answers
343
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 ...
- 31
4
votes
1
answer
6k
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 "...
- 65.8k
0
votes
0
answers
2k
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 ...
3
votes
2
answers
3k
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' );
-- ...
- 2,010
0
votes
1
answer
933
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 ...
2
votes
1
answer
1k
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 ...
- 532
15
votes
1
answer
27k
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 ...
- 251
13
votes
1
answer
9k
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
...
- 283
1
vote
1
answer
58
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 ...
- 1,093
9
votes
2
answers
3k
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 ...
- 221
1
vote
0
answers
216
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 ...
- 41
2
votes
0
answers
3k
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
...
- 125
0
votes
0
answers
59
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: ...
- 125
6
votes
2
answers
2k
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;
...
- 251