Data Source - From External Data Source

Overview

The system uses PostgreSQL's FDW plugin to support mapping external databases to local ones. Supported database types include PostgreSQL, MySQL, SQL Server, and Oracle.

TIP

This feature relies on PostgreSQL's FDW plugin. Please ensure it is installed on the database server. For cloud databases from some vendors, additional configuration may be required. For more information about FDW, please refer to https://www.postgresql.org/docs/13/postgres-fdw.html

Connecting to External Databases

• 1. Create a data table module, select External Database as the data source type
• 2. Create external server parameter configuration

Parameter	Description
Database Address	External database address Example: 104.249.164.122
Database Port	External database port Example: 5432
Database Name	External database name Example: db_test
Other Parameters	Other parameters for creating the external server

• 3. Create user mapping parameter configuration

Parameter	Description
Database Account	External database account Example: postgres
Database Password	External database password Example: 12345678

• 4. Create external table configuration

Parameter	Description
Database Schema	Database Schema Example: public
Data Table Name	Name of the table to be referenced in the external database Example: t_test
Other Parameters	Other parameters for creating the external table
External Database Table Primary Key Column Name	Defaults to id if not filled

TIP

For more information about mysql_fdw, please refer to https://github.com/EnterpriseDB/mysql_fdw

Tencent Cloud Database Cross-Database Access

Tencent Cloud Database cross-database access has some additional custom parameters. For details, please refer to https://www.tencentcloud.com/zh/document/product/409/44356

Cross-Instance Access

Need to set the parameter as instanceid in other parameters, with the value being the instance ID where the remote database is located.

Non-cross-instance, only cross-database access, only need to fill in the dbname parameter.

The target instance is on Tencent Cloud CVM and the network type is basic network.

Need to set the following parameters in other parameters

Parameter	Value	Description
instanceid	ins-xxxxx	Instance ID
access_type	2	Target instance type
region	ap-guangzhou	Target instance region
uin	xxxxxx	Account ID to which the instance belongs, used to authenticate user permissions
own_uin	xxxxxx	Main account ID to which the instance belongs, also needed for permission authentication

The target instance is on Tencent Cloud CVM and the network type is VPC.

Need to set the following parameters in other parameters

Parameter	Value	Description
instanceid	ins-xxxxx	Instance ID
access_type	2	Target instance type
region	ap-guangzhou	Target instance region
uin	xxxxxx	Account ID to which the instance belongs, used to authenticate user permissions
own_uin	xxxxxx	Main account ID to which the instance belongs, also needed for permission authentication
vpcid	vpc-xxxxxx	VPC ID
subnetid	subnet-xxxxx	VPC subnet ID

The target instance is self-built on Tencent Cloud VPN.

Need to set the following parameters in other parameters

Parameter	Value	Description
access_type	4	Target instance type
region	ap-guangzhou	Target instance region
uin	xxxxxx	Account ID to which the instance belongs, used to authenticate user permissions
own_uin	xxxxxx	Main account ID to which the instance belongs, also needed for permission authentication
vpngwid	xxxxxx	VPN gateway ID

The target instance is self-built on private VPN.

Need to set the following parameters in other parameters

Parameter	Value	Description
access_type	5	Target instance type
region	ap-guangzhou	Target instance region
uin	xxxxxx	Account ID to which the instance belongs, used to authenticate user permissions
own_uin	xxxxxx	Main account ID to which the instance belongs, also needed for permission authentication
vpngwid	xxxxxx	VPN gateway ID

The target instance is accessed via Tencent Cloud Direct Connect.

Need to set the following parameters in other parameters

Parameter	Value	Description
access_type	6	Target instance type
region	ap-guangzhou	Target instance region
uin	xxxxxx	Account ID to which the instance belongs, used to authenticate user permissions
own_uin	xxxxxx	Main account ID to which the instance belongs, also needed for permission authentication
dcgid	xxxxxx	Direct Connect ID

Manual Installation of FDW

Overview

FDW (Foreign Data Wrapper) is PostgreSQL's external data wrapper that allows PostgreSQL to access external data sources.

• jdbc_fdw: Used to connect to SQL Server databases
• oracle_fdw: Used to connect to Oracle databases

1. jdbc_fdw Installation (SQL Server)

1.1 Download Installation Package

Download the corresponding installation package based on the operating system:

CentOS 7:

wget https://repo.informat.cn/downloads/installer/fdw/centos/jdbc_fdw_centos7.zip

CentOS 8:

wget https://repo.informat.cn/downloads/installer/fdw/centos/jdbc_fdw_centos8.zip

Ubuntu 22.04/24.04:

wget https://repo.informat.cn/downloads/installer/fdw/ubuntu/jdbc_fdw.zip

1.2 Unzip Installation Package

unzip jdbc_fdw_*.zip
cd jdbc_fdw_*

1.3 Configure Installation Parameters

Before executing the installation script, you need to modify the path parameters in the install.sh script according to the actual environment. Open the script with a text editor:

vim install.sh

Modify the following parameters:

INFORMAT_HOME_PATH="/informat-next"  # Informat installation directory, modify according to actual installation path
HOME_PATH="/tmp"                      # Directory where FDW installation package is located, default is /tmp, can be modified according to actual decompression path
JDK_PATH="$INFORMAT_HOME_PATH/jdk"   # JDK directory, usually no need to modify
PG_DIR="/usr/pgsql-13"               # PostgreSQL installation directory, modify according to actual installation path

Parameter Description:

Parameter	Description	Default Value	Example
INFORMAT_HOME_PATH	Informat system installation root directory	/informat-next	/opt/informat
HOME_PATH	Directory where the FDW installation package is decompressed	/tmp	/opt/fdw_install
JDK_PATH	JDK installation directory (usually under Informat directory)	$INFORMAT_HOME_PATH/jdk	/informat-next/jdk
PG_DIR	PostgreSQL installation directory	/usr/pgsql-13	/usr/pgsql-13

Configuration Example:

If your Informat is installed in /opt/informat, PostgreSQL is installed in /usr/pgsql-13, and the installation package is decompressed in /opt/fdw_setup directory, you should modify it to:

INFORMAT_HOME_PATH="/opt/informat"
HOME_PATH="/opt/fdw_setup"
JDK_PATH="$INFORMAT_HOME_PATH/jdk"
PG_DIR="/usr/pgsql-13"

1.4 Execute Installation Script

After configuration is complete, save and execute the installation script:

sudo chmod +x install.sh
sudo ./install.sh

The installation script will automatically complete the following operations:

1. Check if jdbc_fdw is already installed (to avoid repeated installation)
2. Install dependency packages required for compilation
3. Configure JDK environment variables
4. Copy SQL Server JDBC driver to /opt/fdw/ directory
5. Compile and install jdbc_fdw
6. Configure JDK environment for postgres user

1.5 Configure PostgreSQL Extension

Log in to the database as the postgres user and create the extension:

# Load JDK environment first
sudo -u postgres bash -c "source /var/lib/postgresql/jdk/jdk_env.sh && psql"

Execute in psql:

CREATE EXTENSION IF NOT EXISTS jdbc_fdw;

1.6 Verify Installation

\dx jdbc_fdw

If extension information is displayed, the installation is successful.

2. oracle_fdw Installation (Oracle)

2.1 Download Installation Package

Download the corresponding installation package based on the operating system:

CentOS 7/8:

wget https://repo.informat.cn/downloads/installer/fdw/centos/oracle_fdw.zip

Ubuntu 22.04/24.04:

wget https://repo.informat.cn/downloads/installer/fdw/ubuntu/oracle_fdw.zip

2.2 Unzip Installation Package

unzip oracle_fdw.zip
cd oracle_fdw

2.3 Configure Installation Parameters

Before executing the installation script, you need to modify the path parameters in the install.sh script according to the actual environment. Open the script with a text editor:

vim install.sh

Modify the following parameter:

PG_DIR="/usr/pgsql-13"  # PostgreSQL installation directory, modify according to actual installation path

Parameter Description:

Parameter	Description	Default Value	Example
PG_DIR	PostgreSQL installation directory	/usr/pgsql-13	/usr/pgsql-13

Note: The oracle_fdw installation script will automatically install Oracle Instant Client to the /opt/oracle directory. This path is fixed and does not need to be modified.

2.4 Execute Installation Script

After configuration is complete, save and execute the installation script:

sudo chmod +x install.sh
sudo ./install.sh

The installation script will automatically complete the following operations:

1. Check if oracle_fdw is already installed (to avoid repeated installation)
2. Unzip Oracle Instant Client to /opt/oracle directory
3. Configure Oracle environment variables
4. Compile and install oracle_fdw
5. Configure system dynamic library path
6. Restart PostgreSQL service

2.5 Verify Installation

After the PostgreSQL service restarts, log in to the database:

sudo -u postgres psql

Create the extension:

CREATE EXTENSION IF NOT EXISTS oracle_fdw;

Verify:

\dx oracle_fdw

3. Troubleshooting

3.1 Common jdbc_fdw Issues

Issue 1: Cannot find libjvm.so

# Create symbolic link (note: modify to actual JDK path)
sudo ln -sf /informat-next/jdk/lib/server/libjvm.so /usr/lib64/

Issue 2: Environment variables for postgres user not taking effect

# Manually load environment variables and then start PostgreSQL
sudo -u postgres bash -c "source /var/lib/postgresql/jdk/jdk_env.sh && pg_ctl restart"

Issue 3: Installation fails due to incorrect path configuration

Check if the path configuration in install.sh is correct:

• Confirm that the directory pointed to by INFORMAT_HOME_PATH exists
• Confirm that HOME_PATH is the directory where the current installation package is located
• Confirm that PG_DIR points to the correct PostgreSQL installation directory

You can use the following commands to verify the paths:

ls -la /informat-next/jdk          # Check JDK directory
ls -la /usr/pgsql-13/bin/pg_config # Check PostgreSQL directory

3.2 Common oracle_fdw Issues

Issue 1: Cannot find Oracle client library

# Reload dynamic library configuration
sudo ldconfig

Issue 2: PostgreSQL not restarted

sudo systemctl restart postgresql-13

Issue 3: Environment variables not taking effect

source /etc/profile.d/oracle.sh

Issue 4: Incorrect PostgreSQL directory configuration

Confirm that PG_DIR is configured correctly:

ls -la /usr/pgsql-13/bin/pg_config  # Check if pg_config exists

4. Uninstallation Instructions

4.1 Uninstall jdbc_fdw

-- Remove extension
DROP EXTENSION IF EXISTS jdbc_fdw CASCADE;

# Delete related files
sudo rm -f /usr/pgsql-13/lib/jdbc_fdw.so
sudo rm -f /usr/pgsql-13/share/extension/jdbc_fdw*
sudo rm -rf /var/lib/postgresql/jdk
sudo rm -f /etc/profile.d/jdk.sh
sudo rm -rf /opt/fdw

4.2 Uninstall oracle_fdw

-- Remove extension
DROP EXTENSION IF EXISTS oracle_fdw CASCADE;

# Delete related files
sudo rm -f /usr/pgsql-13/lib/oracle_fdw.so
sudo rm -f /usr/pgsql-13/share/extension/oracle_fdw*
sudo rm -rf /opt/oracle
sudo rm -f /etc/profile.d/oracle.sh
sudo rm -f /etc/ld.so.conf.d/oracle-instantclient.conf
sudo ldconfig