error connecting to localhost port 5432 (role "ploneorg") / missing postgresql step?

[tkimnguyen]

As per the README, make install on macOS 13.5.2 M1 runs nicely for a while then gives the error:

Unable to connect: connection to server at "localhost" (::1), port 5432 failed: FATAL: role "ploneorg" does not exist

Presumably there is an assumption that postgresql is listening on that port...

Traceback (most recent call last):
  File "/Users/kim/src/plone.org/plone.org/backend/./bin/zconsole", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/Zope2/utilities/zconsole.py", line 50, in main
    runscript(namespace.zopeconf, *namespace.scriptargs)
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/Zope2/utilities/zconsole.py", line 13, in runscript
    make_wsgi_app({}, zopeconf)
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/Zope2/Startup/run.py", line 61, in make_wsgi_app
    starter.prepare()
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/Zope2/Startup/starter.py", line 38, in prepare
    self.startZope()
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/Zope2/Startup/starter.py", line 94, in startZope
    Zope2.startup_wsgi()
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/Zope2/__init__.py", line 36, in startup_wsgi
    _startup()
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/Zope2/App/startup.py", line 100, in startup
    DB = dbtab.getDatabase('/', is_root=1)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/Zope2/Startup/datatypes.py", line 245, in getDatabase
    db = factory.open(name, self.databases)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/Zope2/Startup/datatypes.py", line 141, in open
    DB = self.createDB(database_name, databases)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/Zope2/Startup/datatypes.py", line 138, in createDB
    return ZODBDatabase.open(self, databases)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/ZODB/config.py", line 146, in open
    storage = section.storage.open()
              ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/relstorage/config.py", line 43, in open
    return RelStorage(adapter, name=config.name, options=options)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/relstorage/storage/__init__.py", line 174, in __init__
    self._adapter.schema.prepare()
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/relstorage/adapters/schema.py", line 738, in prepare
    self.connmanager.open_and_call(self._prepare_with_connection)
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/relstorage/adapters/connmanager.py", line 291, in open_and_call
    conn, cursor = self._do_open_for_call(callback)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/relstorage/adapters/connmanager.py", line 365, in _do_open_for_call
    return self.open(**_connection_callback_open_args(self, callback))
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "src/perfmetrics/metric.py", line 72, in perfmetrics._metric._AbstractMetricImpl.__call__
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/relstorage/adapters/postgresql/connmanager.py", line 74, in open
    conn = self._db_connect_with_isolation(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/kim/src/plone.org/plone.org/backend/lib/python3.11/site-packages/relstorage/adapters/postgresql/drivers/psycopg2.py", line 138, in connect_with_isolation
    conn = self.connect(dsn)
           ^^^^^^^^^^^^^^^^^
psycopg2.OperationalError: connection to server at "localhost" (::1), port 5432 failed: FATAL:  role "ploneorg" does not exist

make[3]: *** [create-site] Error 1
make[2]: *** [create-site] Error 2
make[1]: *** [install-backend] Error 2
make: *** [install] Error 2

[tkimnguyen]

After running docker compose up -d and seeing pg running and listening on port 5432:

% docker ps
CONTAINER ID   IMAGE           COMMAND                  CREATED         STATUS         PORTS                    NAMES
17239a0fe140   postgres:14.2   "docker-entrypoint.s…"   3 minutes ago   Up 3 minutes   0.0.0.0:5432->5432/tcp   ploneorg-db-1

make install still fails with the error

psycopg2.OperationalError: connection to server at "localhost" (::1), port 5432 failed: FATAL:  role "ploneorg" does not exist

I'm able to connect to the listener:

% telnet localhost 5432
Trying ::1...
Connected to localhost.
Escape character is '^]'.
?
^]
telnet> quit
Connection closed.

[tkimnguyen]

If I comment out these lines in instance.yaml and rerun make install it gets past this error. This is fine, for testing purposes. After that, make start-backend and make start-frontend work and I'm able to browse to the site.

#    db_storage: relstorage
#    db_relstorage_postgresql_driver: psycopg2
#    db_relstorage_postgresql_dsn: dbname='ploneorg' user='ploneorg' host='localhost' password='ploneorg'
#    db_blobs_mode: cache

[davisagli]

role "ploneorg" does not exist means that the ploneorg user doesn't exist in postgres. It is usually created when a db named ploneorg is created. Probably the instructions are missing the step to create an empty db.

[fredvd]

@tkimnguyen I missed this while being on vacation before PloneConf. Thanks you for addressing this, it is the developer experience workflow in the current set up that is still missing.

plone.org was generated using an early version of cookiecutter-plone-starter, when doing local development it is not clear yet how you can easily get and install he database/data from a project. I've been experimenting with the local DX and setup in some projects where we either added an extra docker compose setup for only the database like here. But I see no problem with provding a setup for good old fashioned zodb data.fs/blobstorage set up as well.

The little 🐘 here is also getting a content-db with some representative content to developers in the first place.

the alternative solution to avoid this completely is add scaffolding for a completely clean set up Plone site with some default content. Adding such a setup will also cost us effort and as soon as you need to debug a current situation with existing site you need the data anyway. So I really want to automat this part of DX.

I have been playing with zodbconvert (part of relstorage) where you can convert between rel-db's, zodb and even sqlite, which I find a really interesting option. I'm out of the experimentation phase, I want to set up a maintainance image that launches on our cluster, connects to the postgesql cluster, and uses zodbconvert to dump a content database from our plone sites to S3 where we can give devs access to download a copy for their local dev setup.

a secondary issue here is that we don't want devs to download a 1.2-1.5Gb tarball in whatever format, because that's the current size of (testing).plone.org.

@davisagli @ericof can you please brainstorm a bit with me as well in the next weeks.

What we could do: have a manual workflow from github that launches a service container that

• cleans the content database of for example testing.plone.org with a script to remove most content
• does a zeopack.
• run zodbconvert with relstorage postgresql db from testing.plone.org as input, and zodb or sqlite (😬) as output to a mounted linode S3 storage.
• then drops the pg database from testing.plone.org and does a pg_dump/restore from production plone.org to testing.plone.org

I think this might work, our problem was with postgresql not recovering on filesystem disk space with a client, but a sql export after zeopack should be reduced to a few 100 Mb's depending on how rigorous we remove content. And we need a customised cleaning script per site that is not/can never triggered on production by accident 😳

And yes, we wipe the testing environment database,but tesing sites should be ephemeral anyway and not contain unique content. We keep it manual so that we can check first with marketing team or website team having activity on a testing site.

The parts that tickle my brain the most is the orchestration between the service image and also scaling the tesing environment backends on docker swarm, you really want to disable the zope process before pg_dump/pg_restoring a relstorage db.

/ braindump end

[tkimnguyen]

I think it's sufficient to add the step that creates the database and sets up the ploneorg role :) I will see if I can figure it out.

[tkimnguyen]

thanks David :)