How import a Postgres .dump into a SQL Sever database.

Question

Hi All:

I want to import a postgres dump file(fec_fitem_sched_a.dump) into a  database in sql server. What are the steps for importation?

MRM256

Answer 1

Hi @Mark McCumber

Not an expert on SSIS, but you could try using SSIS export directly from Postgres to SQL Server.

If not work, you could re-dump as CSV and try again. Refer to this article: Import Text and CSV Files into SQL Server Database with SSIS Script Task.

Also, you could bulk import csv/txt into SQL Server using BCP.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

it depends on the dump format. if the format is sql text, you will need to go thru the text file and fix any sql server syntax errors. once this is done, you could use sqlcmd to execute script.

note: probably handy to add "go" statements. you may also want to break the dump text file into smaller files.

Answer 3

I read https://www.postgresql.org/docs/current/app-pgdump.html and I learnt that pg_dump can produce two different types of backup:

Dumps can be output in script or archive file formats

The suffix .dump seems to suggest that you have an archive file. Then again, as it says in a StackOverflow thread I looked at, that is just a name.

You said that you looked that file in UltraEdit, and you saw some SQL commands, but it was in hex format. Did you try change the presentation to normal text file? If you see a nice SQL script, you have a script format, and you can run it on SQL Server, but as Bruce says, you may have to edit it, since syntax may not be the same.

On the other hand, if you see random characters that you cannot make sense of, you presumably have the archive format.

I can see two options:

1. Find (or install) a Postgres server, load the archive file, take a script dump and load to SQL Server. (Or use an SISS package, if there is a Postgres connector.)
2. When I googled, I found https://www.rudderstack.com/guides/how-to-load-data-from-postgresql-to-ms-sql-server/, which seems to suggest that they have a service or a product. I did come as far to see if this comes with a price tag.