Thread: BUG #13042: pg_upgrade --check succeeded but run failed due to missing thesaurus file
BUG #13042: pg_upgrade --check succeeded but run failed due to missing thesaurus file
From
cestel@covermymeds.com
Date:
The following bug has been logged on the website:
Bug reference: 13042
Logged by: CJ Estel
Email address: cestel@covermymeds.com
PostgreSQL version: 9.4.1
Operating system: RHEL7
Description:
Run check, succeeded.
Run pg_upgrade, fails with error about thesaurus file.
Copy file into place, re-init db, and run pg_upgrade again, succeeded.
Thought on desired action: the check should fail because the thesaurus file
doesn't exist in 9.4
Detailed output below from upgrade:
postgres@dc0ppgc2n1:PRODUCTION:~> /usr/pgsql-9.4/bin/pg_upgrade -b
/usr/pgsql-9.3/bin -B /usr/pgsql-9.4/bin -d /var/lib/pgsql/9.3/data -D
/var/lib/pgsql/9.4/data --check
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "line" user columns ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok
*Clusters are compatible*
postgres@dc0ppgc2n1:PRODUCTION:~> /usr/pgsql-9.4/bin/pg_upgrade -b
/usr/pgsql-9.3/bin -B /usr/pgsql-9.4/bin -d /var/lib/pgsql/9.3/data -D
/var/lib/pgsql/9.4/data
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "line" user columns ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Adding support functions to new cluster ok
Restoring database schemas in the new cluster
form_search
*failure*
Consult the last few lines of "pg_upgrade_dump_17063.log" for
the probable cause of the failure.
Failure, exiting
postgres@dc0ppgc2n1:PRODUCTION:~> tail -n 20 pg_upgrade_dump_17063.log
command: "/usr/pgsql-9.4/bin/pg_restore" --host "/var/lib/pgsql" --port
50432 --username "postgres" --exit-on-error --verbose --dbname "form_search"
"pg_upgrade_dump_17063.custom" >> "pg_upgrade_dump_17063.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: creating pg_largeobject pg_largeobject
pg_restore: creating pg_largeobject_metadata pg_largeobject_metadata
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA "public"
pg_restore: creating FUNCTION fn_determinate_form_search(character varying,
character varying, character varying, character, character varying, boolean,
character varying)
pg_restore: creating FUNCTION fn_determinate_form_search_api(character
varying, character varying, character varying, character, character varying,
integer, character varying)
pg_restore: creating FUNCTION fn_drugname("text")
pg_restore: creating FUNCTION fn_searchforms("text", "text", "text", "text",
boolean)
pg_restore: creating FUNCTION fn_searchforms_api("text", "text", "text",
"text", integer)
pg_restore: creating FUNCTION plainto_or_tsquery("text")
pg_restore: creating FUNCTION to_weighted_tsvector("text")
pg_restore: creating TEXT SEARCH DICTIONARY cmm_thesaurus
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1745; 3600 60173 TEXT
SEARCH DICTIONARY cmm_thesaurus postgres
pg_restore: [archiver (db)] could not execute query: ERROR: could not open
thesaurus file "/usr/pgsql-9.4/share/tsearch_data/cmm_thesaurus.ths": No
such file or directory
Command was: CREATE TEXT SEARCH DICTIONARY "cmm_thesaurus" (
TEMPLATE = "pg_catalog"."thesaurus",
dictfile = 'cmm_thesaurus', dic...
postgres@dc0ppgc2n1:PRODUCTION:~> cp
/usr/pgsql-9.{3,4}/share/tsearch_data/cmm_thesaurus.ths
cp: cannot create regular file
â/usr/pgsql-9.4/share/tsearch_data/cmm_thesaurus.thsâ: Permission denied
postgres@dc0ppgc2n1:PRODUCTION:~> exit
exit
root@dc0ppgc2n1:PRODUCTION:cestel> ls -lsa
/usr/pgsql-9.4/share/tsearch_data/cmm_thesaurus.ths
ls: cannot access /usr/pgsql-9.4/share/tsearch_data/cmm_thesaurus.ths: No
such file or directory
root@dc0ppgc2n1:PRODUCTION:cestel> ls -lsa
/usr/pgsql-9.3/share/tsearch_data/cmm_thesaurus.ths
4 -rw-r--r-- 1 root root 1998 Mar 10 17:13
/usr/pgsql-9.3/share/tsearch_data/cmm_thesaurus.ths
root@dc0ppgc2n1:PRODUCTION:cestel> cp
/usr/pgsql-9.{3,4}/share/tsearch_data/cmm_thesaurus.ths
root@dc0ppgc2n1:PRODUCTION:cestel> rm -rf /var/lib/pgsql/9.4/data/*
root@dc0ppgc2n1:PRODUCTION:cestel> /usr/pgsql-9.4/bin/postgresql94-setup
initdb
Initializing database ... OK
root@dc0ppgc2n1:PRODUCTION:cestel> su postgres
postgres@dc0ppgc2n1:PRODUCTION:cestel> cd
postgres@dc0ppgc2n1:PRODUCTION:~> /usr/pgsql-9.4/bin/pg_upgrade -b
/usr/pgsql-9.3/bin -B /usr/pgsql-9.4/bin -d /var/lib/pgsql/9.3/data -D
/var/lib/pgsql/9.4/data
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "line" user columns ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Adding support functions to new cluster ok
Restoring database schemas in the new cluster
ok
Creating newly-required TOAST tables ok
Removing support functions from new cluster ok
Copying user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
delete_old_cluster.sh
Re: BUG #13042: pg_upgrade --check succeeded but run failed due to missing thesaurus file
From
Bruce Momjian
Date:
On Mon, Apr 13, 2015 at 03:10:05PM +0000, cestel@covermymeds.com wrote:
> pg_restore: creating FUNCTION plainto_or_tsquery("text")
> pg_restore: creating FUNCTION to_weighted_tsvector("text")
> pg_restore: creating TEXT SEARCH DICTIONARY cmm_thesaurus
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 1745; 3600 60173 TEXT
> SEARCH DICTIONARY cmm_thesaurus postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: could not open
> thesaurus file "/usr/pgsql-9.4/share/tsearch_data/cmm_thesaurus.ths": No
> such file or directory
> Command was: CREATE TEXT SEARCH DICTIONARY "cmm_thesaurus" (
> TEMPLATE = "pg_catalog"."thesaurus",
> dictfile = 'cmm_thesaurus', dic...
Uh, ideally pg_upgrade would be able to detect every possible case of
restore failure, but unfortunately it can't. It does look through
pg_proc and check that all shared object files referenced exist in the
new cluster. What it does not check, as you have seen, is for
dictionary files.
I looked at at how hard it would be to check for text search dictionary
files, and it looks hard. The file specification is stored in an SQL
text string:
test=> SELECT dictinitoption FROM pg_ts_dict;
dictinitoption
------------------------------------------------------------------
dictfile = 'mythesaurus', dictionary = 'pg_catalog.english_stem'
pg_upgrade would need to parse that string, then add a .ths extension to
the name, as specified here:
./backend/tsearch/dict_thesaurus.c:
filename = get_tsearch_config_filename(filename, "ths");
I don't see how this could be done easily, and yours is the first report
of this problem. I am happy that the debugging log files allowed you to
find the cause and complete the upgrade successfully.
I could document this limitation.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
Re: BUG #13042: pg_upgrade --check succeeded but run failed due to missing thesaurus file
From
CJ Estel
Date:
Thanks for looking into this. Updating the documentation would be great.
If it is easy to throw a warning message that thesauri were found and
should be copied to the new location that would help people as well, even
if it doesn't do the actual check for the existence of the files.
On 4/15/15, 9:01 PM, "Bruce Momjian" <bruce@momjian.us> wrote:
>On Mon, Apr 13, 2015 at 03:10:05PM +0000, cestel@covermymeds.com wrote:
>> pg_restore: creating FUNCTION plainto_or_tsquery("text")
>> pg_restore: creating FUNCTION to_weighted_tsvector("text")
>> pg_restore: creating TEXT SEARCH DICTIONARY cmm_thesaurus
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> pg_restore: [archiver (db)] Error from TOC entry 1745; 3600 60173 TEXT
>> SEARCH DICTIONARY cmm_thesaurus postgres
>> pg_restore: [archiver (db)] could not execute query: ERROR: could not
>>open
>> thesaurus file "/usr/pgsql-9.4/share/tsearch_data/cmm_thesaurus.ths": No
>> such file or directory
>> Command was: CREATE TEXT SEARCH DICTIONARY "cmm_thesaurus" (
>> TEMPLATE =3D "pg_catalog"."thesaurus",
>> dictfile =3D 'cmm_thesaurus', dic...
>
>Uh, ideally pg_upgrade would be able to detect every possible case of
>restore failure, but unfortunately it can't. It does look through
>pg_proc and check that all shared object files referenced exist in the
>new cluster. What it does not check, as you have seen, is for
>dictionary files.
>
>I looked at at how hard it would be to check for text search dictionary
>files, and it looks hard. The file specification is stored in an SQL
>text string:
>
>test=3D> SELECT dictinitoption FROM pg_ts_dict;
> dictinitoption
>------------------------------------------------------------------
>
> dictfile =3D 'mythesaurus', dictionary =3D 'pg_catalog.english_stem'
>
>pg_upgrade would need to parse that string, then add a .ths extension to
>the name, as specified here:
>
>./backend/tsearch/dict_thesaurus.c:
>filename =3D get_tsearch_config_filename(filename, "ths");
>
>I don't see how this could be done easily, and yours is the first report
>of this problem. I am happy that the debugging log files allowed you to
>find the cause and complete the upgrade successfully.
>
>I could document this limitation.
>
>--
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + Everyone has their own god. +
This electronic transmission is confidential and intended solely for the ad=
dressee(s). If you are not an intended addressee, do not disclose, copy or =
take any other action in reliance on this transmission. If you have receive=
d this transmission in error, please delete it from your system and notify =
CoverMyMeds LLC at privacy@covermymeds.com. Thank you.
Re: BUG #13042: pg_upgrade --check succeeded but run failed due to missing thesaurus file
From
Bruce Momjian
Date:
On Thu, Apr 16, 2015 at 02:23:54PM +0000, CJ Estel wrote: > Thanks for looking into this. Updating the documentation would be great. > If it is easy to throw a warning message that thesauri were found and > should be copied to the new location that would help people as well, even > if it doesn't do the actual check for the existence of the files. I have developed the attached documentation patch that I have applied to head and 9.4. I looked into issuing some kind of warning from pg_upgrade, but it is hard to do. Not only can you have thesaurus files, but also dictionaries, synonyms, and stop word files. pg_upgrade would have to know how to parse the pg_ts_dict.dictinitoption string, pick out each of these, know the expected file extension, and check for its existence --- That seems too invasive for pg_upgrade to do. One idea would be to write a server-side function that parses that field and returns all referenced file names, and pg_upgrade can call that on the old server then check those on the new server. I am not sure it is worth it, but if someone writes it, I can have pg_upgrade use it. The big problem there is that you have to run that function on the old server, so it is hard to see how that will help with a 9.5 upgrade --- it would only work for upgrades where 9.5 is the _old_ version. Also, we don't have any easy way to backpatch system catalog changes like adding a new function. Another idea I had was to do a directory listing of old cluster's share/tsearch_data and compare that to the new server, but if we ever add files to share/tsearch_data in a major release, pg_upgrade would fail with no way for users to fix it, so that seems bad. Anyway, the doc fix is there and if anyone has better ideas, I am all ears. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +