Support Questions

Find answers, ask questions, and share your expertise

Fails Restoration of embedded PostgreSQL for CM.

avatar
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

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

 

View solution in original post

1 REPLY 1

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