How to make schema dumps comparable between Windows and Linux

Posted on

How to make schema dumps comparable between Windows and Linux – Managing your servers can streamline the performance of your team by allowing them to complete complex tasks faster. Plus, it can enable them to detect problems early on before they get out of hand and compromise your business. As a result, the risk of experiencing operational setbacks is drastically lower.

But the only way to make the most of your server management is to perform it correctly. And to help you do so, this article will share nine tips on improving your server management and fix some problem about linux, windows, postgresql, , .

I have two systems running, one on linux and the other on windows. From the linux box, I ran pg_dump against both systems and dumped the schema.

pg_dump command:

pg_dump -h HOST -U USER -s -f /tmp/out.sql DB_NAME

After I removed all of the “–” comments, I diffed the files together.

Diff output snippet, linux compared to windows:

-    ADD CONSTRAINT sys_c004775 FOREIGN KEY (ruleid) REFERENCES rule(ruleid);
+    ADD CONSTRAINT sys_c004775 FOREIGN KEY (ruleid) REFERENCES "rule"(ruleid);

The linux dump does not quote any entities and windows does. Is this a function of some encoding or just of a difference between windows and linux? Is there an option in pg_dump to make the output more consistent?

Solution :

It’s rather strange but both lines are correct and will work in any PostgreSQL-database on any OS. “rule” isn’t a reserved word, but it is an object type in PostgreSQL. That might be the reason why some version of pg_dump treats this name different. If you realy need unquoted names, just use another name, a name without any conflicts with object types what so ever.