CREATE PUBLIC DATABASE LINK v17
Name
CREATE [PUBLIC] DATABASE LINK
— Create a database link.
Synopsis
Description
CREATE DATABASE LINK
creates a database link. A database link is an object that allows a reference to a table or view in a remote database in a DELETE
, INSERT
, SELECT
or UPDATE
command. Reference a database link 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 any user can use. Only the database link's owner can use a private database link. Specify the PUBLIC
option to create a public database link. Otherwise, a private database link is created.
When you use the CREATE DATABASE LINK
command, the database link name and the given connection attributes are stored in the EDB Postgres 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 in the edb_dblink
entry is called the remote database. You can use edb_dblink_oci
to access remote Oracle tables and views using any SELECT
, INSERT
, UPDATE
, or DELETE
statement.
You must be connected to the local database when you issue a SQL command containing a reference to a database link. When the SQL command executes, the appropriate authentication and connection is made to the remote database to access the table or view to which the @dblink
reference is appended.
Oracle compatibility
- For EDB Postgres Advanced Server 17, the CREATE DATABASE LINK command has been tested and certified with all the minor versions for use with Oracle versions 10g Release 2, 11g Release 2, 12c Release 1, 18c Release 1, 19c, 21c, and 23.
Note
You can't use a database link to access a remote database in a standby database server. Standby database servers are for high availability, load balancing, and replication.
For information about high availability, load balancing, and replication for Postgres database servers, see the PostgreSQL core documentation.
You can set the
edb_dblink_oci.rescans
GUC toSCROLL
orSERIALIZABLE
at the server level in thepostgresql.conf
file. You can also set it at the session level using theSET
command. However, the setting isn't applied to existing dblink connections due to dblink connection caching.When executing
SELECT
on LOB data of more than 4000 characters, we recommend usingedb_dblink_oci.rescans=serializable
to free up the temporary PGA memory and avoid exceedingPGA_AGGREGATE_LIMIT
.
The edb_dblink_oci
supports both types of rescans: SCROLL
and SERIALIZABLE
. By default it's set to SERIALIZABLE
. When set to SERIALIZABLE
, edb_dblink_oci
uses the SERIALIZABLE
transaction isolation level on the Oracle side, which corresponds to PostgreSQL’s REPEATABLE READ
. This is necessary because a single PostgreSQL statement can lead to multiple Oracle queries. It thereby uses a serializable isolation level to provide consistent results.
A serialization failure can occur when modifying a table concurrent with long-running DML transactions, for example, ADD
, UPDATE
, or DELETE
statements. If such a failure occurs, the OCI reports ORA-08177: can't serialize access for this transaction
, and the application must retry the transaction.
A SCROLL
rescan is quick, but each iteration resets the current row position to 1
. A SERIALIZABLE
rescan has performance benefits over a SCROLL
rescan.
Parameters
PUBLIC
Create a public database link that any user can use. If you omit this parameter, then the database link is private and only the database link’s owner can use it.
name
The name of the database link.
username
The username to use for connecting to the remote database.
CURRENT_USER
Include CURRENT_USER
to use the user mapping associated with the role that's using the link when establishing a connection to the remote server.
password
The password for username
.
postgres_fdw
Specifies foreign data wrapper postgres_fdw
as the connection to a remote EDB Postgres Advanced Server database. If postgres_fdw
isn't installed on the database, use the CREATE EXTENSION
command to install postgres_fdw
. For more information, see the CREATE EXTENSION
command in the PostgreSQL core documentation.
fdw_connection_string
Specifies the connection information for the postgres_fdw
foreign data wrapper.
oci
Specifies a connection to a remote Oracle database. This is the default behavior.
oracle_connection_string
Specifies the connection information for an oci connection.
Note
To create a non-public database link, you need the CREATE DATABASE LINK
privilege. To create a public database link, you need the CREATE PUBLIC DATABASE LINK
privilege.
Setting up an Oracle instant client for OCI database link
To use edb_dblink_oci
, you must download and install an Oracle instant client on the host running the EDB Postgres Advanced Server database in which you want to create the database link.
You can download an instant client here.
Oracle instant client for Linux
These instructions apply to Linux hosts running EDB Postgres Advanced Server.
Be sure the libaio
library (the Linux-native asynchronous I/O facility) is installed on the Linux host running EDB Postgres Advanced Server.
You can install the libaio
library with the following command:
If the Oracle instant client that you downloaded doesn't include the file named libclntsh.so
without a version number suffix, create a symbolic link named libclntsh.so
that points to the downloaded version of the library file. To do so, navigate to the instant client directory and execute the following command:
Where version
is the version number of the libclntsh.so
library. For example:
When executing a SQL command that references a database link to a remote Oracle database, EDB Postgres Advanced Server must know where the Oracle instant client library resides on the EDB Postgres 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, if the installation directory containing libclntsh.so
is /tmp/instantclient
, use:
Alternatively, you can set the value of the oracle_home
configuration parameter in the
postgresql.conf
file. The oracle_home
configuration parameter is an alternative to the
LD_LIBRARY_PATH
environment variable. For more details on the oracle_home
configuration
parameter, see configuration parameters.
The ORACLE_HOME
environment variable must be set and include the path to the Oracle home directory. For example,
Note
You must set either the LD_LIBRARY_PATH
or the oracle_home
configuration parameter and the ORACLE_HOME
environment variable before executing the pg_ctl
utility to start or restart EDB Postgres Advanced Server.
If you're running the current session as the user account (for example, enterprisedb
) that directly invokes pg_ctl
to start or restart EDB Postgres Advanced Server, then set either the LD_LIBRARY_PATH
environment variable or the oracle_home
configuration parameter and the ORACLE_HOME
environment variable before invoking pg_ctl
.
You can set the LD_LIBRARY_PATH
and the ORACLE_HOME
environment variable in the .bash_profile
file under the home directory of the enterprisedb
user account. That is, set LD_LIBRARY_PATH
and ORACLE_HOME
in the file ~enterprisedb/.bash_profile
. This setting ensures that LD_LIBRARY_PATH
and ORACLE_HOME
are set when you log in as enterprisedb
.
If you're using a Linux service script with the systemctl
or service
command to start or restart EDB Postgres Advanced Server, you must set LD_LIBRARY_PATH
and ORACLE_HOME
so it's in effect when the script invokes the pg_ctl
utility.
For example, to set an environment variable for EDB Postgres Advanced Server, you can create a file named /etc/systemd/system/edb-as-14.service
. Include /lib/systemd/system/edb-as-14.service
in the file.
If the LD_LIBRARY_PATH=/tmp/instantclient
, include the environment variable by specifying:
Reload systemd:
Restart the EDB Postgres Advanced Server service:
The script file that you need to modify to include the LD_LIBRARY_PATH
setting depends on the EDB Postgres Advanced Server version and the Linux system on which it was installed.
Oracle instant client for Windows
These instructions apply to Windows hosts running EDB Postgres Advanced Server.
When you're executing a SQL command that references a database link to a remote Oracle database, EDB Postgres Advanced Server must know where the Oracle instant client library resides on the EDB Postgres Advanced Server host.
Set the Windows PATH
system environment variable to include the Oracle client installation directory that contains the oci.dll
file.
Alternatively, 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 overrides the Windows PATH
environment variable.
To set the oracle_home
configuration parameter in the postgresql.conf
file, add the following line:
Substitute the name of the Windows directory that contains oci.dll
for lib_directory
. For example:
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.
Note
If tnsnames.ora
is configured in failover mode, and a client:server failure occurs, the client connection is established with a secondary server (usually a backup server). Later, when the primary server resumes, the client retains its connection to a secondary server until a new session is established. The new client connections is automatically established with the primary server. If the primary and secondary servers are out of sync, then the clients that established a connection to the secondary server and the clients that later connected to the primary server might see a different database view.
Examples
Creating an OCI database link
This example uses the CREATE DATABASE LINK
command to create a database link named chicago
that connects an instance of EDB Postgres Advanced Server to an Oracle server using an edb_dblink_oci
connection. The connection information tells EDB Postgres Advanced Server to log in to Oracle as the user admin
whose password is mypassword
. Including the oci
option tells EDB Postgres Advanced Server that this is an edb_dblink_oci
connection. The connection string '//127.0.0.1/acctg'
specifies the server address and name of the database.
Note
You can specify a hostname in the connection string in place of an IP address.
Creating a postgres_fdw database link
This example uses the CREATE DATABASE LINK
command to create a database link named bedford
. The database link connects an instance of EDB Postgres Advanced Server to another EDB Postgres Advanced Server instance by way of a postgres_fdw
foreign data wrapper connection. The connection information tells EDB Postgres Advanced Server to log in as the user admin
with the password mypassword
. Including the postgres_fdw
option tells EDB Postgres 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.
Note
You can specify a hostname in the connection string in place of an IP address.
Using a database link
These examples use a database link with EDB Postgres Advanced Server to connect to an Oracle database. The examples assume that a copy of the EDB Postgres Advanced Server sample application’s emp
table was created in an Oracle database. A second EDB Postgres 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 the username edb
and password password
.
Issue a SELECT
command on the emp
table in the Oracle database using the database link oralink
.
Create a private database link, named fdwlink
, to the EDB Postgres Advanced Server database named edb
located on host 192.168.2.22
running on port 5444
. Connect to the EDB Postgres Advanced Server database with the 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 EDB Postgres Advanced Server database:
Push down for an OCI database link
When the OCI database link is used to execute SQL statements on a remote Oracle database, sometimes the pushdown of the processing occurs on the foreign server.
Push down refers to the occurrence of processing on the foreign that is, remote server instead of the local client where the SQL statement was issued. Push down can result in performance improvement since the data is processed on the remote server before being returned to the local client.
OCI database link supports push down for
- WHERE clause
- JOIN operations
- AGGREGATE functions
- ORDER BY clause
For push down to occur, some basic conditions must be met. The tables involved in the join operation must belong to the same foreign server and use the identical connection information to the foreign server. This connection information is the same database link defined with the CREATE DATABASE LINK
command.
To determine whether to use push down for a SQL statement, display the execution plan by using the EXPLAIN
command. For information about the EXPLAIN
command, see the PostgreSQL core documentation.
You can restrict the push downs using the edb_dbLink_oci_pushdown.config
configuration file. You can define the list of functions and operators in this file that can push down to the remote server. You can easily add or modify the list as per the requirements.
This file lists the objects as aggregates, functions, and operators allowed to push down to the remote server. Put each entry on a single line. Each entry must have two columns:
- Object type that can be ROUTINE (functions, aggregates, and procedures) or OPERATOR.
- The second column is schema-qualified object names with their arguments.
You can format the second column using the following query:
For ROUTINES:
For OPERATORS:
Example of edb_dbLink_oci_pushdown.config
file:
To find out whether pushdown is used for a SQL statement, display the execution plan by using the EXPLAIN
command.
These examples use the following database link:
This example shows the execution plan of an inner join:
This example shows the execution plan of an left outer join:
This example shows the execution plan of ORDER BY clause:
This example shows a case where the entire processing isn't pushed down because the emp
joined table resides locally instead of on the same foreign server:
The output of this join is the same as the previous left-outer-join example.
Creating a foreign table from a database link
Note
This procedure isn't compatible with Oracle databases.
After you create a database link, you can create a foreign table based on this database link. You can then use the foreign table to access the remote table, referencing it with the foreign table name instead of using the database link syntax. Using the database link requires appending @dblink
to the table or view name referenced in the SQL command, where dblink
is the name of the database link.
You can use this technique for either an oci-dblink connection for remote Oracle access or a postgres_fdw
connection for remote Postgres access.
This example creates a foreign table to access a remote Oracle table.
The following creates a database link named oralink
for connecting to the Oracle database:
The following query shows the database link:
When you create the database link, EDB Postgres Advanced Server creates a corresponding foreign server. The following query displays the foreign server:
For more information about foreign servers, see the CREATE SERVER
command in the PostgreSQL core documentation.
Create the foreign table:
Note the following in the CREATE FOREIGN TABLE
command:
- The name specified in the
SERVER
clause at the end of theCREATE FOREIGN TABLE
command is the name of the foreign server, which isoralink
in this example. You can see this name in thesrvname
column from the query onpg_foreign_server
. - The table name and schema name are specified in the
OPTIONS
clause by thetable
andschema
options. - The column names specified in the
CREATE FOREIGN TABLE
command must match the column names in the remote table. - Generally,
CONSTRAINT
clauses can't be accepted or enforced on the foreign table. They are assumed to have been defined on the remote table.
For more information about the CREATE FOREIGN TABLE
command, see the PostgreSQL core documentation.
The following is a query on the foreign table:
In contrast, the following is a query on the same remote table but using the database link instead of the foreign table:
Note
For backward compatibility, you can still write USING libpq
instead of USING postgres_fdw
. However, the libpq
connector is missing important optimizations that the postgres_fdw
connector has. Therefore, use the postgres_fdw
connector when possible. The libpq
option is deprecated and might be removed in a future EDB Postgres Advanced Server release.