How to Use pg_fasttransfer in PostgreSQL

The pg_fasttransfer extension brings the power of FastTransfer directly into PostgreSQL, allowing you to execute high-speed data transfers between databases using native SQL functions. No external scripts or tools needed – just standard SQL queries.

In this guide, we'll explore how to install, configure, and use pg_fasttransfer to orchestrate data transfers directly from your PostgreSQL database, with built-in password encryption and detailed performance metrics.

Why Use pg_fasttransfer Extension?

Running FastTransfer operations directly from PostgreSQL provides several key benefits:

• Native SQL Integration: Execute transfers using standard SQL functions – no external orchestration needed
• Secure Credential Management: Built-in password encryption using PostgreSQL's pgcrypto extension
• Direct Result Processing: Transfer metrics returned as SQL result sets for easy logging and monitoring
• Simplified Workflows: Trigger transfers from stored procedures, scheduled jobs, or application queries
• Cross-Platform Support: Available for Linux (Debian/Ubuntu) and Windows environments
• Multi-Database Transfers: Transfer data between PostgreSQL, SQL Server, Oracle, MySQL, and more
• Performance Visibility: Get detailed statistics including row counts, transfer time, and exit codes

info

pg_fasttransfer is an open-source extension (MIT License) available on GitHub. It requires a valid FastTransfer license to execute transfers.

Prerequisites

Before installing the extension, ensure you have:

• PostgreSQL 15, 16, 17, or 18 installed
• Sudo/Administrator privileges on your system
• FastTransfer binaries downloaded and installed
• A valid FastTransfer license (request a trial)

tip

The PostgreSQL server process runs under the postgres user account. You must ensure this user has execute permissions on the FastTransfer binary and read permissions on the license file.

What is pg_fasttransfer?

pg_fasttransfer is a PostgreSQL extension that wraps the FastTransfer command-line tool into SQL functions. It provides two main functions:

1. pg_fasttransfer_encrypt(): Encrypts sensitive strings (passwords) using pgp_sym_encrypt
2. xp_RunFastTransfer_secure(): Executes FastTransfer with all parameters passed as function arguments

The extension automatically handles:

• Password decryption for secure credential handling
• Command-line argument construction
• Process execution and output capture
• Result parsing and metric extraction

Installation

Download the Extension

Download the appropriate release for your PostgreSQL version and operating system from the pg_fasttransfer releases page.

Latest version: v0.16.0 (March 10, 2026)

Linux Installation (Debian/Ubuntu)

Automated Installation (Recommended)

The archive includes an automated installation script:

# Extract the archive
tar -xzf pg_fasttransfer-v0.16.0-linux.tar.gz
cd pg_fasttransfer

# Make the installation script executable
chmod +x install-linux.sh

# Run the installer with sudo
sudo ./install-linux.sh

The script automatically detects your PostgreSQL installation and copies files to the correct locations.

Manual Installation

If you prefer manual installation:

# Stop PostgreSQL service
sudo systemctl stop postgresql

# Copy the shared library
sudo cp pg_fasttransfer.so /usr/lib/postgresql/<version>/lib/

# Copy extension control and SQL files
sudo cp pg_fasttransfer.control pg_fasttransfer--1.0.sql \
    /usr/share/postgresql/<version>/extension/

# Restart PostgreSQL
sudo systemctl start postgresql

Set Permissions for postgres User

Ensure the postgres user can execute FastTransfer:

# Make FastTransfer executable
sudo chmod +x /path/to/FastTransfer
sudo chown postgres:postgres /path/to/FastTransfer
sudo chmod 750 /path/to/FastTransfer

# Make license file readable
sudo chown postgres:postgres /path/to/FastTransfer.lic
sudo chmod 640 /path/to/FastTransfer.lic

Windows Installation

Automated Installation (Recommended)

The archive includes a batch file for automated installation:

1. Extract the ZIP file to a folder containing:

   • pg_fasttransfer.dll
   • pg_fasttransfer.control
   • pg_fasttransfer--1.0.sql
   • install-win.bat

2. Right-click install-win.bat and select "Run as administrator"

3. The script will detect PostgreSQL and install the extension automatically

Manual Installation

If you prefer manual installation:

1. Stop the PostgreSQL service
2. Locate your PostgreSQL installation folder (typically C:\Program Files\PostgreSQL\<version>)
3. Copy pg_fasttransfer.dll to the lib folder
4. Copy pg_fasttransfer.control and pg_fasttransfer--1.0.sql to the share\extension folder
5. Restart the PostgreSQL service

Ensure the PostgreSQL service account has:

• Execute permission on FastTransfer.exe
• Read permission on the .lic file

SQL Setup

After installing the extension files, create it in your database:

-- Drop existing extension if upgrading
DROP EXTENSION IF EXISTS pg_fasttransfer CASCADE;

-- Create the extension (requires pgcrypto)
CREATE EXTENSION pg_fasttransfer CASCADE;

The CASCADE option automatically installs the required pgcrypto extension if not already present.

Extension Functions

pg_fasttransfer_encrypt()

Encrypts sensitive strings (like passwords) for secure storage in SQL scripts.

Syntax:

pg_fasttransfer_encrypt(text_to_encrypt text) RETURNS text

Example:

SELECT pg_fasttransfer_encrypt('MySecurePassword');
-- Returns: "c30d04070302de9c45b41a6e95d00e75...base64encodedstring=="

The xp_RunFastTransfer_secure() function automatically decrypts values passed to sourcepassword and targetpassword parameters.

Encryption Key

The encryption key is defined in the PGFT_ENCRYPTION_KEY constant in pg_fasttransfer.c. For production use, consider customizing this key and recompiling the extension.

xp_RunFastTransfer_secure()

Executes the FastTransfer tool with specified parameters and returns detailed metrics.

Key Parameters:

Parameter	Type	Description
sourceconnectiontype	text	Source connection type (e.g., 'mssql', 'pgsql', 'oracle')
sourceserver	text	Source server address and port
sourceuser	text	Source database username
sourcepassword	text	Source password (encrypted with pg_fasttransfer_encrypt())
sourcedatabase	text	Source database name
sourceschema	text	Source schema name
sourcetable	text	Source table name
targetconnectiontype	text	Target connection type (e.g., 'pgcopy', 'msbulk')
targetserver	text	Target server address and port
targetuser	text	Target database username
targetpassword	text	Target password (encrypted)
targetdatabase	text	Target database name
targetschema	text	Target schema name
targettable	text	Target table name
method	text	Parallelization method ('Ntile', 'Mod', 'Range')
degree	integer	Number of parallel workers
distributekeycolumn	text	Column used for data distribution
loadmode	text	Load mode ('Truncate', 'Append', 'Drop')
batchsize	integer	Batch size for data loading
mapmethod	text	Column mapping method ('Position', 'Name')
license	text	Full path to FastTransfer license file
loglevel	text	Log level ('information', 'debug', 'warning')
nobanner	boolean	Suppress banner output
fasttransfer_path	text	Directory containing FastTransfer binary
debug	boolean	Return full output in output column

Return Columns:

Column	Type	Description
exit_code	integer	Process exit code (0 = success)
output	text	Full FastTransfer log (if debug = true)
error_message	text	Lines containing ERROR from output
total_rows	bigint	Total rows transferred
total_columns	integer	Total columns transferred
transfer_time_ms	bigint	Transfer duration in milliseconds
total_time_ms	bigint	Total execution time in milliseconds

Usage Examples

Example 1: SQL Server to PostgreSQL (Windows)

Transfer data from SQL Server to PostgreSQL using parallel processing:

Pre-Encrypted Passwords

In this example, the sourcepassword and targetpassword values have been encrypted beforehand using the pg_fasttransfer_encrypt() function. The extension will automatically decrypt them during execution.

SELECT * FROM xp_RunFastTransfer_secure(
    sourceconnectiontype := 'mssql',
    sourceserver := 'localhost,11433',
    sourceuser := 'FastLogin',
    sourcepassword := 'ww0EBwMC1cHiodZwOgJp0j0Bzb8xGAmA9vwG7gf8IchQX0hYTvpxXFlfSMSBtFD7YDl/A7GBlLA7
NAw2g9DThL1LW9MAvf71g52hYfDO',
    sourcedatabase := 'tpch10',
    sourceschema := 'dbo',
    sourcetable := 'orders',
    
    targetconnectiontype := 'pgcopy',
    targetserver := 'localhost:25433',
    targetuser := 'postgres',
    targetpassword := 'ww0EBwMCja4kgniJIOZz0jkBsIKEB9wnH2klOWdBHkQjlhrW8kvPvEniTIVHyoZ61f0oHlXJQi5x
fmZKyz0D7FpI1D8mwjSoyQU=',
    targetdatabase := 'postgres',
    targetschema := 'public',
    targettable := 'orders',
    
    method := 'Ntile',
    degree := 12,
    distributekeycolumn := 'o_orderkey',
    loadmode := 'Truncate',
    batchsize := 1048576,
    mapmethod := 'Position',
    fasttransfer_path := 'D:\FastTransfer',
    debug := true,
    nobanner := true
);

Result:

Example 2: PostgreSQL to SQL Server (Linux)

Transfer data from PostgreSQL to SQL Server on Linux:

SELECT * FROM xp_RunFastTransfer_secure(
    sourceconnectiontype := 'pgsql',
    sourceserver := 'localhost:5432',
    sourceuser := 'postgres',
    sourcepassword := pg_fasttransfer_encrypt('MyPostgresPassword'),
    sourcedatabase := 'tpch',
    sourceschema := 'tpch_1',
    sourcetable := 'lineitem',
    
    targetconnectiontype := 'msbulk',
    targetserver := 'sqlserver.example.com,1433',
    targetuser := 'migadmin',
    targetpassword := pg_fasttransfer_encrypt('MySQLPassword'),
    targetdatabase := 'target_db',
    targetschema := 'dbo',
    targettable := 'lineitem',
    
    method := 'Ntile',
    degree := 8,
    distributekeycolumn := 'l_orderkey',
    loadmode := 'Truncate',
    
    license := '/opt/fasttransfer/FastTransfer.lic',
    loglevel := 'information',
    nobanner := false,
    fasttransfer_path := '/opt/fasttransfer',
    debug := true
);

Debug Mode

The debug parameter controls the verbosity of the output column:

• debug := false (default): output is empty, only error_message contains error lines
• debug := true: output contains the complete FastTransfer log output

Example with debug enabled:

SELECT exit_code, output, total_rows 
FROM xp_RunFastTransfer_secure(
    -- ... parameters ...
    debug := true
);

This is useful for troubleshooting transfer issues or understanding detailed execution flow.

---

The pg_fasttransfer extension brings enterprise-grade data transfer capabilities directly into PostgreSQL, enabling secure, high-performance transfers with just SQL functions. Whether you're building ETL pipelines, orchestrating complex workflows, or simply need fast data movement, this extension provides a native, production-ready solution.

Give it a try and let us know your feedback on GitHub!

Resources

• GitHub Repository: arpe-io/pg_fasttransfer
• pg_fasttransfer Documentation: fasttransfer.arpe.io
• Report Issues: GitHub Issues
• Get a Trial License: Request FastTransfer Trial