How to wait between PostgreSQL DB restore and reindexing?

Question

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.

Answer 1

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.