Cloud SQL (postgres) foreign data wrapper connection time out to replica instance

Question

Hey we have some issues using postgres_fdw extension in CloudSQL when connecting to a replica instance. We forwarded the problem to the GCP support but their answer was not really related to our problem. Thus, we have another try here:

Problem

we are using the postgres_fdw extension in our application in order to be able to run joins across databases.

We have 2 databases on the master instance (which has a replica instance)

• analytics
• animal (the name has historically reasons :D)

and used to have a foreign data wrapper connection from analytics -> animal.

This works fine using the IP address of the CloudSQL master instance as describe in the documentation.

The foreign data wrapper settings get synced to the replica instance and therefore still point to the master instance (-> still using the master instance's IP).

A "diagram" would look like this:

master --postgres_fdw(IP address A)--> master (IP address A)

replica --postgres_fdw(IP address A)--> master (IP address A)

In order to avoid that we hit the master instance when querying the replica, we wanted to change the setup to:

master --postgres_fdw(IP address B)--> replica (IP address B)

replica --postgres_fdw(IP address B)--> replica (IP address B)

We wanted to change this so that our foreign data wrapper always points to the replica instance (also on master - which is fine) by changing the IP address when setting it up.

However, connecting to the replica via its IP and the foreign data wrapper is not working. We always get a connection timeout when importing the schema (on master)

Setup script which we run on the master

BEGIN;

    CREATE EXTENSION IF NOT EXISTS postgres_fdw;

    --- Update foreign data server
    DROP SERVER IF EXISTS "animal_fdw" CASCADE;
    CREATE SERVER "animal_fdw" FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '$FDW_HOST', dbname '$TARGET_DB_NAME');

    --- Create user mapping for default agrando user and metabase
    CREATE USER MAPPING FOR "$USER" SERVER "animal_fdw" OPTIONS(user '$USER', password '$PASSWORD');

    --- Update Schema
    DROP SCHEMA IF EXISTS "$SCHEMA_NAME";
    CREATE SCHEMA "$SCHEMA_NAME";

    --- The following line times out
    IMPORT FOREIGN SCHEMA public from SERVER "animal_fdw" into "$SCHEMA_NAME"
COMMIT;

• works when $FDW_HOST=<IP_Address_A> (master)
• IMPORT FOREIGN SCHEMA public from SERVER "animal_fdw" into "$SCHEMA_NAME" times out when $FDW_HOST=<IP_Address_B> (replica)

Logs

Thu, 10 Sep 2020 14:56:24 GMT
DROP SERVER
Thu, 10 Sep 2020 14:56:24 GMT
CREATE SERVER
Thu, 10 Sep 2020 14:56:24 GMT
CREATE USER MAPPING
Thu, 10 Sep 2020 14:56:24 GMT
CREATE USER MAPPING
Thu, 10 Sep 2020 14:56:24 GMT
CREATE USER MAPPING
Thu, 10 Sep 2020 14:56:24 GMT
DROP SCHEMA
Thu, 10 Sep 2020 14:58:36 GMT
CREATE SCHEMA
Thu, 10 Sep 2020 14:58:36 GMT
ERROR:  could not connect to server "animal_fdw"
Thu, 10 Sep 2020 14:58:36 GMT
DETAIL:  could not connect to server: Connection timed out
Thu, 10 Sep 2020 14:58:36 GMT
    Is the server running on host "<IP Address B>" and accepting
Thu, 10 Sep 2020 14:58:36 GMT
    TCP/IP connections on port 5432?

We already tried it on all our environments to make sure that the environment has no influence We also made sure that we did not have a typo in the IP address

Answer 1

you used a read replicas, all right? that could not be used like this way.