Using postgres

Available in VPC

Cloud DB for PostgreSQL provides the postgres_fdw extension. See the user guide below.

Installing the extension via the console

Since Cloud DB for PostgreSQL does not provide superuser privileges, extensions that require superuser privileges must be installed through the console.
For more information, see the following guide:

Install extensions

Set ACG

To enable communication between DB servers, you must configure ACG.
For more information, see the following guide:

ACG settings guide

Managing DB users and access control settings

Access control settings for DB users are required to allow external access or to enable smooth communication between Cloud DB for PostgreSQL instances.
For more information, see the following guide:

user management

Communication between Cloud DB for PostgreSQL Instances

postgres_fdw_extension

In the console, go to Cloud DB for PostgreSQL > DB Server, and select the cluster.
Check the private domain of the selected server, and run an nslookup or verify the private IP address.

nslookup {private domain}

Non-authoritative answer:
Name:    {private domain}
Address:  xxx.xxx.xxx.xxx

Use the verified private IP address to configure access control.

postgres_fdw_db_user

Postgres_fdw usage example

Assumes that you have completed all the steps above.

source DB information example

DB name: testdb
DB owner : testappo
private ip : 10.0.0.6
DB port: 5432

target DB information example

DB name: testdb
DB owner : testappo

Creating a schema and data in the source DB

Create the schema and table on the source DB that the remote server will query, and insert the data.

create schema test;

create table test.test_table(aa int , bb text);

insert into test.test_table(aa,bb) values(1,'a');

insert into test.test_table(aa,bb) values(2,'b');

insert into test.test_table(aa,bb) values(3,'c');

insert into test.test_table(aa,bb) values(4,'d');

Configuring postgres_fdw and querying from the target DB

To query the table in the source DB, configure postgres_fdw on the target DB.
Enter the connection information for the source DB in the OPTIONS section of the example below.

#### Create schema.
create schema test;

#### Connect to the DB where postgres_fdw is installed using the DB owner account, and create a FOREIGN DATA WRAPPER named "postgres_server".
CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.0.0.6', port '5432', dbname 'testdb');

#### Check the created FDW
select * from pg_foreign_data_wrapper;

#### Configure user mapping for the DB user (role)
-- Role created in the target DB: testappo
-- Role created in the source DB: testappo
CREATE USER MAPPING FOR testappo SERVER postgres_server OPTIONS (user 'testappo', password '******');

#### Verify user mapping creation
select * from pg_user_mappings

#### Create foreign table
CREATE FOREIGN TABLE test.test_table_fw
(
    aa int,
   bb text
) SERVER postgres_server OPTIONS ( schema_name 'test', table_name 'test_table');

#### Verify foreign table
select * from pg_foreign_table;
SELECT *  FROM information_schema.foreign_tables;

#### Query using the configured FDW
select * from test.test_table_fw;

Using postgres_fdw