CODEWITHSUNDEEP
postgresqlpg-restore
Ben
Ben

Reputation: 1301

postgresql: pg_restore error with "DETAIL: System catalog modifications are currently disallowed."

I am trying to transfer my dev database on laptop to a workstation. Its giving me all sorts of errors, I am not sure how to correctly restore this db dump. without -1 it does finish, but with a lot of objects missing.

The database name on target machine is different from the db I dumped from. But I can't see how could be related to the errors. Totally new on this dump/restore thing. I can't see any thing about system catalog in pg_restore -l I am out of clues... 

D:\db>pg_restore -d checkpoint -U postgres --disable-triggers -x -O -
1 -v -n temp dump-1.backup
pg_restore: connecting to database for restore
Password:
pg_restore: creating TYPE "temp.my_agg"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1037; 1247 89882 TYPE my_agg po
stgres
pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied t
o create "pg_catalog.my_agg"
DETAIL:  System catalog modifications are currently disallowed.
    Command was: CREATE TYPE my_agg AS (
        ecnt integer,
        eids integer
);

dropping -n temp seems to get rid of the priviledge problem. but still more errors coming

pg_restore: creating FUNCTION "public.cmptime(timestamp without time zone, text,
 timestamp without time zone, text, text)"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 355; 1255 60129 FUNCTION cmptim
e(timestamp without time zone, text, timestamp without time zone, text, text) po
stgres
pg_restore: [archiver (db)] could not execute query: ERROR:  language "plv8" doe
s not exist
    Command was: CREATE FUNCTION cmptime(t0 timestamp without time zone, s0 text
, t1 timestamp without time zone, s1 text, rcode text) RETURN...

after manually created plv8 and plpython extension. those language erros are gone. I am having issue with this FDW table. 

pg_restore: creating FOREIGN TABLE "public.t1"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 204; 1259 25543 FOREIGN TABLE r
edrawing postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  server "myserver1"
does not exist
    Command was: CREATE FOREIGN TABLE t1 (
    fid bigint,
    pd_date text,
    team text,
    shift text,
    line text,
    opr1 te...

I tried to create the FDW extension. but FDW depends on public schema. dropping -c --if-exists pg_restore option, recreateing checkpoint database before each restore attempt.

pg_restore: [archiver (db)] could not execute query: ERROR:  server "myserver1"
does not exist
    Command was: CREATE FOREIGN TABLE t1 (
    fid bigint,
    pd_date text,
    team text,
    shift text,
    line text,
    opr1 te...

still having problem with FDW. has to create each FDW servers, after each new FDW error encountered (I don't remember them all). Loop this process untill all FDW server erros are gone (is this really how error-free dababase restore supposed to be done .... ?). next error

pg_restore: [archiver (db)] could not execute query: ERROR:  server "myserver1"
does not exist
    Command was: CREATE FOREIGN TABLE t1 (
    fid bigint,
    pd_date text,
    team text,
    shift text,
    line text,
    opr1 te...

I have materialized views to cache plpython functions results. but some of their logic is depricated, can't be easily fixed.

I guess my error-free restoration ends here. have to remove -1 single transaction mode. Total error ignored down to 6 from 55. Looks like most objects are back.

Is there any way to negelect the materalized view refresh process...

Upvotes: 3

Views: 2542

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45910

The error messages looks strange - but if understand well, the PostgreSQL try to restore custom data type in schema public what should be prohibited - please, try to dump schema only in SQL format and ensure, so there are not directly modified pg_catalogue scheme.

Extensions should be installed first - the extension installation has two parts 1: install rpm / compiled codes to database server, 2: extension registration per database. Only second step should be done by backup.

The error message pg_restore: [archiver (db)] could not execute query: ERROR: server "myserver1" does not exist is clean - probably somewhere the statement CREATE SERVER fails - probably due missing extension on server.

Upvotes: 1

Related Questions