Hi everyone, I have some trouble using postgres_fdw in order to display some data from a Postgres database I do not control in a Postgres database that I do control. I filled out the form from the wiki below and would appreciate any tips. * A description of what you are trying to achieve and what results you expect.: I am trying to import the public schema of a Postgres instance I do not control (I will call it "remote"), but have read-only access to, into a Postgres instance I fully control (I will call it "local"), using the foreign data wrapper postgres_fdw. The result I expect is that the import runs successfully and that I have access to the foreign tables. However, I currently fail to achieve this. I always get an error message regarding the TimeZone setting. I also tried, without success, different versions for the local instance, namely 9.4.14 so I would be as close as possible to the remote version and 13.14 to try an up-to-date version of Postgres 13. * PostgreSQL version number you are running: remote: "PostgreSQL 9.4.13 on x86_64-apple-darwin20.2.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit" local: "PostgreSQL 13.0 (Debian 13.0-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit" * How you installed PostgreSQL: remote: I do not know. It is an instance that runs as part of a software I have no further insight in. local: I am using the official PostgreSQL docker image. * Changes made to the settings in the postgresql.conf file: remote: "name" "current_setting" "source" "application_name" "pgAdmin 4 - CONN:2930678" "client" "bdr.log_conflicts_to_table" "on" "configuration file" "bytea_output" "hex" "session" "checkpoint_completion_target" "0.9" "configuration file" "checkpoint_segments" "32" "configuration file" "client_encoding" "UNICODE" "session" "client_min_messages" "notice" "session" "DateStyle" "ISO, MDY" "session" "default_text_search_config" "pg_catalog.english" "configuration file" "dynamic_shared_memory_type" "posix" "configuration file" "effective_cache_size" "19046MB" "configuration file" "lc_messages" "C" "configuration file" "lc_monetary" "C" "configuration file" "lc_numeric" "C" "configuration file" "lc_time" "C" "configuration file" "listen_addresses" "*" "configuration file" "log_line_prefix" "%t [%d]" "configuration file" "log_timezone" "Europe/Berlin" "configuration file" "logging_collector" "on" "configuration file" "maintenance_work_mem" "1587MB" "configuration file" "max_connections" "100" "configuration file" "max_replication_slots" "10" "configuration file" "max_stack_depth" "2MB" "environment variable" "max_wal_senders" "10" "configuration file" "max_worker_processes" "10" "configuration file" "port" "5432" "configuration file" "shared_buffers" "7936MB" "configuration file" "ssl" "on" "configuration file" "ssl_ca_file" "root.crt" "configuration file" "ssl_cert_file" "server.crt" "configuration file" "ssl_key_file" "server.key" "configuration file" "tcp_keepalives_idle" "10" "configuration file" "TimeZone" "Europe/Berlin" "configuration file" "track_commit_timestamp" "on" "configuration file" "wal_level" "logical" "configuration file" "wal_receiver_timeout" "20min" "configuration file" "wal_sender_timeout" "20min" "configuration file" "wal_sync_method" "fsync_writethrough" "configuration file" "work_mem" "81264kB" "configuration file" local: "name" "current_setting" "source" "application_name" "pgAdmin 4 - CONN:511122" "client" "bytea_output" "hex" "session" "client_encoding" "UNICODE" "session" "client_min_messages" "notice" "session" "DateStyle" "ISO, MDY" "session" "default_text_search_config" "pg_catalog.english" "configuration file" "dynamic_shared_memory_type" "posix" "configuration file" "lc_messages" "en_US.utf8" "configuration file" "lc_monetary" "en_US.utf8" "configuration file" "lc_numeric" "en_US.utf8" "configuration file" "lc_time" "en_US.utf8" "configuration file" "listen_addresses" "*" "configuration file" "log_timezone" "Etc/UTC" "configuration file" "max_connections" "100" "configuration file" "max_stack_depth" "2MB" "environment variable" "max_wal_size" "1GB" "configuration file" "min_wal_size" "80MB" "configuration file" "shared_buffers" "128MB" "configuration file" "TimeZone" "Etc/UTC" "configuration file" * Operating system and version: remote: Mac mini (2018), running macOS Monterey 12.5.1, output of uname -a: "Darwin mac 21.6.0 Darwin Kernel Version 21.6.0: Wed Aug 10 14:25:27 PDT 2022; root:xnu-8020.141.5-2/RELEASE_X86_64 x86_64 local: A Linux VM running Debian 11.3 ARM64, output of uname -a: "Linux debian-gnu-linux-11 5.10.0-28-arm64 #1 SMP Debian 5.10.209-2 (2024-01-31) aarch64 GNU/Linux" * What program you're using to connect to PostgreSQL: pgAdmin 4 * Is there anything relevant or unusual in the PostgreSQL server logs?: No, not that I know of. * What you were doing when the error happened / how to cause the error: First, I successfully enabled the postgres_fdw extension, created the remote server configuration and the user mapping. Then, I tried to import the public schema locally, using: IMPORT FOREIGN SCHEMA public FROM SERVER remote INTO public; * The EXACT TEXT of the error message you're getting, if there is one: ERROR: invalid value for parameter "TimeZone": "UTC" CONTEXT: remote SQL command: SET timezone = 'UTC' SQL state: 22023 Now, I gather that the postgres_fdw extension sets this option for its connection here https://github.com/postgres/postgres/blob/936e3fa3787a51397280c1081587586e83c20399/contrib/postgres_fdw/connection.c#L677 but I really can not figure out why it fails in this scenario and what I could do about it. I thought my next step might be to look into how to create my own foreign data wrapper and basically use a modified version of postgres_fdw where I remove the TimeZone setting. It seems like there should be an easier way, though. :-) Does anyone have an idea for me? Kind regards, Adnan Dautovic
