Questions tagged [foreign-data]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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: ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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....
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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. ...
user avatar
  • 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'); ...
user avatar
  • 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 ...
user avatar
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' ); -- ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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; ...
user avatar
  • 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 ...
user avatar
  • 145