base encoding problem - SQL_ASCII instead of UTF8

madnbri · #1 (**permalink**) 06-29-11, 05:24

Hello,
I've installed PostgreSQL on gentoo from ebuild and used --config as this tutorial shows. When config has ended, I've seen, my base encoding type became the SQL_ASCII. And now? I haven't found in documents (of course I used both of google and pgdocs) how to change it. My postgres, template0 and template1 looks like this:

Code:

Name      |Owner    |Encoding  |...
--------------------------------------------
postgres  |postgres |SQL_ASCII |...
template0 |postgres |SQL_ASCII |...
template1 |postgres |SQL_ASCII |...

I have to change all of encoding type to UTF8 and naturally the base setting also.

Can anybody help?

Regards,
mad

futurity · #2 (**permalink**) 06-30-11, 12:18

It looks like initdb could not determine your system's locale when it was run, which would suggest an overall system misconfiguration.

You can explicitly specify a database's encoding using CREATE DATABASE. So one option might be to drop your template databases and recreate them using the proper settings.

Another option might be to nuke the entire cluster and re-run initdb with the proper settings.

madnbri · #3 (**permalink**) 06-30-11, 14:36

Quote:

Originally Posted by futurity

It looks like initdb could not determine your system's locale when it was run, which would suggest an overall system misconfiguration.

You can explicitly specify a database's encoding using CREATE DATABASE. So one option might be to drop your template databases and recreate them using the proper settings.

Another option might be to nuke the entire cluster and re-run initdb with the proper settings.

OK. Just in queue

I do not know why it couldn't used my system locales. Is it possible reason?:
My locale.gen file looks like this:

Code:

hu_HU.UTF8 UTF8
en_us.UTF8 UTF8

Is there a missing one like this?:

Code:

UTF8 UTF8

It looks strange. UTF8 is a base type. It is not needed to generate again, is it?

Everything works fine in my system, just this program doesn't. I cannot start from scratch again and the same difficulties liable to occur.

Code:

CREATE DATABASE tablename WITH TEMPLATE template0 ENCODING  'utf8';

This works, but doesn't like for me, because of the long command, so this one also doesn't:

Code:

CREATE DATABASE template2 WITH TEMPLATE template0 ENCODING 'utf8';

...and after this every new database can be created with this template:

Code:

CREATE DATABASE testDB WITH TEMPLATE template2;

"nuke entire cluster"…
I haven't got $PGDATA…

Against I do not want to reconfigure everything, I cannot see other way to solve. Can You help to me? What is needed to reconfigure all?

How to remove postgresql with all data and mainly how can I configure it to use UTF8 as default encoding type? I've searched this a lot, but nowhere found a --config option to set up.

futurity · #4 (**permalink**) 07-01-11, 12:42

Quote:

Originally Posted by madnbri

I do not know why it couldn't used my system locales. Is it possible reason?:
My locale.gen file looks like this:

Code:

hu_HU.UTF8 UTF8
en_us.UTF8 UTF8

I don't know; I'm guessing because you have two locales set? It doesn't seem to make sense that you'd need two. But I have no idea how to troubleshoot your system's locale settings, especially on Gentoo.

Quote:

I haven't got $PGDATA…

This is an environment variable that points to the server's data directory. On my system this is located in '/var/lib/postgresql'. So, delete that directory, then rerun initdb specifying your locale explicitly. My system (Debian) has pg_dropcluster and pg_createcluster commands to help make this a little easier, but things are also set up a little differently on Debian compared to a from-scratch PostgreSQL installation, so I have no idea how it is with Gentoo.

An alternative, though this will only fix your database encodings, is to recreate your template databases. Assuming you haven't modified your templates:

Code:

drop database template1;
create database template1 template template0 encoding 'UTF-8';

drop database template0;
create database template0;

You'll want to review the documentation link I posted earlier to correctly configure 'datistemplate' and 'datallowconn' for your new template databases.

I've never done either of these things though; proceed at your own risk.

madnbri · #5 (**permalink**) 07-01-11, 13:26

Quote:

I don't know; I'm guessing because you have two locales set? It doesn't seem to make sense that you'd need two. But I have no idea how to troubleshoot your system's locale settings, especially on Gentoo.

I use duplication because of naming only.

Quote:

Code:

I haven't got $PGDATA…

This is an environment variable that points to the server's data directory

I know it, but nothing created this at installation or configuration.

Quote:

An alternative, though this will only fix your database encodings, is to recreate your template databases. Assuming you haven't modified your templates:

Code:

drop database template1;
create database template1 template template0 encoding 'UTF-8';

drop database template0;
create database template0;

You'll want to review the documentation link I posted earlier to correctly configure 'datistemplate' and 'datallowconn' for your new template databases.

I tried it, but not possible... - on simple way

But it is possible like this:

Code:

# to get information about problems (let it be the select#1):
select datname, datistemplate,
  datallowconn, encoding, datacl 
  from pg_database where 
  datname = 'template0' or 
  datname = 'template1';
# it shows the pg_database.datistemplate and 
# pg_database.datallowconn is setted as true

# set up to false in case of template1
update pg_database set datistemplate = false, 
  datallowconn = false 
  where datname = 'template1';
# and now becamed removable the template1:
drop database template1;

# recreating...
create database template1 
  with template template0 encoding 'utf8';
# we aren't ready of course
# set up the datistemplate and 
# datallowconn at the new template1
update pg_database set 
  datistemplate = true, 
  datallowconn = true 
  where datname = 'template1';
# everithing is OK?:
# use the select#1 to check
# no, there is something missing. 
# datacl shows a value at template0. 
# we can copy it into template1:
update pg_database set 
  datacl = '{=c/postgres,postgres=CTc/postres}' 
  where datname = 'template1';

...and now:

Code:

create database testdb;

creates a database with encoding UTF8

Thanks for help!

Regards,
mad

Internet Services

Web Development

Digital Marketing

Consumer Tech