Fails Restoration of embedded PostgreSQL for CM.

[#1]

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?

[#2 · Accepted solution]

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.