Imanol Y.'s questions

嗨，我在 centos 7 机器上运行，我正在将旧的 postgres 数据库迁移到新服务器，它有 10TB 的数据，所以我尝试使用 pg_basebackup。新服务器有一个独立的磁盘，有足够的存储空间来处理操作，但它不是主磁盘。我想将 PGDATA 设置到这个大数据盘中。

我已按照以下步骤操作：

1. 安装 postgres-12 和 postgres12-contrib

2. 将数据盘挂载到新机器上

3. 在数据盘中创建一个空的 pgdata 目录。全部使用用户 postgres。

4. 利用/usr/bin/pg_basebackup -h IP_OF_OLD_MACHINE -D /mnt/disks/data-disk/pgdata -P -U USERNAME --wal-method=stream

我认为pg_basebackup成功结束了，数据花了几个小时但完全没有提示任何错误。

total 132K
-rw-------. 1 postgres postgres  230 Mar 18 12:36 backup_label
drwx------. 7 postgres postgres 4.0K Mar 18 12:43 base
-rw-------. 1 postgres postgres   30 Mar 19 00:13 current_logfiles
drwx------. 2 postgres postgres 4.0K Mar 19 00:13 global
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 log
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_commit_ts
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_dynshmem
-rw-------. 1 postgres postgres 4.8K Mar 18 12:36 pg_hba.conf
-rw-------. 1 postgres postgres 4.7K Mar 18 12:36 pg_hba.conf~
-rw-------. 1 postgres postgres 1.6K Mar 19 00:13 pg_ident.conf
drwx------. 4 postgres postgres 4.0K Mar 18 12:36 pg_logical
drwx------. 4 postgres postgres 4.0K Mar 18 12:36 pg_multixact
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_notify
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_replslot
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_serial
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_snapshots
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_stat
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_stat_tmp
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_subtrans
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_tblspc
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_twophase
-rw-------. 1 postgres postgres    3 Mar 19 08:27 PG_VERSION
drwx------. 3 postgres postgres 4.0K Mar 18 12:36 pg_wal
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_xact
-rw-------. 1 postgres postgres   88 Mar 18 12:36 postgresql.auto.conf
-rw-------. 1 postgres postgres  24K Mar 19 08:39 postgresql.conf

好的，现在我正在尝试使用我刚刚带来的数据初始化一个 postgres 服务器。

这是我的尝试，查看文档：

/usr/pgsql-12/bin/postgresql-12-setup initdb -D /mnt/disks/data-disk/pgdata
systemctl: invalid option -- 'D'
failed to find PGDATA setting in -D.service


/usr/pgsql-12/bin/postgresql-12-setup initdb --pgdata=/mnt/disks/data-disk/pgdata
systemctl: unrecognized option '--pgdata=/mnt/disks/data-disk/pgdata.service'
failed to find PGDATA setting in --pgdata=/mnt/disks/data-disk/pgdata.service

过程中是否有一些错误？

更新： 正如@pifor 告诉我的那样，我将 PATH 添加到 postgres bash_profile 中，然后开始识别 PG_DATA。现在的问题是 initdb 旨在找到一个空文件夹，并且由于 pg_basebackup 而不是空的

我刚刚删除了存储在用户表中一个 jsonb 中的大部分数据（不再需要它），并在该列中保留一个 id。我希望通过此操作释放大量磁盘空间。

UPDATE users SET my_jsonb = '{id: xxxx }';

问题是当我尝试查看磁盘中的可用空间时保持不变

/dev/md125 935G 501G 387G 57%

还有表

 Table                                            Size        External
 users                                          | 497 GB     | 478 GB

我的最后一个选择是尝试 aVACUUM FULL users但是由于该表用于生产环境，因此将其锁定一段未知的时间并不方便。

我在 CENTOS RHEL7 机器上运行 postgres 10.5 数据库。

metrics是一个未记录的表，大约有 3M 行，我刚刚执行了ALTER TABLE metrics SET LOGGED以记录这些数据。但它似乎被一些 autovacuum 运行进程锁定。

  pid  | usename  | blocked_by |              blocked_query
-------+----------+------------+------------------------------------------
 13462 | postgres | {32090}    | ALTER TABLE sensors.metrics SET LOGGED;

    duration     |                                 query                                  |  pid
-----------------+------------------------------------------------------------------------+-------
 02:03:08.131365 | autovacuum: VACUUM pg_toast.pg_toast_315873283 (to prevent wraparound) | 32090

我的 32 核和 98gigs RAM 的 autovacuum 设置：

 autovacuum_max_workers
------------------------
 12
 autovacuum_vacuum_cost_limit
------------------------------
 -1    
 autovacuum_vacuum_cost_delay
------------------------------
 20ms  
 vacuum_cost_page_hit
----------------------
 1
 vacuum_cost_page_miss
-----------------------
 10
 vacuum_cost_page_dirty
------------------------
 20

我可以做些什么来避免锁定，或者在不终止正在登录数据的 alter table 进程的情况下修复此锁定吗？

我正在尝试将具有 50M 记录的表转储到文件中，我的目标是减少执行此操作的时间。我通常使用COPY metrics TO 'metrics.csv' DELIMITER ',' CSV;这在最好的情况下可能需要一个小时。我也有兴趣以某种纯格式导出数据（避免使用pd_dump目录）。

其中一个想法是通过条件或游标以某种方式访问​​此表，将整个表拆分为相等大小的块，因此您可以同时执行例如 2 个复制查询，将时间减少到一半。

例子：

COPY (SELECT * FROM metrics WHERE id < 25000000) TO 'metrics_1.csv' DELIMITER ',' CSV;
COPY (SELECT * FROM metrics WHERE id >= 25000000) TO 'metrics_2.csv' DELIMITER ',' CSV;

在这些条件下创建的部分索引会有所帮助吗？

有什么想法是实现表的部分复制转储的好方法吗？还有其他解决方案可以更快地转储此表吗？

Postgresql 11 / 100GB RAM / 20 核。

在与 IO 边界进行一些并行化之后，COPY似乎不再是瓶颈。

我已经在容量更大的服务器中迁移了一个促进只读副本的数据库，这个过程运行得很顺利。但是我现在正在尝试从这个提升的服务器创建一个只读副本，以具有与以前类似的设置。问题是当我尝试启动 Postgres 服务器时会发生这种情况。

● postgresql-10.service - PostgreSQL 10 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-10.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since mar 2018-10-16 07:07:42 UTC; 10s ago
   Docs: https://www.postgresql.org/docs/10/static/
   Process: 22746 ExecStart=/usr/pgsql-10/bin/postmaster -D ${PGDATA} 
   (code=exited, status=2)
   Process: 22740 ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 22746 (code=exited, status=2)

我将尽可能准确地描述我的过程，我在 CentOS RHEL 7 上运行。

首先我安装 Postgres 10：

rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
yum install postgresql10-server postgresql10
yum install postgresql10-contrib
su - postgres
/usr/pgsql-10/bin/initdb
systemctl start postgresql-10.service

这行得通，服务器正在运行，所以安装工作正常，让我们从复制开始。我停止服务并继续：

 rm -r /var/lib/pgsql/10/data
 su - postgres
 /usr/pgsql-10/bin/pg_basebackup -h <database host ip> -D /var/lib/pgsql/10/data/ -P -U replicate

replicate是在主数据库中具有复制权限的用户。

pg_basebackup正确完成，然后我将其重命名recovery.done为recovery.conf.

drwx------. 6 postgres postgres   54 oct 16 06:45 base
-rw-------. 1 postgres postgres   30 oct 16 06:55 current_logfiles
drwx------. 2 postgres postgres 4,0K oct 16 06:45 global
drwx------. 2 postgres postgres  188 oct 16 06:53 log
drwx------. 2 postgres postgres    6 oct 16 06:45 pg_commit_ts
drwx------. 2 postgres postgres    6 oct 16 06:45 pg_dynshmem
-rw-------. 1 postgres postgres 4,5K oct 16 06:55 pg_hba.conf
-rw-------. 1 postgres postgres 1,6K oct 16 06:55 pg_ident.conf
drwx------. 4 postgres postgres   68 oct 16 06:53 pg_logical
drwx------. 4 postgres postgres   36 oct 16 06:45 pg_multixact
drwx------. 2 postgres postgres   18 oct 16 06:45 pg_notify
drwx------. 2 postgres postgres    6 oct 16 06:53 pg_replslot
drwx------. 2 postgres postgres    6 oct 16 06:45 pg_serial
drwx------. 2 postgres postgres    6 oct 16 06:45 pg_snapshots
drwx------. 2 postgres postgres    6 oct 16 06:53 pg_stat
drwx------. 2 postgres postgres    6 oct 16 06:53 pg_stat_tmp
drwx------. 2 postgres postgres    6 oct 16 06:45 pg_subtrans
drwx------. 2 postgres postgres    6 oct 16 06:53 pg_tblspc
drwx------. 2 postgres postgres    6 oct 16 06:45 pg_twophase
-rw-------. 1 postgres postgres    3 oct 16 06:55 PG_VERSION
drwx------. 3 postgres postgres 4,0K oct 16 06:45 pg_wal
drwx------. 2 postgres postgres 4,0K oct 16 06:53 pg_xact
-rw-------. 1 postgres postgres   88 oct 16 06:55 postgresql.auto.conf
-rw-------. 1 postgres postgres  23K oct 16 06:55 postgresql.conf
-rw-------. 1 postgres postgres   58 oct 16 06:55 postmaster.opts
-rw-r--r--. 1 postgres postgres  154 oct 16 06:53 recovery.conf

但是在跑步时systemctl start postgresql-10.service，我得到

postgresql-10.service 的作业失败，因为控制进程以错误代码退出。有关详细信息，请参阅“systemctl status postgresql-10.service”和“journalctl -xe”。

我找不到比这条消息更多的线索：

postgresql-10.service: main process exited, code=exited, status=1/FAILURE

我错过了什么？我尝试使用默认运行postgres.conf并pg_hba.conf避免配置错误，但这也不起作用。

完整journalctl输出：

oct 17 09:15:11 database-replica systemd[1]: Starting PostgreSQL 10 database server...
-- Subject: Unit postgresql-10.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-10.service has begun starting up.
oct 17 09:15:11 database-replica postmaster[19514]: 2018-10-17 09:15:11.834 UTC [19514] LOG:  listening on IPv6 address "::1", port 5432
oct 17 09:15:11 database-replica postmaster[19514]: 2018-10-17 09:15:11.834 UTC [19514] LOG:  listening on IPv4 address "127.0.0.1", port 5432
oct 17 09:15:11 database-replica postmaster[19514]: 2018-10-17 09:15:11.836 UTC [19514] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
oct 17 09:15:11 database-replica postmaster[19514]: 2018-10-17 09:15:11.840 UTC [19514] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
oct 17 09:15:11 database-replica postmaster[19514]: 2018-10-17 09:15:11.865 UTC [19514] LOG:  redirecting log output to logging collector process
oct 17 09:15:11 database-replica postmaster[19514]: 2018-10-17 09:15:11.865 UTC [19514] HINT:  Future log output will appear in directory "log".
oct 17 09:15:11 database-replica systemd[1]: postgresql-10.service: main process exited, code=exited, status=1/FAILURE
oct 17 09:15:11 database-replica systemd[1]: Failed to start PostgreSQL 10 database server.
-- Subject: Unit postgresql-10.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-10.service has failed.
--
-- The result is failed.
oct 17 09:15:11 database-replica systemd[1]: Unit postgresql-10.service entered failed state.
oct 17 09:15:11 database-replica systemd[1]: postgresql-10.service failed.
oct 17 09:15:11 database-replica polkitd[676]: Unregistered Authentication Agent for unix-process:19502:16930756 (system bus name :1.160, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale es_ES.UTF-8) (disconnected from bus)

我使用 postgres 10.1 和 Datestyle 配置如下

 DateStyle
 -----------
 ISO, MDY

我有这个错误

错误：日期/时间字段值超出范围：“1535673858” 提示：也许您需要不同的“日期样式”设置。

在执行以下插入语句时：

INSERT INTO ex (taken_ats) VALUES('{ 1535673858 , 1535678856}')

架构：

CREATE TABLE ex (
    taken_ats TIMESTAMP WITHOUT TIME ZONE[]
)

我SELECT在使用<运算符时速度非常慢 我正在寻找修复或解决方法来执行此操作：

EXPLAIN (ANALYZE) 
SELECT * 
FROM "users" 
WHERE (engagement_level(social) < 1) 
    AND (social_peemv(social) < 33.333333333333336) 
    AND (array['United Kingdom'] <@ mixed_frequent_locations(location)) 
    AND (is_visible(social, flags) = TRUE) 
ORDER BY "users"."created_at" ASC 
LIMIT 12 OFFSET 0;
                                                                                                               QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    ---------------------------
 Limit  (cost=0.43..18572.10 rows=12 width=860) (actual time=5658.037..175582.743 rows=12 loops=1)
   ->  Index Scan using created_at_idx on users  (cost=0.43..6244724.16 rows=4035 width=860) (actual time=5658.035..175582.735 rows=12 loops=1)
         Filter: (is_visible(social, flags) AND (engagement_level(social) < 1) AND (social_peemv(social) < '33.3333333333333'::double precision) AND ('{"United Kingdom"}'::text[] <@ mixed_frequent_locations(location)))
         Rows Removed by Filter: 2816798
 Planning time: 1.573 ms
 Execution time: 175583.373 ms
(6 rows)

EXPLAIN (ANALYZE) 
SELECT * 
FROM "users" 
WHERE (engagement_level(social) < 1) 
  AND (social_peemv(social) = 33.3333) 
  AND (array['United Kingdom'] <@ mixed_frequent_locations(location)) 
  AND (is_visible(social, flags) = TRUE)
ORDER BY "users"."created_at" ASC
LIMIT 12 OFFSET 0;
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=380.04..380.31 rows=1 width=863) (actual time=0.051..0.051 rows=0 loops=1)
   ->  Result  (cost=380.04..380.31 rows=1 width=863) (actual time=0.050..0.050 rows=0 loops=1)
         ->  Sort  (cost=380.04..380.05 rows=1 width=896) (actual time=0.049..0.049 rows=0 loops=1)
               Sort Key: created_at
               Sort Method: quicksort  Memory: 25kB
               ->  Index Scan using idx_in_social_peemv on users  (cost=0.43..380.03 rows=1 width=896) (actual time=0.044..0.044 rows=0 loops=1)
                     Index Cond: (social_peemv(social) = '33.3333'::double precision)
                     Filter: (is_visible(social, flags) AND (engagement_level(social) < 1) AND ('{"United Kingdom"}'::text[] <@ mixed_frequent_locations(location)))
 Planning time: 0.459 ms
 Execution time: 0.095 ms

在第一种情况下，没有Index Cond应用并且执行时间增长到175583.373 ms

指数：

 CREATE INDEX idx_in_social_peemv ON users USING BTREE ( social_peemv(social) ) ;
 CREATE INDEX mixed_frequent_locations_idx on users USING GIN ( mixed_frequent_locations(location) ) ;
 CREATE INDEX created_at_idx ON users USING btree (created_at)
 CREATE INDEX idx_in_social_follower_count_and_created_at ON users USING btree (social_follower_count(social) DESC, created_at)
 CREATE INDEX idx_in_egagagement_level_and_created_at ON users USING btree (engagement_level(social), creat
ed_at)

桌子：

CREATE TABLE users (
    id SERIAL PRIMARY KEY NOT NULL,
    name TEXT,
    bio TEXT,
    social jsonb,
    flags array,
    location jsonb,
    search_field ts_vector,
    created_at TIMESTAMP WITHOUT TIMEZONE,
    udpated_at TIMESTAMP WITHOUT TIMEZONE
);

Postgres 版本：10

整个条件匹配 20 条记录，共 3669284 条

每个条件匹配：

(engagement_level(social) < 1)= 801176

(social_peemv(social) < 33.333333333333336)= 1621516

(array['United Kingdom'] <@ mixed_frequent_locations(location))= 91625

(is_visible(social, flags) = TRUE)= 3333733

正如@jjanes 所建议的，我尝试删除LIMITandOFFSET并且查询计划更改为位图堆扫描：

EXPLAIN (ANALYZE)
SELECT *
FROM "users"
WHERE (engagement_level(social) < 1)
     AND (social_peemv(social) < 33.333333333333336)
     AND (array['United Kingdom'] <@ mixed_frequent_locations(location))
     AND (is_visible(social, flags) = TRUE)
ORDER BY "users"."created_at" ASC;
                                                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=77641.99..77652.36 rows=4148 width=1393) (actual time=1195.544..1195.546 rows=20 loops=1)
   Sort Key: created_at
   Sort Method: quicksort  Memory: 59kB
   ->  Bitmap Heap Scan on users  (cost=18046.48..77392.73 rows=4148 width=1393) (actual time=227.471..1195.481 rows=20 loops=1)
         Recheck Cond: (('{"United Kingdom"}'::text[] <@ mixed_frequent_locations(location)) AND (engagement_level(social) < 1))
         Filter: (is_visible(social, flags) AND (social_peemv(social) < '33.3333333333333'::double precision))
         Rows Removed by Filter: 19444
         Heap Blocks: exact=19238
         ->  BitmapAnd  (cost=18046.48..18046.48 rows=28415 width=0) (actual time=218.484..218.484 rows=0 loops=1)
               ->  Bitmap Index Scan on mixed_frequent_locations_idx  (cost=0.00..1356.36 rows=128634 width=0) (actual time=44.794..44.794 rows=108076 loops=1)
                     Index Cond: ('{"United Kingdom"}'::text[] <@ mixed_frequent_locations(location))
               ->  Bitmap Index Scan on idx_in_egagagement_level_and_created_at  (cost=0.00..16687.80 rows=1156662 width=0) (actual time=163.368..163.368 rows=801189 loops=1)
                     Index Cond: (engagement_level(social) < 1)
 Planning time: 3.326 ms
 Execution time: 1197.242 ms

除 之外的所有应用条件is_visible均由用户提供

语境：

PostgreSQL 10，users 表有 3667438 条记录，users 表有一个叫做 social 的 JSONB，我们通常使用索引计算函数输出的策略，因此我们可以将信息聚合到单个索引中。函数的输出engagement(social)是双精度数值类型。

问题：

有问题的子句是，该数据还附加了ORDER BY engagement(social) DESC NULLS LAST一个 btree 索引。idx_in_social_engagement with DESC NULLS LAST

快速查询：

EXPLAIN ANALYZE
SELECT  "users".* FROM "users"
WHERE (follower_count(social) < 500000)
AND (engagement(social) > 0.03)
AND (engagement(social) < 0.25)
AND (peemv(social) < 533)
ORDER BY "users"."created_at" ASC
LIMIT 12 OFFSET 0;

Limit  (cost=0.43..52.25 rows=12 width=1333) (actual time=0.113..1.625 
rows=12 loops=1)
   ->  Index Scan using created_at_idx on users  (cost=0.43..7027711.55 rows=1627352 width=1333) (actual time=0.112..1.623 rows=12 loops=1)
         Filter: ((follower_count(social) < 500000) AND (engagement(social) > '0.03'::double precision) AND (engagement(social) <  '0.25'::double precision) AND (peemv(social) > '0'::double precision) AND (peemv(social) < '533'::double precision))
         Rows Removed by Filter: 8
 Planning time: 0.324 ms
 Execution time: 1.639 ms

慢查询：

EXPLAIN ANALYZE 
SELECT  "users".* FROM "users" 
WHERE (follower_count(social) < 500000) 
AND (engagement(social) > 0.03) 
AND (engagement(social) < 0.25) 
AND (peemv(social) > 0.0) 
AND (peemv(social) < 533) 
ORDER BY engagement(social) DESC NULLS LAST, "users"."created_at" ASC 
LIMIT 12 OFFSET 0;

Limit  (cost=2884438.00..2884438.03 rows=12 width=1341) (actual time=68011.728..68011.730 rows=12 loops=1)
->  Sort  (cost=2884438.00..2888506.38 rows=1627352 width=1341) (actual time=68011.727..68011.728 rows=12 loops=1)
        Sort Key: (engagement(social)) DESC NULLS LAST, created_at
        Sort Method: top-N heapsort  Memory: 45kB
        ->  Index Scan using idx_in_social_engagement on users  (cost=0.43..2847131.26 rows=1627352 width=1341) (actual time=0.082..67019.102 rows=1360633 loops=1)
            Index Cond: ((engagement(social) > '0.03'::double precision) AND (engagement(social) < '0.25'::double precision))
            Filter: ((follower_count(social) < 500000) AND (peemv(social) > '0'::double precision) AND (peemv(social) < '533'::double precision))
            Rows Removed by Filter: 85580
Planning time: 0.312 ms
Execution time: 68011.752 ms

选择带有 * 因为我需要存储在每一行中的所有数据。

更新：

CREATE INDEX idx_in_social_engagement on influencers USING BTREE ( engagement(social) DESC NULLS LAST)

准确的索引定义