💾 Archived View for sdf.org › beni › tech › postgres.gmi captured on 2023-09-08 at 16:51:37. Gemini links have been rewritten to link to archived content

-=-=-=-=-=-=-

PostgreSQL Notes

Change encoding of 'template1'

On a UTF-8 setup you might still require a LATIN1 database template because your supplier tells you so. Most likely you don't want this - but if you do, that's what you need to do:

UPDATE pg_database SET datistemplate=false WHERE datname='template1';
DROP DATABASE template1
CREATE DATABASE template1 WITH OWNER=postgres ENCODING = 'LATIN1' LC_CTYPE = 'POSIX' LC_COLLATE = 'POSIX' TEMPLATE = 'template0';
UPDATE pg_database SET datistemplate=true WHERE datname='template1';

Migrating a database to another encoding

Your only option is to dump the database and recreate it. Here is what I did when I had to migrate from UTF-8 to LATIN1. First dupm the database from a shell

psql_dump <db_name> > <db_name>.sql

Then move the database and recreate it with the correct settings

ALTER DATABASE <db_name> RENAME TO <db_name>_backup;
CREATE DATABASE <db_name> ENCODING = 'LATIN1' LC_CTYPE = 'POSIX' LC_COLLATE='POSIX' TEMPLATE template1;

Then restore the dump, setting the client to the old encoding

PGCLIENTENCODING=UTF8 psql -f <db_name>.sql <db_name>

If everything is successful drop the backup of the database

DROP DATABASE  <db_name>_backup;