CREATE DATABASE LINK

Function

Creates a database link object. For details about database links, see DATABASE LINK.

Syntax

CREATE [ PUBLIC ] DATABASE LINK dblink
  [ CONNECT TO { CURRENT_USER | user IDENTIFIED BY password } ] [ USING ( option 'value' [...]) ];

Parameter Description

• PUBLIC

  Creates a public database link visible to all users. If this clause is omitted, the database link is private and available only to the current user.

• dblink

  Indicates the name of the database link to be created.

• user

  Indicates the name of the user for connecting to the remote end of the created database link.

• password

  Indicates the password of the user for connecting to the remote end of the created database link.

• CURRENT_USER

  Uses the initial username and empty password of the current database for connection.

• USING ( option 'value' [, ... ] )

  Specifies parameters such as the IP address, port number, and remote database name of the database to be connected. The supported options are as follows:

  • host: specifies the IP addresses to be connected. IPv6 addresses are not supported. Multiple IP addresses can be specified using character strings separated by commas (,). Currently, SSL settings, encrypted databases, and certificate authentication are not supported. If no IP address is specified, this parameter is left empty by default.
  • port: specifies the port number for connection. If this parameter is not specified, the default value 5432 is used.
  • dbname: specifies the name of the database to be connected. If this parameter is not specified, the username used for connecting to the remote end is used by default.
  • fetch_size: specifies the amount of data obtained from the remote end each time. The value of fetch_size ranges from 0 to 2147483647. The default value is 100.
  

  • You can write only part of the preceding options in the brackets after USING.
  • If the keyword USING is not written, the content in the brackets is not written as well.
  • When a database link is created, the system does not check whether the connection is successful. If related keywords are missing, an error may be reported.

Example

-- Create a user with the system administrator permission.
gaussdb=# CREATE USER user01 WITH SYSADMIN PASSWORD '********';
gaussdb=# SET ROLE user01 PASSWORD '********';

-- Create a private database link.
gaussdb=# CREATE DATABASE LINK private_dblink CONNECT TO 'user1' IDENTIFIED BY '********' USING (host '192.168.11.11',port '54399',dbname 'db01');

-- Delete the private database link.
gaussdb=# DROP DATABASE LINK private_dblink;

-- Create a public database link.
gaussdb=# CREATE PUBLIC DATABASE LINK public_dblink CONNECT TO 'user1' IDENTIFIED BY '********' USING (host '192.168.11.11',port '54399',dbname 'db01');

-- Delete the public database link.
gaussdb=# DROP PUBLIC DATABASE LINK public_dblink;

-- Delete the created user.
gaussdb=# RESET ROLE;
gaussdb=# DROP USER user01 CASCADE;