Local Development, Remote Data: Accessing Fly.io PostgreSQL from Your Java API

Connecting Remote PostgreSQL Databases to Your Local Java Application: A Practical Guide

Accessing data from external databases during local development is a common challenge. This article details a solution using PostgreSQL Foreign Data Wrapper (FDW) and Fly Proxy to connect a local Java API (using SpringBoot) to a remote PostgreSQL database hosted on Fly.io.

The Problem: Bridging the Gap

The project required fetching data from a PostgreSQL table residing on a Fly.io-hosted application. Direct access from the local development environment was blocked due to network restrictions. This presented two key hurdles:

1. Establishing a secure connection from the local machine to the Fly.io database.
2. Accessing the remote database data within the local Java application.

The Solution: Fly Proxy and PostgreSQL FDW

The solution leverages Fly Proxy to create a secure tunnel and PostgreSQL FDW to treat the remote database as a local data source.

Understanding PostgreSQL FDW

PostgreSQL's Foreign Data Wrapper (FDW) allows querying external data sources as if they were local. The postgres_fdw extension facilitates this connection.

Setting Up the Fly Proxy

Before starting, ensure flyctl is installed. Fly Proxy creates a secure tunnel. The command structure is:

<code class="language-bash">fly proxy <remote> [remote_host] [flags]</code>

For this scenario, the command resembled:

<code class="language-bash">fly proxy 5433:5432 -a db_app_name</code>

This establishes a local tunnel (port 5433) to the Fly.io database (db_app_name).

Configuring PostgreSQL FDW

1. Install the Extension: Install postgres_fdw in your local PostgreSQL database:

   <code class="language-sql">CREATE EXTENSION IF NOT EXISTS postgres_fdw;</code>

2. Create a Foreign Server: Define the connection to the remote database:

   <code class="language-sql">CREATE SERVER fly_server FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (
       host '127.0.0.1',
       port '5433',
       dbname 'db_name'
   );</code>

3. Map Users: Create user mappings to grant access:

   <code class="language-sql">CREATE USER MAPPING FOR local_user SERVER fly_server
   OPTIONS (
       user 'remote_user',
       password 'remote_password'
   );</code>

4. Import the External Table: Import the necessary remote table:

   <code class="language-sql">IMPORT FOREIGN SCHEMA foreign_schema_name
   LIMIT TO (remote_table)
   FROM SERVER fly_server
   INTO local_schema_name;</code>

   (Note: If the remote table depends on ENUM types, these must also be created locally.)

5. Materialized View (Optional): For read-heavy operations, a materialized view can improve performance.

Integrating with the Java API

With FDW and the proxy configured, the SpringBoot application can query the external data through the local database connection, using either the foreign table or the materialized view.

Key Takeaways

• FDW is powerful but requires careful configuration.
• Flyctl Proxy simplifies secure remote database access.
• This approach promotes service decoupling, even with data dependencies.

Conclusion

This method successfully integrated remote data into the local Java application, offering a flexible solution for managing distributed data. Consider FDW for similar challenges! Connect on LinkedIn for further discussion.

<code class="language-bash">fly proxy <remote> [remote_host] [flags]</code>

The above is the detailed content of Local Development, Remote Data: Accessing Fly.io PostgreSQL from Your Java API. For more information, please follow other related articles on the PHP Chinese website!