154 questions
1
vote
2
answers
177
views
PostgreSQL "create foreign table" (file_fdw) on files with single quotes in filename generates ERROR
I have a CSV file with a silly filename (for example): 'test_1111 _2_ABC Hr. Schmidt_2024.csv'
postgres@minisforum:~/16/import$ ll
insgesamt 1212
drwxrwxr-x 3 postgres postgres 4096 Okt 14 19:16 ./...
1
vote
1
answer
196
views
Slow insert using postgres foreign data wrapper
insert into my_foreign_table select * from my_local_table
My columns are two integers and two floats. The number of rows was >100M. I left it running for a few days, but killed it because it took ...
- 6,469
0
votes
0
answers
105
views
Do Aurora Serverless v2 supports Foreign Data Wrapper (FDW)?
Currently I'm using Aurora Serverless v1 which don't supports FDW(foreign data wrappers). I need to setup FDW for some requirement. I just want some ways to tackle this situation.
1.First approach may ...
1
vote
0
answers
371
views
PSQL "server closed the connection unexpectedly" for a local Foreign Data Wrapper server
I had this foreign data wrapper server set up between my 2 databases stored locally and it worked perfectly for the longest time until today and I can't seem to figure out why. I did recently upgrade ...
- 1,338
1
vote
1
answer
2k
views
Can logical replication be stopped anytime I want?
I have two databases in different servers and data streaming from one server (X database) to another server (Y database) with the help of logical replication.
My question is the following: is there ...
0
votes
1
answer
345
views
Trigger Not Working Through Foreign Table in Postgres
I have two Postgres Databases DB1 and DB2. I have a table Tab1 on DB1 that holds some records. There is another table Tab2 on the same DB (DB1) that is managed by a trigger on Tab1 ( basically a count ...
- 27
0
votes
1
answer
1k
views
slow performance with select query on postgresql remote fdw table
I have 2 postgresql instances on different computers(X and Y) on the same network.
Y server has PgAdmin installed on and connected to both instances.
On the Y instance, I created FDW connection for a ...
- 668
0
votes
0
answers
72
views
unable to create table from foreign table taking long time
taking long time to create table with foreign tables
query
select distinct a."scid", a."tt", a."p"
into tableA
from db1_foreigntable1 a
join db1_foreigntable2 b on a.&...
- 11
0
votes
0
answers
428
views
select query taking long time in postgres foreign tables
My select query taking long time in postgres foreign tables. It is taking 15sec in MSSQL server.
PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit
I am querying ...
- 11
1
vote
1
answer
2k
views
Error connecting to postgresql foreign server using SSL
I'm having trouble using SSL to connect to a foreign server using postgres_fdw. I had this working on a different server, but seemingly the same process is not working on my new machine.
I mostly ...
- 73
-1
votes
2
answers
1k
views
Foreign Table with Parameter in Query
How can i use variables in a query of a foreign table?
I could ALTER the option (query) of the foreign tabel and add the variable into the where clause but i am concerned that this locks the foreign ...
1
vote
0
answers
448
views
Postgres upsert
I am trying to do update my database tables via upserts from a postgres-to-postgres foreign data wrapper.
I have two code snippets below. The first is using the constraint on the primary key and the ...
3
votes
1
answer
8k
views
Could not open extension control file POSTGRESQL
While trying to execute PostgreSQL extension in PostgreSQL 13
CREATE EXTENSION postgres_fdw;
I am getting an error
CREATE EXTENSION postgres_fdw "Could not open extension control file "/...
0
votes
1
answer
504
views
Use foreign data wrapper for forwarding
The following architecture: Server A has access to Server B, Server B has access to Server C, but Server A has no direct access to Server C.
I can create a foreign server on Server B like this:
CREATE ...
- 2,855
1
vote
1
answer
4k
views
IMPORT FOREIGN SCHEMA IF NOT EXISTS
Currently IMPORT FOREIGN SCHEMA does not seem to support IF NOT EXISTS so how to solve this case? If I have a script that runs on every reboot for example I would get
ERROR: relation "xxx" ...
- 371
0
votes
1
answer
864
views
PostgreSql foreign table select fails due to special characters rows
I just set up a new foreign table and it works as intended if I just select the "ID" (integer) field.
When I add the "Description"(text) field and try to select the table, it fails ...
- 2,652
1
vote
1
answer
1k
views
Postgres foreign wrapper and SELECT * WHERE ... performance
On a Postgres server A, I am calling a query, that employs foreign table(s) from server B (FROM mav4_gmd_data):
EXPLAIN ANALYZE VERBOSE
SELECT
d....
- 11
0
votes
0
answers
264
views
Is it possible to redirect SELECT statements on different server using postgres foreign data wrapper
We are using PostgreSQL version 13 and our database has a high volume of read request about 95% and 5% write request. We have a read only replica in slave mode as well. So to offload my primary server ...
- 331
0
votes
1
answer
471
views
PostgreSQL foreign data wrapper mimic Constraints
Using PostgreSQL 9.6.9
It appears that constraints are not available when using the Postgres foreign data wrapper https://github.com/postgres/postgres/blob/e1ae40f381d0582981b1e63856bd4b060cfe2d53/src/...
- 531
1
vote
1
answer
1k
views
Selecting data from Postgres foreign table via oracle_fdw does not use the index on Oracle side
in short: we are facing a problem that full table scan is performed on remote Oracle DB instead of using the index.
Setup:
Postgres 12.3 in an enriched docker container with oracle base client, ...
- 11
2
votes
1
answer
1k
views
Foreign table created with inherits stuck
I've been trying to create a foreign table in my PSQL database. The point here is to concatenate the same steps table from 2 different databases. I want to use INHERITS to copy the schema.
I have a ...
- 5,704
0
votes
0
answers
390
views
PostgreSQL connection to MS Access 2013 64bit Trouble
I'm trying to set a connection between a PostgreSql 12 64bit on a Windows 10 (64 bit) with a MS Access 2013 (64 bit) on a windows 8.1 64bit using some of the fdw's. At this regard I:
- Set a System ...
- 314
0
votes
1
answer
1k
views
Joining Tables between Multiple Foreign Servers with Foreign Data Wrapper Causes Performance Issue
One of my legacy PHP applications is using a PostgreSQL database with Foreign Data Wrapper. This database has a local table and two foreign servers set up (one pointing to database A, another pointing ...
- 583
10
votes
1
answer
3k
views
How to force evaluation of subquery before joining / pushing down to foreign server
Suppose I want to query a big table with a few WHERE filters. I am using Postgres 11 and a foreign table; foreign data wrapper (FDW) is clickhouse_fdw. But I am also interested in a general solution.
...
- 908
3
votes
1
answer
10k
views
Foreign-data wrapper "postgres_fdw" does not exist (even if it does)
Using PostgreSQL 10.10, from superuser postgres:
CREATE EXTENSION postgres_fdw;
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO my_user;
Then when doing the following from my_user:
CREATE SERVER ...
- 23.4k