PostgreSQL To SQL Server Open-Source Transfer Tool

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

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 PostgreSQL:

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

Gather the following information for SQL Server:

• Hostname
• Port
• Database name
• Username
• Password
• Target 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 want>",
 "source-type": "postgresql",
 "source-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>",
 "target-name": "<any name you want>",
 "target-type": "mssql",
 "target-connection-string": "Server=<hostname>,<port number>;Database=<db name>;User Id=<username>;Password=<password>;",
 "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>",
 "target-database": "<db name>",
 "target-password": "<password>",
 "target-hostname": "<hostname>;TrustServerCertificate=yes",
 "target-username": "<username>"
}

Note: to supply additional arguments to a mssql target, you must put them in the target-hostname field, e.g. TrustServerCertificate

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 want>", "source-type": "postgresql", "source-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>", "target-name": "<any name you want>", "target-type": "mssql", "target-connection-string": "Server=<hostname>,<port number>;Database=<db name>;User Id=<username>;Password=<password>;", "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>", "target-database": "<db name>", "target-password": "<password>", "target-hostname": "<hostname>;TrustServerCertificate=yes", "target-username": "<username>"}' 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 want>", "source-type": "postgresql", "source-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>", "target-name": "<any name you want>", "target-type": "mssql", "target-connection-string": "Server=<hostname>,<port number>;Database=<db name>;User Id=<username>;Password=<password>;", "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>", "target-database": "<db name>", "target-password": "<password>", "target-hostname": "<hostname>;TrustServerCertificate=yes", "target-username": "<username>"}' localhost:9000/transfers/create

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

{
       "query-transfer": {
               "id": "6146a603-38cb-46fa-922c-868aab623959",
               "created-at": "2023-11-20T12:18:35.271079426Z",
               "status": "queued",
               "keep-files": false,
               "tmp-dir": "/tmp/sqlpipe/6146a603-38cb-46fa-922c-868aab623959",
               "pipe-file-dir": "/tmp/sqlpipe/6146a603-38cb-46fa-922c-868aab623959/pipe-files",
               "final-csv-dir": "/tmp/sqlpipe/6146a603-38cb-46fa-922c-868aab623959/final-csv",
               "source-connection-info": {
                       "instance-name": "my-source",
                       "type": "postgresql",
                       "hostname": "",
                       "database": "",
                       "username": ""
               },
               "target-connection-info": {
                       "instance-name": "my-target",
                       "type": "mssql",
                       "hostname": "mssql;TrustServerCertificate=yes",
                       "database": "mydb",
                       "username": "sa"
               },
               "drop-target-table-if-exists": true,
               "create-target-schema-if-not-exists": true,
               "create-target-table-if-not-exists": true,
               "source-schema": "public",
               "source-table": "my_table",
               "target-schema": "dbo",
               "target-name": "postgresql_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.