BUG #15995: VACUUM not working after setting/unsetting of archive_mode = on, wal_level = replica

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: jo(dot)raj(dot)postgres(dot)dba(at)gmail(dot)com
Subject: BUG #15995: VACUUM not working after setting/unsetting of archive_mode = on, wal_level = replica
Date: 2019-09-09 02:12:22
Message-ID: 15995-fd9ae7b6ca561df6@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15995
Logged by: Daniel Joseph
Email address: jo(dot)raj(dot)postgres(dot)dba(at)gmail(dot)com
PostgreSQL version: 10.8
Operating system: RHEL 7.5
Description:

Hi Postgres Support Team,

I have a huge problem with Postgres, initially I build a database with 9.6,
upgraded to 10.8 after 3 months, during upgraded I took the
archive_mode=off(default) and left

1. Build a brand new postgres cluster in 9.6 version in RHEL 7.5 (no
external extensions/software been installed)
2. Vacuum worked full, freeze etc.
3. After 3 months upgraded the postgres database cluster from 9.6 to 10.8.
4. Database is running fine after the upgrade.
5. During upgrade, have unset archive_mode = on, wal_level = replica, to
leave as default.
6. We don't have any type of streaming/HA environment.
7. Upgrade was successful, database is operational.
8. Again set archive_mode = on, wal_level = replica
9. VACUUM is not working, even after upgrades vacuum is not working. If I
unset archive_mode = on, wal_level = replica and leave to default, vacuum
not working. Following is the message I get, no dead rows removed, but lot
of delete/update has happeded to the table.

test1=# vacuum (full, verbose, analyze ) ;
INFO: vacuuming "pg_catalog.pg_statistic"
INFO: "pg_statistic": found 136 removable, 426 nonremovable row versions in
27 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s.
INFO: vacuuming "pg_catalog.pg_type"
INFO: "pg_type": found 9 removable, 380 nonremovable row versions in 9
pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: analyzing "pg_catalog.pg_type"
INFO: "pg_type": scanned 9 of 9 pages, containing 378 live rows and 2 dead
rows; 378 rows in sample, 378 estimated total rows
INFO: vacuuming "pg_catalog.pg_policy"
INFO: "pg_policy": found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: analyzing "pg_catalog.pg_policy"
INFO: "pg_policy": scanned 0 of 0 pages, containing 0 live rows and 0 dead
rows; 0 rows in sample, 0 estimated total rows
INFO: vacuuming "test1.table1"
INFO: "table1": found 0 removable, 619476 nonremovable row versions in
15644 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.38 s, system: 0.25 s, elapsed: 3.16 s.
INFO: analyzing "test1.table1"
INFO: "table1": scanned 15644 of 15644 pages, containing 619476 live rows
and 0 dead rows; 30000 rows in sample, 619476 estimated total rows
INFO: vacuuming "pg_catalog.pg_authid"

10. Even if I create a brand new postgres cluster + database in 10.8 and
delete/update 1million rows in 10 million row table, vacuum is not removing
the dead rows.

Any help in resolving the issue is appreciated. If this is a know bug, do
you have any workaround.

Thanks,
Daniel

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message fn ln 2019-09-09 03:58:46 Re: BUG #15977: Inconsistent behavior in chained transactions
Previous Message Tom Lane 2019-09-08 21:01:20 pgsql: Fix RelationIdGetRelation calls that weren't bothering with erro