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. Basically, the OLTP instance should have fewer or no ETL overhead. The idea is to create foreign tables on the ETL instance connecting to OLTP remote server using *postgres_fdw*. I understand that Postgres will fetch chunks of data from the remote server using the cursor. Can someone please help me if my understanding is right that running a complex query including foreign tables would use resources(RAM,CPU) from the local server? and is the remote server safe from these executions overhead? And if I am wrong which instance resources would Postgres use to run a complex SQL with joins on foreign tables? Thanks in advance!

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 users that have read_write access for each schemas. I want them also have access to these foreign tables and any subsequent views and tables that are created from these tables. My issue is: All the postgres_fdw commands, I've had to run as superuser. I've run the following:

CREATE EXTENSION postgres_fdw
CREATE SERVER DB_A_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'host_name', dbname 'Database_A');
CREATE USER MAPPING FOR read_write_user
  SERVER DB_A_server
  OPTIONS (user 'readonly_DB_A', password 'readonly_DB_A');
GRANT USAGE ON FOREIGN SERVER  DB_A_server to read_write_user;
SET ROLE read_write_user;

I'm unable to run these commands as the read_write_user for Databaase B. My end goal is to have: read_write users import foreign tables as they please. IMPORT FOREIGN SCHEMA completed LIMIT TO (Database_A_table1, Database_A_table2 ) FROM DB_A_server INTO public What would I have to do in order to give the read_write role access to these foreign tables? Can I do something like: GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO ; https://learn.microsoft.com/en-us/answers/questions/741419/create-foreign-data-wrapper-without-superuser-priv.html Thanks!

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/sqlalchemy/discussions/8348 ) and lately we're facing an issue with a specific query (select statement with cte) - this query runs every hour on 10+ workers (python processes) each with their own conditions. When I run this same query on the original server it takes ~6s, using fdw it's around 2-3 minutes. Since we reached 10+ workers these queries are stuck in an "active" state, I can see them is session manager, and after 20 minutes or so I get the following error: SSL SYSCALL error: EOF detected. (The max connections option is set to 200.) After a few of the workers fail with this error, the last ones fail with the following:

ERROR:app.services.cikk.main:(psycopg2.errors.ConnectionFailure) SSL SYSCALL error: EOF detected
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
CONTEXT:  remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ

The postgres_fdw doc says: > The remote transaction uses SERIALIZABLE isolation level when the local transaction has SERIALIZABLE isolation level; otherwise it uses REPEATABLE READ isolation level. > [...] That behavior would be expected anyway if the local transaction uses SERIALIZABLE or REPEATABLE READ isolation level, but it might be surprising for a READ COMMITTED local transaction. This means that the server keeps read and write locks until the end of the transacion, and the read locks are released as soon as the select operation is performed - but it never finishes. Maybe there's a deadlock (since 10+ queries try to use the same tables on the remote server)? If so how can I overcome this issue? Does this mean I can only make queries "synchronously" using fdw to make this work? postgres version: - PostgreSQL 12.10 - (Debian 12.10-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit server keepalive settings: - tcp_keepalives_idle: 7200 - tcp_keepalives_interval: 75 - tcp_keepalives_count: 9 Thanks for the help in advance! ### UPDATE: I think I figured it out. - I had multiple ~3min queries running simultaneously (these queries used the same tables from a foreign server) and they wouldn't finish - I started these manually to monnitor what's going on using pg_stat_activity as @jjanes suggested - What I saw is all of the queries were in an active state, the wait_event_type was IO and the wait_event was BufFileWrite - Read into those a little bit to find out what's going on: - wait_event_type - IO: The type of event for which the backend is waiting. - which is pretty self explanatory - and if the value is IO it means that some IO operation is in progress - Since the wait_event was BufFileWrite I looked into it what it means exactly: Buffered files in PostgreSQL are primarily temporary files. Temporary files are used for sorts and hashes. BufFileWrite is seen when these in memory buffers are filled and written to disk. - So what could cause this? One site (link down below) says: Large waits on BufFileWrite can indicate that your work_mem setting is too small to capture most of your sorts and hashes in memory and are being dumped to disk. and Ensure your sorts largely occur in memory rather than writing temp files by using explain analyze on your query ... - I checked our work_mem value with show work_mem; which was 20971kB - I thought it should be enough so looked further - The clue here for me was the explain analyze part. I created the foreign server with use_remote_estimate: true, which means When use_remote_estimate is true, postgres_fdw obtains row count and cost estimates from the remote server - The solution was to set this property (use_remote_estimate) to false and now it seems to be working the way it should. Useful links: https://www.postgresql.org/docs/current/monitoring-stats.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.iobuffile.html https://docs.dbmarlin.com/docs/kb/wait-events/postgresql/buffilewrite/ https://www.postgresql.org/docs/current/runtime-config-resource.html https://www.postgresql.org/docs/current/postgres-fdw.html

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');

Will the full query get pushed down by postgres_fdw to the remote server? My alternative would be:

select distinct c.*
from fdw_schema.customer c
join fdw_schema.purchase p on c.id = p.customer_id
where p.purchase_date > now() - interval '30 days';

I'm looking at pg_stat_activity on the foreign server, but all I can see is > FETCH 100 FROM c2

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 "testmachine02" DETAIL: connection to server at "192.168.56.10", port 5432 failed: fe_sendauth: no password supplied Here is the command that I used to create the user mapping: CREATE USER MAPPING for app_user SERVER testmachine02 OPTIONS (password_required 'false'); I also created a pgpass file under /root/.pgpass with the following entries: localhost:5432:local_db:app_user:app_user123 192.168.56.10:5432:admin:admin:admin123 192.168.56.10:5432:remote_db:test:test123 Despite these steps, I am still unable to access the table without a password. How can I create a user mapping without a password and access the table?

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 tables: select * from information_schema.foreign_tables But I want to know table names and structure before create it!

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 foreign function on the foreign server? With FDW or any other way.

I am using redis_fdw in a PL/pgSQL function to interact with a Redis server, and I’ve encountered issues with certain queries. Here is the summary:

CREATE FOREIGN TABLE redis_db7 (key text, val text)
SERVER redis_server
OPTIONS (database '7');

Bulk Insert: The following query works perfectly:

INSERT INTO redis_db7 (key, val) 
SELECT key, agg(...) AS val 
FROM mytable 
INNER JOIN ... 
GROUP BY ...;

Update Issue: The following query fails:

UPDATE redis_db7 r 
SET val = n.val 
FROM (
    SELECT key, agg(...) AS val 
    FROM mytable 
    INNER JOIN ... 
    GROUP BY ...
) n
WHERE r.key = n.key;

In this case, I get the error: [XX000] ERROR: could not find pathkey item to sort. Delete Issue: The following query also doesn’t behave as expected:

DELETE 
FROM redis_db7 r 
USING (
    SELECT key
    FROM mytable 
    INNER JOIN ... 
    GROUP BY ...
) n 
WHERE r.key = n.key;

This query sometimes runs without error [XX000] ERROR: could not find pathkey item to sort., but the corresponding keys in Redis are not deleted, and takes too long. My Questions: Are these operations (UPDATE ... FROM and DELETE ... USING) supported by redis_fdw? If not, is there a recommended workaround for bulk updates and deletes when using redis_fdw with Redis? Is there any known issue causing the [XX000] ERROR: could not find pathkey item to sort. error? I would appreciate any guidance or suggestions to resolve these issues. Thanks in advance for your help!

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 records is around 3M but throws an error for more than 3M records. select date_trunc('month', created_date)::date as month, count(distinct category_id) as categorys, count(distinct user_id) as workers from test.tableA A where is_expired = false and pool in (1, 2, 4, 6, 10) and created_date >= (select now() - interval '180 days') group by 1 Error : ERROR: SSL connection has been closed unexpectedly CONTEXT: remote SQL command: CLOSE c48 WARNING: no connection to the server Query failed PostgreSQL said: SSL connection has been closed unexpectedly Is there any configuration that needs to be added for handling large data?

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 → PC1(Postgresql + postgres_fdw) → PC2(Postgresql + myFDW) → tableA@myUniqueDB tableA: - ts as timestamp - data1 as integer Such as when I type the following in psql: select ts from tableA where (ts > '2022-01-01') and (ts '2022-01-01') and (ts '2022-01-01') and (ts '2022-01-01') and (ts < '2022-12-31');

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 PRIMARY KEY, foo_name text ); I also have a second database, where I use the postgres_fdw foreign data wrapper to access the table in the first database. I’d like to insert a row in the foreign table in the second database, without specifying a value for the primary key, letting the remote server “choose” a value in a conflict-free way. Unfortunately, whenever I try to insert data in the foreign table, without selecting the primary key column, postgres_fdw tries to insert rows with NULLs for the columns that weren’t selected, without using the server-defined default values. Hence it fails as the primary key is defined NOT NULL. As far as I can see, there is not way to use a foreign sequence. I’ve seen that I can define a default value when I create a foreign table but, as I understand it, it is implemented on the local side. Is there a way I can insert a row in a foreign table and let the foreign server use its default values?

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 kind of path to the query planner? As a more concrete example, suppose I have:

CREATE TABLE local (local_id integer primary key, foreign_id integer, selective_col text ...);
CREATE FOREIGN TABLE foreign (foreign_id integer primary key, ...)

And my query is something like:

SELECT foreign.*
FROM local
JOIN foreign USING (foreign_id)
WHERE local.selective_col = "happybirthday"

Statistics are such that SELECT foreign_id FROM local WHERE selective_col = "happybirthday" is highly selective and gets scanned first. Now the question is to join the foreign table onto those rows. Can Postgres plan a join that would batch calls to the foreign table, e.g. SELECT * FROM foreign WHERE foreign_id IN (...), or would it degenerate down to one foreign query per tuple from local? Looking at the merge types - it looks like you could do this sort of batching on a nested loop or merge join, but does the planner actually have the ability to do so?

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 DATA WRAPPER postgres_fdw OPTIONS (host 'my_host', port '5432', dbname 'db1'); CREATE USER MAPPING FOR postgres SERVER remote_server OPTIONS (user 'my_user', password 'my_pass'); and Import to my db2 : IMPORT FOREIGN SCHEMA public LIMIT TO (my_view) FROM SERVER remote_server INTO public; All configuration is correct and my view imported succesfully to foreign table but i get : **ERROR: permission denied for view my_view* by the way i tried : GRANT SELECT ON my_view TO postgres; but i still get same error how can i fix that?

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 FOREIGN SCHEMA public LIMIT TO (tbl_test) FROM SERVER foreign_server INTO public; Create a user on the destination / foreign server : CREATE USER fdwuser WITH PASSWORD 'secret' And then do the necessary settings like GRANT. The FDW works. But then I want to do more. What if I assemble all the users that want to access FDW into a GROUP. Then I do the following: - Create group role : demorole1 - create user : fdwuser3 - grant demorole1 to fdwuser3 - create user mapping for group role "demorole1" : CREATE USER MAPPING FOR demorole1 SERVER foreign_server OPTIONS (user 'fdwuser', password 'secret'); - select * from tbl_test; -- **failed**. - grant demorole1 access to tbl_test, schema and foreign server : GRANT ALL ON TABLE tbl_test TO demorole1; GRANT USAGE ON FOREIGN SERVER foreign_server TO demorole1; GRANT USAGE ON SCHEMA public TO fdwuser3; - Test : select * from tbl_test; --**failed**. user mapping not found for user fdwuser3 - CREATE USER MAPPING FOR demorole1 SERVER foreign_server OPTIONS (user 'fdwuser', password 'secret'); - Test again : select * from tbl_test; -- still **failed**. user mapping not found for "fdwuser3" - Do user mapping : CREATE USER MAPPING FOR fdwuser3 SERVER foreign_server OPTIONS (user 'fdwuser', password 'secret'); - select * from tbl_test; --***WORKS*** Question : How can I create a group role so that the users on the local server can be set as it's member so I don't have to do Granting and Creating user mapping every time there is user wants to access FDW table ? Thanks

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 from a public schema from 2 databases (**db B1 and db B2**), lets call it **Server B**. All tables are "stored" on particular schema on server A on 3 different databases (db A1, A2, A3). **There is also streaming replication between this server and Server C** Here is the summary of FDW that I do : - Set up the **Server B** : add entry on pg_hba to accept all connection, create user fdwuser , reload the config file - Set up the **Server A** : 1. Create these schemas on **db A1, A2, A3** to hold the data from **Server B** : CREATE SCHEMA reference; CREATE SCHEMA profile; GRANT USAGE ON SCHEMA profile TO jhon, bon; GRANT USAGE ON SCHEMA reference TO jhon, bon; 2. Create FDW extension for each databases on db A1, A2, A3: CREATE EXTENSION IF NOT EXISTS postgres_fdw; 3. Create foreign servers: CREATE SERVER foreign_profile FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.10.8.40', port '5432', dbname 'db_profile'); ` CREATE SERVER foreign_referensi FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.10.8.40', port '5432', dbname 'db_reference');` 4. Create user mapping: CREATE USER MAPPING FOR jhon SERVER foreign_reference OPTIONS (user fdwuser, password 'secret'); CREATE USER MAPPING FOR bon SERVER foreign_profile OPTIONS (user fdwuser, password 'secret'); CREATE USER MAPPING FOR jhon SERVER foreign_reference OPTIONS (user fdwuser, password 'secret'); CREATE USER MAPPING FOR bon SERVER foreign_profile OPTIONS (user fdwuser, password 'secret'); CREATE USER MAPPING FOR jhon SERVER foreign_reference OPTIONS (user fdwuser, password 'secret'); CREATE USER MAPPING FOR bon SERVER foreign_profile OPTIONS (user referensi, password 'secret'); 5. Import the foreign schemas into database A1, A2, A3: IMPORT FOREIGN SCHEMA public FROM SERVER foreign_profile INTO profile; IMPORT FOREIGN SCHEMA public FROM SERVER foreign_referencd INTO reference; The whole process is a success. **I can access the foreign table on Server B from Server A**. Fine, no problem. Now, I check the WAL files using SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';. **It shows some 2100+ files**. This is worrying. Here is the setting on server A:

archive_command	cd .
archive_mode	on
checkpoint_completion_target	0.9
checkpoint_flush_after	32
checkpoint_timeout	300
wal_level	replica
wal_keep_segments	8
max_wal_senders	10
max_wal_size	8192
hot_standby	on

Then I do some checking: 1. select * from pg_catalog.pg_stat_activity There are 250 entries, dominated by backend_type = parallel worker or client backend 2. select * from pg_catalog.pg_stat_archiver Failed_count = 0; Archived_count = 71; 3. After a copule of minutes, then I run select * from pg_catalog.pg_stat_archiver again. It now says : Failed_count = 0; Archived_count = 3; -- it seems to be reset for some reasons 4. Check the replication : select * from pg_catalog.pg_stat_replication. Results : state = streaming sync_state = async This server seems to be busy I think. Questions: 1. Why WALs keep on increasing? Is this normal? If not, should I reduce them? 2. Why is the archived_count reset? 3. What does this command do: archive_command cd .?

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 (version 10.5 and 14.1) that point to the foreign view. I setup pg_agent to pull this data in each hour and upsert it into a table. This is where it gets weird. I noticed that the foreign table would return 0 records after 6pm, and then after the recent time change, at 5pm. I did not care that much when the data would not pull after business hours, but since the time change it has become an issue. Both the foreign server and the two local servers are all on Mountain Time, which was -6 UTC before the time change and -7 UTC after the time change. This somewhat explains why the issue was initially starting at 6pm, and now at 5pm, because UTC time is now the next day. Unfortunately, the foreign server saves dates in timestamp without timezone format. I know, it is definitely one of the "Don't Do This" scenarios, but it is a legacy database from a 3rd party vendor, so I have no control over that. After the UTC date change, the view query run directly on the foreign server still returns the appropriate records for the day, but the foreign table on my local servers return 0 records. Is this a bug in the postgres_fdw? Does anyone have a workaround?

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/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/ I have followed below steps: Server 1: CREATE DATABASE postgres=# \c a1 You are now connected to database "a1" as user "postgres". a1=# CREATE SEQUENCE seq; CREATE SEQUENCE a1=# CREATE VIEW seq_view AS SELECT nextval('seq') as a; CREATE VIEW a1=# \c postgres You are now connected to database "postgres" as user "postgres". postgres=# create database a2; CREATE DATABASE postgres=# \c a2; You are now connected to database "a2" as user "postgres". a2=# CREATE EXTENSION postgres_fdw; CREATE EXTENSION a2=# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS a2-# (host '192.168.xx.xxx', port '5432', dbname 'a1'); CREATE SERVER a2=# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS (password ''); CREATE USER MAPPING a2=# CREATE FOREIGN TABLE foreign_seq_table (a bigint) SERVER a2-# postgres_server a2-# OPTIONS (table_name 'seq_view'); CREATE FOREIGN TABLE a2=# select * from foreign_seq_table; a --- 1 (1 row) a2=# select * from foreign_seq_table; a --- 2 (1 row) As it can be seen from above example, it's working properly for 2 databases on same server. However, when I proceed with another server, I did below steps there: postgres=# create database kbc; CREATE DATABASE postgres=# \c kbc You are now connected to database "kbc" as user "postgres". kbc=# CREATE EXTENSION postgres_fdw; CREATE EXTENSION kbc=# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS kbc-# (host '192.168.xx.xxx', port '5432', dbname 'a1'); CREATE SERVER kbc=# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS (password ''); CREATE USER MAPPING kbc=# kbc=# CREATE FOREIGN TABLE foreign_seq_table (a bigint) SERVER postgres_server OPTIONS (table_name 'seq_view'); CREATE FOREIGN TABLE kbc=# select * from foreign_seq_table; ERROR: could not connect to server "postgres_server" DETAIL: fe_sendauth: no password supplied The error at last I'm receiving. Is there anything/any step I'm missing here. I can easily ping to old server(where I created sequence) from new server(where I want to use that sequence). pg_hba.conf file settings on both server -> the entries there are md5 or trust only. Any other entry I need to add into configuration file? Any suggested doc for fdw across different servers will also be helpful. Thanks in advance! Note: I can't use UUID due to some application requirement. That's why we need an auto increment numeric column. It picks the highest id value.

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 doing this by pushing data from A to B. I will be using TRIGGER on Server A via either FDW or DBLINK. If there is CRUD operation on A, then it will immediately pushed into B. Previously, FWD has been used on another table, but it really really slow. I want to take a precaution of doing this before I proceed. Questions: 1. Why FWD is slow? This is the newest method of cross-database operation right? 2. For the start, Initially I need to copy data Server A into server B. Then turning on the trigger. Correct? 3. Is FWD better than DBLINK? If so, what makes it better and what does not? 4. Is there any tool that I can use to achieve this? There is Pentaho. But what else are the options? Many thanks

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 table to the database. It's a subset of observation: all latest observations by stream_id and target_id with the target_key value already joined into it.** I know upfront that 99% of the queries against the target table are asking for the latest row of a certain stream_id and target_id. so I thought instead of manually building another table with duplicate data, I'll try to leverage the power of the database to make a temporal table, or a foreign data wrapper or something, that is essentially a hard coded query on the database which looks like a table we can query, (and since it's querying a subset of the data, its much faster). Ok, so that's what I'm looking for but I don't know which technology to use, as I mentioned I've been searching and I found temporal tables, virtual tables and FDW. But I'm a programmer not a DBA so I'm having a hard time telling the difference or understanding which one matches my need. What technology can I use for this?

Is it possible to execute functions that are located on a foreign server using postgres_fdw? If not, is there any available workaround?