lmemsm

It's getting harder and harder to find C/C++ programs. Many developers are working in other languages. Developers should have the choice to work in whatever language they want. However, it's dismaying to see the lack of C/C++ software currently available.

I recently tried to find FLOSS programs to set up a database structure and transfer tables from a working database to a new one. I'm trying to migrate the information from Microsoft SQL to PostgreSQL. While both systems have some great tools, I found it hard to locate easy to install, stable programs that could get the job done.

Microsoft SQL has sqlcmd and bcp. These programs can be used to query data from scripts or dump tables. PostgreSQL has psql and pg_dump. These have similar functions. While one should theoretically be able to use bcp to output a table and psql with the copy command to load a table, actually doing it successfully is another story. Any irregular data causes the transfer to fail. While these tools are really nice for batch scripting, they have limited use in some cases.

I saw recommendations for tools such as pgloader, ingestr, embulk and DBeaver to help me get the job done. It took forever to get pgloader to accept irregular passwords. I finally got a script that sort of worked. It moved 500 table structures over from Microsoft SQL to PostgreSQL. It started moving data and then it hung saying max connections reached. Changing its settings to try to make it use less connections did nothing. Plus, pgloader wiped out any tables and data I had already tried to move previously. Trying to install ingestr and embulk was a nightmare. Even with package manager tools like dnf, I was unable to get them fully installed and working properly. The tools are written in Python and Java. They require tons of support packages. The packages need to be installed using other methods than the local package manager such as by pip. I got absolutely nowhere with these solutions. I was told dbeaver could be used as a tool to copy schemas similar to pgloader. When I tried to get information on generating indexes in PostgreSQL that were equivalent to the ones in Microsoft SQL, I just kept getting errors. So, I found a lot of these heavyweight tools to be useless for my needs. They downloaded a lot of files that took up a great deal of disk space. It was hard to find and cleanly remove all the files they needed.

All I wanted was a simple C program that could do the data transfer and wouldn't need hundreds of files and packages installed. I knew a C program could do the job properly because I already had a transfer program that I'd written using C and ODBC that could copy tables. It was written to work with one row at a time which works great with smaller tables or individual inquiries but it wasn't designed for a large data warehouse with millions of rows per table. I spent a week rewriting my C table transfer program to work in batch mode. The new program is specifically designed to copy a Microsoft SQL table to an equivalent PostgreSQL table as quickly as possible. It does nothing more. It doesn't copy PostgreSQL to Microsoft SQL or handle deltas or translation of data. However, it just works and it works very quickly. It doesn't need a lot of space or memory. I wish there were more tools like these available. They're small, efficient and do one job well.

I now have a collection of ODBC based C tools that have been tested on a variety of systems from Windows to Linux to AIX. They do things such as run a query against a database, transfer rows from a table from one database to another, download rows to CSV or XML and more. For small tables, I can download each row to its own XML file. I can do that for multiple databases. I can then run directory/file a comparison tool to compare XML files and see if rows are different between two databases. It would be fun to share tools like these and eventually create enough lightweight tools that they could replace functionality of some of the more expensive commercial ETL or ELT solutions. Does anyone know of other lightweight FLOSS data transfer tools that do one job well? Do you have a good FLOSS ETL or ELT solution? Let me know over on Mastodon.