Enhanced PostgreSQL Addon Submitted

Development
21

Updated PR published.

22

Okay. Here is what searching for “postgresql” in the Addon Manager returns on Gramps 6.0.4

(I found 4 problems that need addressing. 3 are registration for the new Enhanced addon. The other is for the Shared one.)

Capture
Capture922×764 76.3 KB

  1. The Requires button requires-mod key for PostgeSQL Enhanced 1.5.2 errors. ( The requires_mod for the SharedPostgreSQL and PostgreSQL includes a number psycopg2. It that needed here too? On the other hand, those listings lack a Requires button. So maybe their requires_mod key is ignore?)
    requires_mod=['psycopg'], # psycopg3 (checks before installation)

  2. The Wiki button help_url or PostgeSQL Enhanced 1.5.2 falls back to the generic Addon page. It points to a folder (without a trailing slash). @Nick-Hall Does the parser have problems with directory URLs? Does it need a file instead? Or a trailing slash?
    help_url="https://github.com/gramps-project/addons-source/wiki/PostgreSQLEnhanced",

  3. there are typos in the description for PostgeSQL Enhanced 1.5.2 with an extra ‘s’: PostgresSQL

    description=_(
        "Advanced PostgreSQL backend with JSONB storage, "
        "graph database support (Apache AGE), vector similarity (pgvector), "
        "and AI/ML capabilities. For advanced users. "
        "Requires PostgreSQL 15+ with extensions. Gramps Web compatible."
    ),
  1. the SharedPostgreSQL 0.1.14 registration by @dsblank and @DavidMStraub should probably be upgraded to add an EXPERT AUDIENCE key to reduce confusion.
    As is, this is the ONLY PostgeSQL add-on that is listed until users change the default filter from EVERYONE. It might lead them to think that the Enhanced is a mislabeling in the forums.
23

It shouldn’t cause a problem. Just provide a valid URL.

24

ah. We need to create the placeholder page on the wiki.

With a link to the GitHub site while it is still in flux
https://github.com/gramps-project/addons-source/tree/maintenance/gramps60/PostgreSQLEnhanced

26

Fortsetzung der Diskussion von Enhanced PostgreSQL Addon Submitted:

I guess I’m matching the profile you describe: 80k individuals, 250k events and so on, leading to a Gramps XML with 3.5M lines. Gramps 6 on SQLite is impossible to use with such a large tree and I was forced to downgrade to 5.1.6 on BSDDB and to write a lot of code to mirror the Gramps data to a PostgreSQL database.

I started testing the plugin but immediately run into the problem that Gramps will not find the psycopg 3 that is installed. A detailled description can be found at the GitHub issue tracker for the plugin.

27

Fortsetzung der Diskussion von Enhanced PostgreSQL Addon Submitted:

I guess I’m matching the profile you describe: 80k individuals, 250k events and so on, leading to a Gramps XML with 3.5M lines. Gramps 6 on SQLite is impossible to use with such a large tree and I was forced to downgrade to 5.1.6 on BSDDB and to write a lot of code to mirror the Gramps data to a PostgreSQL database.

I started testing the plugin but immediately run into the problem that Gramps will not find the psycopg 3 that is installed. A detailled description can be found at the GitHub issue tracker for the plugin.

28

What issues did you have specifically with Gramps 6 + SQLite and large trees? Was it the import, using specific filters, views, etc?

I’m currently testing trees with 1,000,000 people, and using that to find points to focus on. Would appreciate knowing your pain points.

29

Just wondering… did you also look at the @DavidMStraub Shared PostgreSQL fork of the @dsblank PostgreSQL addon?

(Not to see if it could be used instead. But to see if your enhanced version will have the multi-user options needed for Gramps Web.)

Also, the recent discussions of the MongoDB plugin and of the PostgreSQL made me curious. Would each alternative database plugin benefit from a variation of the Query addon that bundles query shell gramplet and quickview interfaces to make the database functions more accessible from winthin the Gramps UI.

Perhaps a PostgreSQL engine would be compatible with the existing plugin? But it seems likely that the MongoQueryLanguage would need a different set of addons.

31

Hi Ulrich,

I haven’t looked at this lately, but I’ll look at the github issue. Support of PSYCOPG3 is one of these breaking changes (although it’s really a matter of 3 or so syntax issues that are easily resolved) that I really encourage Gramps to adopt.

Anyway I’ll give it a look. Thanks.

Greg Lamberson, Lamco Development ( https://www.lamco.ai )

greg@lamco.io

32

Thanks in advance Greg! I’m really desperate to find a solution for Gramps’s performance problem with large trees. My workarounds are not bad but they are workarounds and not the real stuff :wink:

And I’m still dreaming of a solution where the database backend of Gramps is open for other software to read and use the data. A PostgreSQL backend could be a critical step in that direction.

33

Hi Ulrich,

The issue is that you installed psycopg in Python 3.13, but Gramps AIO uses its own bundled Python 3.12.12. These are completely isolated environments.

Solution:

  1. Open Command Prompt as Administrator
  2. Navigate to: cd “C:\Program Files\GrampsAIO64-6.0.6\bin”
  3. Install psycopg in the correct Python:
    python.exe -m pip install “psycopg[binary]”
  4. Verify: python.exe -c “import psycopg; print(psycopg.version)”
  5. Restart Gramps

I’m working on a permanent fix that will provide better error messages and installation guidance. This plugin was developed on Linux and never properly tested with Windows AIO - that’s my mistake.

Let me know if this resolves your problem now, and I’ll get a better fix pushed out today if I can manage it so Gramps can get it into their pipeline.

Greg

34

That’s exactly the point where I arrived at just a few minutes ago. But my solution was a hack: the bundled Python Gramps is based on is obviously using a ZIP archive named library.zip as its “site packages folder”. So I simply added the folders psycopg and psycopg-3.2.3.dist-info from my site packages folder to that archive.

Et voila: the error I described in the GitHub issue tracker is now gone, so the psycopg library appears to be found. But another error pops up that says that it cannot connect with the PostgreSQL database. It tells me that it tried port 5432 on localhost though I configured it in the Gramps preferences with 192.168.178.20:5434. 192.168.178.20 is localhost, which would be ok, but the port I defined in the preferences is obvioulsy ignored. It also tells me that authentication failed but that may be just a consequence of the wrong port no.

File connection_info.txt in the plugin directory C:\Users\Ulrich Demlehner\AppData\Roaming\gramps\gramps60\plugins\PostgreSQLEnhanced contains:

# PostgreSQL Enhanced Connection Configuration

# This file controls how the addon connects to PostgreSQL

# Connection details

host = 192.168.178.20    # PostgreSQL server address
port = 5434              # PostgreSQL port
user = postgres          # Database username
password = password      # Database password

# Database mode: ‘separate’ or ‘monolithic’

database_mode = monolithic

# For monolithic mode only: name of the shared database

shared_database_name = gramps_monolithic

# Optional settings (uncomment to use)

# pool_size = 10         # Connection pool size

# sslmode = prefer       # SSL connection mode

# connect_timeout = 10   # Connection timeout in seconds

Obviously, this is also ignored which will probably crash your approach.

35

You’re almost there actually. I’m only answering you form memory but you’ve correctly figured out that the configuraiton has to be done in a config file, not the interface. I can’t remember why I didn’t tie that together but give me a couple minutes and I’ll give you what I hope is the last thing you’ll need ot get working.

36

I described it here on the forum in some detail with quantitative performance measurements some time ago. If I remember correctly you also where a part of that discussion. Ah, found it: Performance Issues with Gramps - #16 by UlrichDemlehner

Basically the main point is that searches in the person and event views are just too slow and using SQLite as database backend in v6 doubles the time searches need compared with v5.1.6 on BSDDB. This is completely independent of using RegEx searches or “normal” ones. The other views are ok but those have entries in the range up to 10k or so and that has never been a problem with Gramps.

37

No problem, Greg, it’s already evening here in Germany, so I’ll focus on other priorities :wink:

BTW: just for testing on my machine, another hack like setting the correct port no and user-id/password in the plugin code would be fine, so that you have more time to figure out how a clean solution should look like. And if you would like to hear my humble opinion: if the Gramps preferences have something to define host and port, a clean solution should use that. Otherwise Gramps users will only be confused.

38

Thanks for the link, data, and details. I’m actively working on solutions.

39

Hi Ulrich, Yes it’s almost 9PM here in Egypt where I choose to live (Alexandria is beautiful, and I live right on the Med). But this is prime time for me as I prefer to work at night.

Anyway, the issue with the connection defaulting to localhost:5432 is about WHERE the connection_info.txt file needs to be placed.

For monolithic mode (which you’re using), the connection_info.txt must be in the plugin directory, not the tree directory:

Correct location:
C:\Users\Ulrich Demlehner\AppData\Roaming\gramps\gramps60\plugins\PostgreSQLEnhanced\connection_info.txt

Create or edit this file with your settings:

host = 192.168.178.20
port = 5434
user = your_postgres_user
password = your_password
database_mode = monolithic
shared_database_name = gramps_monolithic

The reason: in monolithic mode, ALL family trees share one database, so they all use the same connection settings from the central plugin config. Per-tree configs are only used in “separate” mode where each tree gets its own database.

After creating/updating this file:

  1. Restart Gramps
  2. Try creating or loading a tree
  3. The connection should now use your configured host and port

If you continue having issues, you can enable debug logging to see exactly what’s happening:

Windows Command Prompt:

set GRAMPS_POSTGRESQL_DEBUG=1
"C:\Program Files\GrampsAIO64-6.0.6\gramps.exe"

Then check the debug log at: C:\Users\Ulrich Demlehner\.gramps\postgresql_enhanced_debug.log

Regarding your broader goal of PostgreSQL enabling third-party software access to your genealogy data - that’s exactly what this addon was designed for. Once you’re connected, you can query the database directly with any PostgreSQL-compatible tool, and the JSONB storage makes the data queryable without deserializing pickle blobs.

Let me know if the corrected config file location resolves the connection issue. I think you’ll be golden now, and I think you’re also going to LOVE how amazingly responsive this is.

Let me know!

Greg

40

It’s there, Greg, but it is ignored.

:+1:

41

Yes, you’re right, and I appreciate your feedback. I’ll do that. I frankly got involved in other things after working on this, so I didn’t work on all the details. The less than ideal methods I use to make this work seemed more improtant issues to me, and my impatience, abrasive nature and lack of time to mess around convincing people a cleaner architecrure was needed prevented me from doing the things that should be done.