Moving data from Oracle to PostgreSQL can be a complex and challenging task. Traditional approaches often involve writing custom scripts, dealing with data type mappings, handling schema differences, and managing the entire ETL (Extract, Transform, Load) process manually. This complexity increases when you need to maintain data consistency and handle large volumes of data efficiently.

Enter Sling - a modern data movement platform that simplifies the process of migrating data between different database systems. In this guide, we’ll walk through the process of moving data from Oracle to PostgreSQL using Sling, demonstrating how to set up connections, configure replications, and manage the entire migration process efficiently.

Whether you’re migrating a complete database or setting up continuous synchronization between Oracle and PostgreSQL, Sling provides the tools and features you need to accomplish your data movement goals with minimal effort. Let’s dive in and see how Sling makes this process straightforward and reliable.

Prerequisites and Installation

Before we begin migrating data from Oracle to PostgreSQL, let’s ensure you have everything needed to follow along with this guide.

Installing Sling

Sling offers multiple installation methods to suit your environment:

# Install using Homebrew (macOS)
brew install slingdata-io/sling/sling

# Install using curl (Linux)
curl -LO 'https://github.com/slingdata-io/sling-cli/releases/latest/download/sling_linux_amd64.tar.gz' \
  && tar xf sling_linux_amd64.tar.gz \
  && rm -f sling_linux_amd64.tar.gz \
  && chmod +x sling

# Install using Scoop (Windows)
scoop bucket add sling https://github.com/slingdata-io/scoop-sling.git
scoop install sling

# Install using Python pip
pip install sling

After installation, verify that Sling is properly installed:

# Check Sling version
sling --version

For more detailed installation instructions, visit the Sling CLI Getting Started Guide.

Understanding the Connections

Before we start moving data, it’s important to understand the connection requirements for both Oracle and PostgreSQL. Let’s look at what you’ll need for each database system.

Oracle Connection Requirements

To connect to Oracle, you’ll need:

Host address
Port number (default is 1521)
Service name or SID
Username
Password
Oracle client libraries installed on your system

For Oracle connections, you’ll need to ensure that Oracle Instant Client is installed on your system. You can download it from the Oracle website.

PostgreSQL Connection Requirements

For PostgreSQL, you’ll need:

Host address
Port number (default is 5432)
Database name
Username
Password
SSL mode (if required)

Both connections can be configured using environment variables, the Sling CLI, or a YAML configuration file. Let’s explore each method in detail.

Setting Up Connections

Let’s configure both our source (Oracle) and target (PostgreSQL) connections. Sling provides multiple ways to manage these connections securely.

Setting Up Oracle Connection

You can set up your Oracle connection using any of these methods:

Using Environment Variables

The simplest way is to use environment variables:

# Set Oracle connection using environment variable
export ORACLE_SOURCE='oracle://user:pass@host:1521/service_name'

Using the Sling CLI

Alternatively, use the sling conns set command:

# Set up Oracle connection with individual parameters
sling conns set oracle_source type=oracle host=host.ip user=myuser password=mypass port=1521 service_name=myservice

# Or use a connection URL
sling conns set oracle_source url="oracle://user:pass@host:1521/service_name"

Using the Sling Environment File

You can also add the connection details to your ~/.sling/env.yaml file:

connections:
  oracle_source:
    type: oracle
    host: host.ip
    user: myuser
    password: mypass
    port: 1521
    service_name: myservice

Setting Up PostgreSQL Connection

Similarly, let’s set up the PostgreSQL connection:

Using Environment Variables

# Set PostgreSQL connection using environment variable
export PG_TARGET='postgresql://user:pass@host:5432/dbname?sslmode=require'

Using the Sling CLI

# Set up PostgreSQL connection with individual parameters
sling conns set pg_target type=postgres host=host.ip user=myuser database=mydatabase password=mypass port=5432

# Or use a connection URL
sling conns set pg_target url="postgresql://user:pass@host:5432/dbname?sslmode=require"

Using the Sling Environment File

Add to your ~/.sling/env.yaml:

connections:
  pg_target:
    type: postgres
    host: host.ip
    user: myuser
    password: mypass
    port: 5432
    database: mydatabase
    sslmode: require
    schema: public

Testing Your Connections

After setting up both connections, it’s important to verify they’re working correctly:

# Test Oracle connection
sling conns test oracle_source

# Test PostgreSQL connection
sling conns test pg_target

# List all configured connections
sling conns list

# Discover available tables in Oracle
sling conns discover oracle_source

For more details about connection configuration, visit:

Creating Data Replications

Once your connections are set up, you can start configuring your data replications. Sling provides two main approaches for this: using CLI flags or YAML configuration files. Let’s explore both methods with examples specifically tailored for Oracle to PostgreSQL migration.

Using CLI Flags

The CLI approach is great for quick, one-off transfers or when you’re testing your setup. Here are some examples:

Basic Replication

# Simple table transfer with default options
sling run --src-conn oracle_source --tgt-conn pg_target --stream employees

Advanced Replication with Options

# Transfer with specific options for both source and target
sling run \
  --src-conn oracle_source \
  --tgt-conn pg_target \
  --src-stream employees \
  --mode incremental \
  --select "employee_id, first_name, last_name, email, hire_date" \
  --src-options '{ "table_keys": ["employee_id"] }' \
  --tgt-options '{ "column_casing": "snake", "add_new_columns": true }'

For more details about CLI flags, visit the CLI Flags Documentation.

Using YAML Configuration

For more complex scenarios or production environments, using a YAML configuration file is recommended. This approach provides better version control and reusability.

Basic YAML Configuration

Create a file named oracle_to_postgres.yaml:

# Define source and target connections
source: oracle_source
target: pg_target

# Default settings for all streams
defaults:
  mode: incremental
  target_options:
    # Automatically add new columns if they appear in source
    add_new_columns: true
    # Convert column names to snake_case
    column_casing: snake

# Define the tables to replicate
streams:
  # Single table replication
  employees:
    # Use runtime variable for target table name
    object: public.{stream_table}
    # Specify primary key for incremental updates
    primary_key: [employee_id]
    # Track updates using timestamp column
    update_key: last_update_date

Advanced YAML Configuration

For more complex scenarios, here’s an example that includes multiple tables with specific configurations:

source: oracle_source
target: pg_target

defaults:
  mode: incremental
  target_options:
    add_new_columns: true
    column_casing: snake

streams:
  employees:
    object: public.{stream_table}
    primary_key: [employee_id]
    update_key: last_update_date
    select: [ -sensitive_col] # exclude sensitive column

  departments:
    object: public.{stream_table}
    primary_key: [department_id]
    update_key: last_update_date

  job_history:
    object: public.{stream_table}
    primary_key: [employee_id, start_date]
    columns:
        job_id: varchar(10) # define specific column type

To run the replication using the YAML configuration:

# Run the replication using the YAML file
sling run -r oracle_to_postgres.yaml

For more examples and detailed documentation about replication configuration, visit:

Sling Platform Overview

While the CLI is powerful for local development and testing, Sling also provides a comprehensive web-based platform for managing your data operations at scale. The platform offers a user-friendly interface for creating, managing, and monitoring your Oracle to PostgreSQL migrations.

Managing Connections

The Sling Platform provides a centralized location to manage all your database connections. You can easily add, edit, and test your Oracle and PostgreSQL connections through the intuitive interface.

Sling Platform Connections

Creating Replications

The platform includes a powerful YAML editor with syntax highlighting and auto-completion, making it easy to create and modify your replication configurations.

Sling Platform Editor

Monitoring Executions

Track the progress of your migrations in real-time, view detailed logs, and monitor performance metrics through the execution dashboard.

Sling Platform Execution

Key Platform Features

The Sling Platform offers several advantages for managing Oracle to PostgreSQL migrations:

Visual Interface: Intuitive UI for managing connections and replications
Team Collaboration: Share connections and configurations across your team
Scheduling: Set up recurring migrations with flexible scheduling options
Monitoring: Real-time monitoring and alerting for your data operations
Version Control: Track changes to your replication configurations
Access Control: Role-based access control for team members

For more information about the Sling Platform, visit the Platform Documentation.

Best Practices and Tips

When migrating data from Oracle to PostgreSQL using Sling, consider these best practices to ensure a smooth and efficient process:

Performance Optimization

Use incremental mode with appropriate update_key columns for efficient updates
Leverage select statements to only transfer required columns
Configure appropriate batch sizes for your data volume
Consider using parallel processing for large tables

Schema Handling

Use table_ddl in target options to explicitly define column types
Enable add_new_columns to automatically handle schema changes
Set appropriate column_casing to match your PostgreSQL naming conventions
Define primary keys and indexes in the target database

Data Type Mappings

Sling automatically handles most common data type mappings between Oracle and PostgreSQL, but you may want to customize certain mappings:

Use table_ddl to specify exact numeric types (e.g., numeric(12,2) for financial data)
Consider timezone handling for timestamp columns
Map Oracle-specific types to appropriate PostgreSQL equivalents

For more examples and best practices, visit:

Getting Started

Ready to start migrating your data from Oracle to PostgreSQL? Here’s a quick checklist to get you going:

Install Sling
- Follow the installation instructions at the beginning of this guide
- Verify the installation with sling --version
Set Up Connections
- Configure Oracle source connection
- Configure PostgreSQL target connection
- Test both connections using sling conns test
Create Your First Replication
- Start with a simple table using CLI flags
- Move to YAML configuration for more complex scenarios
- Test thoroughly in a non-production environment
Scale Your Migration
- Consider using the Sling Platform for larger migrations
- Set up monitoring and alerting
- Implement proper error handling and recovery procedures

For more detailed information and support:

Migrate Data from Oracle to PostgreSQL with Sling

Prerequisites and Installation

Installing Sling

Understanding the Connections

Oracle Connection Requirements

PostgreSQL Connection Requirements

Setting Up Connections

Setting Up Oracle Connection

Using Environment Variables

Using the Sling CLI

Using the Sling Environment File

Setting Up PostgreSQL Connection

Using Environment Variables

Using the Sling CLI

Using the Sling Environment File

Testing Your Connections

Creating Data Replications

Using CLI Flags

Basic Replication

Advanced Replication with Options

Using YAML Configuration

Basic YAML Configuration

Advanced YAML Configuration

Sling Platform Overview

Managing Connections

Creating Replications

Monitoring Executions

Key Platform Features

Best Practices and Tips

Performance Optimization

Schema Handling

Data Type Mappings

Getting Started

Platform

Links