BUG #15995: VACUUM not working after setting/unsetting of archive_mode = on, wal_level = replica
От | PG Bug reporting form |
---|---|
Тема | BUG #15995: VACUUM not working after setting/unsetting of archive_mode = on, wal_level = replica |
Дата | |
Msg-id | 15995-fd9ae7b6ca561df6@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #15995: VACUUM not working after setting/unsetting of archive_mode = on, wal_level = replica
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: BUG #15995: VACUUM not working after setting/unsetting ofarchive_mode = on, wal_level = replica (Jeff Janes <jeff.janes@gmail.com>) |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 15995 Logged by: Daniel Joseph Email address: jo.raj.postgres.dba@gmail.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
В списке pgsql-bugs по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: BUG #15995: VACUUM not working after setting/unsetting of archive_mode = on, wal_level = replica