migration-assist: postgres post-migrate gives "could not find the default schema "public" in search_path, consider setting it from the postgresql console"

[ylluminate]

After moving through migration steps and successfully loading information via pgloader-main migration.load, I'm hitting an issue that is unclear as to whether it is a problem or not since it doesn't directly state any errors are occurring @agnivade @isacikgoz:

$ ./migration-assist postgres post-migrate "postgres://mmuser_main:REDACTED@192.168.1.93:5432/mattermost_main"
2025-02-01 18:36:39 could not find the default schema "public" in search_path, consider setting it from the postgresql console
2025-02-01 18:36:39 search_path is set to "public" for the currrent session
2025-02-01 18:36:39 running migrations..
2025-02-01 18:36:39 index creation skipped.

You'll notice that it is having problems with the public schema and then it says the search_path seems to be using it in the next line... 😵‍💫

So, I want to understand what I'm facing here and if this is presenting a problem with the migratory process.

To be clear, this is how I'm creating the database - which I think is well and good, but you can correct me if I'm wrong (note how I'm using a user of mmuser_main and database of mattermost_main instead of the normal names since I plan to have a couple mattermost databases in this PostgreSQL server):

export PGPASSWORD='REDACTED'
psql -h 192.168.1.93 -U postgres -c "DROP DATABASE IF EXISTS mattermost_main;" && \
psql -h 192.168.1.93 -U postgres -c "DROP ROLE IF EXISTS mmuser_main;" && \
psql -h 192.168.1.93 -U postgres -c "CREATE ROLE mmuser_main WITH LOGIN ENCRYPTED PASSWORD 'REDACTED2';" && \
psql -h 192.168.1.93 -U postgres -c "CREATE DATABASE mattermost_main WITH OWNER mmuser_main ENCODING 'UTF8' LC_COLLATE='en_US.utf8' LC_CTYPE='en_US.utf8' TEMPLATE=template0;" && \
psql -h 192.168.1.93 -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE mattermost_main TO mmuser_main;" && \
psql -h 192.168.1.93 -U postgres -c "ALTER ROLE mmuser_main SET search_path = public;" && \
psql -h 192.168.1.93 -U postgres -d mattermost_main -c "ALTER SCHEMA public OWNER TO mmuser_main; SELECT schema_name, schema_owner FROM information_schema.schemata WHERE schema_name = 'public';"

As far as I can tell things should be alright with this sanity check:

$ PGPASSWORD='REDACTED2' psql -h 192.168.1.93 -U mmuser_main -d mattermost_main
psql (17.2 (Ubuntu 17.2-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

mattermost_main=> select current_database();
 current_database
------------------
 mattermost_main
(1 row)

mattermost_main=> select current_schema();
 current_schema
----------------
 public
(1 row)

mattermost_main=> show search_path;
 search_path
-------------
 public
(1 row)

mattermost_main=> ALTER user mmuser_main in database mattermost_main set search_path to 'public';
ALTER ROLE
mattermost_main=> quit

Any thoughts?

[jelmd]

You probably already ran the pgloader migration.load, which screws up the search path dueto double quoting. This change gets made to the pg_catalog.pg_db_role_setting, so dropping the db ... does not change anything.
Probably a ALTER ROLE mmuser_main RESET search_path fixes your problem.

BTW: I'm not a postgresql expert (just preparing our migration to PG), but according to the documentation the OWNER of an object (like DATABASE) has already ALL PRIVILEGES, so GRANT to 'mmuser_main' is a noop. However, IMHO mmuser_main does not need GRANT or DROP privileges for the DATABASE/PUBLIC schema, and therefore it does not need to be the OWNER of the DATABASE and PUBLIC schema. Unfortunately, the pgloader renames the PUBLIC schema and wants to alter the search_path of the DATABASE, so temporarily at least setting the PUBLIC schema owner to 'mmuser_main' is required.

Here e.g. what I do:

psql -U ${DBA:-postgres}
CREATE USER mmuser_main WITH PASSWORD 'whatever';
CREATE DATABASE mattermost_main;
GRANT ALL PRIVILEGES ON DATABASE mattermost_main TO mmuser_main;
REVOKE CONNECT,TEMPORARY ON DATABASE mattermost_main FROM public;
ALTER ROLE mmuser_main RESET search_path;
\c mattermost_main
ALTER SCHEMA public OWNER TO mmuser_main;
SELECT setting FROM pg_settings WHERE name='hba_file';
\q

# adjust 'hba_file' file shown in the last SELECT above and restart the cluster
# adjust firewall rules of the zone if needed.

migration-assist postgres <DSN>  --mattermost-version=8.1.2 --run-migrations 
migration-assist pgloader --mysql='<MY_DSN>' --postgres='<PG_DSN>' \
	>migration.load
sed -i.orig -e '/search_path/ d' -e '/SEARCH_PATH/ d' -e '/TO public/ s/,$/;/' \
	migration.load
pgloader migration.load | tee migration.log

psql -U ${DBA:-postgres} mattermost_main
ALTER SCHEMA public OWNER TO CURRENT_USER;
GRANT ALL PRIVILEGES ON SCHEMA public TO mmuser_main;
\l+ mattermost_main
\dn+

SET ROLE mmuser_main;
DROP INDEX IF EXISTS idx_fileinfo_content_txt;
CREATE INDEX idx_fileinfo_content_txt ON public.fileinfo USING gin(to_tsvector('english', content)) WHERE extension NOT SIMILAR TO 'csv|drawio|ipynb';
SELECT tablename, indexname, indexdef FROM pg_indexes WHERE tablename SIMILAR TO 'fileinfo|posts' ORDER BY tablename, indexname;
\q

migration-assist postgres post-migrate <PG_DSN>

# Complete plugin migrations
...

# Optional cleanup
rm -rf postgres pgloader mysql.output migration.load migration.log

[isacikgoz]

@ylluminate I think the reason that migraiton-assist not being able locate default schema as public is the result of SHOW SEARCH_PATH query. AFAIK it should return in the form of "$user", public. And the logic is specifically checking that pattern. But in your output I see it as only public.

@jelmd Can you elaborate the double quote issue? Is it an error in our configuration, if so please let me understand the issue so that I can fix it.

[jelmd]

@isacikgoz Yepp, np: The AFTER LOAD DO section looks per default like this:

AFTER LOAD DO
    $$ UPDATE mattermost.db_migrations set name='add_createat_to_teamembers' where version=92; $$,
    $$ ALTER SCHEMA mattermost RENAME TO public; $$,
    $$ SELECT pg_catalog.set_config('search_path', '"$user", "$user", public', false); $$,
    $$ ALTER USER mmd SET SEARCH_PATH TO '"$user", public'; $$;

Now run

$ psql -U ${DBA:-postgres} -c "select rolconfig from pg_roles where rolname='mmd';
 rolconfig 
-----------
(0 rows)

$ pgloader migration.load | tee migration.log
...
2025-02-07T17:28:43.004000Z LOG pgloader version "3.6.3~devel"
2025-02-07T17:28:43.004000Z LOG Data errors in '/tmp/pgloader/'
2025-02-07T17:28:43.004000Z LOG Parsing commands from file #P"/tmp/migration.load"
2025-02-07T17:28:43.076002Z LOG Migrating from #<MYSQL-CONNECTION ...>
2025-02-07T17:28:43.076002Z LOG Migrating into #<PGSQL-CONNECTION ...>
2025-02-07T17:28:43.432011Z WARNING Source column "mattermost"."Channels"."type" is casted to type "channel_type" which is not the same as "mattermost.channel_type", the type of current target database column "mattermost"."channels"."type".
2025-02-07T17:28:43.432011Z WARNING Source column "mattermost"."RetentionIdsForDeletion"."ids" is casted to type "jsonb" which is not the same as "character varying[]", the type of current target database column "mattermost"."retentionidsfordeletion"."ids".
2025-02-07T17:28:43.432011Z WARNING Source column "mattermost"."Teams"."type" is casted to type "team_type" which is not the same as "mattermost.team_type", the type of current target database column "mattermost"."teams"."type".
2025-02-07T17:28:43.432011Z WARNING Source column "mattermost"."UploadSessions"."type" is casted to type "upload_session_type" which is not the same as "mattermost.upload_session_type", the type of current target database column "mattermost"."uploadsessions"."type".
2025-02-07T17:28:43.440012Z WARNING PostgreSQL warning: constraint "fk_retentionpolicieschannels_retentionpolicies" of relation "retentionpolicieschannels" does not exist, skipping
2025-02-07T17:28:43.440012Z WARNING PostgreSQL warning: constraint "fk_retentionpoliciesteams_retentionpolicies" of relation "retentionpoliciesteams" does not exist, skipping
2025-02-07T17:29:11.440725Z LOG report summary reset
...

$ psql -U ${DBA:-postgres} -c "select rolconfig from pg_roles where rolname='mmd';" 
                 rolconfig                 
-------------------------------------------
 {"search_path=\"\"\"$user\"\", public\""}
(1 row)

$ psql -U mmd -d mattermost -c "show search_path;"
Password for user mmd: 
     search_path     
---------------------
 """$user"", public"
(1 row)

$ psql -U ${DBA:-postgres} -c "ALTER ROLE mmd RESET search_path;"
ALTER ROLE

$ psql -U ${DBA:-postgres} -c "select rolconfig from pg_roles where rolname='mmd';" 
 rolconfig                 
-----------
(0 rows)

$ psql -U mmd -d mattermost -c "show search_path;"
Password for user mmd: 
 search_path 
-------------
 mattermost
(1 row)

Because in this run I haven't modified the migration.load and made mmd the owner of the DB, the search path of the DB got screwed up as well and needs to be reset, too. Fix with:

$ psql -U ${DBA:-postgres} -c "ALTER DATABASE mattermost RESET search_path;"
ALTER DATABASE

$ psql -U mmd -d mattermost -c "show search_path;"
Password for user mmd: 
   search_path   
-----------------
 "$user", public
(1 row)

[H-B-Schmidt]

I'm having the same problem. What is the official solution for this?

2025-05-16 07:28:52 could not find the default schema "public" in search_path, consider setting it from the postgresql console
2025-05-16 07:28:52 search_path is set to "public" for the currrent session
2025-05-16 07:28:52 running migrations..
2025-05-16 07:28:52 index creation skipped.

[GuillaumeV-cemea]

Hello,
I had the same issue migrating a 9.11.15 instance from mysql 10.3 to postgres 13, all running docker.
To create the index, I had to add to run the following commands :

SELECT pg_catalog.set_config('search_path', '"$user", public', false);
ALTER USER mattermost SET SEARCH_PATH TO 'public';

[H-B-Schmidt]

Hello, I had the same issue migrating a 9.11.15 instance from mysql 10.3 to postgres 13, all running docker. To create the index, I had to add to run the following commands :

SELECT pg_catalog.set_config('search_path', '"$user", public', false);
ALTER USER mattermost SET SEARCH_PATH TO 'public';

Thanks for trying to help. Sadly this did not make a difference for me.
(Mattemost 10.5.5, Postgres 17.5, Mysql 8.0.42)

These steps are performed by pgloader if you look at the end of migration.load.

AFTER LOAD DO
    $$ UPDATE mattermost.db_migrations set name='add_createat_to_teamembers' where version=92; $$,
    $$ ALTER SCHEMA mattermost RENAME TO public; $$,
    $$ SELECT pg_catalog.set_config('search_path', '"$user", public', false); $$,
    $$ ALTER USER mmuser SET SEARCH_PATH TO 'public'; $$;

[GuillaumeV-cemea]

My migration.load looks like this, so it's not the same commands :

AFTER LOAD DO
    $$ UPDATE mattermost.db_migrations set name='add_createat_to_teamembers' where version=92; $$,
    $$ ALTER SCHEMA mattermost RENAME TO public; $$,
    $$ SELECT pg_catalog.set_config('search_path', '"$user", "$user", public', false); $$,
    $$ ALTER USER mattermost SET SEARCH_PATH TO '"$user", public'; $$;

It was generated like this :

migration-assist pgloader --mysql="mattermost:XXX@tcp(localhost:3306)/mattermost" --postgres="postgres://mattermost:XXX@localhost:5432/mattermost?sslmode=disable" > migration.load

[H-B-Schmidt]

I did use the latest migration-assist 0.4. Perhaps that was different to yours?

Btw the correct call to create an index is
./migration-assist postgres post-migrate --create-indexes POSTGRES_DSN

The documentation is bad.

[D33M0N]

You probably ran into this problem I also discovered, that you can not use single quotes for DSN encapsulation. Double quotes are like hard must currently:
#50

And also if your password or username contains any symbols at all (which might have caused you to try single quotes in the first place), it's hard recommendation to change it to just letters and numbers for the migration process and then maybe later change it to something secure.

[ewwollesen]

This was root-caused and fixed by #58 (merged today, will ship in the next release after v0.7).

Quick recap of the chain:

1. Pre-fix, the AFTER LOAD DO block in internal/pgloader/templates/config.tmpl emitted '"$user", "$user", public' for set_config and wrapped the ALTER USER value in single quotes. With .SearchPath populated from SHOW search_path ("$user", public on a default install), this produced a duplicated "$user" and a string-literal ALTER USER value that PostgreSQL persisted as a single mangled identifier.
2. Once mmuser's persistent search_path was mangled, migration-assist postgres post-migrate couldn't find public in it — exactly the error you saw.

End-to-end verified against postgres:16:

• Before Fix duplicated "$user" and string-literal quoting in pgloader search_path #58: SHOW search_path after the ALTER → """$user"", public" (single mangled identifier), \dt finds nothing, post-migrate complains about missing public.
• After Fix duplicated "$user" and string-literal quoting in pgloader search_path #58: SHOW search_path → "$user", public (two items, correct), \dt lists tables, post-migrate runs cleanly.

If you re-run the migration with a build off main (or wait for v0.8) you should not see this error again. Closing as resolved — please reopen if it recurs.