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...