PROBLEM:
2019-09-28:13:30:52-database_restore.pm135.sh: .. Restoring database: pe-activity
file=/fslink/sysinfra/puppet/2018.1.9/backup/pm135/database//pe-activity_201909232245.bin
jobs=2
pg_restore: connecting to database for restore
pg_restore: dropping DATABASE pe-activity
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3099; 1262 16399 DATABASE pe-activity pe-postgres
pg_restore: [archiver (db)] could not execute query: ERROR: database "pe-activity" is being accessed by other users
DETAIL: There are 10 other sessions using the database.
Command was: DROP DATABASE "pe-activity";
pg_restore: processing item 3097 ENCODING ENCODING
pg_restore: processing item 3098 STDSTRINGS STDSTRINGS
pg_restore: processing item 3099 DATABASE pe-activity
pg_restore: creating DATABASE "pe-activity"
pg_restore: [archiver (db)] could not execute query: ERROR: database "pe-activity" already exists
Command was: CREATE DATABASE "pe-activity" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8...
pg_restore: connecting to new database "pe-activity"
pg_restore: connecting to database "pe-activity" as user "pe-postgres"
pg_restore: processing item 3 SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: processing item 3100 COMMENT SCHEMA public
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: processing item 1 EXTENSION plpgsql
pg_restore: creating EXTENSION "plpgsql"
pg_restore: processing item 3102 COMMENT EXTENSION plpgsql
pg_restore: creating COMMENT "EXTENSION plpgsql"
pg_restore: processing item 190 FUNCTION backup_activity_events(timestamp with time zone, text)
pg_restore: creating FUNCTION "public.backup_activity_events(timestamp with time zone, text)"
pg_restore: [archiver (db)] Error from TOC entry 190; 1255 17885 FUNCTION backup_activity_events(timestamp with time zone, text) pe-activity
pg_restore: [archiver (db)] could not execute query: ERROR: function "backup_activity_events" already exists with same argument types
Command was: CREATE FUNCTION backup_activity_events(threshold timestamp with time zone, filename text) RETURNS void
LANGUAGE plpgsql
...
pg_restore: processing item 188 TABLE event_commits
pg_restore: creating TABLE "public.event_commits"
pg_restore: [archiver (db)] Error from TOC entry 188; 1259 17711 TABLE event_commits pe-activity
pg_restore: [archiver (db)] could not execute query: ERROR: relation "event_commits" already exists
Command was: CREATE TABLE event_commits (
id character(40) NOT NULL,
service_id text NOT NULL,
subject_digest_id text NOT NUL...
pg_restore: processing item 189 TABLE events
pg_restore: creating TABLE "public.events"
pg_restore: [archiver (db)] Error from TOC entry 189; 1259 17800 TABLE events pe-activity
pg_restore: [archiver (db)] could not execute query: ERROR: relation "events" already exists
Command was: CREATE TABLE events (
id character(40) NOT NULL,
commit_id text NOT NULL,
action_type text NOT NULL,
action_...
pg_restore: processing item 187 TABLE object_digests
pg_restore: creating TABLE "public.object_digests"
pg_restore: [archiver (db)] Error from TOC entry 187; 1259 17660 TABLE object_digests pe-activity
pg_restore: [archiver (db)] could not execute query: ERROR: relation "object_digests" already exists
Command was: CREATE TABLE object_digests (
id character(40) NOT NULL,
object_type text NOT NULL,
object_id text NOT NULL,
...
pg_restore: processing item 185 TABLE schema_migrations
<snip>
SOLTUION:
Cycle all the Puppet operating system services, then try importing the pe-activity database again.
sv=pxp-agent; echo .. $sv; puppet resource service $sv ensure=stopped
sv=puppet; echo .. $sv; puppet resource service $sv ensure=stopped
sv=pe-nginx; echo .. $sv; puppet resource service $sv ensure=stopped
sv=pe-console-services; echo .. $sv; puppet resource service $sv ensure=stopped
sv=pe-puppetserver; echo .. $sv; puppet resource service $sv ensure=stopped
sv=pe-puppetdb; echo .. $sv; puppet resource service $sv ensure=stopped
sv=pe-orchestration-services; echo .. $sv; puppet resource service $sv ensure=stopped
sv=pe-postgresql echo .. $sv; puppet resource service $sv ensure=stopped
sv=mcollective; echo .. $sv; puppet resource service $sv ensure=stopped
sv=pe-activemq; echo .. $sv; puppet resource service $sv ensure=stopped
ps -ef | grep ^[0-9]
pids=$(ps -ef | grep [p]ostgresql | awk '{print $2}')
[[ -z $pids ]] || kill $pids
