0

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?

migrated from serverfault.com Mar 1 at 15:06

This question came from our site for system and network administrators.

  • 1
    Yes, Postgres uses the collations provided by the operating system. There is not much you can do about. The best solution is to create the database manually before restoring the dump (do not use the --create option with pg_restore) – a_horse_with_no_name Mar 1 at 15:18
  • thanks for the awnser, i am gonna see if we can modify the current script @a_horse_with_no_name – Dorvalla Mar 2 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.