Questions tagged [foreign-data]
Use this tag for questions related to the definition and usage of the SQL feature "External Data" (foreign data wrapper, foreign servers, foreign tables), as defined in the SQL standard, part 9, SQL/MED, or as implemented by the PostgreSQL database management system. If the question is about the PostgreSQL implementation, please also use the [postgresql] tag. Question that deal with the standard should also use the [sql] tag.
29
questions
1
vote
1
answer
279
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 ...
- 193
0
votes
1
answer
61
views
Refreshing development environment from backup that contains FDW in Postgres
Background:
We recently added a foreign data wrapper from one of our Postgres (v14) databases to another Postgres (v14) database in a different system. It works fine in our production environment, but ...
- 282
1
vote
0
answers
43
views
PostgreSQL: data retrieval from a private (authentication-based) RESTful web API using a foreign data wrapper
In some cases, one want to feed or populate some PostgreSQL database tables using data fetched from the web, e.g. through a RESTful API (serving JSON features).
I've recently discovered foreign data ...
- 342
0
votes
1
answer
58
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. ...
- 154
0
votes
1
answer
1k
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
1
vote
1
answer
2k
views
How to re-create or update schema on Postgresql Foreign Data Wrappers?
I had two postgresql database server, each running on separate physical server. One is running version 13.3 (say server A) and the other 9.6 (say server B).
I have installed postgresql's foreign data ...
- 11
0
votes
1
answer
510
views
How I can create a foreign table using a foreign data wrapper that references to a remote one with the same name?
I have 2 databases one named logs that has the table:
http_log:
id: serial
method: Varchar(10)
url: varchar(150)
I also have an another database named archiving that also has a table named http_log:
...
0
votes
1
answer
490
views
PostgreSQL : Refresh a materialized view base on a foreign table
I've got a foreign table from a distant csv host on a external webserver.
I need to enhance this table so I create a materialized view to add some extra fields and join with internal data. Making a ...
- 1
0
votes
2
answers
2k
views
user mapping hide password
I would like to perform queries over different postgresql11-databases. Therefore I created a data wrapper and a user mapping as following:
CREATE server 'test' foreign data wrapper postgres_fdw
...
- 101
0
votes
1
answer
896
views
Listing active PostgreSQL foreign data wrappers
I have a few foreign data wrappers set up from my main PostgreSQL database to other databases. Is there a table I can query to list all of the FDWs? Unfortunately select * from information_schema....
- 103
-1
votes
1
answer
373
views
Postgres 12 Scalability using table Partitioning and Foreign Data Wrappers
I have looked through the archives and I cant find any discussions around the following topic.
I have a fairly in depth question that I would appreciate some guidance with.
Current environment
...
- 11
0
votes
2
answers
168
views
Using IMPORT FOREIGN SCHEMA with Greenplum database
I have setup a PostgreSQL 12 database and am trying to connect to a Greenplum database in order to create proxy tables. I am able to connect to the Greenplum db, but I get an error when I try to use ...
- 1
1
vote
0
answers
117
views
Querying foreign table with SP-GiST index results in "ERROR: cache lookup failed for type 0" [duplicate]
I'm using PostgreSQL 11.3 (compiled by Visual C++ build 1914, 64-bit) and I want to access a table in another Postgres database using postgres_fdw but since the original table has an SP-GiST index on ...
0
votes
1
answer
739
views
Postgres special character rendering
I have an old Sql Server db with a ‘free entry’ field that is being accessed via a Postgres 10.6 foreign table. This field contains a lot of garbage characters (higher than Ascii 128) which Postgres ...
- 1
0
votes
0
answers
405
views
Permission issue with a view built on top of foreign tables
I am consolidating data from several postgresql databases, all with the same schema, into a new one. To do that I mapped the tables I am interested in the new DB as foreign tables with postgres_fdw. ...
- 101
3
votes
2
answers
1k
views
FDW Permissions error using MD5 encrypted password
When creating Foreign Data Wrappers in Postgres 9.6, if I use the following:
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'database', port '5432');
...
- 33
3
votes
1
answer
382
views
Dynamic paths for postgres file_fdw (csv)
I've got a postgres database which relies on the use of foreign tables to support the loading of large data sets based on user-provided (system sanitized) tables. I'd use COPY, but the tables loaded ...
- 133
3
votes
2
answers
2k
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' );
-- ...
- 1,942
2
votes
1
answer
682
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
4
votes
2
answers
2k
views
postgres_fdw slower then copy + scp + copy (~12 times)
foreign server 9.2
local server 9.5
table is 10GB
data transfer performed on same network interface as foreign server works
no indexes set on data
old way:
copy to - 2:36
scp - 08:17
copy from - ...
- 1,173
1
vote
0
answers
2k
views
Can I use Foreign Data Wrapper for SQL Server in Windows Server 2012?
I use PostgreSQL and SQL Server. I want to get SQL Server data in PostgreSQL.
I found PostgreSQL Foreign Data Wrapper. There are installation packs for CentOS and Ubuntu. However, I want to use FDW ...
- 11
8
votes
1
answer
5k
views
Create Materialized View from foreign table
I'd like to create a simple materialized view from a table which lies in a different database. The two databases are on the same server.
What do I have to add to make the query access the foreign ...
- 237
1
vote
1
answer
7k
views
SQL Query; Admin Log System; Foreign Keys; Retriving multiple rows form different tables at once
Table A (users)
id | name | ip
52433 Yashas 1.1.1.1
512 Admin 5.6.7.8
Table B (admin_record)
id | admin | time | date | reason | action
52433 512 2312352 ...
- 365
3
votes
1
answer
4k
views
Postgres 9.5 foreign table inheritance not using indexes
In PostgreSQL 9.5.0 I have a partitioned table that collects data by months. I tried to use the new PostgreSQL feature of foreign table inheritance and pushed one month of data to another PostgreSQL ...
1
vote
1
answer
2k
views
Foreign Data Wrappers & Windows Authentication
I been trying to get data from a SQL Server to use it on a PostgreSQL database. After a previous question I got into use FOREIGN DATA WRAPPERS, first I try with tds_fdw but after a successfull install ...
- 1,689
0
votes
2
answers
82
views
Check attribute table referenced foreign key
I have two tables a and b like this:
a: id, type
b: a_id, ...
There is a limitation for which the references of a in b can only be those which have type equal to a limited set of values.
Is it ...
- 101
5
votes
1
answer
3k
views
Rely on .pgpass in CREATE USER MAPPING
I am trying to create a script which creates a postgres-fdw connection between two postgres 9.4 databases. The script (which is checked in under version control), has been relying on pgpass to do ...
- 151
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
1
vote
1
answer
1k
views
FDW, replication and CREATE SERVER - will it work?
We have two servers (master, slave) and we want to use the FDW. The two databases to be connected with the FDW are both on the master (and the slave of course). When we are to CREATE SERVER we would ...
- 145