ADB to ADB Connector usage examples

Daria Barysheva

This article describes how to transfer data between two ADB clusters via ADB to ADB Connector. The following prerequisites are met:

  • The local ADB cluster is installed according to the Online installation guide.

  • The remote ADB cluster is installed according to the Online installation guide. Master host is called bds-mdw-ext.

  • The adb database exists in both clusters.

  • The adb_to_adb user with password 123 exists in the remote cluster.

  • The adb_to_adb user can access the remote adb database from the local cluster. For this purpose, change the pg_hba.conf file according to the ADB to ADB connector configuration article:

    host    all  adb_to_adb       10.92.17.84/32     password

    where 10.92.17.84/32 is IP address of the master host in the local ADB cluster (with a subnet number).

  • ADB to ADB Connector is installed in the local and remote clusters according to the ADB to ADB connector installation guide.

Select data

To check that the remote ADB cluster data can be read from the local cluster, follow the steps:

  1. Connect to the adb database in the remote ADB cluster under the gpadmin user (for example, via psql). Create the author table:

    CREATE TABLE author(id INT NOT NULL, name TEXT NOT NULL)
WITH (appendoptimized=true)
DISTRIBUTED BY(id);

  2. Insert some data into the author table (on the remote cluster side):

    INSERT INTO author(id, name) VALUES
(1,'Virginia Woolf'),
(2,'J.R.R. Tolkien'),
(3,'Harper Lee'),
(4,'J.D. Salinger'),
(5,'George Orwell'),
(6,'John Steinbeck'),
(7,'Margaret Mitchell'),
(8,'Alan Moore'),
(9,'Jack Kerouac'),
(10,'Ernest Hemingway');

  3. Grant necessary permissions on the new table author to the adb_to_adb user (on the remote cluster side):

    GRANT SELECT, INSERT ON public.author TO adb_to_adb;

  4. Connect to the adb database in the local ADB cluster under the gpadmin user (for example, via psql). Create the following objects in the given order.

    • The test_server server:

      CREATE SERVER test_server
FOREIGN DATA WRAPPER adb_fdw
OPTIONS (
  host 'bds-mdw-ext',
  port '5432',
  dbname 'adb',
  use_remote_estimate 'true');

    • The user mapping for the gpadmin user:

      CREATE USER MAPPING FOR gpadmin
SERVER test_server
OPTIONS (user 'adb_to_adb', password '123');

    • The test_table foreign table:

      CREATE FOREIGN TABLE test_table (id INT, name TEXT)
SERVER test_server
OPTIONS (table_name 'author');

  5. Select data from the test_table. Ensure that the data added on the remote cluster side is available in the local cluster:

    SELECT * FROM test_table ORDER BY id;

    Result:

     id |       name
----+-------------------
  1 | Virginia Woolf
  2 | J.R.R. Tolkien
  3 | Harper Lee
  4 | J.D. Salinger
  5 | George Orwell
  6 | John Steinbeck
  7 | Margaret Mitchell
  8 | Alan Moore
  9 | Jack Kerouac
 10 | Ernest Hemingway
(10 rows)

Insert data

To check that you can add data to the remote ADB database from the local cluster, follow the steps:

  1. Perform steps 1—​4 that are listed above (if you have not passed them yet).

  2. Connect to the adb database in the local ADB cluster under the gpadmin user (for example, via psql). Add some data to the test_table foreign table:

    INSERT INTO test_table(id, name) VALUES
(11, 'Fyodor Dostoevsky'),
(12, 'Alexander Pushkin'),
(13, 'Mikhail Bulgakov');

  3. Connect to the adb database in the remote ADB cluster under the gpadmin user (for example, via psql). Check the presence of new tuples in the author table:

    SELECT * FROM author ORDER BY id;

    The result contains new data received from the source ADB cluster:

     id |       name
----+-------------------
  1 | Virginia Woolf
  2 | J.R.R. Tolkien
  3 | Harper Lee
  4 | J.D. Salinger
  5 | George Orwell
  6 | John Steinbeck
  7 | Margaret Mitchell
  8 | Alan Moore
  9 | Jack Kerouac
 10 | Ernest Hemingway
 11 | Fyodor Dostoevsky
 12 | Alexander Pushkin
 13 | Mikhail Bulgakov
(13 rows)
Found a mistake? Seleсt text and press Ctrl+Enter to report it