>>But what's even stranger is that I can't initialize the database when I
>>specify the database name, host, port, username and password in the
>>configuration file.
>
> That is wierd. Do you have all the settings plus the service
> defined?
No, in this case I had all the settings defined except the service.
> Hmm, db_command should say what command it's having an issue with ans
> what the error is.
>
> Can you change:
>
> - raise RuntimeError('10 attempts to create database failed')
>
> to
>
> + raise RuntimeError('10 attempts to create database failed when running: %s' % command)
>
> around line 96 of roundup/backends/back_postgresql.py. That will get
> some of the info hopefully.
I changed the line. Also, I found that I only get this exception when I
stay logged into the psql console as user roundup_dev. So it might be a
problem with the postgresql instance.
$ roundup-admin initialise
Enter tracker home: core
Admin Password:
Confirm:
WARNING: The database is already initialised!
If you re-initialise it, you will lose all the data!
Erase it? Y/N: Y
2023-12-19 02:43:40,834 INFO DROP DATABASE "roundup_dev"
Traceback (most recent call last):
File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
8, in <module>
sys.exit(run())
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
line 50, in run
sys.exit(tool.main())
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2230, in main
ret = self.run_command(args)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2079, in run_command
return self.do_initialise(self.tracker_home, args)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 1168, in do_initialise
tracker.nuke()
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 213, in nuke
self.backend.db_nuke(self.config)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 64, in db_nuke
db_command(config, command)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 96, in db_command
raise RuntimeError('10 attempts to create database failed when
running: %s' % command)
RuntimeError: 10 attempts to create database failed when running: DROP
DATABASE "roundup_dev"
Otherwise I get another exception:
$ roundup-admin initialise
Enter tracker home: core
Admin Password:
Confirm:
WARNING: The database is already initialised!
If you re-initialise it, you will lose all the data!
Erase it? Y/N: Y
2023-12-19 03:18:14,738 INFO DROP DATABASE "roundup_dev"
2023-12-19 03:18:15,787 INFO CREATE DATABASE "roundup_dev" WITH
ENCODING='UNICODE' TEMPLATE=template0
Traceback (most recent call last):
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 106, in pg_command
cursor.execute(command)
psycopg2.errors.InsufficientPrivilege: permission denied to create
database
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
8, in <module>
sys.exit(run())
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
line 50, in run
sys.exit(tool.main())
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2230, in main
ret = self.run_command(args)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2079, in run_command
return self.do_initialise(self.tracker_home, args)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 1172, in do_initialise
tracker.init(password.Password(adminpw, config=tracker.config),
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 201, in init
db = self.open('admin')
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 111, in open
'db': backend.Database(self.config, name)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/rdbms_common.py",
line 227, in __init__
self.open_connection()
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 212, in open_connection
db_create(self.config)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 57, in db_create
db_command(config, command)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 92, in db_command
if pg_command(cursor, command):
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 118, in pg_command
raise RuntimeError(response)
RuntimeError: permission denied to create database
> Also can you connect to the db with psql and run \du so I can see what
> permissions the user has. I assume none are listed since you don't
> have CREATEDB.
Yes, it is.
postgres=# \du
List of roles
Role name | Attributes
| Member of
-----------------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication,
Bypass RLS | {}
roundup_dev |
| {}
roundup_dev=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale
| Locale Provider | Access privileges
---------------+---------------+----------+---------+-------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | C | C |
| libc |
roundup_dev | roundup_dev | UTF8 | C | C |
| libc |
template0 | postgres | UTF8 | C | C |
| libc | =c/postgres +
| | | | |
| | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C |
| libc | =c/postgres +
| | | | |
| | postgres=CTc/postgres
roundup_dev=# \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
>>Maybe some kind of non-destructive initialization might be possible,
>>without recreating the entire database?
>
> It's supposed IIRC. It checks to see if the db exists. If the db
> exists it tries to load the roundup schema. If that fails it
> initializes the db. But it doesn't look like you are getting that far.
Hmm... I'm also concerned about automation. If I have a pipeline, I
would like to initialize the database only if it is not already
initialized. If some data is already in the database, it is advisable to
skip the initialization step. Of course, this would be easier to do with
a native Roundup command rather than using psql checks. I think it would
be great to have a special flag for the 'initialise' command for such
cases.
>>By the way, why a database and not a schema?
>
> Because thats how it was originally designed and nobody has tried to
> integrate https://issues.roundup-tracker.org/issue2550852 and finish
> up tests/docs etc. I have a vague recollection of testing the patch a
> few years ago and it worked.
I will try to test with the current branch.
>>At the same time, when I directly create Roundup users, it works for me.
>
> Is the sequence here:
>
> set all db params in config.ini (host, database ...)
>
> roundup-admin init
>
> get the error: RuntimeError: 10 attempts to create database failed
>
> roundup-admin create user ....
>
> works.
Yes.
>>Second, there is a problem with using a schema other than 'public'. I
>>can't perform database migration when I create scheme named by my
>>postgresql username and use it in search_path. I see that Roundup
>>expected me to use the 'public' schema.
>
> That is either fixed in issue2550852, or a 'to be fixed' if support
> for schemas comes on board.
Okay.
> Now this is wierd because:
>
> 2023-12-18 03:13:02,033 DEBUG SQL 'CREATE TABLE schema (schema TEXT)'
>
> the schema table and column were created, but probably in your
> alternate schema. ("schema" is a confusing term for Roundup to use
> there....)
>
> How are you creating/directing roundup to use an alternate schema? Is
> there a schema= in pg_service.conf? Is the path associated with the
> logged in user?
$ cat .pg_service.conf
[roundup_dev]
hostaddr=127.0.0.1
port=5432
dbname=roundup_dev
user=roundup_dev
password=ROUNDUPPASS
options=-c search_path=roundup_dev,public
sslmode=disable
But if I remember correctly, postgresql by default tries the schema
named by the username first.
>>If I only use the 'public' schema for my database, the migration command
>>works well.
>
> I wonder why this has an explicit schema on it? Can you check to see
> if your alternate schema (assuming it got committed to the db) has a
> __words table in it?
After running the 'roundup-admin migrate' command:
roundup_dev=> \dn
List of schemas
Name | Owner
-------------+-------------------
public | pg_database_owner
roundup_dev | roundup_dev
roundup_dev=> \dt public.*
Did not find any relation named "public.*".
roundup_dev=> \dt roundup_dev.*
Did not find any relation named "roundup_dev.*".
There are no tables at all.
>
> Can you change the reference to public.__words to just __words around
> line 335 in back_postgresql.py. Then see if the migrate works. I don't
> see any other explicit schema references in that file.
It works. Thank you very much.
roundup_dev=> \dt roundup_dev.*
List of relations
Schema | Name | Type | Owner
-------------+-------------------+-------+-------------
roundup_dev | __fts | table | roundup_dev
roundup_dev | __textids | table | roundup_dev
roundup_dev | __words | table | roundup_dev
roundup_dev | _file | table | roundup_dev
roundup_dev | _issue | table | roundup_dev
roundup_dev | _keyword | table | roundup_dev
roundup_dev | _msg | table | roundup_dev
roundup_dev | _priority | table | roundup_dev
roundup_dev | _query | table | roundup_dev
roundup_dev | _status | table | roundup_dev
roundup_dev | _user | table | roundup_dev
roundup_dev | dual | table | roundup_dev
roundup_dev | file__journal | table | roundup_dev
roundup_dev | issue__journal | table | roundup_dev
roundup_dev | issue_files | table | roundup_dev
roundup_dev | issue_keyword | table | roundup_dev
roundup_dev | issue_messages | table | roundup_dev
roundup_dev | issue_nosy | table | roundup_dev
roundup_dev | issue_superseder | table | roundup_dev
roundup_dev | keyword__journal | table | roundup_dev
roundup_dev | msg__journal | table | roundup_dev
roundup_dev | msg_files | table | roundup_dev
roundup_dev | msg_recipients | table | roundup_dev
roundup_dev | otks | table | roundup_dev
roundup_dev | priority__journal | table | roundup_dev
roundup_dev | query__journal | table | roundup_dev
roundup_dev | schema | table | roundup_dev
roundup_dev | sessions | table | roundup_dev
roundup_dev | status__journal | table | roundup_dev
roundup_dev | user__journal | table | roundup_dev
roundup_dev | user_queries | table | roundup_dev
(31 rows)
roundup_dev=> \dt public.*
Did not find any relation named "public.*". |