Error creating DB on linux

[Glaaki]

Hi I am running PostgreSQL on Debian linux. I can connect to the database just fine, but when I try to create a new database, I get the following error:
1040

ERROR: XX000: encoding SQL_ASCII does not match server's locale da_DK.UTF_8

I have an idea for a workaround using a db created on windows and backed up and restored on the linux server. Do you have any other ideas?

[Glaaki]

I can't restore a database either because of errors stemming from this charset issue.
Why isn't the database using utf-8 charset? That is supported everywhere else.

[zipa]

Are you using this postgres instance for something else than HEM? If not, you could always recreate the database cluster with a different locale.

[Glaaki]

Are you using this postgres instance for something else than HEM? If not, you could always recreate the database cluster with a different locale.

No I am not using it for anithing besides HEM. I am thinking I need locale C for this to work, if I am reading the docs correctly. I can't figure out how to recreate the cluster though. Do you know how to do that? I guess in the end it might be easier to just install the windows version.

[zipa]

You can try to manually create the database for HEM with a different locale first:

"createdb -E SQL_ASCII database_name" (name the db to what you like)

Then have HEM use that database, and it will hopefully create all the necessary tables it needs.

If that doesn't work, you need to reinit the cluster:

1. Stop postgres
2. Delete all data directories
3. Run "initdb -locale=SQL_ASCII"
4. Restart postgres

[Glaaki]

HEM will not use an existing empty database and fill it with tables. You can create the database, the way you suggest but you can't fill it with the necessary data. You can not restore another database to an empty database either (you get errors inserting the db functions).

I will try your suggestion regarding recreating the cluster later.

By the way, I think the command should be "initdb --locale=C" as this disables the charset checking routine in the database. (SQL_ASCII is a charset, not a locale.)

Thanks for the suggestions.

[zipa]

Yes, sorry about that. What you'll want is of course "initdb -E SQL_ASCII". I guess that the safe bet would be to use en_US as the locale, since that is what is specified in the installation guide. So, the full command would be like this:

initdb --locale=en_US -E SQL_ASCII

EDIT: It might work with just --locale=C but I'm not sure. Can't test it now, either.

[Glaaki]

I have found a guide to using PostgreSQL on Debian -

http://www.stuartellis.eu/articles/postgresql-setup

There are special scripts (this is actually a typical Debian way of doing things), that you use to manage clusters:

* pg_createcluster – utility to create new clusters
* pg_lsclusters – utility to display the clusters on the system
* pg_ctlcluster – utility to manage clusters
* pg_upgradecluster – utility to upgrade clusters
* pg_dropcluster – utility to remove clusters
* user_clusters – configuration file to specify the default cluster for users and groups

Instead of manually deleting files and using initdb, I will check out the manpages for these tools. Hopefully I should be able to run both an UTF-8 and a C locale cluster at the same time, so I can use the postgresql for other things as well.

[zipa]

Hopefully I should be able to run both an UTF-8 and a C locale cluster at the same time

I don't see why you couldn't, it should be all in the guides.

[The Minder]

Am I guessing that you folks have HEM running on linux?

[zipa]

Am I guessing that you folks have HEM running on linux?

Nope, Vista, and it looks like Vista in the OP's screenshots as well. Just the database is running on a linux host, I guess.

[Glaaki]

Am I guessing that you folks have HEM running on linux?
I don't know. Are you?

Joking aside, no, I have HEM running on Windows Vista. I don't plan to run it on Linux.

[Glaaki]

Succes! Here is a quick how-to:

Setting up a Debian server with UTF-8 locale for use with Holdem Manager

You first need to create a database cluster that is locale-agnostic.

debian:~# pg_createcluster --locale=C --start 8.3 hem

If you are using a different psql version, replace 8.3 with your version. The name of the cluster in this case "hem", but you can pick another name if you wish.

The script will take a while to run. The new cluster will run on a different port than the original cluster, so make a note of that.

Next set the md5 password for user postgres:

debian:~# su - postgres
postgres@debian:~$ psql -p 5433
Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# \password postgres

At this point it is probably a good idea to create another user that you use for holdem manager, but it is not a requirement.

Log out of psql with \q and return to the root shell.

You now need to change the configuration of the cluster to allow remote logins from your lan.

debian:~# cd /etc/postgresql/8.3/hem/

Change the postgresql.conf so that

listen_addresses = '*'

Change the pg_hba.conf file and add the line:

host all all xxx.xxx.xxx.0/24 md5

It should be the first line to take effect. There is an empty section in the file around the middle of it. It is best to add it there. Change xxx.xxx.xxx to the first three numbers of your servers IP address (see http://en.wikipedia.org/wiki/CIDR for more info.)

Finally restart the server.

liveforever:~# /etc/init.d/postgresql-8.3 restart

Thats it. You should be good to go!

[rod]

Could somebody write the HOWTO for ubuntu?
Thx

[netsrak]

You find tons of postgresql installations for Ubuntu / Linux in the internet.

http://docs.moodle.org/en/Step-by-step_Install_Guide_for_Ubuntu

[Glaaki]

Could somebody write the HOWTO for ubuntu?
Thx

Since it is based on debian, my howto should work.

[rod]

Almost done. Plz help me to do this. I have all the ubuntu part done, just cant connect from VM with winxp to database in ubuntu. help me with that part plz.

[The Minder]

You need an ODBC connection from the VM to Ubuntu.

- go to http://www.postgresql.org/ftp/odbc/versions
- Choose Msi
- Choose the "good" file and download it into the VM guest, in my case I chose psqlodbc_08_03_100.zip
- Unzip to the XP desktop and then double click the icon to install

You may need a full computer reboot afterwards.

[rod]

Thx. It seems to be working. How can i check if its working? Where the database should apear?

[netsrak]

You can install pgadmin (www.pgadmin.org) in the VM and try to connect to your database server.
For HM you have to use the same host settings and then create a database

[rod]

Thx guys. Done.