Enabling pgsql RUM indicies on existing instance

Support
1

Hi all,

Recently I was speaking with other instance operators and one was a single-user instance operator that stated that they find that running pgsql with RUM indices allowed for fast searching.

I thought to give it a try as well, so I tried to upgrade my existing instance to run with RUM indices but when running the migrate command, I get the following error:

su akkoma -s $SHELL -lc "./bin/pleroma_ctl migrate --migrations-path priv/repo/optional_migrations/rum_indexing/"

23:23:50.835 [info] == Running 20190510135645 Pleroma.Repo.Migrations.AddFtsIndexToObjectsTwo.up/0 forward

23:23:50.838 [info] execute "create extension if not exists rum"
** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied to create extension "rum"

    hint: Must be superuser to create this extension.
    (ecto_sql 3.9.1) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.14.2) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.9.1) lib/ecto/adapters/sql.ex:1005: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.9.1) lib/ecto/migration/runner.ex:326: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql 3.9.1) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir 1.14.2) lib/enum.ex:2468: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql 3.9.1) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    nofile:1: (file)

Sounds like there’s a permission error, not sure if it’s wise to run this command as root to get around this. Didn’t want to do the wrong thing here and mess up my Akkoma instance.

Was wondering if anyone knows how best to proceed, any assistance is much appreciated!

Thanks!

2

it’s asking for a postgres superuser, not a Linux one

so you can use the postgres user which should have psql superuser permissions

so:
sudo -Hu postgres psql akkoma
create extension rum with owner akkoma

then try again

3

Hi @FloatingGhost,

Thanks for the reply, much appreciated, I just tried doing in pgsql that but it complains about a syntax error:
image

I also tried without the semicolon at the end but it doesn’t return any value. Once I run the script again, this was the error:

su akkoma -s $SHELL -lc "./bin/pleroma_ctl migrate --migrations-path priv/repo/optional_migrations/rum_indexing/"

15:28:28.207 [info] == Running 20190510135645 Pleroma.Repo.Migrations.AddFtsIndexToObjectsTwo.up/0 forward

15:28:28.211 [info] execute "create extension if not exists rum"
** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied to create extension "rum"

    hint: Must be superuser to create this extension.
    (ecto_sql 3.9.1) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.14.2) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.9.1) lib/ecto/adapters/sql.ex:1005: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.9.1) lib/ecto/migration/runner.ex:326: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql 3.9.1) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir 1.14.2) lib/enum.ex:2468: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql 3.9.1) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    nofile:1: (file)

May you please advise?

Thanks!

4

ah apologies, apparently extensions don’t have an owner

just create extension rum then

2 Likes
5

Please share the results, I am curious.

After reading up on RUM just now I think using these indices requires specific queries, CREATE INDEX tsts_idx ON tsts USING rum…
Does Akkoma automatically use such queries if RUM is enabled?

6

the optional migrations the mentioned in the OP do that: akkoma/20190510135645_add_fts_index_to_objects_two.exs at develop - akkoma - Akkoma Development

1 Like
7

I think it should be

CREATE EXTENSION rum;

if you are running it from the psql shell.