Luciano Andress Martini's questions

使用 rsync 参数 -u 同步 PostgreSQL 复制是否安全？

例如，如果我的复制由于连接问题（例如 WAL 存档已被删除）而丢失了很长时间，我总是需要将我数据库的所有文件再次从主服务器同步到副本。

所以我正在使用类似（或几乎类似）的东西（在奴隶内部）：

systemctl stop postgresql@11-slave 
psql -h 10.0.0.MASTER -Atc "select pg_start_backup('clone', true);"
rsync -avz [email protected]:/var/lib/postgresql/11/main /var/lib/postgresql/11/  
systemctl start postgresql@11-slave 
psql -h 10.0.0.MASTER -Atc "select pg_stop_backup('clone', true);"

上面的解决方案工作得很好，并且经过多次测试，没有任何数据丢失。

但我很想尝试使用更快（危险？）的方法来恢复复制，例如：

systemctl stop postgresql@11-slave 
psql -h 10.0.0.MASTER -Atc "select pg_start_backup('clone', true);"
rsync -uavz [email protected]:/var/lib/postgresql/11/main/ /var/lib/postgresql/11/  
systemctl start postgresql@11-slave 
psql -h 10.0.0.MASTER -Atc "select pg_stop_backup('clone', true);"

在 rsync（更新）上使用参数 -u 而不是重新复制所有文件是否安全，或者当我的复制发生问题时我应该继续复制所有文件吗？

我有一个巨大的表，我无法删除行，只能更新存储大量 base64 数据的列，我应该将其更新为 null 以尝试释放空间。

所以我编写了一个脚本，它能够在 base64 中设置所有图像，期望在 Vacuum 之后释放空间！

图像设置为 null，执行真空，但表仍然具有完全相同的大小，我很确定必须立即释放空间，所以我做错了什么？

真空完全能够从我更新为空的巨大 varchar 数据中释放空间吗？（因为如果我要这样做，我需要锁定桌子，我需要确定）

转储大小减少了 10 倍，因此我预计数据库大小会有类似的行为。

一个 PGDATA 文件夹是否可以有多个 PostgreSQL 实例，例如在存储中共享？

如果不是，是否有某种 RDBMS 能够做这样的事情？

如果我使用了正确的术语，请纠正我：“全局影响”我的意思是，如果我增加序列值，所有其他正在运行的事务都会增加它而无需提交....

我知道序列有这种行为，但我怀疑是否有一个包含一些其他角色的列表，这些角色在全球范围内受到影响，如果我在事务中更新它？

例如，如果我设置alter table mytable enable trigger all；这只会影响本地事务或其他事务中的触发行为，请注意，这只是一个示例，我正在考虑很多其他需要仔细考虑的事情。

我正在通过发出以下命令进行 postgresql 复制：

在主人身上：

>select pg_start_backup('clone', true);
>\q 
#tar -cz main -f main.tar.gz

（说明：main是我系统上的数据目录/var/lib/postgresql/9.6/main）

然后我将 main.tar.gz 传输到从机....

在奴隶上：

--Uncompressing main.tar.gz.... 
#tar -zxf main.tar.gz 
--Copying previously created recovery.conf 
#cp recovery.conf main/
--And then just starts the slave
#service postgresql start

在主人身上：

--And stoping backup mode on the master:
>select pg_stop_backup(); 

我得到的是复制工作正常，而且确实很好用。

但是我在阅读官方文档后怀疑我应该在额外的步骤中复制 pg_xlog ... https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

但我从来没有这样做过，也从来没有遇到过问题——做了很多测试，比如计算每个表的行数，包括主从等等，但最坏的情况会发生吗？

增加 default_statistics_target 值可以使您的数据库更快，特别是在分析之后....

阅读这篇文章，我看到https://discuss.pivotal.io/hc/en-us/articles/201581033-default-statistics-target-Explained

(...)简而言之，此参数控制收集统计信息的方式，值 1 是估计/准确度最低的统计信息，值 1000 是最准确的统计信息，显然会花费时间/资源（CPU、内存等）/空间。通常默认值足以获得准确的计划，但如果您有一个复杂的数据分布/或查询中经常引用一个列，那么设置一个更高的值可能有助于获得更好的表统计信息，因此优化器执行的更好计划。

这是一个很好的解释，但例如，如果我设置 default_statistics_target= 1000 1000 的真正含义是什么？正在生成 1000 KB 的统计信息？或者可能是分析的 1000 行表？也许是 1000 列？或者每次分析可能需要 1000 秒...

所以我的问题是这个数字是如何真正影响分析或查询规划器的？显然，我知道 default_statistics_target = 1000 将获得比 100 更多的时间来运行分析，并且 1000 将产生更好的统计数据......

您好，我可以使用 postgresql 开始流式复制，通过执行以下命令复制数据库文件：

select pg_start_backup('clone', true); 

没错，但问题是我需要将 /var/lib/postgresql/data 中的所有数据库文件复制到一台服务器到另一台服务器。例如，由于索引，这些文件真的很大。

我想知道是否可以使用简单的转储并在​​从站上恢复它来做同样的事情，什么是更小更快的网络传输，它会起作用吗？我应该做什么工作？

我在 PL/pgSQL 中有两个函数：

函数 1 调用函数 2，函数 2 需要处理在函数 1 上创建的临时表，换句话说，这个临时表需要在函数 1 上下文中是全局的（其中包含函数 2）。

FUNCTION1 
   | CREATE TEMPORARY TABLE
   | DO SOME SPECIFIC STUFF...
   | FUNCTION2
         | DO SOME GENERIC STUFF
         | INSERT SOME DATA INTO THE TEMPORARY TABLE  
         | (Uhh!! Ohh! it hurts, table not visible)
   | RAISED ON ERROR

有没有办法将在函数 1 上创建的临时表传递给这个子函数？

我需要它，因为我正在使用一个非常大的函数来处理临时表中的大量数据，然后再插入所有大数据，但是当我尝试变成更模块化的形式时，子函数就看不到临时表）。

它现在正在工作（在一个大函数中），但我正在执行 CTRL+C CTRL+V 来创建新的类似函数，因为可以在其中重复很多代码。如果我可以使用子函数，这个问题就会消失，因为我可以多次使用这个代码。

在对并行查询进行了一些研究后，我们决定迁移到 9.6，因此我们使用 PostgreSQL 9.1 和 9.6 分别启动了具有完全相同硬件、内核和其他硬件的两台相同的机器。

9.1 和 9.6 版本具有相同的配置，除了在 PostgreSQL 9.6 中具有 5 个并行工作器的并行配置。

结果并不像我们预期的那样。一开始，对于像这样的简单查询，它似乎比 9.1 更好select * from something where big_string_column ilike '%test%';

但是当查询开始变得更复杂时，PostgreSQL 9.1 开始获胜，所以我正在寻求帮助。我们试图禁用并行查询，但没有帮助。我无法提供有关数据库结构的更多详细信息（非常抱歉），但如果有人经历过类似的事情并解决了它，它将对我有所帮助。

我在两者上执行的（混淆的）查询：

SELECT case when lbipro = 1 then 'PEM'
            when lbipro = 2 then 'CRE'
            when lbipro = 3 then 'COM'
            when lbipro = 4 then 'REC'
            when lbipro = 6 then 'DES'
            END pnom, 
            lbipro, 
            sum(lbival) as lim,
            sum(coalesce(CASE WHEN lbisal > 0 THEN lbisal END,0)) dsp,
            sum(coalesce(CASE WHEN lbisal <= 0 THEN lbisal END,0)) exc,
            sum(coalesce(obiave,0)) as tom
FROM "lbi"
LEFT JOIN (SELECT sum(obiave) as obiave, obimat, obipro, obiscr
           FROM   obi
           WHERE  obicop = '022017'
           GROUP BY obimat, obipro, obiscr) as oo 
ON        oo.obimat = lbimat 
and       oo.obipro = lbipro
WHERE     lbicon = '5' 
AND       lbicop = '022017'
GROUP BY  "lbipro"
ORDER BY  "lbipro" ASC;

为 9.1 生成的计划（解释分析）：

 Sort  (cost=231411.25..231411.26 rows=1 width=46) (actual time=2389.223..2389.223 rows=3 loops=1)
   Sort Key: lbi.lbipro
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=231411.22..231411.24 rows=1 width=46) (actual time=2389.209..2389.214 rows=3 loops=1)
         ->  Hash Right Join  (cost=193708.93..223165.41 rows=471189 width=46) (actual time=1698.385..1950.493 rows=458094 lo$
               Hash Cond: (((obi.obimat)::text = (lbi.lbimat)::text) AND (obi.obipro = lbi.lbipro))
               ->  HashAggregate  (cost=66042.93..66261.10 rows=21817 width=55) (actual time=470.412..536.613 rows=125480 loo$
                     ->  Seq Scan on obi  (cost=0.00..63861.29 rows=218164 width=55) (actual time=0.137..352.910 rows=215512 $
                           Filter: ((obicop)::text = '022017'::text)
               ->  Hash  (cost=120598.17..120598.17 rows=471189 width=32) (actual time=1227.654..1227.654 rows=458094 loops=1)
                     Buckets: 65536  Batches: 1  Memory Usage: 29292kB
                     ->  Seq Scan on lbi  (cost=0.00..120598.17 rows=471189 width=32) (actual time=5.109..1093.313 rows=45809$
                           Filter: ((lbicon = 5) AND ((lbicop)::text = '022017'::text))
 Total runtime: 2389.494 ms

为 9.6 生成的计划（是的，它是针对同一个查询的）：

 GroupAggregate  (cost=244410.91..256931.05 rows=3 width=164) (actual time=7070.306..7547.298 rows=3 loops=1)
   Group Key: lbi.lbipro
   ->  Merge Left Join  (cost=244410.91..248891.54 rows=459395 width=46) (actual time=6789.774..7290.870 rows=458094 loops=1)
         Merge Cond: ((lbi.lbipro = oo.obipro) AND ((lbi.lbimat)::text = (oo.obiave)::text))
         ->  Sort  (cost=163803.11..164951.59 rows=459395 width=32) (actual time=5102.693..5220.945 rows=458094 loops=1)
               Sort Key: lbi.lbipro, lbi.lbimat
               Sort Method: quicksort  Memory: 48077kB
               ->  Seq Scan on lbi  (cost=0.00..120598.44 rows=459395 width=32) (actual time=6.580..1341.290 rows=458094 loop$
                     Filter: ((lbicon = 5) AND ((lbicop)::text = '022017'::text))
                     Rows Removed by Filter: 3518238
         ->  Sort  (cost=80607.80..80945.86 rows=135222 width=54) (actual time=1687.073..1709.739 rows=125480 loops=1)
               Sort Key: oo.obipro, oo.obiave
               Sort Method: quicksort  Memory: 12876kB
               ->  Subquery Scan on oo  (cost=66041.03..69083.53 rows=135222 width=54) (actual time=695.702..803.308 rows=125$
                     ->  HashAggregate  (cost=66041.03..67731.31 rows=135222 width=81) (actual time=695.701..794.818 rows=125$
                           Group Key: obi.obiave, obi.obipro, obi.obiscr
                           ->  Seq Scan on obi  (cost=0.00..63861.41 rows=217962 width=55) (actual time=0.193..541.695 rows=2$
                                 Filter: ((obicop)::text = '022017'::text)
                                 Rows Removed by Filter: 1728890
 Planning time: 1.528 ms
 Execution time: 7555.976 ms

在 PostgreSQL 9.1 中，查询的运行时间约为 1 或 2 秒。对于 9.6，它超过 8 或 9 秒。我检查了很多次配置，现在它们是相同的。我删除了并行查询，但它仍然比 9.1 慢。我什至试图通过减少或增加 9.6 来惩罚 9.1 work_mem，shared_buffers但 9.1 仍然获胜。我跑vacuum analyze了很多次。

我用 9.4 进行了测试，表现得像 9.1。我怀疑 9.5 会给出相同的结果，并且 9.6 中存在一些性能错误。我删除了数据库并在 9.6 中再次创建它 - 它仍然很慢。最糟糕的是，很多查询在 9.6 中运行得更慢，而少数查询更快。

这种差异是由我可以禁用的某种优化引起的吗？

具体版本为9.6.2。

我有一个返回以下结果集的表：

  mydate    | startstop
------------+----------
 2018-02-07 | start
 2018-02-14 | stop
 2017-02-06 | start
 2017-02-12 | stop
 2016-02-05 | start 
 2016-02-12 | stop 

我需要知道我的当前日期是否在其中一个时间间隔内，例如，如果我从当前日期查询表'2017-02-07'，我需要获取'TRUE'.

我知道它看起来很简单，但它并不简单！

我发现的最好的是：

       select true 
        where '2017-02-06'>=
         (select mydate from mytable where starstop='start' order by id limit 1) 
        and '2017-02-06' <= 
         (select mydate from mytable where startstop='stop' order by id limit 1);

如果日期在每个间隔之一中，它会返回TRUE，但前提是表格在未来没有间隔，正如您所看到的，我的表格在未来有间隔。

注：数据库管理系统为PostgreSQL 9.1

我想知道pg_start_backup()控制功能的风险。

例如，如果有人重置服务器，或者在pg_start_backup运行过程中发生电源故障，会发生什么情况？

postgres如果过程出现问题并且它刚刚消失会发生什么？

如果启用此选项后硬盘已满，会发生什么情况？

这是什么错误？我看到复制工作正常，但我不太确定数据是否已损坏。

我可以安全地忽略此错误吗？

我想知道索引是否在 postgresql 的二进制复制中复制。

如果是，我该如何禁用此行为？

因此，如果从属变为主控，我可以开始使用重新索引对其进行索引吗？

嗨，每次我需要设置二进制复制时，我都会遵循本教程，事实上，我正在使用第二种方法，称为：“仅通过快速主重启启动复制”，可在此处找到： https ://wiki.postgresql.org /wiki/Binary_Replication_Tutorial

那很好用。

在测试环境中，我尝试使用 tar 代替此 rsync：

 rsync -av --exclude pg_xlog --exclude postgresql.conf --exclude postgresql.pid \ 
     data/*  192.168.0.2:/var/lib/postgresql/data/

像这样：

tar -cz data >data.tar.gz

使用数据生成 .tar 文件并在从站上解压缩，这对数据库有多糟糕？

在日志中，slave 显示它已成功连接到 master。

我们运行的是PostgreSQL 9.1.20，数据库大约为 50GB。

1. 一切正常。
2. 几天后，奇怪的意外值出现在数字列中，例如意外位置出现负值或更大的值。
3. 奇怪的部分：如果我们运行一个pg_dump相同的“损坏”数据库，然后恢复它（在同一台服务器上），数据就会变得一切正常。现在相同的查询返回不同的值！

为什么当我们完全从“损坏的”数据库中获取转储时，数据库转储具有不同的值？（转储已经纠正了我们认为的值！）

这可能与某种交易中断或硬盘故障有关吗？请注意，重新启动数据库并不能解决问题。

这个问题背后的原因是什么？

根据评论中的要求，一些数据示例：

    #psql 
    \c base
    select * from salXXXX where sal_id=2323; 
    sal_id | sal_XXXX | XXXXXX | XXXXXX | XXXXX | XXXXX | XXXXX 
--------+-----------+---------------+--------------------+------------+-----------------------+------------
   2323 |     -30.43 | 42586501      |                  6 |         13 | f                     | 

    \quit
    pg_dump -h 127.0.0.1 -p 5432 --user=postgres >backup.sql
    psql 
    drop database base;
    create database base;
    \c base 
    \i backup.sql
    (no errors found here) 
    select * from salXXXX where sal_id=2323; 
    sal_id | sal_XXXX | XXXXXX | XXXXXX | XXXXX | XXXXX | XXXXX 
--------+-----------+---------------+--------------------+------------+-----------------------+------------
   2323 |    245.43 | 42586501      |                  6 |         13 | f                     | 

很难模仿它，很少发生，但当它发生时我们只是做一个转储，这是一个荒谬的解决方案，但它有效。

这只是一个例子，更多的错误值开始出现，即使我们重新处理数据。但是如果数据库被转储和恢复，值又在预期的范围内。

有什么区别：

vacuum;

和

vacuum analyze; 

我阅读了postgresql手册，但这对我来说仍然不是 100% 清楚。

我需要同时运行两者，还是其中之一就足够了？

我知道CREATE INDEX CONCURRENTLY速度较慢，但​​与传统相比需要多长时间才能完成CREATE INDEX？超过 40% 还是更多？

我在一个有 10000 个寄存器的表和一个有 15 个字符的列中创建一个索引。使用传统的CREATE INDEX.

我知道这很难回答，但有办法估计吗？

我有 4 个这样的表（这是一个例子）：

Company:
ID
Name
CNPJ

Department:
ID
Name
Code
ID_Company 

Classification:
ID
Name
Code
ID_Company

Workers:
Id 
Name
Code
ID_Classification
ID_Department

假设我有一个classificationwith id = 20, id_company = 1。还有一个department有id_company = 2（代表另一家公司）。

这将允许创建来自两家公司的工人，因为分类和部门分别链接到公司。我不希望这种情况发生，所以我觉得我的人际关系有问题，我不知道如何解决。