UTF8 error when setting up database
Moderators: a'moaca', rarified
Re: UTF8 error when setting up database
Thanks. Will take me a bit to unravel this, and having dinner myself.
The music is reversible, but time is not.
Re: UTF8 error when setting up database
Alllright. I was able to jump through the necessary hoops to fix the database ownership...
... HOWEVER, i still hit a wall when I run pg_dumpall.
Problem is, I don't see how to get to the CREATE DATABASE statement you're wanting me to check. pg_dump doesn't produce it, and pg_dumpall seems to be blocked as it is running into the permission denied problem. A websearch suggests that if I give superuser permission to the moss role, then I can get the info you're seeking. But nothing in MOSS/Setup suggested I should ever have to do that.
What should I try next?
Code: Select all
moss@moulbox:~$ psql -l moss
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
moss | moss | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Code: Select all
moss@moulbox:~$ pg_dumpall -U moss
--
-- PostgreSQL database cluster dump
--
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
pg_dumpall: query failed: ERROR: permission denied for relation pg_authid
pg_dumpall: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid ORDER BY 2
What should I try next?
The music is reversible, but time is not.
Re: UTF8 error when setting up database
Let's see what your moss role allows. Do this and check the output:
_R
Edit: this also works and shows the inheritance:
Code: Select all
-bash-3.2# psql -U postgres -c 'select * from pg_roles;'
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid
----------+----------+------------+---------------+-------------+--------------+-------------+--------------+-------------+---------------+-----------+-------
postgres | t | t | t | t | t | t | -1 | ******** | | | 10
moss | f | t | f | t | f | t | -1 | ******** | infinity | | 16384
(2 rows)
-bash-3.2#
Edit: this also works and shows the inheritance:
Code: Select all
-bash-3.2# psql -U moss -c '\du'
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+------------
moss | no | no | yes | no limit | {postgres}
postgres | yes | yes | yes | no limit | {}
(2 rows)
-bash-3.2#
One of the OpenUru toolsmiths... a bookbinder.
Re: UTF8 error when setting up database
Code: Select all
moss@moulbox:~$ psql -U postgres -c 'select * from pg_roles;'
psql: FATAL: Peer authentication failed for user "postgres"
moss@moulbox:~$ psql -U moss -c '\du'
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
alderem | Create role, Create DB | {moss}
moss | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
The music is reversible, but time is not.
Re: UTF8 error when setting up database
We may almost be thereMarten wrote: I could... just wipe and restart if that would be easiest.
Can you start a PG shell with "psql -U postgres" and get to the "postgres=# " prompt?
If so we may salvage some of it, but have to change all the ownerships (definitely the "moss" database, perhaps the tables and functions as well).
Or you can just do:
Code: Select all
shell$ dropdb -U postgres moss
shell$ dropuser -U postgres moss
shell$ dropuser -U postgres alderem
Going to head to bed soon, need to be up at 5am
_R
One of the OpenUru toolsmiths... a bookbinder.
Re: UTF8 error when setting up database
Ahhh, one more place permissions are granted. Find your 'pg_hba.conf' file (it would be in the database directory tree for the postgres server. You could run pg_config --configure and look for the --datadir element for the database directory).
I added permisssions for moss that gave some privileges for local connections. The tail end of my pg_hba.conf file:
The x.x.x.x/x is the network address mask for my local network(s).
_R
I added permisssions for moss that gave some privileges for local connections. The tail end of my pg_hba.conf file:
Code: Select all
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
host all moss x.x.x.x/x trust
_R
One of the OpenUru toolsmiths... a bookbinder.
Re: UTF8 error when setting up database
Alright. As it sounds like you have a busy day tomorrow, and I don't want to eat up much more of your time, I think I'll take the lessons I've had from this round, and start over from scratch. And I'll see if I run into the UTF8 problem again, when I get there. It doesn't seem to me like something that should be related to the ownership issues, but I know those ownership issues are going to bite me until I have them sorted out so best to start fresh.
Thanks again. Will let you know how far I get on attempt #2.
Thanks again. Will let you know how far I get on attempt #2.
The music is reversible, but time is not.
-
- Member
- Posts: 317
- Joined: Sat Dec 13, 2008 10:54 am
Re: UTF8 error when setting up database
I don’t know much about PostgreSQL, but 0xe0 0x30 0x31 is in fact invalid UTF-8, and it doesn’t occur in set_to_moul.txt, so it seems to me that your global_sdl_manager is doing something weird. Maybe running it in a debugger and breaking on throwing of a pqxx::sql_error helps you figure out what.Marten wrote:No node found for age city (use 'c')
Created new default 'city' SDL
SQL error: ERROR: invalid byte sequence for encoding "UTF8": 0xe0 0x30 0x31
Writing the node failed for reason 1
There are unsaved changes, change anyway? Variable philDRCVest not found
SQL error: ERROR: invalid byte sequence for encoding "UTF8": 0xe0 0x30 0x31
Writing the node failed for reason 1
Re: UTF8 error when setting up database
I dumped the databases and went back to *Set Up the Database*, using the user account "moss" instead of the user account 'alderem.' This time I took more notes about the instruction problems I encountered, and how I adapted around them.
I have not yet loaded the SDL file. I'm at a "sanity checkpoint" and I'd like input and thoughts.
For setting up the DB, the wiki says:
Problem #1: Instructions assume the PostgreSQL user account is 'pgsql'. It isn't on Ubuntu. It's 'postgres'.
Problem #2: After adapting for problem #1, the instructions also don't say you should run these commands as root. If I run this from any account other than root, I get prompted for the postgres user password, which I don't know.
I adapted the instructions to this. Does this seem right?
That brings me to the sanity checks I wanted to run. First one:
Note that the moss database is owned by 'postgres'. This is different than what rarified showed. When I look at the instructions, it seems clear that 'postgres' should be the expected owner, because the database is created before the moss role is instantiated. But given the problems I already noted above, I'm suspicious that while I've gotten the result I expected, it might not be the result I actually need.
Second one:
I've figured out that I still get the same error I previously mentioned from pg_dumpall *if I try to run the command from the 'moss' account*, but if I use the 'postgres' account then it works fine! I found the CREATE DATABASE line that rarified was asking about. Here's the line as it appeared in my output, without the "ENCODING=" that rarified suggested might need to be there:
Here's the whole dump, in case anyone is curious:
https://www.dropbox.com/s/ztdx3fcmlh932 ... l.txt?dl=0
...
So there are my 2 sanity checks and concerns. The database belongs to postgres, and the CREATE DATABASE line does not mention ENCODING. These are the results from following the MOSS/Setup instructions to my best ability, and to the extent that they can be followed (adapting to the assumptions and omissions I've noted).
So - before I proceed to load the SDL - is my setup sane, or is it broken? And if it's broken, can someone give me working instructions that lead to the right outcome?
Thanks
I have not yet loaded the SDL file. I'm at a "sanity checkpoint" and I'd like input and thoughts.
For setting up the DB, the wiki says:
Code: Select all
su pgsql -c "psql postgres"
create database moss with encoding='UTF8';
\c moss
create role moss with login;
\q
su pgsql -c "psql moss -f moss.sql"
Problem #2: After adapting for problem #1, the instructions also don't say you should run these commands as root. If I run this from any account other than root, I get prompted for the postgres user password, which I don't know.
I adapted the instructions to this. Does this seem right?
Code: Select all
moss@moulbox:~$ sudo su - postgres -c "psql postgres"
create database moss with encoding='UTF8';
\c moss
create role moss with login;
\q
sudo su - postgres -c "psql moss -f ~moss/MOSS/postgresql/moss.sql"
Code: Select all
moss@moulbox:~/MOSS/postgresql$ psql -U moss -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
moss | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Second one:
I've figured out that I still get the same error I previously mentioned from pg_dumpall *if I try to run the command from the 'moss' account*, but if I use the 'postgres' account then it works fine! I found the CREATE DATABASE line that rarified was asking about. Here's the line as it appeared in my output, without the "ENCODING=" that rarified suggested might need to be there:
Code: Select all
CREATE DATABASE moss WITH TEMPLATE = template0 OWNER = postgres;
https://www.dropbox.com/s/ztdx3fcmlh932 ... l.txt?dl=0
...
So there are my 2 sanity checks and concerns. The database belongs to postgres, and the CREATE DATABASE line does not mention ENCODING. These are the results from following the MOSS/Setup instructions to my best ability, and to the extent that they can be followed (adapting to the assumptions and omissions I've noted).
So - before I proceed to load the SDL - is my setup sane, or is it broken? And if it's broken, can someone give me working instructions that lead to the right outcome?
Thanks
The music is reversible, but time is not.
Re: UTF8 error when setting up database
Will reply longer after lunch, but your first observation about postgres vs. pgsql is correct.
Also, yes it's correct that pg_dumpall would only be allowed under postgres.
_R
Also, yes it's correct that pg_dumpall would only be allowed under postgres.
_R
One of the OpenUru toolsmiths... a bookbinder.