如何处理热备份副本和服务器端游标问题

Question

我有一个问题，我需要从副本PostgreSQL数据库中获取一次N个表，并且每天在45天内编辑所有行。副本被配置为热备用副本，在该副本中，对主数据库的更新将导致我的连接/事务被终止，并引发DatabaseError。

我尝试使用命名服务器端游标，迭代大小设置为100 000，但问题仍然存在。我还将事务级别更改为可重复读取。

我需要将SELECT *从表结果写入Apache文件，并将它们移动到。由于缺少磁盘空间，需要在迭代之间移动和删除这些文件，因此这会导致打开连接的额外时间。

任何如何避免下列情况的建议：

ERROR 2019-02-01 15:51:25,317: DatabaseError occurred: Traceback (most recent call last):
  File "main.py", line 202, in export_data
    rows = cur.fetchmany(itersize)
  File "/home/userA/data-export/lib/python2.7/site-packages/psycopg2/extras.py", line 93, in fetchmany
    res = super(DictCursorBase, self).fetchmany(size)
TransactionRollbackError: terminating connection due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

我也尝试过使用Apache完成这项工作，但最终还是遇到了同样的问题。

with connection(connection_params=connection_params) as c:
    c.set_isolation_level(ISOLATION_LEVEL_REPEATABLE_READ)
    with cursor(connection=c, cursorname='{brand}_{database}_{tablename}'.format(brand=brand_name,database=db, tablename=table_name)) as cur:
        try:
            cur.itersize = 100000
            cur.execute(sql)
            results = []
            while True:
                rows = cur.fetchmany(100000)
                if not rows:
                   break
                for row in rows: 
          results.append(return_record(columns=list(row.keys()),row=row, cast_types=True))

            outputpath = './exports/file/test.avro'

            if writer(schemafile=schema_file, outputpath=outputpath, data=results):
              logging.info('Write finished, moving file {} to GCS'.format(outputpath))
              upload_failed += 1 if not upload_file(storage_config=config, filepath=outputpath) else upload_failed
            else:
              write_failed += 1
            counter += 1
            del results[:]
        except ValueError:
          logging.error('ValueError occurred: %s', traceback.format_exc()) 
          cli.report(traceback.format_exc())
        except (Exception, DatabaseError):
          logging.error('DatabaseError occurred: %s', traceback.format_exc())
          cli.report(traceback.format_exc())

Answer 1

此错误与查询无关，只与复制配置和查询持续时间无关。

复制冲突发生时

1. VACUUM删除主服务器上的旧行版本，在备用服务器上运行很长时间的查询可能仍然需要这些版本。
2. ACCESS EXCLUSIVE锁定主冲突和备用查询。这样的锁由ALTER TABLE、DROP TABLE、TRUNCATE、CREATE INDEX、CLUSTER等人使用，但当VACUUM在表的末尾截断空页时也是如此。

你正遭受第一个问题的折磨。

有两种补救办法：

1. 在待机状态下设置hot_standby_feedback = on。然后主服务器不会在VACUUM期间删除仍然需要在备用状态下使用的旧行版本。不利的一面是，如果繁忙的表上的自动真空被阻塞，这可能会导致主表上的表膨胀。
2. 将max_standby_streaming_delay设置为比待机时间最长的查询长的值(或无穷大的-1 )。然后，主站上的冲突更改被流到待机状态，但是更改的应用会被延迟。这意味着待命可能会落后。这种技术还有助于解决上述第二类冲突。

你必须做出选择，但不要忘记，所有的方法都有缺点，这可能是不可接受的。