1

We tried to run a full vacuum on our Postgres database the other day because of disk problems. A bunch of things went wrong along the way and eventually we stopped the vacuum.

Now we have some new issues. For example, this constraint on our transactions table:

CONSTRAINT transactions_pkey PRIMARY KEY (transaction_id)

If I try to count the rows for transaction_id 12345 with seqscan=on:

set enable_seqscan=on;
with cte as (
    select count(*), transaction_id from transactions group by transaction_id
)
select count from cte where transaction_id='12345';

The result is a count of 12.

And if I run the same count with secscan=off (to use transactions_pkey):

set enable_seqscan=off;
with cte as (
    select count(*), transaction_id from transactions group by transaction_id
)
select count from cte where transaction_id='12345';

That returns a count of 8.

I only use an awkward count query so that the first example doesn't use the index.

The sequential scan shows 12 rows and the index scan shows 8 rows, but the pkey constraint shouldn't allow more than a single row.

Maybe there was a short period of time during the failed vacuum where the pkey was ignored and duplicate records were inserted? Then somehow the pkey was re-added?

We've tried to script our own cleanup, but deciding which records to keep/update/remove for hundreds of tables with unique constraints has been troublesome. We might need to try another full vacuum, but our database seems to be in an uncommon error state and we're not sure if a vacuum will fix it or make the problem worse.

  • 1
    wiki.postgresql.org/wiki/Corruption It might be already too late - do you have a backup from before the VACUUM FULL? Did you happen to touch pg_clog, for example? Also, which exact version are you on? Do you have replication running (with a recent failover, maybe)? – dezso May 12 '17 at 8:14
  • Could you add some info regarding "A bunch of things went wrong" ? Those must be disk related, but what were your operations at this time ? As dezso stated above, you might want to have a look at your most recent valid backup. Corruption analysis can be a rabbit-hole and you production server might not have this time available. – KookieMonster May 12 '17 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.