(C) 2005 by Julia Dubenskaya 
and Sergei Vyshenski 
for OpenCA project.

This document can be modified and redistributed along the terms
of the GNU Free Documentation License 1.2 (or newer)
You can obtain a copy at http://www.gnu.org/licenses/fdl.txt

This HOWTO-PostgreSQL-UTF8 text says how to enable 
processing of UTF8-encoded data in PostgreSQL database.
It is meant for persons that are confortable with PostgreSQL 
and are concerned with just enabling UTF8 support on data level.
It says nothing about change of the interface language.
================================================
UTF8 support in PostgreSQL

To provide utf8 support in PostgreSQL databases it is necessary 
to explicitly define database encoding on the stage of creating 
database cluster.

The example below implies use of FreeBSD-5.4 and 
PostgreSQL-7.4.8 suite installed from FreeBSD ports collection, 
but basic idea is universal.

If you already have pre-installed PostgreSQL suite then:
1) stop PostgreSQL server
2) go to point 3 below.

To install PostgreSQL suite anew, proceed as follows.

To create a new PostgreSQL database you should:

 As root
 #######

1. Install the following ports for postgre suite 
(from /usr/ports/databases/):

       postgresql74-client
       postgresql74-server
       postgresql-contrib
       postgresql-docs       
     
2. Install the following ports to enable access from perl 
modules to postgre (from /usr/ports/databases/):

       p5-Pg     
       p5-DBI
       p5-DBD-Pg
       postgresql-plperl

3. Create a new directory for (UTF8-enabled) database cluster: 

       mkdir pg_dir

4. Change permissions for the directory 'pg_dir', so that the 
database user (when installing from FreeBSD ports the default 
user name is pgsql which belongs to group pgsql) can do anything, 
while others can do nothing:

       chown pgsql:pgsql pg_dir
       chmod 700 pg_dir

5. To start PostgreSQL server at the system startup add 
to '/etc/rc.conf':

       postgresql_enable="YES"
       postgresql_data="pg_dir"

6. To become the database user say:
  
       su pgsql


  As pgsql
  ###########
    
7. Create a new database cluster using option 'encoding' set 
to utf8:

       initdb --pgdata=pg_dir   \ 
              --encoding=utf8   \
              --username=pgsql  \
              --pwprompt
     
8. Run PostgreSQL server with pg_dir which is set 
in /etc/rc.conf (see point 5 above):

       _PREFIX_/etc/rc.d/010.pgsql.sh start
  
       In FreeBSD the default value of _PREFIX_ is '/usr/local'.

You can use 'pg_ctl' or 'postmaster' to start the server. 
In this case you will have to define pg_dir manually 
every time you run the program:

       pg_ctl start -D pg_dir
                 or
       postmaster -D pg_dir   

8. Then create databases as it is described in the OpenCA guide.

9. To check encoding of the datafields say:
   
       psql -d openca -U openca        

psql console opens and prompts you with "openca=>". 
From this console say:

      \encoding
      
If everything is OK you will see:
 
       UNICODE

This is normal. Database just wants to say that it understands 
the meaning: UTF = UNICODE TRASFORMATION FORMAT

======================================================
31.7.2005