Table of Contents Previous Next


2 The SQL Language : 2.3 SQL Commands : 2.3.18 CREATE [PUBLIC] DATABASE LINK

CREATE [PUBLIC] DATABASE LINK -- create a new database link.
username IDENTIFIED BY 'password'}
USING { postgres_fdw 'fdw_connection_string' |
[ oci ] 'oracle_connection_string' }
CREATE DATABASE LINK creates a new database link. A database link is an object that allows a reference to a table or view in a remote database within a DELETE, INSERT, SELECT or UPDATE command. A database link is referenced by appending @dblink to the table or view name referenced in the SQL command where dblink is the name of the database link.
Database links can be public or private. A public database link is one that can be used by any user. A private database link can be used only by the database link’s owner. Specification of the PUBLIC option creates a public database link. If omitted, a private database link is created.
When the CREATE DATABASE LINK command is given, the database link name and the given connection attributes are stored in the Advanced Server system table named, pg_catalog.edb_dblink. When using a given database link, the database containing the edb_dblink entry defining this database link is called the local database. The server and database whose connection attributes are defined within the edb_dblink entry is called the remote database
A SQL command containing a reference to a database link must be issued while connected to the local database. When the SQL command is executed, the appropriate authentication and connection is made to the remote database to access the table or view to which the @dblink reference is appended.
Note: A database link cannot be used to access a remote database within a standby database server. Standby database servers are used for high availability, load balancing, and replication.
Note: For Advanced Server 10, the CREATE DATABASE LINK command is tested against and certified for use with Oracle version 10g Release 2 (10.2), Oracle version 11g Release 2 (11.2), and Oracle version 12c Release 1 (12.1).
Include CURRENT_USER to specify that Advanced Server should use the user mapping associated with the role that is using the link when establishing a connection to the remote server.
Specifies foreign data wrapper postgres_fdw as the connection to a remote Advanced Server database. If postgres_fdw has not been installed on the database, use the CREATE EXTENSION command to install postgres_fdw. For more information, please see the CREATE EXTENSION command in the PostgreSQL Core documentation at:
Specify the connection information for the postgres_fdw foreign data wrapper.
Specifies a connection to a remote Oracle database. This is Advanced Server’s default behavior.
To create a non-public database link you must have the CREATE DATABASE LINK privilege. To create a public database link you must have the CREATE PUBLIC DATABASE LINK privilege.
Be sure the libaio library (the Linux-native asynchronous I/O facility) has already been installed on the Linux host running Advanced Server.
The libaio library can be installed with the following command:
If the Oracle instant client that you've downloaded does not include the file specifically named libclntsh.so without a version number suffix, you must create a symbolic link named libclntsh.so that points to the downloaded version of the library file. Navigate to the instant client directory and execute the following command:
ln -s libclntsh.so.version libclntsh.so
Where version is the version number of the libclntsh.so library. For example:
When you are executing a SQL command that references a database link to a remote Oracle database, Advanced Server must know where the Oracle instant client library resides on the Advanced Server host.
The LD_LIBRARY_PATH environment variable must include the path to the Oracle client installation directory containing the libclntsh.so file. For example, assuming the installation directory containing libclntsh.so is /tmp/instantclient:
Note: This LD_LIBRARY_PATH environment variable setting must be in effect when the pg_ctl utility is executed to start or restart Advanced Server.
If you are running the current session as the user account (for example, enterprisedb) that will directly invoke pg_ctl to start or restart Advanced Server, then be sure to set LD_LIBRARY_PATH before invoking pg_ctl.
You can set LD_LIBRARY_PATH within the .bash_profile file under the home directory of the enterprisedb user account (that is, set LD_LIBRARY_PATH within file ~enterprisedb/.bash_profile). In this manner, LD_LIBRARY_PATH will be set when you log in as enterprisedb.
If however, you are using a Linux service script with the systemctl or service command to start or restart Advanced Server, LD_LIBRARY_PATH must be set within the service script so it is in effect when the script invokes the pg_ctl utility.
The particular script file that needs to be modified to include the LD_LIBRARY_PATH setting depends upon the Advanced Server version, the Linux system on which it was installed, and whether it was installed with the graphical installer or an RPM package.
See the appropriate version of the EDB Postgres Advanced Server Installation Guide to determine the service script that affects the startup environment. The installation guides can be found at the following location:
When you are executing a SQL command that references a database link to a remote Oracle database, Advanced Server must know where the Oracle instant client library resides on the Advanced Server host.
Set the Windows PATH system environment variable to include the Oracle client installation directory that contains the oci.dll file.
As an alternative you, can set the value of the oracle_home configuration parameter in the postgresql.conf file. The value specified in the oracle_home configuration parameter will override the Windows PATH environment variable.
To set the oracle_home configuration parameter in the postgresql.conf file, edit the file, adding the following line:
oracle_home = 'lib_directory '
After setting the PATH environment variable or the oracle_home configuration parameter, you must restart the server for the changes to take effect. Restart the server from the Windows Services console.
The following example demonstrates using the CREATE DATABASE LINK command to create a database link (named chicago) that connects an instance of Advanced Server to an Oracle server via an oci-dblink connection. The connection information tells Advanced Server to log in to Oracle as user admin, whose password is mypassword. Including the oci option tells Advanced Server that this is an oci-dblink connection; the connection string, '//127.0.0.1/acctg' specifies the server address and name of the database.
The following example demonstrates using the CREATE DATABASE LINK command to create a database link (named bedford) that connects an instance of Advanced Server to another Advanced Server instance via a postgres_fdw foreign data wrapper connection. The connection information tells Advanced Server to log in as user admin, whose password is mypassword. Including the postgres_fdw option tells Advanced Server that this is a postgres_fdw connection; the connection string, 'host=127.0.0.1 port=5444 dbname=marketing' specifies the server address and name of the database.
The following examples demonstrate using a database link with Advanced Server to connect to an Oracle database. The examples assume that a copy of the Advanced Server sample application’s emp table has been created in an Oracle database and a second Advanced Server database cluster with the sample application is accepting connections at port 5443.
Create a public database link named, oralink, to an Oracle database named, xe, located at 127.0.0.1 on port 1521. Connect to the Oracle database with username, edb, and password, password.
Issue a SELECT command on the emp table in the Oracle database using database link, oralink.
Create a private database link named, fdwlink, to the Advanced Server database named, edb, located on host 192.168.2.22 running on port 5444. Connect to the Advanced Server database with username, enterprisedb, and password, password.
Display attributes of database links, oralink and fdwlink, from the local edb_dblink system table:
Perform a join of the emp table from the Oracle database with the dept table from the Advanced Server database:
Pushdown refers to the occurrence of processing on the foreign (that is, the remote) server instead of the local client where the SQL statement was issued. Pushdown can result in performance improvement since the data is processed on the remote server before being returned to the local client.
For information about the EXPLAIN command, please see the PostgreSQL Core documentation at:
Note that the INNER JOIN operation occurs under the Foreign Scan section. The output of this join is the following:
The following example shows a case where the entire processing is not pushed down because the emp joined table resides locally instead of on the same foreign server.
Note: The procedure described in this section is not compatible with Oracle databases.
For more information about foreign servers, please see the CREATE SERVER command in the PostgreSQL Core documentation at:
Note the following in the CREATE FOREIGN TABLE command:
The name specified in the SERVER clause at the end of the CREATE FOREIGN TABLE command is the name of the foreign server, which is oralink in this example as displayed in the srvname column from the query on pg_foreign_server.
The table name and schema name are specified in the OPTIONS clause by the table and schema options.
The column names specified in the CREATE FOREIGN TABLE command must match the column names in the remote table.
Generally, CONSTRAINT clauses may not be accepted or enforced on the foreign table as they are assumed to have been defined on the remote table.
For more information about the CREATE FOREIGN TABLE command, please see the PostgreSQL Core documentation at:
Note: For backward compatibility reasons, it is still possible to write USING libpq rather than USING postgres_fdw. However, the libpq connector is missing many important optimizations which are present in the postgres_fdw connector. Therefore, the postgres_fdw connector should be used whenever possible. The libpq option is deprecated and may be removed entirely in a future Advanced Server release.

2 The SQL Language : 2.3 SQL Commands : 2.3.18 CREATE [PUBLIC] DATABASE LINK

Table of Contents Previous Next