I am running into an issue with a script that makes a backup towards the SQL. What happens is that with our script we make a dump of SQL we can read out to restore the PostgreSQL database.
Our client runs this on an CENTOS server, where we installed the SQL via commandline with our regular settings (basic locale). They also have a windows machine where our software runs that can make the backup. This is controlled by a script which basically controls
Here we run it in windows where we install it also with basic settings.
Both run on the 9.6.3 version.
With our software, which is windows based, we have a backup tool that runs a cmd batch. This controls the bin/pg_dump of Postgres.
bin\pg_dump -h 10.0.0.80 -p 5432 -U postgres -F c -b -v -f %BACKUP_FILE% dbcatocs 2>"%BACKUP_DIR%logs\backuplog_%datestr%.txt"
So when we make a dump at their place, in linux environment, it goes wrong on the database creation when we do a restore. It dumps the query as following.
CREATE DATABASE dbcatocs WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'
While when we run it in windows it creates the database as following
CREATE DATABASE dbcatocs WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252';
So as you can see, windows handles the language code and the character set completely different than the server at their place. This causes the query in windows not to be executed.
I reinstalled PostgreSQL on our system here to see if we could match it to get it to output UTF8 as part of the process, however, windows sticks to numbers rather than the characterset. Plus the languagesets just simply don't match. So PostgreSQL decides just not to execute, leaving us with nada.
Now if we make a dump from PgAdmin manually in their system (we installed a pgadmin on their main windows server and can control there the windows output), it works fine, but since pgAdmin just dumps plain SQL queries, it shouldn't matter. So we have a way around it.
My question simply is if we can control the locale coallation and lc_type to simply be ignored scriptwise (so to create a database with standard entries, rather than trying to pick a locale that isn't there) within postgresql, or is it mandatory. And if so, is it controllable by the pg_dump script?