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 9.6, 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:
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:
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.
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.
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.
To set the oracle_
home configuration parameter in the
postgresql.
conf file, edit the file, adding the following line:
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.
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.
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.
Note that the INNER JOIN operation occurs under the Foreign Scan section. The output of this join is the following:
Note: The procedure described in this section is not compatible with Oracle databases.
|
•
|
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.
|
|
•
|
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.
|
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.
