AnalyticDB for PostgreSQL:Query data across databases

Overview

A database is a high-level unit in PostgreSQL. Different databases in an instance cannot query data of each other. This ensures data isolation between business fields but brings inconvenience to associated queries across business fields or databases.

AnalyticDB for PostgreSQL allows you to query data across databases by using a foreign data wrapper (FDW). The FDW is developed based on the massively parallel processing (MPP) architecture of AnalyticDB for PostgreSQL and uses the performance capabilities of compute nodes to improve data access efficiency for cross-database queries.

Usage notes

• The cross-database query feature is supported only for the following minor versions:

  • AnalyticDB for PostgreSQL V7.0 in elastic storage mode: V7.0.1.x and later.

  • AnalyticDB for PostgreSQL V6.0 in elastic storage mode: V6.3.11.2 and later.

  • AnalyticDB for PostgreSQL in Serverless mode: V1.0.6.x and later.

• FDW foreign tables support only the SELECT and INSERT operations. The UPDATE and DELETE operations are not supported.

• Only AnalyticDB for PostgreSQL V7.0 in elastic storage mode supports join pushdown and aggregate pushdown.

• Only the Orca optimizer in AnalyticDB for PostgreSQL V7.0 in elastic storage mode can generate execution plans for FDW foreign tables. AnalyticDB for PostgreSQL V6.0 in elastic storage mode and AnalyticDB for PostgreSQL in Serverless mode use the native optimizer to generate execution plans for FDW foreign tables.

Procedure

To enable data query between the db01 and db02 databases of your AnalyticDB for PostgreSQL instance, perform the following steps. After you enable the feature, you can access tables of the db02 database from the db01 database.

1. Use psql to connect to your AnalyticDB for PostgreSQL instance. For more information, see Client connection.

2. Create two databases named db01 and db02.

   CREATE DATABASE db01;
   CREATE DATABASE db02;

3. Install the greenplum_fdw and gp_parallel_retrieve_cursor extensions for the db01 and db02 databases.

   CREATE EXTENSION IF NOT EXISTS gp_parallel_retrieve_cursor;
   CREATE EXTENSION IF NOT EXISTS greenplum_fdw;
   GRANT USAGE ON FOREIGN DATA WRAPPER greenplum_fdw TO PUBLIC;

   Note

   • AnalyticDB for PostgreSQL does not allow you to manually install extensions. If the required extensions are not installed on your instance, submit a ticket.

   • The gp_parallel_retrieve_cursor extension is not required for AnalyticDB for PostgreSQL V7.0.

4. Switch to the db02 database and prepare test data.

   \c db02
   CREATE SCHEMA s01;
   CREATE TABLE s01.t1(a int, b int, c text); 
   INSERT INTO s01.t1 VALUES(generate_series(1,10),generate_series(11,20),'t1');

5. Switch to the db01 database and create a server and a user mapping.

   • Create a server.

     \c db01
     CREATE SERVER db02 FOREIGN DATA WRAPPER greenplum_fdw
         OPTIONS (host 'localhost',
           			 dbname 'db02');

     The following table describes the parameters.

     Parameter	Description
     host	The endpoint that is used to query data across databases. Set this parameter to localhost.
     dbname	The name of the source database. In this example, this parameter is set to db02.

   • Create a user mapping. For more information about user mappings, see CREATE USER MAPPING.

     CREATE USER MAPPING FOR CURRENT_USER SERVER db02
         OPTIONS (user 'report', password '******');

     The following table describes the parameters.

     Parameter	Description
     user	The name of the database account that is used to connect to the instance. The database account must have read permissions on the db02 database. If you want to perform INSERT operations, write permissions are also required. For information about how to create a database account, see Create a database account.
     password	The password of the database account.

6. Enable cross-database query for the db01 database.

   You can use one of the following methods to enable cross-database query:

   • Create foreign tables for the source tables.

     CREATE SCHEMA s01;
     CREATE FOREIGN TABLE s01.t1(a int, b int) 
     	server db02 options(schema_name 's01', table_name 't1');

     This method has the following advantages and disadvantages:

     • Advantages: You can specify a custom DDL structure for foreign tables. For example, the t1 table in the db02 database contains the a, b, and c fields, but the db01 database contains only the a and b fields. By using this method, you can specify fields when you create a foreign table.

     • Disadvantages: You must know the DDL structure of each table. A long period of time is required to import multiple foreign tables at a time.

   • Import all tables from the schema of the source database.

     CREATE SCHEMA s01;
     IMPORT FOREIGN SCHEMA s01 LIMIT TO (t1, t2, t3) 
     	FROM SERVER db02 INTO s01;

     This method has the following advantages and disadvantages:

     • Advantages: You can import foreign tables in a short period of time. You do not need to know the DDL structure of each table.

     • Disadvantages: Foreign tables must have the same names and fields as the tables in the source database.

     For more information, see IMPORT FOREIGN SCHEMA.

7. Query data of the db02 database from the db01 database.

   Query data of the db01 database.

   SELECT * FROM s01.t1;

   Sample result:

    a  | b  | c
   ----+----+----
     2 | 12 | t1
     3 | 13 | t1
     4 | 14 | t1
     7 | 17 | t1
     8 | 18 | t1
     1 | 11 | t1
     5 | 15 | t1
     6 | 16 | t1
     9 | 19 | t1
    10 | 20 | t1
   (10 rows)

References

AnalyticDB for PostgreSQL supports the cross-instance query feature. For more information, see Query data across instances.