Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Unanswered: base encoding problem - SQL_ASCII instead of UTF8

    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

  2. #2
    Join Date
    May 2008
    Posts
    277
    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.

  3. #3
    Join Date
    Jun 2011
    Posts
    3
    Quote Originally Posted by futurity View Post
    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.

  4. #4
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by madnbri View Post
    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.

    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.
    Last edited by futurity; 07-01-11 at 13:48.

  5. #5
    Join Date
    Jun 2011
    Posts
    3
    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.

    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.

    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
    Last edited by madnbri; 07-01-11 at 15:01. Reason: solved

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •