Thread: Error during analyze after upgrade from 10.10 -> 11.4
Hi,
I get the following error after upgrading from Postgres 10.10 -> Postgres 11.4
After upgrade completes when running "vacuumdb -p 5432 -U postgres -a -Z --analyze-in-stages" I get the following error:
vacuumdb: processing database "#DBNAME#": Generating minimal optimizer statistics (1 target)
vacuumdb: vacuuming of database "#DBNAME#" failed: ERROR: could not access status of transaction 6095
DETAIL: Could not open file "pg_xact/0000": No such file or directory.
After logging into the database using psql and running "VACUUM FULL #table#" for each table I can see it's an issue with pg_statistic
Running VACUUM FULL on table pg_statistic
psql.exe : ERROR: could not access status of transaction 6095
At line:23 char:9
+ psql.exe -d $db -c "vacuum full $table"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (ERROR: could n...ransaction 6095:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError
DETAIL: Could not open file "pg_xact/0000": No such file or directory.
Any ideas as to how this could have happened or how I can fix this issue? I had the issue once on another server and running VACUUM FULL on entire database fixed the error.
Note: I ran "VACUUM FREEZE ANALYZE" before the upgrade on every database and there were no errors.
After upgrade completes when running "vacuumdb -p 5432 -U postgres -a -Z --analyze-in-stages" I get the following error:
vacuumdb: processing database "#DBNAME#": Generating minimal optimizer statistics (1 target)
vacuumdb: vacuuming of database "#DBNAME#" failed: ERROR: could not access status of transaction 6095
DETAIL: Could not open file "pg_xact/0000": No such file or directory.
After logging into the database using psql and running "VACUUM FULL #table#" for each table I can see it's an issue with pg_statistic
Running VACUUM FULL on table pg_statistic
psql.exe : ERROR: could not access status of transaction 6095
At line:23 char:9
+ psql.exe -d $db -c "vacuum full $table"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (ERROR: could n...ransaction 6095:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError
DETAIL: Could not open file "pg_xact/0000": No such file or directory.
Any ideas as to how this could have happened or how I can fix this issue? I had the issue once on another server and running VACUUM FULL on entire database fixed the error.
Note: I ran "VACUUM FREEZE ANALYZE" before the upgrade on every database and there were no errors.
Ben Snaidero | |
Geotab | |
Senior Database Specialist | |
Direct | +1 (289) 230-7749 |
Toll-free | +1 (877) 436-8221 |
Visit | www.geotab.com |
Twitter | Facebook | YouTube | LinkedIn |
Ben Snaidero <bensnaidero@geotab.com> writes: > After upgrade completes when running "vacuumdb -p 5432 -U postgres -a -Z > --analyze-in-stages" I get the following error: > * vacuumdb: processing database "#DBNAME#": Generating minimal optimizer > statistics (1 target) vacuumdb: vacuuming of database "#DBNAME#" failed: > ERROR: could not access status of transaction 6095 DETAIL: Could not > open file "pg_xact/0000": No such file or directory.* > After logging into the database using psql and running "VACUUM FULL > #table#" for each table I can see it's an issue with pg_statistic If it's only pg_statistic then you're in luck, because all the data in that is rebuildable. You can try "delete from pg_statistic", and then "vacuum full pg_statistic". I'm not sure that will work though, it may hit the same problem. If so, you need a bigger hammer: "TRUNCATE pg_statistic" will fix it, but you'll need to stop the server and restart with allow_system_table_mods enabled to be allowed to do that. (Turn allow_system_table_mods back off afterwards!) Once you've got an empty pg_statistic, run "ANALYZE;" (as superuser) to rebuild all the stats. Hard to tell what the underlying issue is here --- perhaps pg_upgrade messed up, but it would take some detailed investigation to find out. regards, tom lane