Use correct SQL table encoding for Postgresql 10.x

[bacula/bacula] / bacula / src / cats / create_postgresql_database.in

diff --git a/bacula/src/cats/create_postgresql_database.in b/bacula/src/cats/create_postgresql_database.in
index 2d9e43f3d0727aceb8f565196bbd7c196e485b6c..deff1017c5ef9ba381812005762cee54144ee48b 100644 (file)
--- a/bacula/src/cats/create_postgresql_database.in
+++ b/bacula/src/cats/create_postgresql_database.in
@@ -1,16 +1,63 @@
 #!/bin/sh
 #
+# Copyright (C) 2000-2017 Kern Sibbald
+# License: BSD 2-Clause; see file LICENSE-FOSS
+#
 # shell script to create Bacula database(s)
 #
 
-bindir=@SQL_BINDIR@
+PATH="@POSTGRESQL_BINDIR@:$PATH"
+db_name=${db_name:-@db_name@}
+
+#
+# use SQL_ASCII to be able to put any filename into
+#  the database even those created with unusual character sets
+
+PSQLVERSION=`psql -d template1 -c 'select version()' $* | awk '/PostgreSQL/ {print $2}' | cut -d '.' -f 1,2`
+
+#
+# Note, LC_COLLATE and LC_TYPE are needed on 8.4 and beyond, but are
+#   not implemented in 8.3 or below.
+# This must be updated for future versions of PostgreSQL
+#
+case ${PSQLVERSION} in
+   8.[456789] | 9.* | 10.*)
+       ENCODING="ENCODING 'SQL_ASCII' LC_COLLATE 'C' LC_CTYPE 'C'"
+   ;;
+   *)
+       ENCODING="ENCODING 'SQL_ASCII'"
+   ;;
+esac
+
+
 
-if $bindir/psql $* -f - <<END-OF-DATA
-CREATE DATABASE bacula;
+#
+# Please note: !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
+#  We do not recommend that you use ENCODING 'SQL_UTF8'
+#  It can result in creating filenames in the database that
+#  cannot be seen or restored.
+# !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
+#
+#
+if psql -f - -d template1 $* <<END-OF-DATA
+\set ON_ERROR_STOP on
+CREATE DATABASE ${db_name} $ENCODING TEMPLATE template0;
+ALTER DATABASE ${db_name} SET datestyle TO 'ISO, YMD';
 END-OF-DATA
 then
-   echo "Creation of bacula database succeeded."
+   echo "Creation of ${db_name} database succeeded."
+else
+   echo " "
+   echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
+   echo "!!!! Creation of ${db_name} database failed. !!!!"
+   echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
+   exit 1
+fi
+if psql -l | grep " ${db_name}.*SQL_ASCII" >/dev/null; then 
+   echo "Database encoding OK"
 else
-   echo "Creation of bacula database failed."
+   echo " "
+   echo "Database encoding bad. Do not use this database"
+   echo " "
+   exit 1
 fi
-exit 0