主页 / user-25209

Sébastien Clément's questions

Martin Hope
Sébastien Clément
Asked: 2023-06-03 05:53:23 +0800 CST
  • 5

我创建了一个触发器函数来设置远程数据库表中序列的值，使用dblink和setval。

CREATE OR REPLACE FUNCTION my_function () RETURNS TRIGGER AS $$
DECLARE
  ...

BEGIN
    PERFORM dblink('dbname=remote_db user=user3 password=PW12345',  
    'SELECT setval(''my_sequence'',123);')
    ;
    
    RETURN NULL;

END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
;

但是，执行函数时出现以下错误消息：

ERROR:  function returning record called in context that cannot accept type record

最好的方法是什么？

postgresql
Martin Hope
Sébastien Clément
Asked: 2022-05-11 07:35:25 +0800 CST
  • 2

我们PostgreSQL 11在CentOS 7服务器下运行。最近，我们在日志中有几个警告导致 PostgreSQL 服务器崩溃：

2022-05-09 23:26:29 EDT pg_restore      db123      132.132.132.32  postgres        ERROR:  could not extend file "base/71592268/71621568": No space left on device
2022-05-09 23:26:29 EDT pg_restore      db123      132.132.132.32  postgres        HINT:  Check free disk space.
(...)
2022-05-09 23:26:33 EDT                                 DETAIL:  Could not open file "pg_notify/0000": No space left on device.
2022-05-09 23:26:34 EDT                                 LOG:  database system is shut down

目录下有几个数据库/var/lib/pgsql/11/data/base/：

# du -h /var/lib/pgsql/11/data/base/
7.7M    /var/lib/pgsql/11/data/base/1
7.7M    /var/lib/pgsql/11/data/base/13877
8.1M    /var/lib/pgsql/11/data/base/13878
0       /var/lib/pgsql/11/data/base/pgsql_tmp
61M     /var/lib/pgsql/11/data/base/852671
166M    /var/lib/pgsql/11/data/base/852672
699M    /var/lib/pgsql/11/data/base/852673
83M     /var/lib/pgsql/11/data/base/852674
14M     /var/lib/pgsql/11/data/base/852675
476M    /var/lib/pgsql/11/data/base/852676
131M    /var/lib/pgsql/11/data/base/852677
14G     /var/lib/pgsql/11/data/base/1102146
9.9M    /var/lib/pgsql/11/data/base/30205118
9.4M    /var/lib/pgsql/11/data/base/34117470
274M    /var/lib/pgsql/11/data/base/71255881
9.2G    /var/lib/pgsql/11/data/base/71501647
9.2G    /var/lib/pgsql/11/data/base/71592267
9.8M    /var/lib/pgsql/11/data/base/60242430
9.2G    /var/lib/pgsql/11/data/base/71423479
8.1G    /var/lib/pgsql/11/data/base/71423480
6.5G    /var/lib/pgsql/11/data/base/71592268

问题：

  1. 在不访问 PostgreSQL 服务器的情况下，如何知道哪个基目录对应哪个数据库名称？
  2. 一旦我们知道哪个目录分配给哪个数据库，删除它是否安全？
postgresql disk-space
Martin Hope
Sébastien Clément
Asked: 2021-10-13 09:02:41 +0800 CST
  • 3

我正在尝试更改我已经是所有者的表的所有权。我也是数据库的所有者，并且拥有 PUBLIC 模式的 CREATE 和 USAGE 权限。我收到以下错误消息：

ts_d=> ALTER TABLE table1 OWNER TO user2;
ERROR:  permission denied for schema public

如何在不以超级用户身份登录的情况下更改所有权？

postgresql permissions
Martin Hope
Sébastien Clément
Asked: 2018-02-09 08:08:08 +0800 CST
  • 0

实际操作系统和 PostgreSQL 版本：

CentOS 7.3，PostgreSQL 10.1 更新到 10.2。

脚步：

停止服务：

/bin/systemctl stop postgresql-10.service

检查可用的软件包更新：

yum check-update
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: centos.mirror.ca.planethoster.net
 * extras: muug.ca
 * updates: muug.ca

libgudev1.x86_64                                                                            219-42.el7_4.7                                                                        updates
pgdg-centos10.noarch                                                                        10-2                                                                                  pgdg10-updates-testing
systemd.x86_64                                                                              219-42.el7_4.7                                                                        updates
systemd-libs.x86_64                                                                         219-42.el7_4.7                                                                        updates
systemd-sysv.x86_64                                                                         219-42.el7_4.7                                                                        updates

更新 PostgreSQL：

yum update pgdg-centos10.noarch
    Loaded plugins: fastestmirror
    Loading mirror speeds from cached hostfile
     * base: mirror2.evolution-host.com
     * extras: muug.ca
     * updates: muug.ca
    Resolving Dependencies
    --> Running transaction check
    ---> Package pgdg-centos10.noarch 0:10-1 will be updated
    ---> Package pgdg-centos10.noarch 0:10-2 will be an update
    --> Finished Dependency Resolution

    Dependencies Resolved

    ========================================================================================================================================================================================================
     Package                                           Arch                                       Version                                  Repository                                                  Size
    ========================================================================================================================================================================================================
    Updating:
     pgdg-centos10                                     noarch                                     10-2                                     pgdg10-updates-testing                                     4.6 k

    Transaction Summary
    ========================================================================================================================================================================================================
    Upgrade  1 Package

    Total download size: 4.6 k
    Is this ok [y/d/N]: y
    Downloading packages:
    Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
    pgdg-centos10-10-2.noarch.rpm                                                                                                                                                    | 4.6 kB  00:00:03
    Running transaction check
    Running transaction test
    Transaction test succeeded
    Running transaction
      Updating   : pgdg-centos10-10-2.noarch                                                                                                                                                            1/2
      Cleanup    : pgdg-centos10-10-1.noarch                                                                                                                                                            2/2
      Verifying  : pgdg-centos10-10-2.noarch                                                                                                                                                            1/2
      Verifying  : pgdg-centos10-10-1.noarch                                                                                                                                                            2/2

    Updated:
      pgdg-centos10.noarch 0:10-2

    Complete!

重启服务：

/bin/systemctl start postgresql-10.service

检查版本：

psql -U myuser -d mydb -c "SELECT version()"
Password for user myuser:
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

仍为 10.1。有什么解释吗？

postgresql update
Martin Hope
Sébastien Clément
Asked: 2018-02-03 11:43:27 +0800 CST
  • 25

我在 CentOS 7.3 环境下安装了 PostgreSQL 10.1。

服务已启动（postmaster.pid文件存在于 下/var/lib/pgsql/10/data），但我需要重新加载配置或在更改pg_hba.conf.

但是，尝试不同的命令，我得到以下信息：

pg_ctl reload -D /var/lib/pgsql/10/data
bash: pg_ctl: command not found

service postgresql reload
Redirecting to /bin/systemctl reload postgresql.service
Failed to reload postgresql.service: Unit not found.
postgresql centos-7
Martin Hope
Sébastien Clément
Asked: 2015-10-09 13:08:53 +0800 CST
  • 18

在 PostgreSQL 9.2.2（Windows 32 位）下，我有一个pg_restore命令会系统地导致有关检查点频率的日志警告，例如：

LOG:  checkpoints are occurring too frequently (17 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".

该数据库大小约为 3.3 Gb，包含 112 个表/160 个视图，并在大约 14 分钟内恢复。

期间发生这种情况正常pg_restore吗？

postgresql restore
Martin Hope
Sébastien Clément
Asked: 2014-07-11 07:55:52 +0800 CST
  • 7

我有一个 pl/pgsql 函数（见下文），它列出了一些字段并使用动态构造的 UPDATE 命令清除它们的内容。

当我设置时log_statement = 'mod'，我在执行该函数时在日志上看不到任何内容SELECT fnct_clear_temp_fields();。当我设置log_statement = 'all'并执行我可以SELECT fnct_clear_temp_fields();在日志中看到的功能时，而不是底层的 UPDATE 命令。

有没有办法让 UPDATE 命令也出现在日志中？

有关信息，这里是函数：

CREATE OR REPLACE FUNCTION fnct_clear_temp_fields() RETURNS VOID AS $$
DECLARE
    --Put into a cursor a view dynamically listing all user-defined fields beginning with 'temp_'
    dataset_1 CURSOR FOR 
        SELECT 
            column_name,
            table_name
        FROM information_schema.tables 
        NATURAL JOIN information_schema.columns 
        WHERE 
            table_schema='public'
            AND table_type='BASE TABLE'
            AND column_name ~ '^temp_'
        ORDER BY table_name,column_name;

    --Record variable to go through each line of the view above
    dataset_1_row RECORD;

BEGIN
    OPEN dataset_1; --Open the cursor
    FETCH dataset_1 INTO dataset_1_row; --first row of the view

    WHILE FOUND LOOP
        RAISE NOTICE 'Table: %, Column: %',  dataset_1_row.table_name,dataset_1_row.column_name;

        --Set to NULL the contents of the current 'temp_' column
        EXECUTE 'UPDATE '||dataset_1_row.table_name||' SET '||dataset_1_row.column_name||'=NULL WHERE '||dataset_1_row.column_name||' IS NOT NULL';

        FETCH dataset_1 INTO dataset_1_row; --next row please.
    END LOOP; --while end

    CLOSE dataset_1;

    RETURN;
END;
$$ LANGUAGE plpgsql;
postgresql functions