Change server encoding after the fact

From: Cody Caughlan <toolbag(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Change server encoding after the fact
Date: 2011-09-30 17:25:22
Message-ID: 5163A702-BFC8-4EA9-9962-A9EE6017D4B6@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I would like to change my server_encoding which is currently SQL_ASCII to UTF8.

I have existing data that I would like to keep.

From my understanding of the steps I need to:

1) alter the template1 database encoding via

UPDATE pg_database SET encoding = 6 where datname IN ('template0', 'template1');

2) Dump my current database

pg_dump -Fc foo > foo.db

3) Drop my current database

drop database foo;

4) recreate it with the proper encoding

create database foo with template = template1 encoding = 'UTF-8';

5) restore from backup

pg_restore -d foo foo.db

Are these the correct steps to perform or is there an easier / in-place way?

Also, when I dump my old DB and restore it, will it be converted appropriately (e.g. it came from am SQL_ASCII encoding and its going into a UTF-8 database)?

Thank you

/Cody

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Henry Drexler 2011-09-30 17:41:33 Re: postgres for OLAP & data mining
Previous Message pasman pasmański 2011-09-30 16:17:19 Re: how to improve this similarity query?