pg_fasttransfer

A PostgreSQL extension to run the FastTransfer tool from an SQL function, enabling fast data transfer between databases.

Table of Contents

• FastTransfer Tool Requirement

• Prerequisites

  • Linux

  • Windows

    • Required Workload

    • Individual Components to Select

    • Installation Steps

• Installation

  • Linux

  • Windows

• SQL Setup

  • Drop existing extension

  • Create the extension

• Function: pg_fasttransfer_encrypt

• Function: xp_RunFastTransfer_secure Usage

  • Linux example

  • Windows example

• Function Return Structure

• Notes

FastTransfer Tool Requirement

This extension requires the FastTransfer tool to be installed separately.

Download FastTransfer and get a free trial license here: https://www.arpe.io/get-your-fasttransfer-trial

Once downloaded, extract the archive and provide the folder path using the fasttransfer_path parameter when calling the xp_RunFastTransfer_secure SQL function.

---

Available Versions

Linux

There are two versions available depending on your system architecture:

• x64 (amd64)
• ARM64 (aarch64)

Windows

Only one version is available for Windows x64 systems.

---

Example Directory Structure

After extraction, the directory should contain:

FastTransfer/
├── FastTransfer           # or FastTransfer.exe on Windows
├── FastTransfer.lic
└── ... (other files)

---

Example fasttransfer_path Values

Linux Example

fasttransfer_path := '/opt/FastTransfer_linux-x64'

Windows Example

fasttransfer_path := 'C:\tools\FastTransfer_windows-x64'

You do not need to include the executable name itself — just the directory containing it.

---

Prerequisites

• PostgreSQL installed with development headers
• Administrator privileges (sudo on Linux, appropriate rights on Windows)

---

Linux

• gcc or clang (C compiler)

• make

---

Windows

You need to install the C/C++ compiler and required build tools using Build Tools for Visual Studio 2022.

Required Workload

• C++ build tools (main workload)

Individual Components to Select

Component Group	Specific Items to Select	Description
MSVC Compiler	✅ MSVC v143 - VS 2022 C++ x64/x86 build tools (Latest)	The core C++ compiler
Windows SDK	✅ Windows 10 SDK (10.0.19041.0) or Windows 11 SDK	Headers and libraries
Additional Tools	✅ CMake tools for Visual Studio ✅ MSBuild support for LLVM toolset	Optional, but helpful for broader compatibility

Installation Steps

1. Download Build Tools for Visual Studio 2022 from the official Microsoft website.

2. Launch the installer.

3. Select the C++ build tools workload.

4. On the right-hand panel, check the individual components listed above.

5. Install.

Installation

Linux

1. Stop PostgreSQL

sudo systemctl stop postgresql

2. Remove any existing installation

sudo rm -f /usr/lib/postgresql/*/lib/pg\_fasttransfer.so
sudo rm -f /usr/share/postgresql/*/extension/pg\_fasttransfer\*

3. Build and install

make clean && make && sudo make install

4. Restart PostgreSQL

sudo systemctl start postgresql

---

Windows

1. Open x64 Native Tools Command Prompt for VS 2022 (or equivalent).

2. Ensure pg_config is available in your PATH.

3. Run the build script in the project directory:

build_for_windows.bat

This script will:

• Verify required tools (compiler, pg_config)

• Compile and link the extension DLL

• Copy the extension files to the appropriate PostgreSQL directories

4. Restart the PostgreSQL service.

---

SQL Setup

Drop existing extension (if any)

DROP EXTENSION IF EXISTS pg_fasttransfer CASCADE;

Create the extension

CREATE EXTENSION pg_fasttransfer;

Function: pg_fasttransfer_encrypt

This function encrypts a given text string using pgp_sym_encrypt and encodes the result in base64. This is useful for storing sensitive information, such as passwords, in a secure manner within your SQL scripts or configuration. The xp_RunFastTransfer_secure function will automatically decrypt any values passed to its --sourcepassword and --targetpassword arguments using the same encryption key. The encryption/decryption key is defined by the PGFT_ENCRYPTION_KEY variable in the C source file (pg_fasttransfer.c) and can be changed by recompiling the extension.

Syntax:

pg_fasttransfer_encrypt(text_to_encrypt text) RETURNS text

Example:

SELECT pg_fasttransfer_encrypt('MySecurePassword');
-- Returns: A base64-encoded encrypted string, e.g., "PgP...base64encodedstring=="

Function: xp_RunFastTransfer_secure Usage

This is the main function to execute the FastTransfer tool. It takes various parameters to configure the data transfer operation. Password arguments (sourcepassword, targetpassword) will be automatically decrypted if they were encrypted using pg_fasttransfer_encrypt.

Syntax:

xp_RunFastTransfer_secure(
sourceconnectiontype text DEFAULT NULL,
sourceconnectstring text DEFAULT NULL,
sourcedsn text DEFAULT NULL,
sourceprovider text DEFAULT NULL,
sourceserver text DEFAULT NULL,
sourceuser text DEFAULT NULL,
sourcepassword text DEFAULT NULL, 
sourcetrusted boolean DEFAULT FALSE,
sourcedatabase text DEFAULT NULL,
sourceschema text DEFAULT NULL,
sourcetable text DEFAULT NULL,
query text DEFAULT NULL,
fileinput text DEFAULT NULL,
targetconnectiontype text DEFAULT NULL,
targetconnectstring text DEFAULT NULL,
targetserver text DEFAULT NULL,
targetuser text DEFAULT NULL,
targetpassword text DEFAULT NULL, 
targettrusted boolean DEFAULT FALSE,
targetdatabase text DEFAULT NULL,
targetschema text DEFAULT NULL,
targettable text DEFAULT NULL,
degree integer DEFAULT NULL,
method text DEFAULT NULL,
distributekeycolumn text DEFAULT NULL,
datadrivenquery text DEFAULT NULL,
loadmode text DEFAULT NULL,
batchsize integer DEFAULT NULL,
useworktables boolean DEFAULT FALSE,
runid text DEFAULT NULL,
settingsfile text DEFAULT NULL,
mapmethod text DEFAULT NULL,
license text DEFAULT NULL,
fasttransfer_path text DEFAULT NULL
) RETURNS RECORD

Linux example

SELECT * FROM xp_RunFastTransfer_secure(
targetconnectiontype := 'msbulk',
sourceconnectiontype := 'pgsql',
sourceserver := 'localhost:5432',
sourceuser := 'pytabextract_pguser',
sourcepassword := pg_fasttransfer_encrypt('MyActualPassword'), 
sourcedatabase := 'tpch',
sourceschema := 'tpch_1',
sourcetable := 'orders',
targetserver := 'localhost,1433',
targetuser := 'migadmin',
targetpassword := pg_fasttransfer_encrypt('AnotherSecurePassword'), 
targetdatabase := 'target_db',
targetschema := 'tpch_1',
targettable := 'orders',
loadmode := 'Truncate',
license := '/tmp/FastTransfer_linux-x64_v0.13.5/FastTransfer.lic',
fasttransfer_path := '/tmp/FastTransfer_linux-x64_v0.13.5'
);

Windows example

SELECT * from xp_RunFastTransfer_secure(
sourceconnectiontype := 'mssql',
sourceserver := 'localhost',
sourcepassword := pg_fasttransfer_encrypt('MyWindowsPassword'),
sourceuser := 'FastLogin',
sourcedatabase := 'tpch10',
sourceschema := 'dbo',
sourcetable := 'orders',
targetconnectiontype := 'pgcopy',
targetserver := 'localhost:15433',
targetuser := 'postgres',
targetpassword := pg_fasttransfer_encrypt('MyPostgresPassword'), 
targetdatabase := 'postgres',
targetschema := 'public',
targettable := 'orders',
method := 'Ntile',
degree := 12,
distributekeycolumn := 'o_orderkey',
loadmode := 'Truncate',
batchsize := 1048576,
mapmethod := 'Position',
fasttransfer_path := 'D:\sources\FastTransfer'
);

Function Return Structure

Column	Type	Description
exit_code	integer	FastTransfer process exit code
output	text	Full FastTransfer logs
total_rows	bigint	Total rows transferred (-1 if not found)
total_columns	integer	Total columns transferred (-1 if not found)
transfer_time_ms	bigint	Transfer time in milliseconds (-1 if not found)
total_time_ms	bigint	Total time in milliseconds (-1 if not found)

Notes

• On Windows, the build_for_windows.bat script must be run from a Developer Command Prompt for Visual Studio where the compiler is available.

• The extension uses pg_config to locate PostgreSQL paths.

• .control and .sql files are installed automatically via the Makefile or Windows build script.

• To change the encryption/decryption key, edit the PGFT_ENCRYPTION_KEY static variable in the pg_fasttransfer.c source file and recompile the extension.