Questions tagged [foreign-data]
The foreign-data tag has no usage guidance.
23
questions
0
votes
1
answer
22
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
66
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
1
answer
406
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
0
answers
80
views
Best practice for managing multiple Postgres DBs with FDW
Requirements: our application (Django app, let's say) has a Postgres instance with a single DB (main_db). For every new account that signs up on the site, a new DB should be created within this DB ...
0
votes
1
answer
124
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
267
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
107
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
115
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
573
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
339
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
2
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');
...
- 23
3
votes
1
answer
318
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,792
2
votes
1
answer
343
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 ...
- 442
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
7
votes
1
answer
3k
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 ...
- 227
1
vote
1
answer
5k
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 ...
- 325
2
votes
1
answer
3k
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
1k
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,681
0
votes
2
answers
61
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
2k
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