PostgreSQL 13 + Python 3.7.9 + plpython3u:'psql:服务器意外关闭了连接.'+ '应用程序丢失了数据库连接.' [英] PostgreSQL 13 + Python 3.7.9 + plpython3u: 'psql: server closed the connection unexepectedly.' + 'The application has lost the database connection.'

查看：20
本文介绍了PostgreSQL 13 + Python 3.7.9 + plpython3u:'psql:服务器意外关闭了连接.'+ '应用程序丢失了数据库连接.'的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我已经添加了我能找到的所有详细信息以及所有链接，似乎无法让 plpython3u 在 PostgreSQL 13 的 Windows 上工作.

最好不要通读这个长问题，而是直接跳到答案:当您需要 plpython3u 时不要使用 Windows PostgreSQL.这个问题已经打开很久了，看不到解决方案.

也许 Windows 的更高版本的 PostgreSQL 会解决这个问题，那么请回答.

分拆

这是来自

的衍生产品

因此，需要 Python 3.7.

学分去:

  • 从

    我安装下载 Windows x86-64 基于 Web 的安装程序"；(旁注:您不能更改安装路径，他们似乎强迫您使用它；要快速访问它，请在 Windows 资源管理器中，输入路径 %appdata% --> 转到父文件夹appdata"--> 然后到本地"-->程序"-->python"以快速到达那里)并选中添加 PATH 变量的框.

    您的用户环境变量PATH"中将有一个新条目；你可以检查这个，但你不需要:

    C:\Users\MY_USER\AppData\Local\Programs\Python\Python37\Scripts\

    和

    C:\Users\MY_USER\AppData\Local\Programs\Python\Python37\

    学分去:

    • 另一个检查:

      SELECT * FROM pg_language;

      输出:

       lanname |业主 |拉尼斯普 |lanpltrusted |lanplcallfoid |羊毛脂 |验证器 |乳酸菌------------+---------+---------+--------------+---------------+-----------+--------------+--------内部 |10 |f |f |0 |0 |第2246章| |10 |f |f |0 |0 |第2247章sql |10 |f |吨|0 |0 |第2248章plpgsql |10 |吨|吨|12279 |12280 |12281 |plpython3u |10 |吨|f |40963 |40964 |40965 |(5 行)

      现在可用的扩展(即可以安装的所有可能的扩展)也显示 plpython3u 扩展的 installed_version = 1.0:

      SELECT * FROM pg_available_extensions WHERE name LIKE '%python%' ORDER BY name;

      输出:

      或在 psql 中运行相同的输出:

       名称 |default_version |安装版本|评论------------+--------------------+------------+--------------------------------------------plpython3u |1.0 |1.0 |PL/Python3U 不受信任的过程语言(1 泽勒)

      我们在这里看到的可能是最近没有提供 plpython 扩展的图像的主要原因之一:PL/Python3U 不可信的过程语言.

      另一个显示相同的查询:

      SELECT * FROM pg_pltemplate;

      输出:

       tmplname |tmpltrusted |tmpldbacreate |tmplhandler |tmplinline |tmplvalidator |图书馆 |tmplacl-------------+-------------+---------------+------------+----------------------------+---------------------+-------------------+---------plpgsql |吨|吨|plpgsql_call_handler |plpgsql_inline_handler |plpgsql_validator |$libdir/plpgsql |pltcl |吨|吨|pltcl_call_handler |||$libdir/pltcl |pltclu |f |f |pltclu_call_handler |||$libdir/pltcl |plperl |吨|吨|plperl_call_handler |plperl_inline_handler |plperl_validator |$libdir/plperl |普鲁鲁 |f |f |plperlu_call_handler |plperlu_inline_handler |plperlu_validator |$libdir/plperl |plpythonu |f |f |plpython_call_handler |plpython_inline_handler |plpython_validator |$libdir/plpython2 |plpython2u |f |女 |plpython2_call_handler |plpython2_inline_handler |plpython2_validator |$libdir/plpython2 |plpython3u |f |f |plpython3_call_handler |plpython3_inline_handler |plpython3_validator |$libdir/plpython3 |(8 行)

      对于 plpython 扩展，我们看到 tmpltrusted 列中的 False 和 tmpdbacreate 列中的 False，而三个受信任的扩展plpgsql"、pltcl"和plperl"在同一列中是 True.

      学分去:

      • 当我在此之后运行另一个查询时，它会运行，但在单击继续"之前，我得到:

        应用程序丢失了数据库连接.- 如果连接空闲，则可能已被强行断开.- 应用程序服务器或数据库服务器可能已重新启动.- 用户会话可能已超时.您想继续并建立一个新的会话吗?

        这可能会通过线程解决

        但由于 v3.6.7 版本似乎曾经有效，我认为没有理由投资于此.

        学分去:

        相关:

        使用 EDB 和 Stack Builder 进行 Windows 安装

        EDB 和 Stack Builder 是 PostgreSQL 网站推荐的安装方法.我在 Cannot install plpython for postgres 12(一个只处理根本无法创建扩展，因此无法提供帮助).我安装了 PostgreSQL 10，因为 plpython3u 在 timescaleDB Linux 容器中使用它(参见下面的Docker")，我希望这个较低的 PostgreSQL 版本可以解决它.但是使用这种官方安装方法，使用 EDB，然后使用 Stack Builder 进行附加的pl/python 语言包"，我仍然遇到相同的错误.

        问题

        Python 3.7 的哪个子版本(v3.7.10、v3.7.0 或其他；也许我的 v3.7.9 也已经正确，因为可以用它创建 plpython 扩展)肯定与 PostgreSQL13 一起工作，以及如何如果不只是通过测试，这必须被发现吗?如果选择正确的 Python 子版本不是这里的问题(更有可能)，我还能如何修复使用 LANGUAGE plpython3u 弹出的 步骤 5 错误代码>:

        ERROR: 服务器意外关闭了连接这可能意味着服务器在处理请求之前或处理请求时异常终止.

        (这是psql:服务器意外关闭连接的问题 但不关注这个 Python 扩展问题)

        和

        应用程序丢失了数据库连接.

        (这是 当我尝试使用 plpython3u [WIN10, pgAdmin4 3.5] 创建函数时，PosgreSQL 11 丢失了连接，但这意味着仅从源安装 v3.7.10 以获得最新的子版本，然后我会尝试找出正确的子版本或其他技巧来让它运行，然后再这样做)

        解决方案

        解决方法:plpython3u 确实适用于 Linux！所以在 Linux 上使用它.

        最后一段不是问题的一部分，只是在 Windows plpython3u 安装不起作用时列出了 Linux 上的一些步骤作为替代方法.

        码头工人:

        • 在 Windows 上，安装 Docker Desktop(推荐)或在 WSL2 上使用 Docker.否则，直接在 Linux 上安装 Docker.

        • 可以在 Docker PostgreSQL 9.6 - 安装扩展 plpython3u(与分位数扩展冲突).

        • Docker 有一个问题，你需要额外的技巧才能保存数据库，即使你删除了容器，比如:

          • pg_dump/pb_restore/psql ... >/psql... < 备份到本地 Linux 磁盘，然后从您选择的已安装卷恢复数据库，或者
          • 用于永久保存您的数据库的 Web 服务器.

        • 您可能还会从诸如 Docker 上的 PostgreSQL:如何在 plpython3u 下安装和运行 python 依赖项?，开始，或采取 官方 postgres 镜像使用 docker-compose 指南作为 postgres 基础，并通过 plpython3 进行扩展.

        • 未能使用 plpython3u 的容器中的一个主要技巧是添加符号链接而不是硬编码的安装路径，请参阅 将 plpython3 扩展添加到 Postgres/timescaledb Alpine Docker 映像.这对我有用.使用这个 alpine TimescaleDB Dockerfile，我可以使用 plpython3u！这个容器中这个旧的 Python 3.6 版本的警告:我无法为上面的 kmeans 测试安装所需的包，这些包是 pandas、scikit-learn 和 pickle，无论是 pip 还是 Poetry.似乎这个带有 Python 3.6 的 alpine 容器不支持 Pandas，而 Python 3.7 会:在码头阿尔派.如果无法安装所需的包，则 plpython3u 在此 docker 容器中没有任何价值.这就是为什么您应该使用更新的 PostgreSQL Docker 镜像(或例如 timescaleDB)，这样您就不会遇到 Python 3.6 依赖项的此类遗留问题.

        独立

        当永久保存数据和本地数据变得更加重要时，您也可以尝试在独立 Linux 上进行安装.

        猜测答案

        凭借 WSL、WSL2 和 Docker Desktop，多年来，Linux 已成为 Windows 的朋友.Windows 似乎鼓励这一点.最近在 Linux 上转向 postgreSQL 可能是 plpython3u 对 Windows 支持不佳的原因.同时，您应该将其安装在 Linux 上(独立或在 Docker 容器中).

        但是 Windows 安装可能有什么问题?

        • 如前所述，Windows 没有像 Linux 那样得到 PostgreSQL 的关注.我想在 Windows 上，我必须从源代码安装 PostgreSQL，连同 plpython 扩展及其依赖项，才能使 plpython3u 正常运行.

        • 也许，普通的 Windows 安装程序也不支持 plpython 仅仅是因为一个技术细节:上述问题中的查询显示:PL/Python3U untrusted procedural language.通常的生产系统可能不允许这样做.例如，Webserver 服务 TimescaleForge(timescaleDB，基于 PostgreSQL)回答说，由于安全风险，他们不提供任何 plpython 扩展，即使客户端要求它.他们宁愿为明确的问题提供可信的扩展，而不是可以做任何事情的完整语言，因此存在安全风险.显然，您可以在从源代码构建时使用不受信任的扩展，就像 TimescaleForge 使用自己的扩展一样.

        • 导入 Python 时，可能需要设置 PATH 变量，如 未找到模块"中的答案在 plpython3u 过程中打包.

        • 也许在 Windows 上，安装前必须在某处更改 Python 的默认版本?这只是来自 使用 make 的 Linux 安装的一个非常模糊的猜测，Python 设置在/etc/make.conf

        • 最后:

        当使用与上述链接的 Dockerfile 中使用的安装技巧相同的安装技巧时，plpython3u 似乎有可能在 Windows 上的 PostgreSQL 中运行，将 plpython3 扩展添加到 Postgres/timescaledb Alpine Docker 映像，其中 plpython3u 工作:

        RUN set -ex \&&apk 添加 --no-cache --virtual .plpython3-deps --repository http://nl.alpinelinux.org/alpine/edge/testing \postgresql-plpython3 \&&ln -s/usr/lib/postgresql/plpython3.so/usr/local/lib/postgresql/plpython3.so \&&ln -s/usr/share/postgresql/extension/plpython3u.control/usr/local/share/postgresql/extension/plpython3u.control \&&ln -s/usr/share/postgresql/extension/plpython3u--1.0.sql/usr/local/share/postgresql/extension/plpython3u--1.0.sql \&&ln -s/usr/share/postgresql/extension/plpython3u--unpackaged--1.0.sql/usr/local/share/postgresql/extension/plpython3u--unpackaged--1.0.sql

        因此，必须安装 .plpython3-deps 和 postgresql-plpython3 并且必须添加符号链接.

        也许，这样的 SymLinks 也已经是 Windows 上的主要技巧，尽管我无法在快速测试中使用 SymLinks，请参阅 Windows 上的 PostgreSQL:获取plpython3u"扩展以在 SymLinks 的帮助下运行?.

        I have added all of the details I could find, with all of the links, and there is no way to get plpython3u to work on Windows in PostgreSQL 13, it seems.

        Better do not read through this long question and rather just jump to the answer: not to use Windows PostgreSQL when you need plpython3u. This question has been opened long enough, no solution in sight.

        Perhaps a higher PostgreSQL version for Windows will solve this, then please answer.

        Spin-off

        This is a spin-off from

        Can't "install" plpython3u - postgresql and all of its comments

        and from

        PosgreSQL 11 lost connection when i'm trying to create function with plpython3u [WIN10, pgAdmin4 3.5].

        Steps of errors and solutions up to now

        I have taken these steps which were totally scattered across Stack Overflow:

        Step 0

        If you run a sql that uses the language plpython3u without it being installed, you get

        ERROR: language "plpython3u" does not exist HINT: Use CREATE EXTENSION to load the language into the database.

        SQL state: 42704

        Related:

        Step 1

        At error

        ERROR: could not load library "C:/Program Files (x86)/PostgreSQL/13/lib/plpython3u.dll": The specified module could not be found.

        SQL state: 58P01

        look up C:\Program Files\PostgreSQL\13\doc\installation-notes.html to find the needed Python version to be installed for the installed PostgreSQL version.

        PostgreSQL 13

        Installation Notes

        Welcome to the PostgreSQL 13 Installation Wizard.

        Procedural Languages

        The procedural languages pl/Perl, pl/Python and pl/Tcl are included in this distribution of PostgreSQL. The server has been built using the LanguagePack community distributions of those language interpreters. To use any of the these languages from within PostgreSQL, download and install the appropriate interpreters and ensure they are included in the PATH variable under which the database server will be started. The versions used are shown below - newer minor (bugfix) releases may also work, but have not been tested:

        Perl 5.26
Python 3.7
Tcl 8.6

        Thus, Python 3.7 is needed.

        Credits go to:

        Related:

        Step 2

        Install Python version using the webinstaller of Python Releases for Windows

        The most recent sub-version 3.7.10 does not have any files in the list of stable releases and I am too lazy to install Python from source on Windows. The source code of v3.7.10 is available here Looking for a specific release?, for anyone who wants to try):

        Python 3.7.10 - Feb. 15, 2021

        Note that Python 3.7.10 cannot be used on Windows XP or earlier.

        No files for this release.

        Explanation copied from How to build Python 3.4.6 from source?

        The Python 3.7 branch is in security fixes only mode. This means that only security fixes will be accepted on this branch, no more non-critical bug fixes. New releases on this branch are source-only, no binaries will be provided.

        See the official announcement.

        If you really need a python 3.7.10 binary for windows, you will have to compile it yourself.

        Cannot install plpython for postgres 12 recommends to install a specific version from source:

        you want to use a specific python version > use source and compile it

        Again, since I am lazy, I take the most recent stable release of 3.7, which is sub-version 3.7.9, and this should be no problem following the remark, as you seem to be free to choose the sub-version:

        Try version python-3.4.0.amd64 for windows 64bit or other versions from this Python 3.4.0 downloads Link

        From: could not load library plpython3.dll

        As I said, I am too lazy to take the effort of compiling the binaries of v3.7.10 on Windows when v3.7.9 is available, thus:

        Python 3.7.9 - Aug. 17, 2020

        Note that Python 3.7.9 cannot be used on Windows XP or earlier.

        Download Windows help file
Download Windows x86-64 embeddable zip file
Download Windows x86-64 executable installer
Download Windows x86-64 web-based installer
Download Windows x86 embeddable zip file
Download Windows x86 executable installer
Download Windows x86 web-based installer

        I install "Download Windows x86-64 web-based installer" (side-note: you cannot change the installation path, they seem to force you to use this; to reach it quickly, in Windows Explorer, type in the path %appdata% --> go to parent folder "appdata" --> then to "local" --> "programs" --> "python" to quickly get there) and check the box for adding the PATH variables as well.

        You will have a new entry in your user environment variable "PATH" and you may check this, but you do not need to:

        C:\Users\MY_USER\AppData\Local\Programs\Python\Python37\Scripts\

        and

        C:\Users\MY_USER\AppData\Local\Programs\Python\Python37\

        Credits go to:

        Step 3

        When executing

        CREATE EXTENSION plpython3u;

        in the query tool of PostgreSQL pgAdmin4, I get the error:

        could not load library "C:/Program Files/PostgreSQL/13/lib/plpython3u.dll": The specified module could not be found

        Go to your Python 3.7 installation folder, in my case

        C:\Users\MY_USER\AppData\Local\Programs\Python\Python37

        and copy "python37.dll" from there to

        C:\Windows\System32

        by confirming that you have admin rights.

        Now execute again and it will work:

        CREATE EXTENSION plpython3u;

        Credits go to:

        Related questions:

        Step 4 (optional)

        SELECT * FROM pg_extension;

        Output:

        old    | extname       | extowner | extrelocatable | extversion | extversion | extconfig | extcondition
"13428"| "plpgsql"     | "10"     | "11"           | false      | "1.0"      | [null]    | [null]
"16776"| "plpython3u"  | "10"     | "11"           | false      | "1.0"      | [null]    | [null]

        And another check with:

        SELECT * FROM pg_language;

        Output:

          lanname   | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
------------+----------+---------+--------------+---------------+-----------+--------------+--------
 internal   |       10 | f       | f            |             0 |         0 |         2246 |
 c          |       10 | f       | f            |             0 |         0 |         2247 |
 sql        |       10 | f       | t            |             0 |         0 |         2248 |
 plpgsql    |       10 | t       | t            |         12279 |     12280 |        12281 |
 plpython3u |       10 | t       | f            |         40963 |     40964 |        40965 |
(5 rows)

        Now the available extensions (that is, all possible extensions that can be installed) also show installed_version = 1.0 for the plpython3u extension:

        SELECT * FROM pg_available_extensions WHERE name LIKE '%python%' ORDER BY name;

        Output:

        or the output when running the same in psql:

            name    | default_version | installed_version |                  comment
------------+-----------------+-------------------+-------------------------------------------
 plpython3u | 1.0             | 1.0               | PL/Python3U untrusted procedural language
(1 Zeile)

        We see here probably one of the main reasons why there is no recent image that offers plpython extensions: PL/Python3U untrusted procedural language.

        And another query which shows the same:

        SELECT * FROM pg_pltemplate;

        Output:

          tmplname  | tmpltrusted | tmpldbacreate |      tmplhandler       |        tmplinline        |    tmplvalidator    |    tmpllibrary    | tmplacl
------------+-------------+---------------+------------------------+--------------------------+---------------------+-------------------+---------
 plpgsql    | t           | t             | plpgsql_call_handler   | plpgsql_inline_handler   | plpgsql_validator   | $libdir/plpgsql   |
 pltcl      | t           | t             | pltcl_call_handler     |                          |                     | $libdir/pltcl     |
 pltclu     | f           | f             | pltclu_call_handler    |                          |                     | $libdir/pltcl     |
 plperl     | t           | t             | plperl_call_handler    | plperl_inline_handler    | plperl_validator    | $libdir/plperl    |
 plperlu    | f           | f             | plperlu_call_handler   | plperlu_inline_handler   | plperlu_validator   | $libdir/plperl    |
 plpythonu  | f           | f             | plpython_call_handler  | plpython_inline_handler  | plpython_validator  | $libdir/plpython2 |
 plpython2u | f           | f             | plpython2_call_handler | plpython2_inline_handler | plpython2_validator | $libdir/plpython2 |
 plpython3u | f           | f             | plpython3_call_handler | plpython3_inline_handler | plpython3_validator | $libdir/plpython3 |
(8 rows)

        For the plpython extensions, we see False in the tmpltrusted column and False in the tmpdbacreate column, while the three trusted extensions "plpgsql", "pltcl" and "plperl" are True in the same columns.

        Credits go to:

        Step 5

        Now run a general test query like this:

        CREATE OR REPLACE FUNCTION return_version()
  RETURNS VARCHAR
AS $$
    import sys
    return sys.version
$$ LANGUAGE plpython3u;

        If this worked, you would be able to run the SQL query SELECT return_version() and get

        Output:

        CREATE FUNCTION

        Test:

        postgres=# SELECT return_version();
              return_version
------------------------------------------
 3.8.10 (default, Jun  2 2021, 10:49:15) +
 [GCC 9.4.0]
(1 row)

        Of course, we cannot see this, elsewise the question would be solved. It would be 3.7.9 in this case, I used the Linux installation where plpython3u works, see the Linux hint in the answer.

        Side note: a more complicated test with loaded modules

        Normally, you can ignore this second test and stop at the return_version() function test.

        Of course, if the creating the function return_version() fails, the following will also fail. This second test is just to check whether you can also load modules as soon as plpython3u can be used. You will need to install the needed Python packages which must be compatible with Python 3.7, in this case. It seems that one has to use pip and not conda since Python was meant to be downloaded from the official website. To avoid dependency conflicts, it might be good to use Poetry as a package manager (similar to conda, just for pip).

        When executing this PostgreSQL query of Machine Learning in PostgreSQL Part 1: Kmeans clustering, using the language plpython3u (the needed packages "pandas" and "sklearn" are installed in the base environment of Python3.7, that is, no virtual environment is used to avoid the unsolved Can python venv be used with plpython3u for postgresql?, which is absolutely not what I expected from a standard setter like PostgreSQL):

        CREATE OR replace FUNCTION kmeans(input_table text, columns text[], clus_num int) RETURNS bytea AS
 
$$
 
from pandas import DataFrame
from sklearn.cluster import KMeans
from pickle import dumps
 
all_columns = ",".join(columns)
if all_columns == "":
    all_columns = "*"
 
rv = plpy.execute('SELECT %s FROM %s;' % (all_columns, plpy.quote_ident(input_table)))
 
frame = []
 
for i in rv:
    frame.append(i)
df = DataFrame(frame).astype('float')
kmeans = KMeans(n_clusters=clus_num, random_state=0).fit(df._get_numeric_data())
return dumps(kmeans)
 
$$ LANGUAGE plpython3u;

        End of the sidenote

        Any test query using LANGUAGE plpython3u will cause the error:

        ERROR: server closed the connection unexpectedly

This probably means the server terminated abnormally before or while processing the request.

        and when I run another query after this, it runs, but before clicking "Continue", I get:

        The application has lost the database connection.

- If the connection was idle, it may have been forcibly disconnected.
- The application server or database server may have been restarted.
- The user session may have timed out.

Do you want to continue and establish a new session?

        This might be solved by the thread PosgreSQL 11 lost connection when i'm trying to create function with plpython3u [WIN10, pgAdmin4 3.5]. Such an answer shows that the sub-version of v3.7.9 or v3.7.10 or another could indeed matter! Do I need to install version 3.7.10 from source just to have the most recent version?

        I do not want to take the effort of installing Python 3.7.10 from source just to check this out. Who says that changing from v3.6.5 to v3.6.7 has really solved it in the link above, and that it was not rather something happening just because of a new install?

        I could also try out v3.7.0.

        Python 3.7.0 - June 27, 2018

        Note that Python 3.7.0 cannot be used on Windows XP or earlier.

        Download Windows help file
Download Windows x86-64 embeddable zip file
Download Windows x86-64 executable installer
Download Windows x86-64 web-based installer
Download Windows x86 embeddable zip file
Download Windows x86 executable installer
Download Windows x86 web-based installer

        But since version v3.6.7 once seems to have worked, I do not see a reason why I should invest into this.

        Credits go to:

        Related:

        Windows installation with EDB and Stack Builder

        EDB and Stack Builder is the installation method that is recommended by the PostgreSQL website. I found this at Cannot install plpython for postgres 12 (a thread which just deals with not being able to create the extension at all and therefore cannot help out). I installed PostgreSQL 10 since plpython3u works with that in the timescaleDB Linux container (see "Docker" below) and my hope was that this lower PostgreSQL version would solve it. But with this official installation method, using EDB and then the Stack Builder for the additional "pl/python language pack", I still get the same error.

        Question

        Which sub-version of Python 3.7 (v3.7.10, v3.7.0, or another; perhaps my v3.7.9 is also already right since plpython extension could be created with that) is surely working together with PostgreSQL13, and how would this have to be found out if not just by testing around? And if choosing the right Python sub-version is not the issue here (which is more likely), how else can I fix the Step 5 errors that pop up from using the LANGUAGE plpython3u:

        ERROR: server closed the connection unexpectedly

This probably means the server terminated abnormally before or while processing the request.

        (which is a question at psql: server closed the connection unexepectedly but is not focused on this Python extension problem)

        and

        The application has lost the database connection.

        (which is a question at PosgreSQL 11 lost connection when i'm trying to create function with plpython3u [WIN10, pgAdmin4 3.5] but would mean installing v3.7.10 from source only to have the most recent sub-version, and I try to find out the right sub-version or another trick to get it run before doing so)

        解决方案

        Workaround: plpython3u does work on Linux! So use it on Linux.

        This last paragraph is not part of the question and just lists some steps on Linux as an alternative in the meantime when the Windows plpython3u installation does not work.

        Docker:

        • On Windows, install Docker Desktop (recommended) or use Docker on WSL2. Elsewise, just install Docker directly on Linux.

        • A typical postgres Docker setup that you can easily change to a more recent version can be found at Docker PostgreSQL 9.6 - installing extension plpython3u (clashing with quantile extension).

        • Docker has the problem that you need extra tricks so that the database is saved even if you remove the container, like:

          • pg_dump / pb_restore / psql ... > / psql... < to backup on the local Linux disk and then restore the database from a mounted volume of your choice, or
          • a Web Server to save your db permanently.

        • You might also catch a first glimpse from threads like PostgreSQL on Docker: How to install and run python dependencies under plpython3u?, to start with, or take the official postgres image using docker-compose guide as the postgres base and extend it by plpython3.

        • One main trick in one container that failed to use plpython3u was to add SymLinks instead of hardcoded installation paths, see Add plpython3 Extension to Postgres/timescaledb Alpine Docker Image. This worked for me. Using this alpine TimescaleDB Dockerfile, I could use plpython3u! Caveat of this old Python 3.6 version in this container: I could not install the needed packages for the kmeans test above which are pandas, scikit-learn and pickle, neither with pip nor with Poetry. And it seems that this alpine container with Python 3.6 does not support pandas while Python 3.7 would: Installing pandas in docker Alpine. If the needed packages cannot be installed, plpython3u is of no value in this docker container. That is why you should use a more recent Docker image of PostgreSQL (or for example timescaleDB) so that you do not run into such legacy issues of Python 3.6 dependencies.

        Standalone

        You might also try an installation on standalone Linux when saving the data permanently and locally gets more important.

        Guessed answer

        With WSL, WSL2 and Docker Desktop, since years, Linux has become a friend of Windows. Windows seems to encourage this. The shift towards postgreSQL on Linux is probably the reason for the poor Windows support of plpython3u, recently. In the meantime, you should install it on Linux (standalone or in a Docker container).

        But what might be wrong wrong with the Windows installation?

        • As already said, Windows does not get the PostgreSQL attention that Linux gets. I guess that on Windows, I have to install PostgreSQL from source, together with the plpython extension and its dependencies, to get plpython3u to run properly.

        • Perhaps, the normal Windows installer also does not support plpython just because of a mere technical detail: The query in the question above shows: PL/Python3U untrusted procedural language. It may not be allowed on a usual production system. For example, the Webserver service TimescaleForge (timescaleDB, based on PostgreSQL) have answered that they do not offer any plpython extension because of the security risk, even if the client asks for it. They rather offer trusted extensions for clear problems, not a full language that can do anything and is therefore a security risk. Obviously, you can use untrusted extensions when building from source, as TimescaleForge do with their own extensions.

        • There might be the need to set a PATH variable as in the answer at "Module not found" when importing a Python package within a plpython3u procedure.

        • Perhaps on Windows, the default version of Python must be changed somewhere before installation? This is just a very vague guess from a Linux installation using make, with Python settings in /etc/make.conf

        • And finally:

        It seems possible that plpython3u will run in PostgreSQL on Windows when the same installation tricks are used as have been used in this Dockerfile of the mentioned link above, Add plpython3 Extension to Postgres/timescaledb Alpine Docker Image, where plpython3u works:

        RUN set -ex \
    && apk add --no-cache --virtual .plpython3-deps --repository http://nl.alpinelinux.org/alpine/edge/testing \
    postgresql-plpython3 \
    && ln -s /usr/lib/postgresql/plpython3.so /usr/local/lib/postgresql/plpython3.so \
    && ln -s /usr/share/postgresql/extension/plpython3u.control /usr/local/share/postgresql/extension/plpython3u.control \
    && ln -s /usr/share/postgresql/extension/plpython3u--1.0.sql /usr/local/share/postgresql/extension/plpython3u--1.0.sql \
    && ln -s /usr/share/postgresql/extension/plpython3u--unpackaged--1.0.sql /usr/local/share/postgresql/extension/plpython3u--unpackaged--1.0.sql

        Thus, .plpython3-deps and postgresql-plpython3 must be installed and the SymLinks must be added.

        Perhaps, such SymLinks are already the main trick on Windows as well, though I could not get it to work with SymLinks in a quick test, see PostgreSQL on Windows: get "plpython3u" extension to run with the help of SymLinks?.

        这篇关于PostgreSQL 13 + Python 3.7.9 + plpython3u:'psql:服务器意外关闭了连接.'+ '应用程序丢失了数据库连接.'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆