Unable to change pg_pathman schema from public

[p2p-mdurbha]

Problem description

Hello, I am trying to refresh a prod database to test using pg_dump/pg_restore. Unfortunately, my pg_restore fails with "...already exists" error for pg_pathman objects. So I am trying to move the pg_pathman extension from public to a different schema (so that I can exclude it in the pg_dump).
However, my query fails with the error below -

postgres@mdtest2=# alter extension pg_pathman set schema extensions; ERROR: extension "pg_pathman" does not support SET SCHEMA

How can I accomplish this? I am concerned about dropping and re-creating pg_pathman extension, as I have several partitioned tables in this Production environment.

Can you please help?

1. Is there an easier way to exclude pg_pathman objects from pg_dump or pg_restore?
2. If not, how can I safely move pg_pathman to a different schema, so that I can exclude that schema from pg_dump?

Thanks

Environment

emr_prod=# select * from pg_extension; extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition --------------------+----------+--------------+----------------+------------+-------------------+-------------- plpgsql | 10 | 11 | f | 1.0 | | adminpack | 10 | 11 | f | 1.0 | | ltree | 10 | 2200 | t | 1.0 | | pg_buffercache | 10 | 2200 | t | 1.1 | | pg_pathman | 10 | 2200 | f | 1.4 | {8738685,8738694} | {"",""} pg_stat_statements | 10 | 2200 | t | 1.3 | | pg_trgm | 10 | 2200 | t | 1.1 | | pgcrypto | 10 | 2200 | t | 1.2 | | (8 rows)

emr_prod=# select version(); version

PostgreSQL 9.6.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit

emr_prod=# SELECT pgpro_version(); ERROR: function pgpro_version() does not exist

emr_prod=# SELECT get_pathman_lib_version(); get_pathman_lib_version

10409 (1 row)

[funbringer]

Hi @p2p-mdurbha,

Unfortunately, my pg_restore fails with "...already exists" error for pg_pathman objects.

Could you show the error messages? I'm sure that your problem is negligible. We have tests that dump/restore the database using pg_dump, and everything seems to work fine.

So I am trying to move the pg_pathman extension from public to a different schema

This is not supported. Current design implies that pg_pathman's objects should not be moved after the extension has been created.

[p2p-mdurbha]

Here are the errors. I realize these are non-critical errors, but these errors cause post-refresh steps to not execute. So I have to address them.

pg_restore: [archiver (db)] could not execute query: ERROR: event trigger "pathman_ddl_trigger" already exists Command was: CREATE EVENT TRIGGER pathman_ddl_trigger ON sql_drop EXECUTE PROCEDURE public.pathman_ddl_trigger_func();

pg_restore: [archiver (db)] could not execute query: ERROR: policy "allow_select" for table "pathman_config" already exists Command was: CREATE POLICY allow_select ON pathman_config FOR SELECT TO PUBLIC USING (true);

[funbringer]

I suspect you're using Ansible, right? In that case you could hide non-zero exit code, for example:

pg_restore ... || true

[p2p-mdurbha]

I cannot hide errors, incase there are genuine errors that need to be addressed. I tried to manually drop the trigger, so that the pg_restore would create it but that fails with the error below.

postgres@mdtest2=# drop event trigger pathman_ddl_trigger;
ERROR:  cannot drop event trigger pathman_ddl_trigger because extension pg_pathman requires it
HINT:  You can drop extension pg_pathman instead.

Is there a way to drop this trigger, or just assign the pg_pathman plugin to another schema?

Thanks very much.

[funbringer]

I cannot hide errors, incase there are genuine errors that need to be addressed.

True, but in a sense it's a genuine error. You see, the problem is that PostgreSQL doesn't understand that our row level security rule (RLS) and event trigger belong to pg_pathman. That's why CREATE EVENT TRIGGER and CREATE POLICY statements are emitted by pg_dump.

It's a true error, but it's not important. You have to solve it somehow, though. Unfortunately, I haven't heard of a nicer way to do this.

By the way, what are you trying to achieve by dumping/restoring the DB?

Is there a way to drop this trigger, or just assign the pg_pathman plugin to another schema?

No and no. You can't change pg_pathman's schema, and you can't separately drop its trigger.

[p2p-mdurbha]

What would be the impact of dropping the pg_pathman extension and re-creating it under a new schema on Production? Would I lose all the partitioning information that currently exists? Does it cause any data corruption or data loss?

[zilder]

@p2p-mdurbha Do you have only range-partitioned tables? If so, then you should be able to drop extension and then recreate it on new database and then add all partitioned tables to pathman_config via add_to_pathman_config() function. But try it on test database first.

[alexmsu75]

@funbringer, just want to clarify if I understood you correctly: if I installed pg_pathman into public schema on server1 and took a backup to restore into another server (server2) without any errors I must manually remove pg_pathman on server2 before initiate the restore, otherwise restore will fail with errors mentioned above.

[funbringer]

@alexmsu75,

I guess you got it wrong. What I said means that pg_restore will always complain about these objects if you dump whole DB with pg_pathman installed. The reason is that pg_pathman creates RLS policies in its install script, and pg_dump emits duplicate create statements as if it didn't understand that create extension is responsible for that.

I'm going to investigate pg_dump's source code and send a patch to pgsql-hackers. Stay tuned.

[funbringer]

@alexmsu75, @p2p-mdurbha

My research has led me to the following conclusion: you can safely drop policies before dumping your DB, since they'll be restored by the create extension statement.

There's no point in writing a patch to solve original issue with RLS policies, though. It just so happens that tables which belong to extensions are not protected from potentially harmful actions, e.g. drop index, alter table add column etc. IMHO the patch should aim to fix those shortcomings too, or else it would be mostly useless.