I've written a script to upgrade PostgreSQL from 8.2.1 to 9.2.1 on a Linux From Scratch distribution:
- Start the
- Vacuum all DBs (not sure if this is needed)
- Backup with
- Stop the
- Move away the directory where the data is stored (
/var/pg; it's a simple, single-server setup)
- Install PostgreSQL 9.2.1
- Start the server
- Restore the dumped data
- Recreate the
- 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.
Process 3980: UPDATE tablename SET [...]
Looks like the problem is a cron job which was trying to update the database during the reindexing.
psqlfinishes 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?
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
reindexdbloop? 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?
REINDEXstep 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.
reindexing of database "myschema" failed- is that just a search and replace error where you concealed the real DB and schema names?