Created on 03-11-2019 02:08 AM - edited 03-11-2019 02:14 AM
Hi.
I am using Embedded PostgreSQL for CM.
Backed up as described in the document below.
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?
Created on 03-11-2019 08:10 PM - edited 03-11-2019 08:13 PM
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.
Created on 03-11-2019 08:10 PM - edited 03-11-2019 08:13 PM
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.