The easiest way to migrate a PostgreSQL database to SQL Server is one

Question

I have a PostgreSQL database that I want to move to SQL Server - both schema and data. I am poor, so I do not want to pay money. I am also lazy, so I do not want to work hard. I am currently doing this table at the table, and there are about 100 tables. This is very tiring.

Is there any trick that does what I want?

Answer 1

I believe that you may have gotten low voices because of the amazing ease of creating a simple SQL script from PostgreSQL that can (theoretically) run again in almost any DBMS. If you are a regular PostgreSQL user, this sounds like a dumb question.

This is not fair, as it turns out that this is actually a moderately difficult problem (although more due to the odd syntax and interface of SQL Server than any PostgreSQL failure).

You should find useful information in the accepted answer on this Serverfault page: https://serverfault.com/questions/65407/best-tool-to-migrate-a-postgresql-database-to-ms-sql-2005 .

If you can convert a schema without data, you can shorten the steps for the data with this command:

pg_dump --data-only --column-inserts your_db_name > data_load_script.sql 

This loading will be rather slow, but the --column-inserts option generates the most common INSERT statements for each row of data and should be compatible.

EDIT: Suggestions for transforming the circuit should be:

I would start resetting the scheme, but deleting everything related to property rights or rights. That should be enough:

 pg_dump --schema-only --no-owner --no-privileges your_db_name > schema_create_script.sql 

Edit this file to add the BEGIN TRANSACTION; line BEGIN TRANSACTION; to the beginning and ROLLBACK TRANSACTION; to end. Now you can download it and run it in the query window on SQL Server. If you get any errors, make sure that you move to the bottom of the file, highlight the ROLLBACK statement and run it (by pressing F5 when you highlight the statement).

Basically, you should resolve every error until the script passes cleanly. You can then change the ROLLBACK TRANSACTION to COMMIT TRANSACTION and do it one last time.

Unfortunately, I can’t help with what errors you can see, since I never switched from PostgreSQL to SQL Server, just the opposite. Some things that I would expect to be a problem, however (obviously, NOT a complete list):

• PostgreSQL automatically increments fields by binding the NOT NULL INTEGER field to SEQUENCE using DEFAULT . In SQL Server, this is an IDENTITY column, but they are not exactly the same. I'm not sure if they are equivalent, but if your original schema is filled with id fields, you might have problems. I don't know if SQL Server has a CREATE SEQUENCE , so you might have to delete them.
• Database Functions / Stored Procedures are not transferred between RDBMS platforms. You will need to remove any CREATE FUNCTION statements and translate the algorithms manually.
• Use caution when encoding a data file. I am a Linux person, so I have no idea how to check the encoding on Windows, but you need to make sure that the expected SQL Server is the same as the file that you import from PostgreSQL. pg_dump has the --encoding= option, which allows you to set a specific encoding. I seem to remember that Windows tends to use double-byte UTF-16 encoding for Unicode, where PostgreSQL uses UTF-8. I had a problem with SQL Server on PostgreSQL due to the output of UTF-16, so it would be interesting to research.
• The PostgreSQL TEXT data type is simply a VARCHAR with no maximum length. In SQL Server, TEXT is ... complicated (and deprecated). Each field in the source schema declared as TEXT must be scanned for the corresponding SQL Server data type.
• SQL Server has additional data types for UNICODE data. I am not familiar enough with this to make suggestions. I just point out that this could be a problem.

Answer 2

I found a faster and easier way to accomplish this.

First, copy the table (or query) into a tab delimited file as follows:

 COPY (SELECT siteid, searchdist, listtype, list, sitename, county, street, city, state, zip, georesult, elevation, lat, lng, wkt, unlocated_bool, id, status, standard_status, date_opened_or_reported, date_closed, notes, list_type_description FROM mlocal) TO 'c:\SQLAzureImportFiles\data_script_mlocal.tsv' NULL E'' 

Then you need to create your table in SQL, this will not process any schema for you. The schema must match the exported TSV file in order and data types.

Finally, you run the SQL bcp utility to enter the tsv file as follows:

 bcp MyDb.dbo.mlocal in "\\NEWDBSERVER\SQLAzureImportFiles\data_script_mlocal.tsv" -S tcp:YourDBServer.database.windows.net -U YourUserName -P YourPassword -c 

A few comments that I met. Postgres and SQL Server handle logical fields differently. For your SQL Server schema, your logical fields must be set to varchar (1), and the resulting data will be "f", "t", or "null". Then you will need to translate this field a little. something like:

 ALTER TABLE mlocal ADD unlocated bit; UPDATE mlocal SET unlocated=1 WHERE unlocated_bool='t'; UPDATE mlocal SET unlocated=0 WHERE unlocated_bool='f'; ALTER TABLE mlocal DROP COLUMN unlocated_bool; 

Another thing is that the fields of geography / geometry are very different between the two platforms. Export geometry fields as WKT with ST_AsText(geo) and convert accordingly to the end of SQL Server.

There may be more incompatibilities that require such settings.

EDIT. Therefore, when this method technically works, I try to transfer several million records from 100+ tables in SQL Azure and bcp to SQL Azure, it turns out pretty flaky. I constantly get intermittent. It is not possible to open errors in the files of the BCP server files, the server is interrupted with a delay and for some reason some records are not transmitted without any signs of errors or problems. Therefore, this method is unstable for transferring large amounts of Azure SQL data.