Admin Search Engine Problem (Wagtail 4, Postgres 42.2.22)

[Diego Portillo]

I have this issue related to the pages search, on the admin side of wagtail. This is the Django Error:

ProgrammingError at /admin/pages/search/ function ts_rank(unknown, text, tsquery) does not exist LINE 1: ...ry"."body") @@ (to_tsquery('''irp''')) ORDER BY ((ts_rank('{... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

When i debug the wagtail code, the pages query result returns this:

Unable to get repr for <class 'wagtail.search.backends.database.postgres.postgres.PostgresSearchResults'>

After looking at wagtail 4 documentation, there's no info about the postgres search engine. Only in documentation up to 2.15 version. And this:

WAGTAILSEARCH_BACKENDS = { "default": { "BACKEND": "wagtail.search.backends.database", }

}

...should work with Postrgres from then on.

Is there any configuration/upgrade I missed to make wagtails admin search work with postgres?

[Diego Portillo]

Already solved, thankyou!

Here's the solution response on Slack:

mattwestcott:apuntando_hacia_la_derecha:  hace 1 hora

42.2.22 isn’t a Postgres version number - I think that’s the version of your Java JDBC driver. Could you check the actual Postgres version? You can do that by entering: select version(); at the psql command line.

Diego Portillo  hace 1 hora

Sorry, you're right! This is the version: PostgreSQL 12.12

mattwestcott:apuntando_hacia_la_derecha:  hace 1 hora

Just to check - you have wagtail.search listed in INSTALLED_APPS and you’ve run ./manage.py migrate to ensure your database is up to date?

Diego Portillo  hace 1 hora

ok, I'll try!

Diego Portillo  hace 1 hora

wagtail.search is in the list and database is up to date

Diego Portillo  hace 1 hora

An important detail is that the database was originally in sqlite and i migrated to Postgres with pgloader

Diego Portillo  hace 1 hora

Maybe there was an error in the migration? Like data types cast/conversion?

mattwestcott:apuntando_hacia_la_derecha:  hace 1 hora

That'll be the problem - the migrations for the wagtail.search app have different steps depending on the database backend. So, if you started out on sqlite, it would have installed the search functions for sqlite - and then when you migrated that to postgres, it would be missing the postgres search functions. And the migration history in the database will still record that you've run all migrations, so it thinks there's nothing to do.

mattwestcott:apuntando_hacia_la_derecha:  hace 1 hora

You can probably fix that problem by running

./manage.py migrate wagtailsearch 0004 ./manage.py migrate wagtailsearch

but it's possible that there are similar problems elsewhere in the database setup.

Diego Portillo  hace 1 hora

ok, the project is a small new project with not much data. I can start over. If i erase the data base tables and migration files and  then rerun the makemigrations -> migrate commands, can it work?

mattwestcott:apuntando_hacia_la_derecha:  hace 1 hora

don't delete the migration files - that'll create more problems

Diego Portillo  hace 1 hora

ok

mattwestcott:apuntando_hacia_la_derecha:  hace 1 hora

If you're happy to start over with a fresh database, drop and recreate the database with dropdb databasename / createdb databasename then run ./manage.py migrate

Diego Portillo  hace 41 minutos

ok, I'll try!

Diego Portillo  hace 25 minutos

Ok, now it works! Thank you!
Finally, is there a recommended way to migrate instead of using pgloader?

mattwestcott:apuntando_hacia_la_derecha:  hace 11 minutos

Glad you got it working! If you can, please post a follow-up to Stackoverflow and the Google group so that anyone searching on there in future will find the answer too.

mattwestcott:apuntando_hacia_la_derecha:  hace 7 minutos

Not sure about the best way of doing a SQLite to Postgres migration… ideally it would be something that just copies the data over after you’ve run migrate on the new database. Django’s dumpdata/loaddata commands might do it, but you’ll need to take care to get the command-line options right - maybe someone’s written that up in a blog post somewhere.

Diego Portillo  hace 4 minutos

Ok, great! Thank you. I'll post the follow up.