Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've written a script to upgrade PostgreSQL from 8.2.1 to 9.2.1 on a Linux From Scratch distribution:

  1. Start the pg service
  2. Vacuum all DBs (not sure if this is needed)
  3. Backup with pg_dumpall
  4. Stop the pg service
  5. Move away the directory where the data is stored (/var/pg; it's a simple, single-server setup)
  6. Install PostgreSQL 9.2.1
  7. initdb
  8. Start the server
  9. Restore the dumped data
  10. reindexdb all DBs
  11. Recreate the referential_constraints view
  12. Vacuum all DBs (AFAIK required after this upgrade)

Unfortunately reindexing fails in about one third of the executions, even when using a virtual machine which I reset between tries. The code:

PGOPTIONS='--client-min-messages=warning' psql \
    --no-psqlrc \
    --variable=ON_ERROR_STOP=1 \
    --quiet \
    --log-file="$restore_log" \
    --single-transaction \
    --username postgres \
    --file="$sql_backup"
[error checking which stops the script if the import failed]
for database in $(databases); do
    reindexdb --username postgres $database
    [error checking which stops the script if the reindex failed]
done

The restore log shows no error at the end. The last lines of the reindexdb output (anonymized to be able to publish it):

NOTICE:  table "myschema.mytable" was reindexed
reindexdb: reindexing of database "myschema" failed: ERROR:  could not open relation with OID 16738

/var/log/pg/postmasterlog contents during the upgrade:

After a VM reset I found that the OID belongs to a table which is backed up and restored properly. Could it be a problem that the reindexdb step is run immediately after psql has processed the backup file?

Edit: This is getting even worse - This time I encountered another issue I haven't seen before:

reindexdb: reindexing of database "myschema" failed: ERROR:  deadlock detected
DETAIL:  Process 3883 waits for AccessExclusiveLock on relation 17684 of database 16385; blocked by process 3980.
Process 3980 waits for RowExclusiveLock on relation 16513 of database 16385; blocked by process 3883.

In /var/log/messages:

Process 3980: UPDATE tablename SET [...]

Looks like the problem is a cron job which was trying to update the database during the reindexing.

share|improve this question
    
That shouldn't happen, something dodgy's going on here. Once psql finishes running a script to restore the DB and commits, it should be ready for use. Is this reindex running on 8.2.23? Or are you dumping and reloading, then running the reindex on 9.2? If so, 9.2.what and on what platform? Is there anything informative in the server logs between the end of the restore and the reindexdb run? –  Craig Ringer Oct 23 '12 at 10:18
    
Also: Was the dump created with 9.2's pg_dump, or 8.2's? What happens if you insert psql postgres -c 'CHECKPOINT;' between the end of the restore and the start of the reindexdb loop? Are you using any GiST or GIN indexes? Finally, can you reproduce this issue by restoring a dump file that you're able to publish/distribute, or is it specific to this one dump from your old system? –  Craig Ringer Oct 23 '12 at 10:22
    
Note that the REINDEX step only applies if you're updating an 8.3.x cluster in-place from x<5 to x>=5. It does not apply for dump and restore. You do not need to perform this step. You won't even be using 8.3, you'll be going straight from 8.2 to 9.2 (right?). That said, this really shouldn't be happening, so more info as requested. –  Craig Ringer Oct 23 '12 at 10:28
    
Oh, and when you say reindexing of database "myschema" failed - is that just a search and replace error where you concealed the real DB and schema names? –  Craig Ringer Oct 23 '12 at 10:35
    
Updated as far as I can from your suggestions - I'll add more info if I manage to reproduce the problem soon. If you make your third comment an answer, then I can accept it. Unfortunately I can't promise to work much longer on this. –  l0b0 Oct 23 '12 at 12:16

1 Answer 1

up vote 3 down vote accepted

Note that the REINDEX step only applies if you're updating an 8.3.x cluster in-place from x<5 to x>=5. It does not apply for dump and restore. You do not need to perform this step for what you are doing. You won't even be using 8.3, you'll be going straight from 8.2 to 9.2 (right?).

Also, as per the upgrade documentation, you should use the new PostgreSQL's pg_dump when preparing for an upgrade, not the old version.

That said, this really shouldn't be happening; see comments.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.