Hi Dale: I don't have access to a laptop right now, but:
1) To avoid conflicts with items that have been added to template1 you can specify template0 as the target for your createdb command using the "-T template0" argument for createdb. I'll add this to the wiki as the safest way of avoiding conflicts. 2) The createdb command definitely should specify -E UTF8 (or -E UNICODE). That's my mistake - thanks for catching it. Thanks Dale! On 27/06/2008, Dale Arntson <[EMAIL PROTECTED]> wrote: > Hi All, > > I decided to test backup and restore of our evergreen database. I am > running postgresql 8.1.13 and Evergreen 1.2.2.0 on Redhat 5. For the > backup/restore, I followed the instructions from > > > http://open-ils.org/dokuwiki/doku.php?id=evergreen-admin:maintenance:backups&s=backups > > > which are as follows: > > > # pg_dump -U <username> -h <hostname> -f <output-file> <database-name> > pg_dump -U evergreen -h localhost -f evergreen_db.backup.2008-06-15 > evergreen > > createdb -U evergreen -h localhost new_evergreen > psql -U evergreen -h localhost -f evergreen_db.backup.2008-06-15 > new_evergreen > > > After I completed the restore, and restarted everything, I tried to > search the restored database in the evergreen client, and got a JSON > parsing error. I then checked and found the following error in the > osrfsys.log: > > > DBD::Pg::db selectall_arrayref failed: ERROR: no tsearch config > > > I then looked for messages from the restore procedure in the postgres > log, and found what look like what look like tsearch-related errors: > > > ERROR: relation "pg_ts_cfg" already exists > ERROR: relation "pg_ts_cfgmap" already exists > ERROR: relation "pg_ts_dict" already exists > ERROR: relation "pg_ts_parser" already exists > ERROR: duplicate key violates unique constraint "pg_ts_cfg_pkey" > CONTEXT: COPY pg_ts_cfg, line 1: "default default C" > ERROR: duplicate key violates unique constraint "pg_ts_cfgmap_pkey" > CONTEXT: COPY pg_ts_cfgmap, line 1: "default lword {en_stem}" > ERROR: duplicate key violates unique constraint "pg_ts_dict_pkey" > CONTEXT: COPY pg_ts_dict, line 1: "simple dex_init(internal) \N > dex_lexize(internal,internal,integer) Simple example of dictionary." > ERROR: duplicate key violates unique constraint "pg_ts_parser_pkey" > > > Now, when I installed evergreen for the first time, I followed the > instructions in the Ubuntu guide about how to create the evergreen > database. Here are the relevant commands from that guide: > > > sudo -u postgres createuser -U postgres -s -P evergreen > > sudo -u postgres psql template1 < > /usr/share/postgresql/8.2/contrib/tsearch2.sql > > sudo -u postgres psql template1 < > /usr/share/postgresql/8.2/contrib/tablefunc.sql > > sudo -u postgres createlang plpgsql -d template1 > > sudo createdb -W -U evergreen -E UTF8 evergreen > > sudo -u postgres createlang plperl -d evergreen > > sudo -u postgres createlang plperlu -d evergreen > > > Since I added the tsearch2.sql to the template1 database, it is getting > copied into the newly created evergreen database. But the restore is > also trying to copy the tsearch2.sql back into the database. Is this > duplicate sql invalidating the tsearch configuration in some way? If so, > how do I fix it? > > Also, in contrast to the backup/restore instructions above, the ubuntu > install creates the evergreen database using the -E UTF8 encoding > option, and adds the plperl and plperlu languages to the evergreen > database after it has been created. Do I need to do these for a restore > as well? > > Any and all advice will be greatly appreciated. > > Regards, > > -dale > -- Dan Scott Laurentian University
