Version: 0.28

PostgreSQL

The pg_fastbcp extension allows you to run the FastBCP tool directly from an SQL function, enabling fast data export from databases to various formats (CSV, Parquet) and cloud storage directly from PostgreSQL.

Extension Overview

Repository: arpe-io/pg_fastbcp
License: MIT
Latest version: v0.30.0
Platforms: Linux (Debian/Ubuntu) and Windows

pg_fastbcp Demo

Features

Native PostgreSQL Integration: Execute FastBCP commands directly from SQL
Secure Password Management: Built-in encryption for sensitive credentials
Detailed Result Return: Get export statistics and performance metrics as SQL results
Cross-Platform: Available for both Linux and Windows environments
Flexible Configuration: Support for all FastBCP parameters
Multiple Output Formats: Export to CSV, Parquet, or directly to cloud storage (AWS S3, Azure Blob, Google Cloud Storage, OneLake)

Prerequisites

PostgreSQL 15, 16, 17, or 18
Sudo/Administrator privileges to install the extension
Valid FastBCP license (request a trial license)
FastBCP binaries installed on the system

Important

The PostgreSQL server process usually runs under the postgres user account. You must ensure that this user has the appropriate permissions to execute the FastBCP binary and read the license file.

Compatibility

Windows
Linux

PostgreSQL Version	Supported
PostgreSQL 18
PostgreSQL 17
PostgreSQL 16
PostgreSQL 15

info

Other distributions or PostgreSQL versions may work but have not been officially tested.

Installation

Download the Extension

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

Linux
Windows

Automated Installation

The easiest way to install the extension on Linux is by using the install-linux.sh script included in the archive.

Extract the contents of the archive into a folder. This folder should contain:
- pg_fastbcp.so
- pg_fastbcp.control
- pg_fastbcp--1.0.sql
- install-linux.sh
Make the script executable:

chmod +x install-linux.sh

Run the script with administrator privileges:

sudo ./install-linux.sh

The script will automatically detect your PostgreSQL installation and copy the files to the correct locations.

Manual Installation

If the automated script fails or you prefer to install the files manually:

Stop your PostgreSQL service:

sudo systemctl stop postgresql

Locate your PostgreSQL installation directory, typically: /usr/lib/postgresql/<version>
Copy the files into the appropriate directories:

sudo cp pg_fastbcp.so /usr/lib/postgresql/<version>/lib/
sudo cp pg_fastbcp.control pg_fastbcp--1.0.sql /usr/share/postgresql/<version>/extension/

Restart your PostgreSQL service:

sudo systemctl start postgresql

Permissions Setup

Ensure the postgres user has proper permissions:

# Grant execute permission on the FastBCP binary
sudo chmod +x /path/to/FastBCP
sudo chown postgres:postgres /path/to/FastBCP
sudo chmod 750 /path/to/FastBCP

# Grant read permission on the license file
sudo chown postgres:postgres /path/to/license.lic
sudo chmod 640 /path/to/license.lic

Automated Installation

Extract the contents of the ZIP file into a folder. This folder should contain:
- pg_fastbcp.dll
- pg_fastbcp.control
- pg_fastbcp--1.0.sql
- install-win.bat
Right-click on the install-win.bat file and select "Run as administrator"
The script will automatically detect your PostgreSQL installation and copy the files to the correct locations

Manual Installation

If the automated script fails or you prefer to install the files manually:

Stop your PostgreSQL service
Locate your PostgreSQL installation folder, typically:

C:\Program Files\PostgreSQL\<version>

Copy the pg_fastbcp.dll file into the lib folder
Copy the pg_fastbcp.control and pg_fastbcp--1.0.sql files into the share\extension folder
Restart your PostgreSQL service

Permissions Setup

Make sure that the PostgreSQL service account (by default NT AUTHORITY\NetworkService or postgres) has:

Execute permission on the FastBCP.exe binary
Read permission on the .lic file

SQL Setup

After the files are in place, you need to set up the extension in your database.

Drop existing extension (if any)

DROP EXTENSION IF EXISTS pg_fastbcp CASCADE;

Create the extension

CREATE EXTENSION pg_fastbcp CASCADE;

Functions

pg_fastbcp_encrypt

This function encrypts a given text string using pgp_sym_encrypt and encodes the result in base64. It is useful for storing sensitive information, such as passwords, in a secure manner within your SQL scripts.

The xp_RunFastBcp_secure function will automatically decrypt any values passed to its password argument.

Syntax:

pg_fastbcp_encrypt(text_to_encrypt text) RETURNS text

Example:

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

xp_RunFastBcp_secure

This is the main function to execute the FastBCP tool. It takes various parameters to configure the data export operation.

Password arguments (password) will be automatically decrypted.

Syntax:

xp_RunFastBcp_secure(
    connectiontype text DEFAULT NULL,
    sourceconnectstring text DEFAULT NULL,
    dsn text DEFAULT NULL,
    provider text DEFAULT NULL,
    server text DEFAULT NULL,
    user_ text DEFAULT NULL,
    password text DEFAULT NULL,
    trusted boolean DEFAULT NULL,
    database_name text DEFAULT NULL,
    applicationintent text DEFAULT NULL,
    fileinput text DEFAULT NULL,
    query text DEFAULT NULL,
    sourceschema text DEFAULT NULL,
    sourcetable text DEFAULT NULL,
    fileoutput text DEFAULT NULL,
    directory text DEFAULT NULL,
    timestamped boolean DEFAULT NULL,
    encoding text DEFAULT NULL,
    delimiter text DEFAULT NULL,
    usequotes boolean DEFAULT NULL,
    dateformat text DEFAULT NULL,
    decimalseparator text DEFAULT NULL,
    boolformat text DEFAULT NULL,
    noheader boolean DEFAULT NULL,
    parquetcompression text DEFAULT NULL,
    cloudprofile text DEFAULT NULL,
    parallelmethod text DEFAULT NULL,
    paralleldegree integer DEFAULT NULL,
    distributekeycolumn text DEFAULT NULL,
    datadrivenquery text DEFAULT NULL,
    merge boolean DEFAULT NULL,
    loglevel text DEFAULT NULL,
    runid text DEFAULT NULL,
    settingsfile text DEFAULT NULL,
    config text DEFAULT NULL,
    nobanner boolean DEFAULT NULL,
    license text DEFAULT NULL,
    fastbcp_path text DEFAULT NULL
) RETURNS TABLE (
    exit_code integer,
    output text,
    total_rows bigint,
    total_columns integer,
    total_time bigint
)

Usage Examples

Linux Example - PostgreSQL to CSV

Export data from PostgreSQL to CSV files:

SELECT * FROM xp_RunFastBcp_secure(
    connectiontype := 'pgsql',
    server := 'localhost:5432',
    user_ := 'postgres',
    password := pg_fastbcp_encrypt('MyActualPassword'),
    database_name := 'tpch',
    sourceschema := 'tpch_1',
    sourcetable := 'orders',
    fileoutput := 'orders.csv',
    directory := '/tmp/export',
    paralleldegree := 4,
    parallelmethod := 'Ntile',
    distributekeycolumn := 'o_orderkey',
    noheader := false,
    delimiter := ',',
    license := '/tmp/FastBCP_linux-x64_v0.30.0/FastBCP.lic',
    loglevel := 'information',
    nobanner := false,
    fastbcp_path := '/tmp/FastBCP_linux-x64_v0.30.0'
);

Function Return Structure

The function returns a table with the following columns:

Column	Type	Description
`exit_code`	integer	The exit code of the FastBCP process.
`output`	text	The full log output from the FastBCP tool.
`total_rows`	bigint	The total number of rows transferred.
`total_columns`	integer	The total number of columns transferred.
`total_time`	bigint	The total execution time in milliseconds.

Output

You can view the full FastBCP output using the output column returned by the function.

Extension Overview​

Features​

Prerequisites​

Compatibility​

Installation​

SQL Setup​

Drop existing extension (if any)​

Create the extension​

Functions​

pg_fastbcp_encrypt​

xp_RunFastBcp_secure​

Usage Examples​

Linux Example - PostgreSQL to CSV​

Function Return Structure​

Output​

Resources​