Export from PostgreSQL to File

CloudQuery is an open-source data integration platform that allows you to export data from any source to any destination.

The CloudQuery PostgreSQL plugin allows you to sync data from PostgreSQL to any destination, including File. It's free, open source, requires no account, and takes only minutes to get started.

Ready? Let's dive right in!

Step 1. Install the CloudQuery CLI

The CloudQuery CLI is a command-line tool that runs the sync. It supports MacOS, Linux and Windows.

brew install cloudquery/tap/cloudquery

Step 2. Configure the PostgreSQL source plugin

Create a configuration file for the PostgreSQL plugin and set up authentication.

Configuration

Create a file called postgresql.yaml and add the following contents:

kind: source
spec:
  name: "postgresql"
  registry: "github"
  path: "cloudquery/postgresql"
  version: "v1.1.1"
  tables: ["*"]
  destinations: ["file"]
  spec:
    connection_string: "postgresql://postgres:pass@localhost:5432/postgres?sslmode=disable"
    cdc: false  # Set to true to enable Change Data Capture mode (logical replication, or CDC)

This example configures a PostgreSQL source, located at localhost:5432. The (top level) spec section is described in the Source Spec Reference.

The PostgreSQL plugin supports the following database versions:

PostgreSQL >= v11 (Needed for CDC, logical replication).
PostgreSQL >= v10 (If no need for CDC).

Set cdc to true to enable Change Data Capture mode (logical replication, or CDC). This will enable the PostgreSQL plugin to use the PostgreSQL logical replication feature to capture changes in the database. This is useful for use cases where you want to capture changes in the database in real time. For more information on CDC, see the CDC section of the documentation.

Fine-tune this configuration to match your needs. For more information, see the PostgreSQL Plugin ↗ page in the docs.

Step 3. Configure the File destination plugin

Create a configuration file for the File plugin and set up authentication.

Configuration

Create a file called file.yaml and add the following contents:

This example configures the file destination, to create CSV files in ./cq_csv_output. You can also choose json or parquet as the output format. Note that the file plugin only supports append write-mode.

The (top level) spec section is described in the Destination Spec Reference.

kind: destination
spec:
  name: "file"
  path: "cloudquery/file"
  version: "v3.2.0"
  write_mode: "append" # file only supports 'append' mode

  spec:
    path: "path/to/files/{{TABLE}}/{{UUID}}.{{FORMAT}}"
    format: "csv" # supported values are 'csv', 'json' and 'parquet'

Fine-tune this configuration to match your needs. For more information, see the File Plugin ↗ page in the docs.

Step 4. Start the Sync

Run the following command in your terminal to start the sync

cloudquery sync postgresql.yaml file.yaml

And away we go! 🚀 The sync will run until completion, fetching all selected tables from PostgreSQL. Any errors will be logged to a file called cloudquery.log.

Export from PostgreSQL to File

Step 1. Install the CloudQuery CLI

Step 2. Configure the PostgreSQL source plugin

Configuration

Step 3. Configure the File destination plugin

Configuration

Step 4. Start the Sync

Further Reading