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

[Dan Scott]

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
>


-- 
Dan Scott
Laurentian University