There are many databases available in the market for users to mull them over. PostgreSQL and SQL Server are two common options that businesses would vacillate.
PostgreSQL is an open-source and enterprise object-relational database system that enables SQL and JSON querying. The database is a popular choice among organizations of mobile, web, analytics, and geospatial apps as the data warehouse.
PostgreSQL emphasizes compliance and extensibility, and at the same time, it boasts rich features such as point-in-time recovery, tablespaces, multi-version concurrency, hot backups, and so on.
But it also has a relatively low reading speed and limited app support than other databases. PostgreSQL lacks upgrade utility, so it requires data export or replication to the new version for the purpose. And the upgrade of PostgreSQL entails double storage.
Compared to PostgreSQL, MS SQL Server is easier to install and configure, and provides record-breaking performance and data protection in motion. You can use both Microsoft Data Migration Assistant and SQL Server Installation Center to upgrade SQL Server to another edition.
Thus, some users may want to migrate data from PostgreSQL to SQL Server.
Convert PostgreSQL to SQL Server Using Bcp
1. Use psql to write 100m rows to a local file.
time psql -h /tmp -p 1925 -U postgres postgres -c "copy (select id,md5(random()::text) from generate_series(1,100000000) t(id)) to stdout WITH (NULL '')" > /data02/test.out real 2m2.551s
user 0m11.545s
sys 0m12.576s
2. Use bcp to load local files to MS SQL Server in bulk.
bcp test in /data01/test.out -c -k -S localhost -U SA -P Digoal_mssql -b 65535 -a 65535 -r
Starting copy...
100000000 rows copied.
Network packet size (bytes): 4097
Clock Time (ms.) Total : 779500 Average : (128309.0 rows per sec.)
Convert PostgreSQL to SQL Server Using Pg_dump and Bcp
1. Dump PostgreSQL schema and remove ownership or permissions with pg_dump.
pg_dump --schema-only --no-owner --no-privileges your_db_name > schema_create_script.sql
2. Convert the results into a bcp format.
3. Import the data into SQL Server using bcp, and load it into the database.
4. Or if you can convert the schema without data, you can shorten the steps.
pg_dump --data-only --column-inserts your_db_name > data_load_script.sql
Convert PostgreSQL to SQL Server Using Pg_dump
1. Export table definitions of PostgreSQL with pg_dump -s.
2. Convert the results into SQL Server format.
3. Load the statements to SQL Server with the Import and Export Wizard or sqlcmd. exe client tool.
4. Export PostgreSQL data into a script with pg_dump -a -b –inserts.
5. Convert it into SQL Server format and load into the database.
Backup PostgreSQL and SQL Server in Advance
The database migration process may be dangerous without preventive measures. Data loss from system failures or anything during the migration could be huge. So, it’s recommended that database users that want to translate PostgreSQL to SQL Server try the migration first in an isolated test environment.
If everything is correct, then migrate the database data. You can also backup the data beforehand to ensure data security even if the migration fails.
Vinchin Backup & Recovery is a reliable backup solution for both 10+ virtualizations (VMware, Xen, Red Hat Virtualization, etc.) and 6 databases (Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB).
With Vinchin Backup & Recovery, you can get:
Manage multiple databases together: you can manage a multi-type database environment in one solution to reduce cost and overhead. Vinchin provides a centralized web console to manage all backups of virtualizations or databases.
Database backup automation: backup is effortless with Vinchin Backup & Recovery which automates the process under repeated schedules and email notifications.
Smart backup strategies: choose from full, differential, incremental, transaction log backup (depending on the database) and reduce database data to save storage. Keep data per restore point/day for automatic data management.
Restore database easily: recover the whole database to the original database or create a new one in 4 steps.
Anti-ransomware data protection: protect all backups and the backup server against malware with backup storage protection that denies any unauthorized access except the Vinchin applications.
Extremely reasonable price: you can get a perpetual license or yearly prescription based on your needs at an extremely cost-effective price.
Want to give it a try? Download the 60-day full-featured free trial of the Enterprises version now.
Conclusion
In this article, I introduced 3 methods to migrate PostgreSQL to SQL Server (see how to convert SQL Server to PostgreSQL), you could choose one to try according to your IT environment or tools available.
But please note that backup before or after the database migration is necessary for data loss prevention. Try Vinchin Backup & Recovery with multiple advanced features, user-friendly operations, and a satisfactory price.
