[rt-users] [SOLVED] Re: charset troubles

[Alex Vandiver]

On Thu, 14 May 2015 16:50:23 +0200 manu at netbsd.org (Emmanuel Dreyfus)
wrote:
> I fixed it. Replying to myself with the whole story for someone else's
> future reference.

Good to hear the full debugging story.

> The problem was database encoding. RT can use PostgreSQL with encoding
> "UTF-8" or the default "SQL_ASCII". That later encoding means PostgreSQL
> does not care about encoding and just gives back the bytes it was given
> without any check. The former enforces UTF-8 usage and is able to
> automatically transcode if the client claims to use another encoding.
> 
> My RT installation had been configured with the PostgreSQL database
> using "UTF-8" encoding for a while. At some time I upgraded PostgreSQL
> and I reloaded the data from a dump after reinitializing the database.
> But since I did not check for it, it got "SQL_ASCII", a setup where the
> application must take care of data encoding.

So this is the first place where things went awry.  How was the
database created to reload the database dump, such that it got
SQL_ASCII?  By hand using 'createdb' from the command line?  And is
your template0 database marked as 'SQL_ASCII' ?

For reference,
https://docs.bestpractical.com/backups#Restoring-from-backups1 is the documented technique for loading in a Pg backup.
 - Alex