Hello all,sometimes it's funny ;-) I had the same problem two days ago, after I had wondered that my PostgreSQL databases were all created with ASCII charset only and that became my undoing with MantisBT[1]. In the IRC mlelstv and Riastradh have provided me then kindly some crucial tips, whereby I create my databases now in such a way:
``` #!/bin/sh if [ -z "$1" ]; thenecho -e "\nPlease call '$0 <database> <username> <password> <username-ro> <password-ro>' to run this command!\n"
exit 1
fi
DATABASE=$1
USERNAME=$2
PASSWORD=$3
USERNAME_RO=$4
PASSWORD_RO=$5
psql -d template1 -U pgsql << EOF
create user $USERNAME with password '$PASSWORD';
create user $USERNAME_RO with password '$PASSWORD_RO';
CREATE DATABASE $DATABASE WITH TEMPLATE = template0 ENCODING = 'UTF8'
LC_COLLATE = 'C' LC_CTYPE = 'de_DE.UTF-8';
grant all privileges on database $DATABASE to $USERNAME; \q EOF psql -d $DATABASE -U $USERNAME << EOF grant select on all tables in schema public to $USERNAME_RO; \q EOF ``` Kind regards Matthias [1] https://mantisbt.org/bugs/view.php?id=32365 On 14.04.23 16:31, Edgar Fuß wrote:
I'm unsure whether this is related to your problem, but what I remember (and which is encoded in my scripts which have been proven to be able to start it all from scratch) seems to be (when creating the database): 1. use template0 as TEMPLATE, not template 2. use ENCODING UTF8 3. use LC_TYPE (in SQL, not in the Environment) de_DE.UTF-8 I.e. CREATE DATABASE foo WITH OWNER bar TEMPLATE template0 ENCODING UTF8 LC_CTYPE de_DE.UTF-8 The script doesn't set any Environment, so that should be irrelevant.
Attachment:
smime.p7s
Description: S/MIME Cryptographic Signature