Data sharing - AnalyticDB for PostgreSQL - Alibaba Cloud Documentation Center

AnalyticDB for PostgreSQL in Serverless mode provides the data sharing feature to share and query data across instances. This topic describes how to use the data sharing feature.

Description

Two AnalyticDB for PostgreSQL instances in Serverless mode are required. In this topic, they are referred to as Instance A and Instance B. These two instances must belong to the same Alibaba Cloud account and reside within the same region. For more information about how to create an AnalyticDB for PostgreSQL instance in Serverless mode, see Create an AnalyticDB for PostgreSQL instance.

In this topic, you can enable data sharing for Instance A and Instance B. Then, you can create a share for a source database in Instance A, add a test table to the share, and then authorize a destination database in Instance B to consume the share. After you subscribe to the share for the destination database in Instance B, you can query the data contained in the test table from the destination database in Instance B.

Procedure

Add AnalyticDB for PostgreSQL instances in Serverless mode for data sharing.
1. Log on to the AnalyticDB for PostgreSQL console.
2. In the upper-left corner of the console, select the region where the instances reside.
3. In the left-side navigation pane, click Data Sharing.
4. On the Instances for Data Sharing tab, select the instances that you want to add for data sharing, and then click Add.
  
  Note Data can be shared only between instances that are added for data sharing. Select at least two instances. In this topic, Instance A shares data and Instance B consumes the data shared by Instance A.
5. In the Add Instances for Data Sharing message, click OK.
  
  Note This process may take 5 to 10 minutes to complete. After these two instances are added for data sharing, the shared data can be queried across the instances.
Use a client tool to connect to Instance A and Instance B. For more information, see Use client tools to connect to an instance.
In this topic, client tools psql and Data Management (DMS) are used.
Create a source database on Instance A and query the universally unique identifier (UUID) of the source database.
1. Create a source database named db01 on Instance A.
```
CREATE DATABASE db01;
```
2. Query the UUID of the source database for later use in Step 8.
  Execute \c db01; to switch to the db01 database and then execute the following statement to query the UUID:
```
SELECT current_database_uuid();
```
Create a destination database on Instance B and query the UUID of the destination database.
1. Create a destination database named db02 on Instance B.
```
CREATE DATABASE db02;
```
2. Query the UUID of the destination database for later use in Step 5.
  Execute \c db02; to switch to the db02 database and then execute the following statement to query the UUID:
```
SELECT current_database_uuid();
```
Create a share for the source database and authorize the destination database to consume the share.
1. Switch to the source database.
  - psql
```
\c db01;
```
  - DMS
    1. In the left-side navigation pane, click Instances Connected and find Instance A.
    2. Click the source database db01.
    3. Double-click the public schema to switch to the destination database.
  You can execute the following statement to check whether db02 is used:
```
SELECT current_database();
```
  If the current database is db01, the following information is returned:
```
 current_database
------------------
 db01
(1 row)
```
2. Create a share.
```
CREATE DATASHARE s01;
```
3. Authorize the destination database to consume the share.
```
GRANT USAGE ON DATASHARE s01 TO DATABASE "<target_dbuuid>";
```
  <target_dbuuid> specifies the UUID of db02 queried in Step 4.

Create a test table named t1_1 in the source database db01 and insert data into the table.

CREATE TABLE t1_1 (a int, b int, c int, d int)  DISTRIBUTED BY (a);
INSERT INTO t1_1 SELECT v,v,v,v FROM generate_series(1, 10) AS v;

Schema:

 a  | b  | c  | d
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
  5 |  5 |  5 |  5
  6 |  6 |  6 |  6
  7 |  7 |  7 |  7
  9 |  9 |  9 |  9
 10 | 10 | 10 | 10
  3 |  3 |  3 |  3
  4 |  4 |  4 |  4
  8 |  8 |  8 |  8
(10 rows)

Add the table to the share.
```
ALTER DATASHARE s01 ADD TABLE t1_1;
```
Subscribe to the share for the destination database db02.
1. Switch to the destination database.
  - psql
```
\c db02;
```
  - DMS
    1. In the left-side navigation pane, click Instances Connected and find Instance B.
    2. Click the destination database db02.
    3. Double-click the public schema to switch to the destination database.
  You can execute the following statement to check whether db02 is used:
```
SELECT current_database();
```
  If the current database is db02, the following information is returned:
```
 current_database
------------------
 db02
(1 row)
```
2. Subscribe to the share and set an alias for the share in the destination database.
```
IMPORT DATASHARE s01 AS s01a FROM DATABASE "<source_dbuuid>";
```
  <source_dbuuid> specifies the UUID of db01 queried in Step 3.

Query the data shared by the source database db01 from the destination database db02.

SELECT * FROM s01a.public.t1_1 ORDER BY 1;

The following information is returned:

 a  | b  | c  | d
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
  5 |  5 |  5 |  5
  6 |  6 |  6 |  6
  7 |  7 |  7 |  7
  9 |  9 |  9 |  9
 10 | 10 | 10 | 10
  3 |  3 |  3 |  3
  4 |  4 |  4 |  4
  8 |  8 |  8 |  8
(10 rows)