在Python中跨进程共享与postgres db的连接 [英] Share connection to postgres db across processes in Python

问题描述

我有一个Python脚本作为守护程序运行。在启动时，它产生5个进程，每个进程都连接到Postgres数据库。现在，为了减少数据库连接的数量（最终将变得非常大），我试图找到一种在多个进程之间共享单个连接的方法。为此，我正在查看 multiprocessing.sharedctypes.Value API。但是，我不确定如何在整个进程中使用此API传递 psycopg2.connection 对象。谁能告诉我怎么做？

I have a Python script running as a daemon. At startup, it spawns 5 processes, each of which connects to a Postgres database. Now, in order to reduce the number of DB connections (which will eventually become really large), I am trying to find a way of sharing a single connection across multiple processes. And for this purpose I am looking at the multiprocessing.sharedctypes.Value API. However, I am not sure how I can pass a psycopg2.connection object using this API across processes. Can anyone tell me how it might be done?

为了解决这个问题，我也愿意接受其他想法。

I'm also open to other ideas in order to solve this issue.

我之所以不考虑将连接作为构造函数的一部分传递给5个进程的原因是互斥处理。如果使用这种方法，我不确定如何防止多个进程访问连接。有人可以告诉我这是正确的做法吗？

The reason why I did not consider passing the connection as part of the constructor to the 5 processes is mutual exclusion handling. I am not sure how I can prevent more than one process from accessing the connection if I follow this approach. Can someone tell me if this is the right thing to do?

推荐答案

您无法在多个进程之间疯狂共享数据库连接，例如那。您可以 sort-of 在个线程之间共享一个连接，但前提是您必须确保一次仅一个线程使用该连接。在进程之间这是行不通的，因为在客户端的地址空间中存储了连接的客户端状态。

You can't sanely share a DB connection across processes like that. You can sort-of share a connection between threads, but only if you make sure the connection is only used by one thread at a time. That won't work between processes because there's client-side state for the connection stored in the client's address space.

如果您需要大量并发工作器，但是它们为了避免一直使用数据库，您应该有一组数据库工作进程进程来处理所有数据库访问并与其他工作进程交换数据。每个数据库工作进程都有一个数据库连接。其他进程仅通过数据库工作人员与数据库对话。

If you need large numbers of concurrent workers, but they're not using the DB all the time, you should have a group of database worker processes that handle all database access and exchange data with your other worker processes. Each database worker process has a DB connection. The other processes only talk to the database via your database workers.

Python的多处理队列，fifos等为此提供了适当的消息传递功能。

Python's multiprocessing queues, fifos, etc offer appropriate messaging features for that.

这篇关于在Python中跨进程共享与postgres db的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！