How to change database encoding in an Amazon RDS Postgresql instance?

Question

I need to create a database with SQL_ASCII encoding in Amazon RDS PostgreSql database instance.

Since template0 and template1 are both with utf8 encoding, I get the following error

$ createdb -h endpoint.rds.amazonaws.com -U user -E SQL_ASCII -T template0

createdb: database creation failed: ERROR:  encoding "SQL_ASCII" does not match locale "en_US.UTF-8"

DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".

In previous versions of Postgresql, template0 had no predefined encoding, so that command gave no errors.

Also, I don't have permissions to change template1's enconding:

postgres=> update pg_database set datistemplate = FALSE where datname = 'template1';
ERROR:  permission denied for relation pg_database

Although the \l command says my user is the owner of template1

Any ideas?

Answer 1

You must pass a locale that matches your desired encoding and is supported by the host as well. For example, if you don't want localised collations etc, you could pass --locale=C. Otherwise you might want --locale=en_US.ISO-8859-1 (or just en_US).