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.
- Log on to the AnalyticDB for PostgreSQL console.
- In the upper-left corner of the console, select the region where the instances reside.
- In the left-side navigation pane, click Data Sharing.
- 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.
- 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.
- Create a source database named db01 on Instance A.
- 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.
- Create a destination database named db02 on Instance B.
- 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.
- Switch to the source database.
- psql
\c db01;
- DMS
- In the left-side navigation pane, click Instances Connected and find Instance A.
- Click the source database db01.
- 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)
- Create a share.
- 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.
- Switch to the destination database.
- psql
\c db02;
- DMS
- In the left-side navigation pane, click Instances Connected and find Instance B.
- Click the destination database db02.
- 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)
- 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)