May 20, 2021 · 6 minute read

postgres to snowflake migration with striim

In this article, we’ll go over how to use Striim to migrate data from an existing PostgreSQL database into Snowflake.

  1. What is Striim?
  2. Tutorial: Move Data from Postgres to Snowflake with Striim
  3. Conclusion: Start your Free Trial

What is Striim? 

Striim is a next generation Cloud Data Integration product that offers change data capture (CDC) enabling continuous replication from popular databases such as Oracle, SQLServer, PostgreSQL and many others.

In addition to CDC connectors, Striim has hundreds of automated adapters for file-based data (logs, xml, csv), IoT data (OPCUA, MQTT), and applications such as Salesforce and SAP. Our SQL-based stream processing engine makes it easy to enrich and normalize data before it’s written to Snowflake.

In this tutorial, we’ll show you how to use Striim to migrate schemas and data from an existing PostgreSQL database into Snowflake.

Tutorial: Move Data from Postgres to Snowflake with Striim

  1. Prerequisites
  2. Prepare a Snowflake Database and Launch Striim
  3. Create a Striim Service to Host a Data Migration app
  4. Create a Data Migration app on the Striim Service
  5. Prepare for Data Migration to Snowflake
  6. Prepare Your Target and Migrate Your Data to Snowflake

Prerequisites

The following are required to complete this tutorial: 

  • Striim Account. You can sign up for a free trial account that provides you with seven days of full functionality to get you started.
  • Snowflake Account. You can sign up for a free 30-day trial account to get started.
  • PostgreSQL database accessible over the Internet. Ensure you have the database’s name, hostname, port, username, and password as they will be required in the steps below to connect to your database.

Prepare a Snowflake Database and Launch Striim

Before migrating your data from PostgreSQL, you must first create a database within Snowflake to store the migrated data. After that database has been created you can launch Striim as a Snowflake partner service directly from within Snowflake. 

Follow the steps below to prepare a database and launch Striim in Snowflake:

  • Launch Snowflake in a web browser.
  • Click on Databases > Create:

create Snowflake database

  • Enter a unique name for the database and click Finish:

create a Snowflake database step 2

  • Click on Partner Connect in the top right corner of the navigation bar.
  • Locate and click on Striim in the list of Snowflake partners. Note: you may need to first switch your user role to ACCOUNTADMIN in order to launch Striim from Snowflake:

Snowflake partner connect

  • Activate the partner account if the account has not been previously activated:

activate partner connect account

  • Confirm that the database you created in steps 2 and 3 above is listed in Database(s) with USAGE privilege granted and click Connect:

connect to Striim

  • Note: On subsequent launches after activation has been completed for the first time, Snowflake will just prompt you to launch:

subsequent launch

Create a Striim Service to Host a Data Migration app

In Striim an app will be used to migrate the data. Before you can create that app, you need to first create and configure a service to host the app.

Follow the steps below to create a new Striim service:

  • Click on Marketplace in the top menu.
  • Locate the Snowflake app and click on Create:

striim marketplace

  • Enter a unique name in the Name field noting the naming requirements listed:

new striim service

  • (Optional) Click Show advanced options and specify the Service Version and Cluster Type.
  • Click Create. The browser will redirect to the Services screen.
  • Wait for the new service to enter the Running state.
  • Click on Launch:
service screen
The service will open in a new browser tab.

Create a Data Migration app on the Striim Service

With the service now created and launched, you must create an app that runs on that service to perform the data migration.

Follow the steps below to create a new data migration app:

  • Click on Apps to display the app management screen:

service main screen

  • Click Create app:

app management screen

  • Click on PostgreSQL Database to Snowflake:

select postgres to snowflake

  • Enter a name for the new application and the namespace and click Save:

enter name for new app

  • The data migration wizard is displayed:

data migration wizard

Prepare for Data Migration to Snowflake

In this section you will configure your app to access your source PostgreSQL database. As you proceed through Striim’s migration wizard, Striim will validate that it can access and fetch the metadata and data of your source PostgreSQL database.

Follow the steps below to migrate data using Striim’s step-by-step wizard:

  • Enter the details of your existing PostgreSQL database from which data is to be migrated and click Next:

Postgres source info

  • Striim will verify that it can connect to your database and obtain metadata:

validate connection

  • Click Next to advance to the Select Schemas screen.
  • Select the schemas to migrate from your PostgreSQL database to Snowflake and click Next:

select schemas

  • Striim will fetch and validate metadata for each table in your database:

validate schemas

  • Click Next to advance to the Select Tables screen. Navigate through each schema on the left-hand side, and select the tables from each to migrate:

select tables

  • Click Next to complete the wizard. The target creation screen is displayed:

create targets

Prepare Your Target and Migrate Your Data to Snowflake

Now that Striim can read from your source PostgreSQL database, you must configure Striim to write to your target Snowflake database.

Follow the steps below to prepare a Snowflake target and start the migration process:

  • Enter a unique name for the target in the Target Name field on the Create Snowflake Target(s) screen.
  • Ensure Input From is set to the stream you created using the steps in the previous sections. Note that the name will be in the form of <your application name> + _OutputStream.
  • Prepare the URL of the target Snowflake database: copy the following URL into the Connection URL field and replace YOUR_HOST with the base host domain assigned by Snowflake to your account, and YOUR_DATABASE with the name of your database:

jdbc:snowflake://YOUR_HOST.snowflakecomputing.com/?db=YOUR_DATABASE_NAME&schema=public

For example, the following URL has a base URL of xr86987.ca-central-1.aws and the database name set to RNA

jdbc:snowflake://xr86987.ca-central-1.aws.snowflakecomputing.com/?db=RNA&schema=public

  • Enter your credentials corresponding to your Snowflake account into the Username and Password fields.
  • (Optional) Modify which tables to migrate by configuring the table name(s) listed in the Tables field. By default, the tables listed will be based on those specified in the steps from the previous section and include the % as a wildcard character:

create snowflake target

  • Click Next. Striim will recreate the schema(s) in your Snowflake database:

create schema

  • Click Next after target creation is complete. Striim will begin migrating your data to Snowflake and will provide a detailed Application Progress popup showing how the migration is progressing:

monitor migration

The amount of time required for migration will vary depending on how much data is being migrated from your source PostgreSQL database and on your internet connection speed.

Conclusion: Start Your Free Trial

Our tutorial showed you how easy it is to migrate data from PostgreSQL to Snowflake, a leading cloud data warehouse. Once your data has been migrated, Striim enables continuous, real-time updates via Change Data Capture. For instances where changes continue to be made to the data in your source database, Striim enables zero-downtime, zero-data loss migrations to Snowflake.

As always, feel free to reach out to our integration experts to schedule a demo, or try Striim for free here.