Re: Catastrophic changes to PostgreSQL 8.4

On Wednesday 02 December 2009 5:18:52 am Kern Sibbald wrote:
> Hello,
>
> I am the project manager of Bacula. One of the database backends that
> Bacula uses is PostgreSQL.
>
> This email is to notify you that a change you made to setting database
> character codes has created havoc with certain unfortunate Bacula users.
>
> Bacula sets the database encoding to SQL_ASCII, because although
> Bacula "supports" UTF-8 character encoding, it cannot enforce it. Certain
> operating systems such as Unix, Linux and MacOS can have filenames that are
> not in UTF-8 format. Since Bacula stores filenames in PostgreSQL tables,
> we use SQL_ASCII.
>
> We set SQL_ASCII by default when creating the database via the command
> recommended in recent versions of PostgreSQL (e.g. 8.1), with:
>
> CREATE DATABASE bacula ENCODING 'SQL_ASCII';
>
> However, with PostgreSQL 8.4, the above command is ignored because the
> default table copied is not template0. This means that some Bacula users
> who have created PostgreSQL databases with version 8.4, typically find them
> created with SQL_UTF8 format, which results in serious errors when doing
> backups for certain machines.

When I tried the above Postgres did not ignore the command, instead it gave me
the following error and did not create the database:
CREATE DATABASE bacula ENCODING 'SQL_ASCII';
ERROR: new encoding (SQL_ASCII) is incompatible with the encoding of the
template database (UTF8)
HINT: Use the same encoding as in the template database, or use template0 as
template.

>
> Regards,
>
> Kern

--
Adrian Klaver
aklaver(at)comcast(dot)net