Creating a Foreign Server

Manually Creating a Foreign Server

1. Connect to the default database postgres as a database administrator through the database client tool provided by GaussDB(DWS).

   You can use the gsql client to log in to the database in either of the following ways:

   You can use either of the following methods to create the connection:

   • If you have logged in to the gsql client, run the following command to switch the database and user:

     1	\c postgres dbadmin;

     Enter the password as prompted.

   • If you have not logged in to the gsql client or have exited the gsql client by running the \q command, run the following command to reconnect to it:

     1	gsql -d postgres -h 192.168.2.30 -U dbadmin -p 8000 -W password -r

2. Run the following command to query the information about the foreign server that is automatically created:

   1	SELECT * FROM pg_foreign_server;

   The returned result is as follows:

   1 2 3 4 5 6

   srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions --------------------------------------------------+----------+--------+---------+------------+--------+--------------------------------------------------------------------------------------------------------------------- gsmpp_server | 10 | 13673 | | | | gsmpp_errorinfo_server | 10 | 13678 | | | | hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca | 16476 | 13685 | | | | {"address=192.168.1.245:25000,192.168.1.218:25000",hdfscfgpath=/MRS/8f79ada0-d998-4026-9020-80d6de2692ca,type=hdfs} (3 rows)

   In the query result, each row contains the information about a foreign server. The foreign server associated with the MRS data source connection contains the following information:

   • The value of srvname contains hdfs_server and the ID of the MRS cluster, which is the same as the MRS ID in the cluster list on the MRS management console.
   • The address parameter in the srvoptions field contains the IP addresses and ports of the active and standby nodes in the MRS cluster.

   You can find the foreign server you want based on the above information and record the values of its srvname and srvoptions.

3. Switch to the user who is about to create a foreign server to connect to the corresponding database.
   In this example, run the following command to use common user dbuser created in Creating a User and a Database and Granting the User Foreign Table Permissions to connect to mydatabase created by the user:

   1	\c mydatabase dbuser;

4. Create a foreign server.

   For details about the syntax for creating foreign servers, see CREATE SERVER. For example:

   1 2 3 4 5 6 7

   CREATE SERVER hdfs_server_8f79ada0_d998_4026_9020_80d6de2692cahdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS ( address '192.168.1.245:25000,192.168.1.218:25000', hdfscfgpath '/MRS/8f79ada0-d998-4026-9020-80d6de2692ca', type 'hdfs' );

   Mandatory parameters are described as follows:

   • Name of the foreign server

     You can customize a name.

     In this example, specify the name to the value of the srvname field recorded in 2, such as hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca.

     Resources in different databases are isolated. Therefore, the names of foreign servers in different databases can be the same.

   • FOREIGN DATA WRAPPER

     This parameter can only be set to HDFS_FDW, which already exists in the database.

   • OPTIONS parameters
     Set the following parameters to the values under srvoptions recorded in 2.

     • address

       Specifies the IP address and port number of the primary and standby nodes of the HDFS cluster.

     • hdfscfgpath

       Specifies the configuration file path of the HDFS cluster. This parameter is available only when type is HDFS. You can set only one path.

     • type

       Its value is hdfs, which indicates that HDFS_FDW connects to HDFS.

5. View the foreign server.

   1	SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca';

   The server is successfully created if the returned result is as follows:

   1 2 3 4

   srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions --------------------------------------------------+----------+--------+---------+------------+--------+--------------------------------------------------------------------------------------------------------------------- hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca | 16476 | 13685 | | | | {"address=192.168.1.245:25000,192.168.1.218:25000",hdfscfgpath=/MRS/8f79ada0-d998-4026-9020-80d6de2692ca,type=hdfs} (1 row)