Skip to main content

Mocking postgres_fdw foreign servers for local dev and CI

Question​

Schemas that use postgres_fdw reference an upstream PostgreSQL server (SERVER, USER MAPPING, FOREIGN TABLE). That upstream server is often reachable only from inside the VPC of environments such as staging or production: local workstations and CI runners have no network path to it.

Working with such schemas in Atlas, whether for schema planning, schema testing, or simulating the real setup locally, triggers an FDW connection attempt on the dev-database that fails with errors such as:

08001 could not connect to server "upstream_db"

A common case is a materialized view that selects from a foreign table, which opens the FDW connection at CREATE time. What is the recommended pattern for stubbing the foreign server so that schema design, plan, lint, and diff run hermetically against the dev-database?

Answer​

Why the upstream must be reachable on the dev-database​

Atlas uses the dev-database to simulate the real environment, whether for validating the schema, planning a schema migration, or testing a data migration. Whatever the purpose, the relevant statements are executed against the dev-database. For postgres_fdw, that simulation reaches the upstream server:

  • A materialized view that selects FROM foreign_table is populated at CREATE time unless WITH NO DATA is given.
  • A foreign table is bound to a SERVER, and the server is bound to a real host:port.
  • PostgreSQL opens the FDW connection from whichever database is running CREATE MATERIALIZED VIEW ... AS SELECT ....

Something must answer at the FDW hostname with the table shape the foreign tables expect. In production that role is filled by the real upstream. For local dev and CI, the upstream can be stubbed inside the dev-database itself, the same way any external service is mocked in a unit test.

Stub the upstream inside the dev-database​

In the example below, upstream_db stands in for the hostname of the real upstream server: the production-only PostgreSQL that the SERVER definition points at and that the foreign tables read from. The goal is to make the dev-database resolve that hostname to a stub PostgreSQL running locally.

atlas.hcl
docker "postgres" "dev" {
  image       = "postgres:18"
  extra_hosts = ["upstream_db:127.0.0.1"]
  init        = <<-SQL
    CREATE DATABASE upstream_db;
    CREATE EXTENSION IF NOT EXISTS dblink;
    SELECT dblink_exec(
      'dbname=upstream_db user=postgres password=...',
      'CREATE TABLE public.events (id bigint)'
    );
    DROP EXTENSION dblink;
  SQL
}

Two pieces:

  • extra_hosts adds /etc/hosts entries inside the dev-database so the dev DB resolves the upstream hostname to itself (or to a sidecar). It maps to docker run --add-host.
  • init provisions the stub: a database named like the real upstream, with the tables the foreign tables expect.

The dev DB now resolves the upstream hostname, finds a real PostgreSQL listening there, and CREATE MATERIALIZED VIEW ... AS SELECT FROM foreign_table succeeds end-to-end. Plan, lint, and diff are reproducible on any workstation or CI runner with no network access to production.

The stub is a test double, not a config switch. It lives only in the dev-database; production continues to use the real upstream.

Why WITH NO DATA is not enough​

Atlas supports a with_no_data diff policy that creates matviews without populating them at apply time:

diff {
  materialized {
    with_no_data = true
  }
}

This is the right escape hatch for matviews that are populated lazily and have no external dependencies. It is not the right answer for a matview that selects from a postgres_fdw foreign table.

The whole point of exercising the matview on the dev-database is to verify that it is well-formed against the upstream it depends on: that the foreign tables resolve, that column shapes line up, that joins and expressions compile. Skipping the populate hides exactly the failure mode worth catching before production. Stubbing the upstream keeps that validation end-to-end in local dev and CI.

Production still needs the real upstream​

The stub is a dev-database mock for local and CI use only. At apply time, the production database executes the same CREATE MATERIALIZED VIEW and opens its own FDW connection to the real upstream. That network path must already exist in production (e.g., VPC peering, security group rules, internal DNS). The dev-side mock does not affect, simulate, or substitute for that connectivity. It only removes the planning stage's dependency on it.

Summary​

postgres_fdw runs the foreign connection from the database executing the statement. For local dev and CI, that database is the Atlas dev-database, so the upstream must be reachable there. Stubbing it inside the dev-database with extra_hosts and an init script makes schema design, plan, lint, and diff hermetic and reproducible, while production keeps talking to the real upstream as before.

Have additional questions or feedback? Reach out via the Intercom chat widget on this site.