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

[Dale Arntson]

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