Connections to PostgreSQL "hot standby" clusters

[brahici]

We are changing our ZODB architecture from multiple ZEOs (synchronized with ZRS) to several RelStorage/PostgreSQL instances.

Currently, we run a main R/W ZEO for the web back-end, and 4 R/O ZEOs to serve data to our web front-ends (actually 5 R/O, the fifth is used for backup purposes).

Our plan is to use a primary Postgresql 11 cluster, and replicate it to one or more hot standby clusters.

But we constantly fail to define a connection on our replica.

Here's a script summarizing our tests (considering localhost:5432 to be the primary server and localhost:5434 a hot standby).

# coding: utf-8

import traceback
import ZODB.config

conf1 = """
%import relstorage
<zodb main>
    <relstorage>
        keep-history false
        <postgresql>
            dsn host=localhost dbname=rspg port=5432
        </postgresql>
    </relstorage>
</zodb>
"""

conf2 = """
%import relstorage
<zodb main>
    <relstorage>
        keep-history false
        read-only true
        <postgresql>
            dsn host=localhost dbname=rspg port=5434
        </postgresql>
    </relstorage>
</zodb>
"""

# file replica.conf contains a unique row : localhost:5434
conf3 = """
%import relstorage
<zodb main>
    <relstorage>
        keep-history false
        read-only true
        ro-replica-conf replica.conf
        <postgresql>
            dsn host=localhost dbname=rspg port=5432
        </postgresql>
    </relstorage>
</zodb>
"""

def test_conf(conf):
    return ZODB.config.databaseFromString(conf)

for idx, conf in enumerate([conf1, conf2, conf3], start=1):
    print(f"configuration {idx}")
    try:
        db = test_conf(conf)
        print(f"{db}", len(db.storage))
    except Exception as error:
        traceback.print_exc()
    print("=" * 80)

The resulting output is:

configuration 1
<ZODB.DB.DB object at 0x7f956e4c7730> 1
================================================================================
configuration 2
Traceback (most recent call last):
  File "test.py", line 53, in <module>
    db = test_conf(conf)
  File "test.py", line 47, in test_conf
    return ZODB.config.databaseFromString(conf)
  File "[...]/lib/python3.8/site-packages/ZODB/config.py", line 57, in databaseFromString
    return databaseFromFile(StringIO(s))
  File "[...]/lib/python3.8/site-packages/ZODB/config.py", line 65, in databaseFromFile
    return databaseFromConfig(config.database)
  File "[...]/lib/python3.8/site-packages/ZODB/config.py", line 79, in databaseFromConfig
    db = factory.open(databases)
  File "[...]/lib/python3.8/site-packages/ZODB/config.py", line 131, in open
    storage = section.storage.open()
  File "[...]/lib/python3.8/site-packages/relstorage/config.py", line 38, in open
    return RelStorage(adapter, name=config.name, options=options)
  File "[...]/lib/python3.8/site-packages/relstorage/storage/__init__.py", line 177, in __init__
    self._adapter.schema.prepare()
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/schema.py", line 699, in prepare
    self.connmanager.open_and_call(self._prepare_with_connection)
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/connmanager.py", line 242, in open_and_call
    conn, cursor = self._do_open_for_call(callback)
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/connmanager.py", line 316, in _do_open_for_call
    return self.open(
  File "src/perfmetrics/metric.py", line 72, in perfmetrics._metric._AbstractMetricImpl.__call__
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/postgresql/connmanager.py", line 73, in open
    conn = self._db_connect_with_isolation(
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/postgresql/drivers/psycopg2.py", line 92, in connect_with_isolation
    cursor.execute('SET SESSION application_name = %s', (application_name,))
psycopg2.errors.FeatureNotSupported: cannot set transaction read-write mode during recovery

================================================================================
configuration 3
Traceback (most recent call last):
  File "test.py", line 53, in <module>
    db = test_conf(conf)
  File "test.py", line 47, in test_conf
    return ZODB.config.databaseFromString(conf)
  File "[...]/lib/python3.8/site-packages/ZODB/config.py", line 57, in databaseFromString
    return databaseFromFile(StringIO(s))
  File "[...]/lib/python3.8/site-packages/ZODB/config.py", line 65, in databaseFromFile
    return databaseFromConfig(config.database)
  File "[...]/lib/python3.8/site-packages/ZODB/config.py", line 79, in databaseFromConfig
    db = factory.open(databases)
  File "[...]/lib/python3.8/site-packages/ZODB/config.py", line 131, in open
    storage = section.storage.open()
  File "[...]/lib/python3.8/site-packages/relstorage/config.py", line 38, in open
    return RelStorage(adapter, name=config.name, options=options)
  File "[...]/lib/python3.8/site-packages/relstorage/storage/__init__.py", line 209, in __init__
    with self._load_connection.isolated_connection() as cur:
  File "/home/brice/applications/python.local/lib/python3.8/contextlib.py", line 113, in __enter__
    return next(self.gen)
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/connections.py", line 244, in isolated_connection
    conn, cursor = self._new_connection()
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/connmanager.py", line 271, in open_for_load
    conn, cursor = self._do_open_for_load()
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/postgresql/connmanager.py", line 116, in _do_open_for_load
    return self.open(
  File "src/perfmetrics/metric.py", line 72, in perfmetrics._metric._AbstractMetricImpl.__call__
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/postgresql/connmanager.py", line 73, in open
    conn = self._db_connect_with_isolation(
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/postgresql/drivers/psycopg2.py", line 92, in connect_with_isolation
    cursor.execute('SET SESSION application_name = %s', (application_name,))
psycopg2.errors.FeatureNotSupported: cannot use serializable mode in a hot standby
HINT:  You can use REPEATABLE READ instead.

================================================================================

Failure of "conf2" was a bit of a deception; failure of "conf3" as well.

Actually, I succeeded with "conf3", but it required a small patch in RelStorage. I am wondering whether this patch should be accepted, considering your comments in Psycopg2ConnectionManager._do_open_for_load.

diff --git a/src/relstorage/adapters/postgresql/connmanager.py b/src/relstorage/adapters/postgresql/connmanager.py
index 1e3dfa0..872cab0 100644
--- a/src/relstorage/adapters/postgresql/connmanager.py
+++ b/src/relstorage/adapters/postgresql/connmanager.py
@@ -114,7 +114,7 @@ class Psycopg2ConnectionManager(AbstractConnectionManager):
         # faster, as the *only* serializable transactions we have
         # should be READ ONLY.
         return self.open(
-            self.isolation_load,
+            self.isolation_repeatable_read,
             read_only=True,
             deferrable=False,
             replica_selector=self.ro_replica_selector,

We'd rather have actual R/O connections (like in "conf2"), because with "conf3", along with connections open on replica, there are useless (considering our use case) connections on primary cluster.

[jamadden]

For "conf2", you will need to set the option create-schema to false in order to prevent the attempt to open the write connection at startup.

After that you'd run into the same issue as with "conf3". The use of SERIALIZABLE dates to the origins of RelStorage, and I think that's because prior to 9.1 PostgreSQL interpreted things differently than it does now, with what is now REPEATABLE READ being what they then called SERIALIZABLE. I've tested with REPEATABLE READ and confirmed that it's sufficient; I had intended to make that an actual change but I guess that slipped my mind; I'll get that in.

[brahici]

For "conf2", you will need to set the option create-schema to false in order to prevent the attempt to open the write connection at startup.

I was pretty sure I gave a try with this option set to false, without any success.

So I tried again, and unfortunately, it's failing. Here's the test script.

# coding: utf-8

import traceback
import ZODB.config

conf = """
%import relstorage
<zodb main>
    <relstorage>
        keep-history false
        read-only true
        create-schema false
        <postgresql>
            dsn host=localhost dbname=rspg port=5434
        </postgresql>
    </relstorage>
</zodb>
"""

try:
    db = ZODB.config.databaseFromString(conf)
    print(f"{db}", len(db.storage))
except Exception as error:
    traceback.print_exc()

And here's the traceback (slightly different than previous one).

Traceback (most recent call last):
  File "test2.py", line 21, in <module>
    db = ZODB.config.databaseFromString(conf)
  File "[...]/lib/python3.8/site-packages/ZODB/config.py", line 57, in databaseFromString
    return databaseFromFile(StringIO(s))
  File "[...]/lib/python3.8/site-packages/ZODB/config.py", line 65, in databaseFromFile
    return databaseFromConfig(config.database)
  File "[...]/lib/python3.8/site-packages/ZODB/config.py", line 79, in databaseFromConfig
    db = factory.open(databases)
  File "[...]/lib/python3.8/site-packages/ZODB/config.py", line 131, in open
    storage = section.storage.open()
  File "[...]/lib/python3.8/site-packages/relstorage/config.py", line 38, in open
    return RelStorage(adapter, name=config.name, options=options)
  File "[...]/lib/python3.8/site-packages/relstorage/storage/__init__.py", line 180, in __init__
    self._adapter.schema.verify()
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/schema.py", line 702, in verify
    self.connmanager.open_and_call(self._verify)
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/connmanager.py", line 242, in open_and_call
    conn, cursor = self._do_open_for_call(callback)
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/connmanager.py", line 316, in _do_open_for_call
    return self.open(
  File "src/perfmetrics/metric.py", line 72, in perfmetrics._metric._AbstractMetricImpl.__call__
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/postgresql/connmanager.py", line 73, in open
    conn = self._db_connect_with_isolation(
  File "[...]/lib/python3.8/site-packages/relstorage/adapters/postgresql/drivers/psycopg2.py", line 92, in connect_with_isolation
    cursor.execute('SET SESSION application_name = %s', (application_name,))
psycopg2.errors.FeatureNotSupported: cannot set transaction read-write mode during recovery

The error still occurs in method connect_with_isolation, but in this case, is triggered from self._adapter.schema.verify() ; in my previous test, it was from self._adapter.schema.prepare().

Am I missing something ? Is there another option to set ?

As errors occurred in adapters/postgresql/drivers/psycopg2.py, I gave a try with another driver, pg8000, but it also failed.

%import relstorage
<zodb main>
    <relstorage>
        keep-history false
        read-only true
        create-schema false
        <postgresql>
            driver pg8000
            dsn host=localhost dbname=rspg port=5434 user=brice
        </postgresql>
    </relstorage>
</zodb>

pg8000.core.ProgrammingError: {'S': 'ERROR', 'V': 'ERROR', 'C': '0A000', 'M': 'cannot use serializable mode in a hot standby', 'H': 'You can use REPEATABLE READ instead.', 'F': 'guc.c', 'L': '10195', 'R': 'call_string_check_hook'}

[jamadden]

Yup, there was more to do. With #379 I was able to connect to the hot standby and run read transactions against it. This isn't something that's tested by CI so it's bound to be somewhat fragile.

I've attached a wheel of that PR if you'd like to test it (you'll need to strip the final '.zip' suffix from the file to get pip to install it, I think).

RelStorage-3.0b4.dev0-cp38-cp38-manylinux1_x86_64.whl.zip

[brahici]

Thank you very much. I think I can test it by tomorrow and let you know.

[brahici]

The test is successful, I can connect hot standbys and perform requests. Thank you again. Have you plans for a release of 3.0b4 ?

[jamadden]

3.0rc1 is on PyPI.

[brahici]

rc1 \o/ Even better =) Many thanks!