Gramps Version 6.0.6 on Ubuntu Linux 26.04
Steps to reproduce: Perform Gramps update to version 6.0.6 in conjunction with the operating system update to Ubuntu Linux 26.04
65803: ERROR: dbloader.py: line 106: syntax error at or near "NOT"
LINE 1: CREATE COLLATION IF NOT EXISTS "de_DE"(LOCALE = 'de_DE.UTF-8...
^
Traceback (most recent call last):
File "/usr/lib/python3/dist-packages/gramps/gui/dbloader.py", line 197, in read_file
db.load(
filename,
^^^^^^^^^
...<4 lines>...
password=password,
^^^^^^^^^^^^^^^^^^
)
^
File "/usr/lib/python3/dist-packages/gramps/gen/db/generic.py", line 732, in load
self._initialize(directory, username, password)
~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/tux/.local/share/gramps/gramps60/plugins/PostgreSQL/postgresql.py", line 105, in _initialize
self.dbapi = Connection(**dbkwargs)
~~~~~~~~~~^^^^^^^^^^^^
File "/home/tux/.local/share/gramps/gramps60/plugins/PostgreSQL/postgresql.py", line 121, in __init__
self.check_collation(glocale)
~~~~~~~~~~~~~~~~~~~~^^^^^^^^^
File "/home/tux/.local/share/gramps/gramps60/plugins/PostgreSQL/postgresql.py", line 133, in check_collation
self.execute('CREATE COLLATION IF NOT EXISTS "%s"'
~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
"(LOCALE = '%s')" % (collation, locale.collation))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/tux/.local/share/gramps/gramps60/plugins/PostgreSQL/postgresql.py", line 164, in execute
self.__cursor.execute(sql, args, **kwargs)
~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.SyntaxError: syntax error at or near "NOT"
LINE 1: CREATE COLLATION IF NOT EXISTS "de_DE"(LOCALE = 'de_DE.UTF-8...
^
System Information:
===================
Gramps version: 6.0.6
Python version: 3.14.4
BSDDB version: 6.2.9 (5, 3, 28)
sqlite version: 3.46.1
orjson version: 3.11.5
LANG: de_DE.UTF-8
OS: Linux
Distribution: 7.0.0-14-generic
GTK version : 3.24.52
gobject version: 3.56.2
cairo version : (1, 27, 0)
Is anyone else having this problem? Can anyone help?
I also reported this here: https://gramps-project.org/bugs/view.php?id=14199
The bug is a PostgreSQL version compatibility problem in check_collation.
The code is:
self.execute('CREATE COLLATION IF NOT EXISTS "%s"'
"(LOCALE = '%s')" % (collation, locale.collation))
CREATE COLLATION IF NOT EXISTS is a PostgreSQL 12+ feature. If you’re running PostgreSQL 11 or earlier, the server doesn’t recognize the IF NOT EXISTS clause.
Why it’s triggered by locale: The collation creation only happens when the user’s LANG isn’t the default. The reporter has LANG: de_DE.UTF-8, so Gramps tries to create a de_DE collation in PostgreSQL on startup. A user with LANG=en_US.UTF-8 on an older PostgreSQL
might never hit it if an en_US collation already exists.
You can use the above workaround (LANG=en_US.UTF-8) or make a bug report and we can fix the postgresql addon-on.
But I use PostgreSQL 18.4?
tux@tux-W35-37ET:~$ psql --version
psql (PostgreSQL) 18.4 (Ubuntu 18.4-0ubuntu0.26.04.1)
tux@tux-W35-37ET:~$ pg_config --version
PostgreSQL 18.4 (Ubuntu 18.4-0ubuntu0.26.04.1)
The actual root cause on PG 18.4 is more likely a client/server version mismatch:
psql --version
reports the client version. The server they’re actually connecting to could be a different (older) version. You should run this inside psql to check:
SELECT version();
Ah. Okay. Nasty trap.
=> SELECT version();
version
PostgreSQL 9.5.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.2.0-5ubuntu12) 6.2.0 20161005, 64-bit
(1 row)
~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.5 main 5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
9.6 main 5433 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
10 main 5434 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11 main 5435 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
12 main 5436 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
13 main 5437 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
14 main 5438 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15 main 5439 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
16 main 5441 down postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
17 main 5442 down postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
18 main 5440 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
If I uninstall the old PostgreSQL versions, will the Gramps database automatically be used with the latest version (18), or will the database be deleted during the uninstallation, requiring me to migrate it beforehand? What steps do I need to take to migrate it? It is crucial that no data is lost; I have invested a great deal of time in my genealogical research.
Sorry, I don’t know postgresql enough to answer more questions. But it sounds like you have some ideas.
I will assure you that we won’t let your data be inaccessible; we’ll work with you further if we need to!
Is there anyone here with enough PostgreSQL knowledge to tell me how to migrate my Gramps database from version 9.5.6 to version 18 without the risk of data loss?
I see that you were able to resolve the issue by upgrading Postgresql: https://gramps-project.org/bugs/view.php?id=14199#c70780
Glad to hear it!
Yes. Thanks for your help. I still have the old database instance and could use it to test your fix for the plugin, provided you tell me how to manually install the fixed plugin in Gramps. Up to now, I’ve only done this via the Addon Manager, but the new, fixed plugin isn’t available there yet.