transfer guide

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.

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.