Question

Help PostgreSQL restaure.

  • 2 December 2022
  • 4 replies
  • 114 views

Userlevel 1
Badge +6

Dear sirs please your valuable help with the following situation:

I have been trying to restore a postgresql database, the job progresses to 70%, and although I confirm that in the database server if data is being restored, this job does not finish and generates this error.(see image).

 

Thank you for your help 🙏.
Blessings.
Felipe Roxenvinge✌️.


4 replies

Userlevel 3
Badge +6

Hello Felipe,

 

Error description shows create statements and error may be printed later in the same output. Complete error output will be captured to files under Job results directory of commvault install. It will be under this path:  <CV Job Results Directory>/CV_JobResults/2/0/<restore_job_id> 

 

Example in unix client:

/opt/commvault/iDataAgent/jobResults/CV_JobResults/2/0/122153/pg_restore_122153_20867

where 122153 is the restore job id

 

in windows client: 

C:\Program Files\Commvault\ContentStore\iDataAgent\JobResults\CV_JobResults\2\0\3913

where 3913 is the restore job id.

 

Please can you check and share the complete error output? Also, is this a database level restore performed after table level browse? 

Userlevel 1
Badge +6

Hello Meera,
Yes, I am backing up the vicidial database from one server and restoring this database on another server (192.168.87.85).
I attach the results of the job.

 

pg_restore_1811_30884

 

pg_restore: connecting to database for restore

pg_restore: creating DATABASE "vicidial"
pg_restore: connecting to new database "vicidial"
pg_restore: creating SCHEMA "vicidial"
pg_restore: creating TABLE "vicidial.call_log"
pg_restore: creating TABLE "vicidial.vicidial_agent_log"
pg_restore: creating SEQUENCE "vicidial.vicidial_agent_log_agent_log_id_seq"
pg_restore: creating SEQUENCE OWNED BY "vicidial.vicidial_agent_log_agent_log_id_seq"
pg_restore: creating TABLE "vicidial.vicidial_closer_log"
pg_restore: creating SEQUENCE "vicidial.vicidial_closer_log_closecallid_seq"
pg_restore: creating SEQUENCE OWNED BY "vicidial.vicidial_closer_log_closecallid_seq"
pg_restore: creating TABLE "vicidial.vicidial_list"
pg_restore: creating TABLE "vicidial.vicidial_lists"
pg_restore: creating TABLE "vicidial.vicidial_log"
pg_restore: creating DEFAULT "vicidial.vicidial_agent_log agent_log_id"
pg_restore: creating DEFAULT "vicidial.vicidial_closer_log closecallid"
pg_restore: processing data for table "vicidial.call_log"
pg_restore: processing data for table "vicidial.vicidial_agent_log"
pg_restore: processing data for table "vicidial.vicidial_closer_log"
pg_restore: processing data for table "vicidial.vicidial_list"
pg_restore: processing data for table "vicidial.vicidial_lists"
pg_restore: processing data for table "vicidial.vicidial_log"
pg_restore: executing SEQUENCE SET vicidial_agent_log_agent_log_id_seq
pg_restore: executing SEQUENCE SET vicidial_closer_log_closecallid_seq
pg_restore: creating CONSTRAINT "vicidial.call_log call_log_pkey"
pg_restore: creating CONSTRAINT "vicidial.vicidial_agent_log vicidial_agent_log_pkey"
pg_restore: creating CONSTRAINT "vicidial.vicidial_closer_log vicidial_closer_log_pkey"
pg_restore: creating CONSTRAINT "vicidial.vicidial_list vicidial_list_pkey"
pg_restore: creating CONSTRAINT "vicidial.vicidial_lists vicidial_lists_pkey"
pg_restore: creating CONSTRAINT "vicidial.vicidial_log vicidial_log_pkey"
pg_restore: creating ACL "SCHEMA vicidial"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3889; 0 0 ACL SCHEMA vicidial postgres
pg_restore: error: could not execute query: ERROR:  role "vicirep" does not exist
Command was: GRANT ALL ON SCHEMA vicidial TO vicirep;
GRANT ALL ON SCHEMA vicidial TO consulta;


pg_restore: creating ACL "vicidial.TABLE call_log"
pg_restore: from TOC entry 3890; 0 0 ACL TABLE call_log postgres
pg_restore: error: could not execute query: ERROR:  role "vicirep" does not exist
Command was: GRANT SELECT,INSERT,UPDATE ON TABLE vicidial.call_log TO vicirep;
GRANT SELECT ON TABLE vicidial.call_log TO consulta;


pg_restore: creating ACL "vicidial.TABLE vicidial_agent_log"
pg_restore: from TOC entry 3891; 0 0 ACL TABLE vicidial_agent_log postgres
pg_restore: error: could not execute query: ERROR:  role "vicirep" does not exist
Command was: GRANT SELECT,INSERT,UPDATE ON TABLE vicidial.vicidial_agent_log TO vicirep;
GRANT SELECT ON TABLE vicidial.vicidial_agent_log TO consulta;


pg_restore: creating ACL "vicidial.TABLE vicidial_closer_log"
pg_restore: from TOC entry 3893; 0 0 ACL TABLE vicidial_closer_log postgres
pg_restore: error: could not execute query: ERROR:  role "vicirep" does not exist
Command was: GRANT SELECT,INSERT,UPDATE ON TABLE vicidial.vicidial_closer_log TO vicirep;
GRANT SELECT ON TABLE vicidial.vicidial_closer_log TO consulta;


pg_restore: creating ACL "vicidial.TABLE vicidial_list"
pg_restore: from TOC entry 3895; 0 0 ACL TABLE vicidial_list postgres
pg_restore: error: could not execute query: ERROR:  role "vicirep" does not exist
Command was: GRANT SELECT,INSERT,UPDATE ON TABLE vicidial.vicidial_list TO vicirep;
GRANT SELECT ON TABLE vicidial.vicidial_list TO consulta;


pg_restore: creating ACL "vicidial.TABLE vicidial_lists"
pg_restore: from TOC entry 3896; 0 0 ACL TABLE vicidial_lists postgres
pg_restore: error: could not execute query: ERROR:  role "vicirep" does not exist
Command was: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE vicidial.vicidial_lists TO vicirep;
GRANT SELECT ON TABLE vicidial.vicidial_lists TO consulta;


pg_restore: creating ACL "vicidial.TABLE vicidial_log"
pg_restore: from TOC entry 3897; 0 0 ACL TABLE vicidial_log postgres
pg_restore: error: could not execute query: ERROR:  role "vicirep" does not exist
Command was: GRANT SELECT,INSERT,UPDATE ON TABLE vicidial.vicidial_log TO vicirep;
GRANT SELECT ON TABLE vicidial.vicidial_log TO consulta;


pg_restore: warning: errors ignored on restore: 7

Userlevel 7
Badge +19

Hey @FeRox,

From the error provided it seems that required roles are not at the destination server

pg_restore: error: could not execute query: ERROR:  role "vicirep" does not exist

 

Per the documentation, the roles need to exist on the destination before the restore can be done:

For a Dump Based backupset

Roles and tablespaces to which the database or table belongs should preexist.

Source

 

So you would need to create the roles (in this case, vicirep) before the restore can be successful.

Userlevel 1
Badge +6

@Damian Andre @Meera Thank you very much for your help, when I have a response from the client with the observations given by you in addition to restart the activity, I will be updating you ....
Thank you very much for your help. 👍

Reply