transfer guide

Oracle to PostgreSQL Open-Source Transfer Tool

SQLpipe is a free, open-source tool that can transfer the result of a query, or an entire table, from Oracle to PostgreSQL.

If SQLpipe helps you, please consider starring our GitHub repository! It really helps us.

Step 1 – Install / run SQLpipe

The easiest way to run SQLpipe is with Docker:

docker run --publish 9000:9000 --name sqlpipe sqlpipe/sqlpipe

There are multiple ways to acquire the program, however. You can view all of those ways on the SQLpipe downloads page.

Step 2 – Gather connection information

Gather the following information for Oracle:

  • Hostname
  • Port
  • Database name
  • Username
  • Password
  • Schema and table name (for full table transfer) or write a query

Gather the following information for PostgreSQL:

  • Hostname
  • Port
  • Database name
  • Username
  • Password
  • Schema name

Step 3 – Submit a transfer command

Transferring data is accomplished by sending a POST request to SQLpipe's /transfer/create route. You can send this kind of command any way you want - from an Airflow DAG, a Cron command, or just Curl on your command line. Here is an outline of the kind of payload you must submit:

{
 "source-name": "<any name you like>",
 "source-type": "oracle",
 "source-connection-string": "oracle://<username>:<password>@<hostname>:<port>/<db name>?dba privilege=sysdba",
 "target-name": "<any name you like>",
 "target-type": "postgresql",
 "target-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>",
 "source-schema": "<source schema name>",
 "source-table": "<source table name>",
 "target-table": "<target table name>",
 "drop-target-table-if-exists": true,
 "create-target-table-if-not-exists": true,
 "create-target-schema-if-not-exists": true,
 "target-schema": "<target schema name>"
}

Example Curl commands

To move the result of a query, you can use the following command as a starting command:

curl -d '{"source-name": "<any name you like>", "source-type": "oracle", "source-connection-string": "oracle://<username>:<password>@<hostname>:<port>/<db name>?dba privilege=sysdba", "target-name": "<any name you like>", "target-type": "postgresql", "target-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>", "query": "<query>", "target-table": "<target table name>", "drop-target-table-if-exists": true, "create-target-table-if-not-exists": true, "create-target-schema-if-not-exists": true, "target-schema": "<target schema name>"}' localhost:9000/transfers/create

To move an entire table (and gain slightly better automatic type inference), you can use the following command as a starting template:

curl -d '{"source-name": "<any name you like>", "source-type": "oracle", "source-connection-string": "oracle://<username>:<password>@<hostname>:<port>/<db name>?dba privilege=sysdba", "target-name": "<any name you like>", "target-type": "postgresql", "target-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>", "source-schema": "<source schema name>", "source-table":"<source table name>", "target-table": "<target table name>", "drop-target-table-if-exists": true, "create-target-table-if-not-exists": true, "create-target-schema-if-not-exists": true, "target-schema": "<target schema name>"}' localhost:9000/transfers/create

If the command is successful, the program will tell you that a transfer was queued:

{
       "query-transfer": {
               "id": "13af9876-8f0e-475f-8c9c-59f9fcd72914",
               "created-at": "2023-11-21T11:28:21.055446184Z",
               "status": "queued",
               "keep-files": false,
               "tmp-dir": "/tmp/sqlpipe/13af9876-8f0e-475f-8c9c-59f9fcd72914",
               "pipe-file-dir": "/tmp/sqlpipe/13af9876-8f0e-475f-8c9c-59f9fcd72914/pipe-files",
               "final-csv-dir": "/tmp/sqlpipe/13af9876-8f0e-475f-8c9c-59f9fcd72914/final-csv",
               "source-connection-info": {
                       "instance-name": "my-source",
                       "type": "oracle",
                       "hostname": "",
                       "database": "",
                       "username": ""
               },
               "target-connection-info": {
                       "instance-name": "my-target",
                       "type": "postgresql",
                       "hostname": "",
                       "database": "",
                       "username": ""
               },
               "drop-target-table-if-exists": true,
               "create-target-schema-if-not-exists": true,
               "create-target-table-if-not-exists": true,
               "source-schema": "mydb_admin",
               "source-table": "my_table",
               "target-schema": "public",
               "target-name": "oracle_my_table",
               "delimiter": "{dlm}",
               "newline": "{nwln}",
               "null": "{nll}"
       }
}

If you make a mistake submitting the data, SQLpipe will do its best to tell you what's wrong. For example, if you forgot to specify a target table, it would return:

{
       "error": {
               "target-table": "must be provided"
       }
}

Conclusion

Yes, it really is that easy!

If you’d like more in-depth documentation, or want to let us know about a bug, we please make an issue on SQLpipe’s Github page.

By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. View our Privacy Policy for more information.