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?
