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.