Re: Change database encoding

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "Shoaib Mir" <shoaibmir(at)gmail(dot)com>
Cc: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Change database encoding
Date: 2006-03-29 20:43:40
Message-ID: 20060329203545.M43728@narrowpathinc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

> > On 3/28/06, Keith Worthington <keithw(at)narrowpathinc(dot)com> wrote:
> >
> > Hi All,
> >
> > When I originally built a database I did not understand the
> > ramifications of choosing the encoding. Not knowing any better
> > I chose SQL-ASCII. Since upgrading pgadmin3 to the latest
> > release I am cautioned that I should switch to UNICODE.
> >
> > How do I go about changing the database encoding?
> >
> > Is UNICODE the 'best' choice for a general purpose database?
> >
> > TIA
> >
> > Kind Regards,
> > Keith
>
> On Tue, 28 Mar 2006 21:34:41 +0500, Shoaib Mir wrote
> I guess you can not alter database encodng, so the way to change
> database encoding can be following these steps:
>
> 1. Take the database dump for which you wish to change encodig
> using pg_dump.
>
> 2. Now drop the database
>
> 3. Recreate the database with the encoding you wish to
> For exampe --> createdb testdb -e unicode
>
> 4. Check if the dump file created in the first step has any special
> characters and do the required changes.
>
> 5. Now you can restore the dump file into the newly created database.
>
> The advantage of using a Unicode database is that UTF-8
> supports/includes all known encodings at once. Therefore, in the
> process of development, it can help you save time.
>
> /Shoaib Mir

Shoaib,

Thank you very much for the guidance. For posterity's sake using bash
I ended up with the following command.

vacuumdb --full --analyze --username postgres --dbname MYDB && pg_dump MYDB -Ft
-v -U postgres -f tmp/MYDB.tar && dropdb MYDB --username postgres && createdb
--encoding UNICODE MYDB --username postgres && pg_restore tmp/MYDB.tar | psql
--dbname MYDB --username postgres && vacuumdb --full --analyze --username
postgres --dbname MYDB

Kind Regards,
Keith

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message george young 2006-03-30 15:09:12 Re: Does a connection support multiple transactions.
Previous Message John DeSoi 2006-03-29 18:32:50 Re: PHP libpq Version != Actual PGSQL Version