I'm trying to restore a database after creating some materialized views.
This is the command used for dump: pg_dump --blobs -Fc -f bkp_database_siop.dmp.fc -x siop;
The command used for restore: pg_restore -Fc -C -O -x -v -j 4 --disable-triggers -d postgres bkp_database_siop.dmp.fc;
Here is the error I get:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 12351; 0 3565870 MATERIALIZED VIEW DATA vw_ws_execucaoorcamentaria_mensal_sispes postgres
pg_restore: [archiver (db)] could not execute query: Command was: REFRESH MATERIALIZED VIEW vw_ws_execucaoorcamentaria_mensal_sispes;
pg_restore: finished item 12351 MATERIALIZED VIEW DATA vw_ws_execucaoorcamentaria_mensal_sispes
pg_restore: launching item 9983 CONSTRAINT pk_analiseemenda
pg_restore: [archiver (db)] could not execute query: no connection to the server
Command was: SET statement_timeout = 0;
pg_restore: [archiver (db)] could not execute query: no connection to the server
Command was: SET lock_timeout = 0;
pg_restore: [archiver (db)] could not execute query: no connection to the server
Command was: SET client_encoding = 'UTF8';
pg_restore: [archiver (db)] could not execute query: no connection to the server
Command was: SET standard_conforming_strings = on;
pg_restore: [archiver (db)] could not execute query: no connection to the server
Command was: SET check_function_bodies = false;
pg_restore: [archiver (db)] could not execute query: no connection to the server
Command was: SET client_min_messages = warning;
pg_restore: [archiver (db)] could not execute query: no connection to the server
Command was:
pg_restore: creating CONSTRAINT pk_analiseemenda
pg_restore: [archiver] Error from TOC entry 9983; 2606 18281 CONSTRAINT pk_analiseemenda postgres
pg_restore: [archiver] could not set search_path to "emenda": no connection to the server
pg_restore: [archiver] could not set default_tablespace to "": no connection to the server
pg_restore: [archiver (db)] could not execute query: no connection to the server
Command was: ALTER TABLE ONLY analiseemenda
ADD CONSTRAINT pk_analiseemenda PRIMARY KEY (analiseemendaid);
pg_restore: finished item 9983 CONSTRAINT pk_analiseemenda
pg_restore: launching item 9984 INDEX uk_analiseemenda_01
pg_restore: [archiver (db)] could not execute query: no connection to the server
Command was: SET statement_timeout = 0;
... I've tried restoring the dump with just one job instead of 4. It finished with success, but the total time increased by approximately 2h.
What do I need to do to fix this?
--
Att,
Marcos Paulo Milanêz Santana
Contato: +55 (61) 9111-1430
+55 (61) 8288-6725