In the course of migrating from (I think) Postgres 8.4 under Fedora 12
to Postgres 9.1 under Fedora 16 I managed to loose 'template1' and
associated data. The immediate effect is that, although my schema and
data seem to be intact and are working correctly, pg_dumpall refused to
run because it can't find 'template1'.
The sequence was something like this:
1) first step was to bring my updates fully up to date by running
pg_dumpall with output to a USB hard drive and to run the incremental
backup that's part of my application (a mail archive that backs up into
a set of mbox files).
2)installed Fedora 16, including the Postgres 9.1 package, and fully
updated all packages
3)initialised the Postgres user and basic database structures, started
the server.
4)attempted to use pg_restore to recover my database. This threw lots of
errors and was obviously stupid, so I killed it and reloaded the backup
with psql. This ran OK except that some data was incorrectly restored to
my mail messages table because tabs in some messages confused the COPY
command.
5) Started again: I dropped my schema and recreated it before letting my
application restore the database from its mbox files.
6) The next scheduled backup using pg_dumpall failed immediately because
it couldn't find 'template1'.
Observations
============
a) could the abrupt termination of pg_restore have removed 'template1'
from the database structure?
b) I've seen the confusion between tabs in data and the tabs used as
delimiters by pg_dump cause this problem in the past. It was fixed then,
but seems to have crept back in.
Question
========
Is it possible to reinstate 'template1' and all its works without
reinitialising the database from scratch. It was suggested to me that
running "initdb" might fix it while leaving my data in place though my
source was uncertain about its success. Would this work or is there a
better way to restore 'template1' in a populated database system?
Martin
