0

We are getting below error while moving system_catalog table to non-default tablepsaces(means in those tablespace which are not assigned as default tablespace for test database

For example:

 test=# select schemaname,tablename,tablespace,tableowner from pg_tables where schemaname='pg_catalog' and tablename='pg_attribute';
 schemaname |  tablename   | tablespace | tableowner
------------+--------------+------------+------------
 pg_catalog | pg_attribute |   tbs1     | pginst13
(1 row)

 

test=# ALTER TABLE pg_catalog.pg_attribute set tablespace pg_default;
ERROR:  permission denied: "pg_attribute" is a system catalog

I am not able to find any information in doc that shows we can't do above.

Thank you for help.

1
  • This sounds like a really strange thing to do. What problem are you trying to solve with that?
    – user1822
    Jun 29, 2021 at 16:31

1 Answer 1

1

To move the catalog tables of a database to another tablespace, move the database to that tablespace:

\c postgres

ALTER DATABASE test SET TABLESPACE pg_default;

If you want to move an individual catalog table, you have to change allow_system_table_mods to on. Then you should be able to use ALTER TABLE on a catalog.

However, I don't think that is a good idea. First, I see no benefit. Second, the change will be lost when you upgrade PostgreSQL, Finally, the documentation for allow_system_table_mods warns you in no uncertain terms:

Ill-advised use of this setting can cause irretrievable data loss or seriously corrupt the database system.

4
  • Hello Laurenz, Thanks for your quick ans. we want to move particular catalog tables not database to another tablespace.
    – Adam Mulla
    Jun 29, 2021 at 14:14
  • Either all catalogs or none of them. Jun 29, 2021 at 14:29
  • Thanks for you ans. if you don't mind may I know official doc link.. actually i want to show to end users they want from PG officials.
    – Adam Mulla
    Jun 29, 2021 at 14:33
  • There actually is a ways that should make it possible, although I don't recommend it. See the updated answer. Jun 29, 2021 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.