Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Fails Restoration of embedded PostgreSQL for CM.

SOLVED Go to solution

Fails Restoration of embedded PostgreSQL for CM.

Explorer

Hi.

 

I am using Embedded PostgreSQL for CM.

 

Backed up as described in the document below.

 

https://www.cloudera.com/documentation/enterprise/5-16-x/topics/cm_ag_backup_dbs.html#cmig_topic_5_6...

pg_dump -h hostname -p 7432 -U scm > /tmp/scm_server_db_backup.$(date +%Y%m%d)

 

But trying to restore fails.

 

[root@localhost ~]$ psql -w -h localhost -p 7432 -U scm -f /tmp/scm_server_db_backup.$(date +%Y%m%d)



psql:scm_server_db_backup:54: ERROR: relation "audits" already exists ALTER TABLE psql:scm_server_db_backup:77: ERROR: relation "client_configs" already exists ALTER TABLE psql:scm_server_db_backup:89: ERROR: relation "client_configs_to_hosts" already exists ALTER TABLE . . . psql:scm_server_db_backup:1036: ERROR: duplicate key value violates unique constraint "audits_pkey" DETAIL: Key (audit_id)=(1) already exists. CONTEXT: COPY audits, line 1 psql:scm_server_db_backup:1056: ERROR: duplicate key value violates unique constraint "client_configs_pkey" DETAIL: Key (client_config_id)=(1) already exists. CONTEXT: COPY client_configs, line 1 psql:scm_server_db_backup:1093: ERROR: duplicate key value violates unique constraint "cluster_activated_releases_pkey" DETAIL: Key (cluster_id, release_id)=(1, 1) already exists. . . . psql:scm_server_db_backup:30181: ERROR: multiple primary keys for table "audits" are not allowed psql:scm_server_db_backup:30189: ERROR: multiple primary keys for table "client_configs" are not allowed psql:scm_server_db_backup:30197: ERROR: multiple primary keys for table "cluster_activated_releases_aud" are not allowed . . .

 

For the time being, I attempted to execute restoration after dropping all DB.

 

drop_all.sql

drop database "postgres";
drop database "scm";
drop database "amon";
drop database "rman";
drop database "nav";
drop database "navms";

 

But it gets an error.

[root@localhost ~]$ psql -U cloudera-scm -p 7432 -h localhost -d postgres -f drop_all.sql
Password for user cloudera-scm:



psql:drop_all.sql:1: ERROR: cannot drop the currently open database
psql:drop_all.sql:2: ERROR: database "scm" is being accessed by other users
DETAIL: There are 11 other sessions using the database.

 

I tried to drop the process killing of scm database, but the process will be restored while killing.

 

How can I restore Embedded PostgreSQL?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Fails Restoration of embedded PostgreSQL for CM.

Explorer

Sorry, I resolved by myself.

By performing backup with the --clean option, restoration was successful.


backup

[root@localhost ~]$ pg_dump -w -h localhost -p 7432 -U scm --clean > /tmp/scm_server_db_backup.$(date +%Y%m%d)

 

restore

[root@localhost ~]$ psql -w -h localhost -p 7432 -U scm -f /tmp/scm_server_db_backup.$(date +%Y%m%d)

 

The above restore was successful.

 

1 REPLY 1
Highlighted

Re: Fails Restoration of embedded PostgreSQL for CM.

Explorer

Sorry, I resolved by myself.

By performing backup with the --clean option, restoration was successful.


backup

[root@localhost ~]$ pg_dump -w -h localhost -p 7432 -U scm --clean > /tmp/scm_server_db_backup.$(date +%Y%m%d)

 

restore

[root@localhost ~]$ psql -w -h localhost -p 7432 -U scm -f /tmp/scm_server_db_backup.$(date +%Y%m%d)

 

The above restore was successful.