Unable to create a new database with PostgreSQL 8.3.4?

[Guest]

Maybe somebody can help me with this: I have recently upgraded my PostgreSQL installation to 8.3.4 on my Linux box. First I backed up my PT3 database, then upgraded PostgreSQL, then restored the database, no problems encountered. Except a massive increase in performance, which was a good thing.

Now I want to start recording some play money tourneys I have played in a separate database, so I want to create a new database from the PT3 GUI. And unfortunately this doesn't work:

Unable to create PostgreSQL database: PT3 PM
Reason: Unable to execute query: CREATE DATABASE "PT3 PM" WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII';; Reason: Fatal Error (ERROR: encoding SQL_ASCII does not match server's locale en_US DETAIL: The server's LC_CTYPE setting requires encoding LATIN1. )

Oddly though I have used initdb explicitely with LC_CTYPE=SQL_ASCII:
postgres=# \l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
PT3 DB | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII

And this command also works from the psql terminal:
postgres=# CREATE DATABASE "PT3 PM" WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII';
CREATE DATABASE
postgres=#

So the question is: Why doesn't it work from the PT3 GUI?

Any ideas?

[kraada]

That's odd. I'm not sure what would cause that off the top of my head, but you can manually initialize the schema required for PT3 if you can create databases on your own (which it seems you can). Copy schema.postgre.sql to the Linux box, and while connected to the new (empty) database run \i /path/to/schema.postgre.sql and that will create your database in line with the PT3 specifications, you can the connect to it via the Database Management screen (New --> Browse) and you should be able to import to it normally thereafter.

[Guest]

Ah, thanks. Will try that.

[Guest]

Ah, it seems I have solved this issue: I used a specific user to create the database, not the "postgres" admin user. Probably there is some right this PT3 user did not have that was required.

So the answer is: Use the postgresql admin user when creating a database.

[kraada]

That makes sense. Given that the postgres user can't really do anything else other than interact with postgres, why would you want to use a different user anyway?

[Guest]

Basically because I have more applications running off of this database server :) (which were the reason I could not upgrade before, no certification yet)

[kraada]

Well, I'm glad to hear you've gotten things working now, either way.

Now you can look forward to PostgreSQL 8.4 getting certification ;)