[OPEN-ILS-DEV] Problem with backup and restore of evergreen database

[Dale Arntson]

Hi Scott,

That makes perfect sense. The restore includes the code added to 
template1. Therefore, I need to restore to a template0 database.

Thanks a lot!

-dale

Dan Scott wrote:
> 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 <arnt at uchicago.edu> 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
>>
> 
>