Project

General

Profile

Actions

Bug #22313

closed

Problem in Server backup and migration documentation

Added by Lars Koenen about 1 year ago. Updated 10 months ago.

Status:
Released
Priority:
N/A
Category:
Documentation
Target version:
Severity:
Minor - inconvenience | misleading | easy workaround
UX impact:
It bothers me each time
User visibility:
Operational - other Techniques | Rudder settings | Plugins
Effort required:
Priority:
43
Name check:
To do
Fix check:
Checked
Regression:
No

Description

I followed the step-by-step instructions given in the "Server backup and migration" section of the documentation, to migrate a rudder-server (v6.2.12) to a new system. (In preparation for a planned upgrade to v7.2.3).

While restoring the PostgreSQL backup, pg_restore generated a long list of errors.

root@rudder:~/rudder_backup_2023_01_25__14_14_22# sudo -u postgres pg_restore -d rudder --clean --create < rudder-backup.sql
could not change directory to "/root/rudder_backup_2023_01_25__14_14_22": Permission denied
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3211; 1262 16385 DATABASE rudder rudder
pg_restore: error: could not execute query: ERROR:  cannot drop the currently open database
Command was: DROP DATABASE rudder;
pg_restore: error: could not execute query: ERROR:  database "rudder" already exists
Command was: CREATE DATABASE rudder WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

pg_restore: from TOC entry 3; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;

pg_restore: from TOC entry 204; 1259 16478 TABLE archivednodecompliance rudder
pg_restore: error: could not execute query: ERROR:  relation "archivednodecompliance" already exists
Command was: CREATE TABLE public.archivednodecompliance (
    nodeid text NOT NULL,
    runtimestamp timestamp with time zone NOT NULL,
    endoflife timestamp with time zone,
    runanalysis text NOT NULL,
    summary text NOT NULL,
    details text NOT NULL,
    CONSTRAINT archivednodecompliance_details_check CHECK ((details <> ''::text)),
    CONSTRAINT archivednodecompliance_nodeid_check CHECK ((nodeid <> ''::text)),
    CONSTRAINT archivednodecompliance_runanalysis_check CHECK ((runanalysis <> ''::text)),
    CONSTRAINT archivednodecompliance_summary_check CHECK ((summary <> ''::text))
)
WITH (autovacuum_vacuum_threshold='0', autovacuum_vacuum_scale_factor='0.1');

pg_restore: from TOC entry 202; 1259 16452 TABLE archivednodeconfigurations rudder
pg_restore: error: could not execute query: ERROR:  relation "archivednodeconfigurations" already exists
Command was: CREATE TABLE public.archivednodeconfigurations (
    nodeid text NOT NULL,
    nodeconfigid text NOT NULL,
    begindate timestamp with time zone NOT NULL,
    enddate timestamp with time zone,
    configuration text NOT NULL,
    CONSTRAINT archivednodeconfigurations_configuration_check CHECK ((configuration <> ''::text)),
    CONSTRAINT archivednodeconfigurations_nodeconfigid_check CHECK ((nodeconfigid <> ''::text)),
    CONSTRAINT archivednodeconfigurations_nodeid_check CHECK ((nodeid <> ''::text))
)
WITH (autovacuum_vacuum_threshold='0');

pg_restore: from TOC entry 198; 1259 16406 TABLE archivedruddersysevents rudder
pg_restore: error: could not execute query: ERROR:  relation "archivedruddersysevents" already exists
Command was: CREATE TABLE public.archivedruddersysevents (
    id bigint NOT NULL,
    executiondate timestamp with time zone NOT NULL,
    nodeid text NOT NULL,
    directiveid text NOT NULL,
    ruleid text NOT NULL,
    serial integer NOT NULL,
    component text NOT NULL,
    keyvalue text,
    executiontimestamp timestamp with time zone NOT NULL,
    eventtype text,
    policy text,
    msg text,
    CONSTRAINT archivedruddersysevents_component_check CHECK ((component <> ''::text)),
    CONSTRAINT archivedruddersysevents_directiveid_check CHECK ((directiveid <> ''::text)),
    CONSTRAINT archivedruddersysevents_nodeid_check CHECK ((nodeid <> ''::text)),
    CONSTRAINT archivedruddersysevents_ruleid_check CHECK ((ruleid <> ''::text))
)
WITH (autovacuum_vacuum_scale_factor='0.005');

pg_restore: from TOC entry 212; 1259 16561 SEQUENCE changerequestid rudder
pg_restore: error: could not execute query: ERROR:  relation "changerequestid" already exists
Command was: CREATE SEQUENCE public.changerequestid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

pg_restore: from TOC entry 213; 1259 16563 TABLE changerequest rudder
pg_restore: error: could not execute query: ERROR:  relation "changerequest" already exists
Command was: CREATE TABLE public.changerequest (
    id integer DEFAULT nextval('public.changerequestid'::regclass) NOT NULL,
    name text,
    description text,
    creationtime timestamp with time zone,
    content xml,
    modificationid text,
    CONSTRAINT changerequest_name_check CHECK ((name <> ''::text))
);

pg_restore: from TOC entry 219; 1259 16617 SEQUENCE directivesid rudder
pg_restore: error: could not execute query: ERROR:  relation "directivesid" already exists
Command was: CREATE SEQUENCE public.directivesid
    START WITH 101
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

pg_restore: from TOC entry 220; 1259 16619 TABLE directives rudder
pg_restore: error: could not execute query: ERROR:  relation "directives" already exists
Command was: CREATE TABLE public.directives (
    id integer DEFAULT nextval('public.directivesid'::regclass) NOT NULL,
    directiveid text NOT NULL,
    directivename text,
    directivedescription text,
    priority integer NOT NULL,
    techniquename text,
    techniqueversion text,
    techniquedescription text,
    techniquehumanname text,
    starttime timestamp with time zone NOT NULL,
    endtime timestamp with time zone,
    CONSTRAINT directives_directiveid_check CHECK ((directiveid <> ''::text))
)
WITH (autovacuum_vacuum_scale_factor='0.05');

pg_restore: from TOC entry 206; 1259 16519 SEQUENCE eventlogidseq rudder
pg_restore: error: could not execute query: ERROR:  relation "eventlogidseq" already exists
Command was: CREATE SEQUENCE public.eventlogidseq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

pg_restore: from TOC entry 207; 1259 16521 TABLE eventlog rudder
pg_restore: error: could not execute query: ERROR:  relation "eventlog" already exists
Command was: CREATE TABLE public.eventlog (
    id integer DEFAULT nextval('public.eventlogidseq'::regclass) NOT NULL,
    creationdate timestamp with time zone DEFAULT '2021-01-19 11:58:08.191082+01'::timestamp with time zone NOT NULL,
    severity integer,
    causeid integer,
    modificationid text,
    principal text,
    reason text,
    eventtype text,
    data xml
);

pg_restore: from TOC entry 211; 1259 16553 TABLE gitcommit rudder
pg_restore: error: could not execute query: ERROR:  relation "gitcommit" already exists
Command was: CREATE TABLE public.gitcommit (
    gitcommit text NOT NULL,
    modificationid text
);

pg_restore: from TOC entry 227; 1259 16677 SEQUENCE globalscheduleid rudder
pg_restore: error: could not execute query: ERROR:  relation "globalscheduleid" already exists
Command was: CREATE SEQUENCE public.globalscheduleid
    START WITH 101
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

pg_restore: from TOC entry 228; 1259 16679 TABLE globalschedule rudder
pg_restore: error: could not execute query: ERROR:  relation "globalschedule" already exists
Command was: CREATE TABLE public.globalschedule (
    id integer DEFAULT nextval('public.globalscheduleid'::regclass) NOT NULL,
    "interval" integer,
    splaytime integer,
    start_hour integer,
    start_minute integer,
    starttime timestamp with time zone DEFAULT now(),
    endtime timestamp with time zone
);

pg_restore: from TOC entry 216; 1259 16592 SEQUENCE groupsid rudder
pg_restore: error: could not execute query: ERROR:  relation "groupsid" already exists
Command was: CREATE SEQUENCE public.groupsid
    START WITH 101
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

pg_restore: from TOC entry 217; 1259 16594 TABLE groups rudder
pg_restore: error: could not execute query: ERROR:  relation "groups" already exists
Command was: CREATE TABLE public.groups (
    id integer DEFAULT nextval('public.groupsid'::regclass) NOT NULL,
    groupid text NOT NULL,
    groupname text,
    groupdescription text,
    nodecount integer,
    groupstatus integer DEFAULT 2,
    starttime timestamp with time zone DEFAULT now(),
    endtime timestamp with time zone,
    CONSTRAINT groups_groupid_check CHECK ((groupid <> ''::text))
)
WITH (autovacuum_vacuum_scale_factor='0.05');

pg_restore: from TOC entry 218; 1259 16606 TABLE groupsnodesjoin rudder
pg_restore: error: could not execute query: ERROR:  relation "groupsnodesjoin" already exists
Command was: CREATE TABLE public.groupsnodesjoin (
    grouppkeyid integer NOT NULL,
    nodeid text NOT NULL,
    CONSTRAINT groupsnodesjoin_nodeid_check CHECK ((nodeid <> ''::text))
);

pg_restore: from TOC entry 208; 1259 16534 SEQUENCE migrationeventlogid rudder
pg_restore: error: could not execute query: ERROR:  relation "migrationeventlogid" already exists
Command was: CREATE SEQUENCE public.migrationeventlogid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

pg_restore: from TOC entry 209; 1259 16536 TABLE migrationeventlog rudder
pg_restore: error: could not execute query: ERROR:  relation "migrationeventlog" already exists
Command was: CREATE TABLE public.migrationeventlog (
    id integer DEFAULT nextval('public.migrationeventlogid'::regclass) NOT NULL,
    detectiontime timestamp with time zone NOT NULL,
    detectedfileformat integer,
    migrationstarttime timestamp with time zone,
    migrationendtime timestamp with time zone,
    migrationfileformat integer,
    description text
);

pg_restore: from TOC entry 203; 1259 16463 TABLE nodecompliance rudder
pg_restore: error: could not execute query: ERROR:  relation "nodecompliance" already exists
Command was: CREATE TABLE public.nodecompliance (
    nodeid text NOT NULL,
    runtimestamp timestamp with time zone NOT NULL,
    endoflife timestamp with time zone,
    runanalysis text NOT NULL,
    summary text NOT NULL,
    details text NOT NULL,
    CONSTRAINT nodecompliance_details_check CHECK ((details <> ''::text)),
    CONSTRAINT nodecompliance_nodeid_check CHECK ((nodeid <> ''::text)),
    CONSTRAINT nodecompliance_runanalysis_check CHECK ((runanalysis <> ''::text)),
    CONSTRAINT nodecompliance_summary_check CHECK ((summary <> ''::text))
)
WITH (autovacuum_vacuum_threshold='0', autovacuum_vacuum_scale_factor='0.1');

pg_restore: from TOC entry 205; 1259 16490 TABLE nodecompliancelevels rudder
pg_restore: error: could not execute query: ERROR:  relation "nodecompliancelevels" already exists
Command was: CREATE TABLE public.nodecompliancelevels (
    nodeid text NOT NULL,
    runtimestamp timestamp with time zone NOT NULL,
    ruleid text NOT NULL,
    directiveid text NOT NULL,
    pending integer DEFAULT 0,
    success integer DEFAULT 0,
    repaired integer DEFAULT 0,
    error integer DEFAULT 0,
    unexpected integer DEFAULT 0,
    missing integer DEFAULT 0,
    noanswer integer DEFAULT 0,
    notapplicable integer DEFAULT 0,
    reportsdisabled integer DEFAULT 0,
    compliant integer DEFAULT 0,
    auditnotapplicable integer DEFAULT 0,
    noncompliant integer DEFAULT 0,
    auditerror integer DEFAULT 0,
    badpolicymode integer DEFAULT 0,
    CONSTRAINT nodecompliancelevels_directiveid_check CHECK ((directiveid <> ''::text)),
    CONSTRAINT nodecompliancelevels_nodeid_check CHECK ((nodeid <> ''::text)),
    CONSTRAINT nodecompliancelevels_ruleid_check CHECK ((ruleid <> ''::text))
)
WITH (autovacuum_vacuum_scale_factor='0.05');

pg_restore: from TOC entry 201; 1259 16439 TABLE nodeconfigurations rudder
pg_restore: error: could not execute query: ERROR:  relation "nodeconfigurations" already exists
Command was: CREATE TABLE public.nodeconfigurations (
    nodeid text NOT NULL,
    nodeconfigid text NOT NULL,
    begindate timestamp with time zone NOT NULL,
    enddate timestamp with time zone,
    configuration text NOT NULL,
    CONSTRAINT nodeconfigurations_configuration_check CHECK ((configuration <> ''::text)),
    CONSTRAINT nodeconfigurations_nodeconfigid_check CHECK ((nodeconfigid <> ''::text)),
    CONSTRAINT nodeconfigurations_nodeid_check CHECK ((nodeid <> ''::text))
)
WITH (autovacuum_vacuum_threshold='0');

pg_restore: from TOC entry 225; 1259 16662 SEQUENCE nodesid rudder
pg_restore: error: could not execute query: ERROR:  relation "nodesid" already exists
Command was: CREATE SEQUENCE public.nodesid
    START WITH 101
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

pg_restore: from TOC entry 226; 1259 16664 TABLE nodes rudder
pg_restore: error: could not execute query: ERROR:  relation "nodes" already exists
Command was: CREATE TABLE public.nodes (
    id integer DEFAULT nextval('public.nodesid'::regclass) NOT NULL,
    nodeid text NOT NULL,
    nodename text,
    nodedescription text,
    starttime timestamp with time zone DEFAULT now(),
    endtime timestamp with time zone,
    CONSTRAINT nodes_nodeid_check CHECK ((nodeid <> ''::text))
)
WITH (autovacuum_vacuum_scale_factor='0.05');

pg_restore: from TOC entry 200; 1259 16430 TABLE nodes_info rudder
pg_restore: error: could not execute query: ERROR:  relation "nodes_info" already exists
Command was: CREATE TABLE public.nodes_info (
    node_id text NOT NULL,
    config_ids text,
    CONSTRAINT nodes_info_node_id_check CHECK ((node_id <> ''::text))
)
WITH (autovacuum_vacuum_threshold='0');

pg_restore: from TOC entry 199; 1259 16419 TABLE reportsexecution rudder
pg_restore: error: could not execute query: ERROR:  relation "reportsexecution" already exists
Command was: CREATE TABLE public.reportsexecution (
    nodeid text NOT NULL,
    date timestamp with time zone NOT NULL,
    complete boolean NOT NULL,
    nodeconfigid text,
    insertionid bigint
)
WITH (autovacuum_vacuum_scale_factor='0.05');

pg_restore: from TOC entry 210; 1259 16545 TABLE rudderproperties rudder
pg_restore: error: could not execute query: ERROR:  relation "rudderproperties" already exists
Command was: CREATE TABLE public.rudderproperties (
    name text NOT NULL,
    value text
)
WITH (autovacuum_vacuum_threshold='0');

pg_restore: from TOC entry 196; 1259 16386 SEQUENCE serial rudder
pg_restore: error: could not execute query: ERROR:  relation "serial" already exists
Command was: CREATE SEQUENCE public.serial
    START WITH 101
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

pg_restore: from TOC entry 197; 1259 16388 TABLE ruddersysevents rudder
pg_restore: error: could not execute query: ERROR:  relation "ruddersysevents" already exists
Command was: CREATE TABLE public.ruddersysevents (
    id bigint DEFAULT nextval('public.serial'::regclass) NOT NULL,
    executiondate timestamp with time zone NOT NULL,
    nodeid text NOT NULL,
    directiveid text NOT NULL,
    ruleid text NOT NULL,
    serial integer NOT NULL,
    component text NOT NULL,
    keyvalue text,
    executiontimestamp timestamp with time zone NOT NULL,
    eventtype text,
    policy text,
    msg text,
    CONSTRAINT ruddersysevents_component_check CHECK ((component <> ''::text)),
    CONSTRAINT ruddersysevents_directiveid_check CHECK ((directiveid <> ''::text)),
    CONSTRAINT ruddersysevents_nodeid_check CHECK ((nodeid <> ''::text)),
    CONSTRAINT ruddersysevents_ruleid_check CHECK ((ruleid <> ''::text))
);

pg_restore: from TOC entry 221; 1259 16631 SEQUENCE rulesid rudder
pg_restore: error: could not execute query: ERROR:  relation "rulesid" already exists
Command was: CREATE SEQUENCE public.rulesid
    START WITH 101
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

pg_restore: from TOC entry 222; 1259 16633 TABLE rules rudder
pg_restore: error: could not execute query: ERROR:  relation "rules" already exists
Command was: CREATE TABLE public.rules (
    rulepkeyid integer DEFAULT nextval('public.rulesid'::regclass) NOT NULL,
    ruleid text NOT NULL,
    serial integer DEFAULT 0,
    categoryid text,
    name text,
    shortdescription text,
    longdescription text,
    isenabled boolean,
    starttime timestamp with time zone NOT NULL,
    endtime timestamp with time zone,
    CONSTRAINT rules_ruleid_check CHECK ((ruleid <> ''::text))
)
WITH (autovacuum_vacuum_scale_factor='0.05');

pg_restore: from TOC entry 224; 1259 16651 TABLE rulesdirectivesjoin rudder
pg_restore: error: could not execute query: ERROR:  relation "rulesdirectivesjoin" already exists
Command was: CREATE TABLE public.rulesdirectivesjoin (
    rulepkeyid integer NOT NULL,
    directiveid text NOT NULL,
    CONSTRAINT rulesdirectivesjoin_directiveid_check CHECK ((directiveid <> ''::text))
);

pg_restore: from TOC entry 223; 1259 16644 TABLE rulesgroupjoin rudder
pg_restore: error: could not execute query: ERROR:  relation "rulesgroupjoin" already exists
Command was: CREATE TABLE public.rulesgroupjoin (
    rulepkeyid integer,
    targetserialisation text
);

pg_restore: from TOC entry 215; 1259 16584 TABLE statusupdate rudder
pg_restore: error: could not execute query: ERROR:  relation "statusupdate" already exists
Command was: CREATE TABLE public.statusupdate (
    key text NOT NULL,
    lastid bigint NOT NULL,
    date timestamp with time zone NOT NULL
)
WITH (autovacuum_vacuum_threshold='0');

pg_restore: from TOC entry 214; 1259 16573 TABLE workflow rudder
pg_restore: error: could not execute query: ERROR:  relation "workflow" already exists
Command was: CREATE TABLE public.workflow (
    id integer,
    state text
);

pg_restore: from TOC entry 3197; 0 16619 TABLE DATA directives rudder
pg_restore: error: COPY failed for table "directives": ERROR:  duplicate key value violates unique constraint "directives_pkey" 
DETAIL:  Key (id)=(101) already exists.
CONTEXT:  COPY directives, line 1
pg_restore: from TOC entry 3184; 0 16521 TABLE DATA eventlog rudder
pg_restore: error: COPY failed for table "eventlog": ERROR:  duplicate key value violates unique constraint "eventlog_pkey" 
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY eventlog, line 1
pg_restore: from TOC entry 3205; 0 16679 TABLE DATA globalschedule rudder
pg_restore: error: COPY failed for table "globalschedule": ERROR:  duplicate key value violates unique constraint "globalschedule_pkey" 
DETAIL:  Key (id)=(101) already exists.
CONTEXT:  COPY globalschedule, line 1
pg_restore: from TOC entry 3194; 0 16594 TABLE DATA groups rudder
pg_restore: error: COPY failed for table "groups": ERROR:  duplicate key value violates unique constraint "groups_pkey" 
DETAIL:  Key (id)=(101) already exists.
CONTEXT:  COPY groups, line 3
pg_restore: from TOC entry 3177; 0 16430 TABLE DATA nodes_info rudder
pg_restore: error: COPY failed for table "nodes_info": ERROR:  duplicate key value violates unique constraint "nodes_info_pkey" 
DETAIL:  Key (node_id)=(root) already exists.
CONTEXT:  COPY nodes_info, line 246
pg_restore: from TOC entry 3187; 0 16545 TABLE DATA rudderproperties rudder
pg_restore: error: COPY failed for table "rudderproperties": ERROR:  duplicate key value violates unique constraint "rudderproperties_pkey" 
DETAIL:  Key (name)=(reportLoggerLastId) already exists.
CONTEXT:  COPY rudderproperties, line 1
pg_restore: from TOC entry 3199; 0 16633 TABLE DATA rules rudder
pg_restore: error: COPY failed for table "rules": ERROR:  duplicate key value violates unique constraint "rules_pkey" 
DETAIL:  Key (rulepkeyid)=(101) already exists.
CONTEXT:  COPY rules, line 1
pg_restore: from TOC entry 3201; 0 16651 TABLE DATA rulesdirectivesjoin rudder
pg_restore: error: COPY failed for table "rulesdirectivesjoin": ERROR:  duplicate key value violates unique constraint "rulesdirectivesjoin_pkey" 
DETAIL:  Key (rulepkeyid, directiveid)=(101, inventory-all) already exists.
CONTEXT:  COPY rulesdirectivesjoin, line 1
pg_restore: from TOC entry 3192; 0 16584 TABLE DATA statusupdate rudder
pg_restore: error: COPY failed for table "statusupdate": ERROR:  duplicate key value violates unique constraint "statusupdate_pkey" 
DETAIL:  Key (key)=(executionStatus) already exists.
CONTEXT:  COPY statusupdate, line 1
pg_restore: from TOC entry 3006; 2606 16489 CONSTRAINT archivednodecompliance archivednodecompliance_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "archivednodecompliance" are not allowed
Command was: ALTER TABLE ONLY public.archivednodecompliance
    ADD CONSTRAINT archivednodecompliance_pkey PRIMARY KEY (nodeid, runtimestamp);

pg_restore: from TOC entry 2999; 2606 16462 CONSTRAINT archivednodeconfigurations archivednodeconfigurations_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "archivednodeconfigurations" are not allowed
Command was: ALTER TABLE ONLY public.archivednodeconfigurations
    ADD CONSTRAINT archivednodeconfigurations_pkey PRIMARY KEY (nodeid, nodeconfigid, begindate);

pg_restore: from TOC entry 2985; 2606 16417 CONSTRAINT archivedruddersysevents archivedruddersysevents_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "archivedruddersysevents" are not allowed
Command was: ALTER TABLE ONLY public.archivedruddersysevents
    ADD CONSTRAINT archivedruddersysevents_pkey PRIMARY KEY (id);

pg_restore: from TOC entry 3025; 2606 16572 CONSTRAINT changerequest changerequest_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "changerequest" are not allowed
Command was: ALTER TABLE ONLY public.changerequest
    ADD CONSTRAINT changerequest_pkey PRIMARY KEY (id);

pg_restore: from TOC entry 3037; 2606 16628 CONSTRAINT directives directives_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "directives" are not allowed
Command was: ALTER TABLE ONLY public.directives
    ADD CONSTRAINT directives_pkey PRIMARY KEY (id);

pg_restore: from TOC entry 3016; 2606 16530 CONSTRAINT eventlog eventlog_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "eventlog" are not allowed
Command was: ALTER TABLE ONLY public.eventlog
    ADD CONSTRAINT eventlog_pkey PRIMARY KEY (id);

pg_restore: from TOC entry 3023; 2606 16560 CONSTRAINT gitcommit gitcommit_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "gitcommit" are not allowed
Command was: ALTER TABLE ONLY public.gitcommit
    ADD CONSTRAINT gitcommit_pkey PRIMARY KEY (gitcommit);

pg_restore: from TOC entry 3050; 2606 16685 CONSTRAINT globalschedule globalschedule_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "globalschedule" are not allowed
Command was: ALTER TABLE ONLY public.globalschedule
    ADD CONSTRAINT globalschedule_pkey PRIMARY KEY (id);

pg_restore: from TOC entry 3031; 2606 16605 CONSTRAINT groups groups_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "groups" are not allowed
Command was: ALTER TABLE ONLY public.groups
    ADD CONSTRAINT groups_pkey PRIMARY KEY (id);

pg_restore: from TOC entry 3033; 2606 16614 CONSTRAINT groupsnodesjoin groupsnodesjoin_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "groupsnodesjoin" are not allowed
Command was: ALTER TABLE ONLY public.groupsnodesjoin
    ADD CONSTRAINT groupsnodesjoin_pkey PRIMARY KEY (grouppkeyid, nodeid);

pg_restore: from TOC entry 3019; 2606 16544 CONSTRAINT migrationeventlog migrationeventlog_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "migrationeventlog" are not allowed
Command was: ALTER TABLE ONLY public.migrationeventlog
    ADD CONSTRAINT migrationeventlog_pkey PRIMARY KEY (id);

pg_restore: from TOC entry 3003; 2606 16474 CONSTRAINT nodecompliance nodecompliance_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "nodecompliance" are not allowed
Command was: ALTER TABLE ONLY public.nodecompliance
    ADD CONSTRAINT nodecompliance_pkey PRIMARY KEY (nodeid, runtimestamp);

pg_restore: from TOC entry 3010; 2606 16514 CONSTRAINT nodecompliancelevels nodecompliancelevels_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "nodecompliancelevels" are not allowed
Command was: ALTER TABLE ONLY public.nodecompliancelevels
    ADD CONSTRAINT nodecompliancelevels_pkey PRIMARY KEY (nodeid, runtimestamp, ruleid, directiveid);

pg_restore: from TOC entry 2997; 2606 16449 CONSTRAINT nodeconfigurations nodeconfigurations_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "nodeconfigurations" are not allowed
Command was: ALTER TABLE ONLY public.nodeconfigurations
    ADD CONSTRAINT nodeconfigurations_pkey PRIMARY KEY (nodeid, nodeconfigid, begindate);

pg_restore: from TOC entry 2993; 2606 16438 CONSTRAINT nodes_info nodes_info_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "nodes_info" are not allowed
Command was: ALTER TABLE ONLY public.nodes_info
    ADD CONSTRAINT nodes_info_pkey PRIMARY KEY (node_id);

pg_restore: from TOC entry 3048; 2606 16674 CONSTRAINT nodes nodes_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "nodes" are not allowed
Command was: ALTER TABLE ONLY public.nodes
    ADD CONSTRAINT nodes_pkey PRIMARY KEY (id);

pg_restore: from TOC entry 2991; 2606 16426 CONSTRAINT reportsexecution reportsexecution_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "reportsexecution" are not allowed
Command was: ALTER TABLE ONLY public.reportsexecution
    ADD CONSTRAINT reportsexecution_pkey PRIMARY KEY (nodeid, date);

pg_restore: from TOC entry 3021; 2606 16552 CONSTRAINT rudderproperties rudderproperties_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "rudderproperties" are not allowed
Command was: ALTER TABLE ONLY public.rudderproperties
    ADD CONSTRAINT rudderproperties_pkey PRIMARY KEY (name);

pg_restore: from TOC entry 2982; 2606 16400 CONSTRAINT ruddersysevents ruddersysevents_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "ruddersysevents" are not allowed
Command was: ALTER TABLE ONLY public.ruddersysevents
    ADD CONSTRAINT ruddersysevents_pkey PRIMARY KEY (id);

pg_restore: from TOC entry 3041; 2606 16643 CONSTRAINT rules rules_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "rules" are not allowed
Command was: ALTER TABLE ONLY public.rules
    ADD CONSTRAINT rules_pkey PRIMARY KEY (rulepkeyid);

pg_restore: from TOC entry 3044; 2606 16659 CONSTRAINT rulesdirectivesjoin rulesdirectivesjoin_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "rulesdirectivesjoin" are not allowed
Command was: ALTER TABLE ONLY public.rulesdirectivesjoin
    ADD CONSTRAINT rulesdirectivesjoin_pkey PRIMARY KEY (rulepkeyid, directiveid);

pg_restore: from TOC entry 3027; 2606 16591 CONSTRAINT statusupdate statusupdate_pkey rudder
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "statusupdate" are not allowed
Command was: ALTER TABLE ONLY public.statusupdate
    ADD CONSTRAINT statusupdate_pkey PRIMARY KEY (key);

pg_restore: from TOC entry 2977; 1259 16405 INDEX changes_executiontimestamp_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "changes_executiontimestamp_idx" already exists
Command was: CREATE INDEX changes_executiontimestamp_idx ON public.ruddersysevents USING btree (executiontimestamp) WHERE (eventtype = 'result_repaired'::text);

pg_restore: from TOC entry 2978; 1259 16402 INDEX composite_node_execution_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "composite_node_execution_idx" already exists
Command was: CREATE INDEX composite_node_execution_idx ON public.ruddersysevents USING btree (nodeid, executiontimestamp);

pg_restore: from TOC entry 3013; 1259 16532 INDEX creationdate_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "creationdate_idx" already exists
Command was: CREATE INDEX creationdate_idx ON public.eventlog USING btree (creationdate);

pg_restore: from TOC entry 3034; 1259 16630 INDEX directive_end rudder
pg_restore: error: could not execute query: ERROR:  relation "directive_end" already exists
Command was: CREATE INDEX directive_end ON public.directives USING btree (endtime);

pg_restore: from TOC entry 3035; 1259 16629 INDEX directive_id_start rudder
pg_restore: error: could not execute query: ERROR:  relation "directive_id_start" already exists
Command was: CREATE INDEX directive_id_start ON public.directives USING btree (directiveid, starttime);

pg_restore: from TOC entry 2979; 1259 16404 INDEX endrun_control_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "endrun_control_idx" already exists
Command was: CREATE INDEX endrun_control_idx ON public.ruddersysevents USING btree (id) WHERE ((eventtype = 'control'::text) AND (component = 'end'::text));

pg_restore: from TOC entry 3014; 1259 16533 INDEX eventlog_fileformat_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "eventlog_fileformat_idx" already exists
Command was: CREATE INDEX eventlog_fileformat_idx ON public.eventlog USING btree ((((xpath('/entry//@fileFormat'::text, data))[1])::text));

pg_restore: from TOC entry 3017; 1259 16531 INDEX eventtype_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "eventtype_idx" already exists
Command was: CREATE INDEX eventtype_idx ON public.eventlog USING btree (eventtype);

pg_restore: from TOC entry 2986; 1259 16418 INDEX executiontimestamp_archived_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "executiontimestamp_archived_idx" already exists
Command was: CREATE INDEX executiontimestamp_archived_idx ON public.archivedruddersysevents USING btree (executiontimestamp);

pg_restore: from TOC entry 2980; 1259 16401 INDEX executiontimestamp_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "executiontimestamp_idx" already exists
Command was: CREATE INDEX executiontimestamp_idx ON public.ruddersysevents USING btree (executiontimestamp);

pg_restore: from TOC entry 3028; 1259 16616 INDEX groups_end rudder
pg_restore: error: could not execute query: ERROR:  relation "groups_end" already exists
Command was: CREATE INDEX groups_end ON public.groups USING btree (endtime);

pg_restore: from TOC entry 3029; 1259 16615 INDEX groups_id_start rudder
pg_restore: error: could not execute query: ERROR:  relation "groups_id_start" already exists
Command was: CREATE INDEX groups_id_start ON public.groups USING btree (groupid, starttime);

pg_restore: from TOC entry 3000; 1259 16477 INDEX nodecompliance_endoflife rudder
pg_restore: error: could not execute query: ERROR:  relation "nodecompliance_endoflife" already exists
Command was: CREATE INDEX nodecompliance_endoflife ON public.nodecompliance USING btree (endoflife);

pg_restore: from TOC entry 3001; 1259 16475 INDEX nodecompliance_nodeid rudder
pg_restore: error: could not execute query: ERROR:  relation "nodecompliance_nodeid" already exists
Command was: CREATE INDEX nodecompliance_nodeid ON public.nodecompliance USING btree (nodeid);

pg_restore: from TOC entry 3004; 1259 16476 INDEX nodecompliance_runtimestamp rudder
pg_restore: error: could not execute query: ERROR:  relation "nodecompliance_runtimestamp" already exists
Command was: CREATE INDEX nodecompliance_runtimestamp ON public.nodecompliance USING btree (runtimestamp);

pg_restore: from TOC entry 3007; 1259 16517 INDEX nodecompliancelevels_directiveid_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "nodecompliancelevels_directiveid_idx" already exists
Command was: CREATE INDEX nodecompliancelevels_directiveid_idx ON public.nodecompliancelevels USING btree (directiveid);

pg_restore: from TOC entry 3008; 1259 16515 INDEX nodecompliancelevels_nodeid rudder
pg_restore: error: could not execute query: ERROR:  relation "nodecompliancelevels_nodeid" already exists
Command was: CREATE INDEX nodecompliancelevels_nodeid ON public.nodecompliancelevels USING btree (nodeid);

pg_restore: from TOC entry 3011; 1259 16516 INDEX nodecompliancelevels_ruleid_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "nodecompliancelevels_ruleid_idx" already exists
Command was: CREATE INDEX nodecompliancelevels_ruleid_idx ON public.nodecompliancelevels USING btree (ruleid);

pg_restore: from TOC entry 3012; 1259 16518 INDEX nodecompliancelevels_runtimestamp rudder
pg_restore: error: could not execute query: ERROR:  relation "nodecompliancelevels_runtimestamp" already exists
Command was: CREATE INDEX nodecompliancelevels_runtimestamp ON public.nodecompliancelevels USING btree (runtimestamp);

pg_restore: from TOC entry 2994; 1259 16451 INDEX nodeconfigurations_nodeconfigid rudder
pg_restore: error: could not execute query: ERROR:  relation "nodeconfigurations_nodeconfigid" already exists
Command was: CREATE INDEX nodeconfigurations_nodeconfigid ON public.nodeconfigurations USING btree (nodeconfigid);

pg_restore: from TOC entry 2995; 1259 16450 INDEX nodeconfigurations_nodeid rudder
pg_restore: error: could not execute query: ERROR:  relation "nodeconfigurations_nodeid" already exists
Command was: CREATE INDEX nodeconfigurations_nodeid ON public.nodeconfigurations USING btree (nodeid);

pg_restore: from TOC entry 3045; 1259 16676 INDEX nodes_end rudder
pg_restore: error: could not execute query: ERROR:  relation "nodes_end" already exists
Command was: CREATE INDEX nodes_end ON public.nodes USING btree (endtime);

pg_restore: from TOC entry 3046; 1259 16675 INDEX nodes_id_start rudder
pg_restore: error: could not execute query: ERROR:  relation "nodes_id_start" already exists
Command was: CREATE INDEX nodes_id_start ON public.nodes USING btree (nodeid, starttime);

pg_restore: from TOC entry 2987; 1259 16427 INDEX reportsexecution_date_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "reportsexecution_date_idx" already exists
Command was: CREATE INDEX reportsexecution_date_idx ON public.reportsexecution USING btree (date);

pg_restore: from TOC entry 2988; 1259 16428 INDEX reportsexecution_insertionid_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "reportsexecution_insertionid_idx" already exists
Command was: CREATE INDEX reportsexecution_insertionid_idx ON public.reportsexecution USING btree (insertionid);

pg_restore: from TOC entry 2989; 1259 16429 INDEX reportsexecution_nodeid_nodeconfigid_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "reportsexecution_nodeid_nodeconfigid_idx" already exists
Command was: CREATE INDEX reportsexecution_nodeid_nodeconfigid_idx ON public.reportsexecution USING btree (nodeid, nodeconfigid);

pg_restore: from TOC entry 3038; 1259 16661 INDEX rule_end rudder
pg_restore: error: could not execute query: ERROR:  relation "rule_end" already exists
Command was: CREATE INDEX rule_end ON public.rules USING btree (endtime);

pg_restore: from TOC entry 3039; 1259 16660 INDEX rule_id_start rudder
pg_restore: error: could not execute query: ERROR:  relation "rule_id_start" already exists
Command was: CREATE INDEX rule_id_start ON public.rules USING btree (ruleid, starttime);

pg_restore: from TOC entry 3042; 1259 16650 INDEX rulegroupjoin_id_group rudder
pg_restore: error: could not execute query: ERROR:  relation "rulegroupjoin_id_group" already exists
Command was: CREATE INDEX rulegroupjoin_id_group ON public.rulesgroupjoin USING btree (rulepkeyid);

pg_restore: from TOC entry 2983; 1259 16403 INDEX ruleid_idx rudder
pg_restore: error: could not execute query: ERROR:  relation "ruleid_idx" already exists
Command was: CREATE INDEX ruleid_idx ON public.ruddersysevents USING btree (ruleid);

pg_restore: from TOC entry 3051; 2606 16579 FK CONSTRAINT workflow workflow_id_fkey rudder
pg_restore: error: could not execute query: ERROR:  constraint "workflow_id_fkey" for relation "workflow" already exists
Command was: ALTER TABLE ONLY public.workflow
    ADD CONSTRAINT workflow_id_fkey FOREIGN KEY (id) REFERENCES public.changerequest(id);

pg_restore: warning: errors ignored on restore: 98

The problem is basically that the database was not deleted correctly with the commands listed in the guide.
Which of course causes the re-creation to fail.
Restoring the data seems to have worked normally.

I have used the instructions for 6.2:
https://docs.rudder.io/history/6.2/reference/6.2/administration/procedures.html#_restore

Since the PostgreSQL related commands in the documentation for 7.2 are identical, I suspect that the recovery in the newer versions will also show errors during the re-import.
https://docs.rudder.io/reference/7.2/administration/procedures.html#_restore


Subtasks 1 (0 open1 closed)

Bug #22583: Typo in parent ticketReleasedAlexis MoussetActions
Actions #1

Updated by Alexis Mousset about 1 year ago

  • Assignee set to Nicolas CHARLES
Actions #2

Updated by Alexander Brunhirl about 1 year ago

As a workaround you can drop and (re)create the database before the restore

linux:~ # sudo -u postgres psql -c "DROP DATABASE rudder;" 
could not change directory to "/root": Permission denied
DROP DATABASE
linux:~ # sudo -u postgres psql -c "CREATE DATABASE rudder WITH OWNER = rudder;" 
could not change directory to "/root": Permission denied
CREATE DATABASE
linux:~ # sudo -u postgres psql -c "\l" 
could not change directory to "/root": Permission denied
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rudder    | rudder   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

linux:~ # sudo -u postgres pg_restore -d rudder --clean --create < /root/rudder-backup.sql
could not change directory to "/root": Permission denied
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3966; 1262 16385 DATABASE rudder rudder
pg_restore: error: could not execute query: ERROR:  cannot drop the currently open database
Command was: DROP DATABASE rudder;
pg_restore: error: could not execute query: ERROR:  database "rudder" already exists
Command was: CREATE DATABASE rudder WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

pg_restore: from TOC entry 3; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;

pg_restore: warning: errors ignored on restore: 3
linux:~ # 
Actions #3

Updated by Nicolas CHARLES 11 months ago

  • Target version changed from old 6.2 issues to relocate to 7.2.6
Actions #4

Updated by Nicolas CHARLES 11 months ago

  • Status changed from New to In progress
Actions #5

Updated by Nicolas CHARLES 11 months ago

  • Status changed from In progress to Pending technical review
  • Assignee changed from Nicolas CHARLES to Alexis Mousset
  • Pull Request set to https://github.com/Normation/rudder-doc/pull/970
Actions #6

Updated by Nicolas CHARLES 11 months ago

  • Status changed from Pending technical review to Pending release
Actions #7

Updated by Nicolas CHARLES 11 months ago

  • Subtask #22583 added
Actions #8

Updated by Vincent MEMBRÉ 10 months ago

  • Priority changed from 44 to 43
  • Fix check changed from To do to Checked
Actions #9

Updated by Vincent MEMBRÉ 10 months ago

  • Status changed from Pending release to Released

This bug has been fixed in Rudder 7.2.6 and 7.3.1 which were released today.

Actions

Also available in: Atom PDF