Lost gateway data when upgrading 4.2.0 to 4.3.1

scottmil · April 27, 2024, 9:09pm

I attempted a full upgrade from 4.2.0 to 4.3.1 by downloading the latest Sysupgrade image and installing using the Wrt web interface, but I lost all my device templates, API keys, applications and devices configurations. Do I need to re-enter this data manually? Should I have created a PostgreSQL + Redis backup before upgrading as mentioned in the Changelog? Does this backup include the gateway data I lost?

I successfully restored a backup of my 4.2.0 installation, but I would like to attempt another upgrade to 4.3.1 once I understand how to keep my gateway data.

scottmil · May 1, 2024, 10:25pm

I backed up the PostgresSQL_Redis databases per the Changelog before attempting another upgrade from Gateway O 4.2.0 to 4.3.1 using the Wrt web interface. After the 4.3.1 upgrade concluded, I navigated to System->Custom Commands->Restore ChirpStack Backup which failed. (see attached) Again I was not able to restore my server device templates, device configs, and applications on my RAK7248 gateway after the upgrade. What am I doing incorrectly?

brocaar · May 2, 2024, 11:00am

These are the create statements, could check if it performed any inserts (by scrolling down the log)? ChirpStack will automatically create the tables on first start, which is the reason why the import fails on these statements.

scottmil · May 12, 2024, 3:18am

I tried upgrading from Gateway OS 4.2.0 to 4.3.2, and again I the ChirpStack Restore command failed (see restore log below).
Steps:

Currently running 4.2.0 with tenant, API keys, device profiles, applications…
Backup my Postgres and Redis databases per 4.3.0 Changelog instructions.
Download the 4.3.2 Sysupgrade image for Raspberry Pi 4B (my RAK7248)
Flash the Sysupgrade image using OpenWrt web interface
Run the ChirpStack Restore command

‘/opt/chirpstack/restore.sh’

Restore PostgreSQL database
Restore Redis database
pg_restore: error: could not execute query: ERROR: relation “__diesel_schema_migrations” already exists
Command was: CREATE TABLE public.__diesel_schema_migrations (
version character varying(50) NOT NULL,
run_on timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “api_key” already exists
Command was: CREATE TABLE public.api_key (
id uuid NOT NULL,
created_at timestamp with time zone NOT NULL,
name character varying(100) NOT NULL,
is_admin boolean NOT NULL,
tenant_id uuid
);

pg_restore: error: could not execute query: ERROR: relation “application” already exists
Command was: CREATE TABLE public.application (
id uuid NOT NULL,
tenant_id uuid NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL,
name character varying(100) NOT NULL,
description text NOT NULL,
mqtt_tls_cert bytea,
tags jsonb NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “application_integration” already exists
Command was: CREATE TABLE public.application_integration (
application_id uuid NOT NULL,
kind character varying(20) NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL,
configuration jsonb NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “device” already exists
Command was: CREATE TABLE public.device (
dev_eui bytea NOT NULL,
application_id uuid NOT NULL,
device_profile_id uuid NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL,
last_seen_at timestamp with time zone,
scheduler_run_after timestamp with time zone,
name character varying(100) NOT NULL,
description text NOT NULL,
external_power_source boolean NOT NULL,
battery_level numeric(5,2),
margin integer,
dr smallint,
latitude double precision,
longitude double precision,
altitude real,
dev_addr bytea,
enabled_class character(1) NOT NULL,
skip_fcnt_check boolean NOT NULL,
is_disabled boolean NOT NULL,
tags jsonb NOT NULL,
variables jsonb NOT NULL,
join_eui bytea NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “device_keys” already exists
Command was: CREATE TABLE public.device_keys (
dev_eui bytea NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL,
nwk_key bytea NOT NULL,
app_key bytea NOT NULL,
dev_nonces integer NOT NULL,
join_nonce integer NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “device_profile” already exists
Command was: CREATE TABLE public.device_profile (
id uuid NOT NULL,
tenant_id uuid NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL,
name character varying(100) NOT NULL,
region character varying(10) NOT NULL,
mac_version character varying(10) NOT NULL,
reg_params_revision character varying(20) NOT NULL,
adr_algorithm_id character varying(100) NOT NULL,
payload_codec_runtime character varying(20) NOT NULL,
uplink_interval integer NOT NULL,
device_status_req_interval integer NOT NULL,
supports_otaa boolean NOT NULL,
supports_class_b boolean NOT NULL,
supports_class_c boolean NOT NULL,
class_b_timeout integer NOT NULL,
class_b_ping_slot_nb_k integer NOT NULL,
class_b_ping_slot_dr smallint NOT NULL,
class_b_ping_slot_freq bigint NOT NULL,
class_c_timeout integer NOT NULL,
abp_rx1_delay smallint NOT NULL,
abp_rx1_dr_offset smallint NOT NULL,
abp_rx2_dr smallint NOT NULL,
abp_rx2_freq bigint NOT NULL,
tags jsonb NOT NULL,
payload_codec_script text NOT NULL,
flush_queue_on_activate boolean NOT NULL,
description text NOT NULL,
measurements jsonb NOT NULL,
auto_detect_measurements boolean NOT NULL,
region_config_id character varying(100),
is_relay boolean NOT NULL,
is_relay_ed boolean NOT NULL,
relay_ed_relay_only boolean NOT NULL,
relay_enabled boolean NOT NULL,
relay_cad_periodicity smallint NOT NULL,
relay_default_channel_index smallint NOT NULL,
relay_second_channel_freq bigint NOT NULL,
relay_second_channel_dr smallint NOT NULL,
relay_second_channel_ack_offset smallint NOT NULL,
relay_ed_activation_mode smallint NOT NULL,
relay_ed_smart_enable_level smallint NOT NULL,
relay_ed_back_off smallint NOT NULL,
relay_ed_uplink_limit_bucket_size smallint NOT NULL,
relay_ed_uplink_limit_reload_rate smallint NOT NULL,
relay_join_req_limit_reload_rate smallint NOT NULL,
relay_notify_limit_reload_rate smallint NOT NULL,
relay_global_uplink_limit_reload_rate smallint NOT NULL,
relay_overall_limit_reload_rate smallint NOT NULL,
relay_join_req_limit_bucket_size smallint NOT NULL,
relay_notify_limit_bucket_size smallint NOT NULL,
relay_global_uplink_limit_bucket_size smallint NOT NULL,
relay_overall_limit_bucket_size smallint NOT NULL,
allow_roaming boolean NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “device_profile_template” already exists
Command was: CREATE TABLE public.device_profile_template (
id text NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL,
name character varying(100) NOT NULL,
description text NOT NULL,
vendor character varying(100) NOT NULL,
firmware character varying(100) NOT NULL,
region character varying(10) NOT NULL,
mac_version character varying(10) NOT NULL,
reg_params_revision character varying(20) NOT NULL,
adr_algorithm_id character varying(100) NOT NULL,
payload_codec_runtime character varying(20) NOT NULL,
payload_codec_script text NOT NULL,
uplink_interval integer NOT NULL,
device_status_req_interval integer NOT NULL,
flush_queue_on_activate boolean NOT NULL,
supports_otaa boolean NOT NULL,
supports_class_b boolean NOT NULL,
supports_class_c boolean NOT NULL,
class_b_timeout integer NOT NULL,
class_b_ping_slot_nb_k integer NOT NULL,
class_b_ping_slot_dr smallint NOT NULL,
class_b_ping_slot_freq bigint NOT NULL,
class_c_timeout integer NOT NULL,
abp_rx1_delay smallint NOT NULL,
abp_rx1_dr_offset smallint NOT NULL,
abp_rx2_dr smallint NOT NULL,
abp_rx2_freq bigint NOT NULL,
tags jsonb NOT NULL,
measurements jsonb NOT NULL,
auto_detect_measurements boolean NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “device_queue_item” already exists
Command was: CREATE TABLE public.device_queue_item (
id uuid NOT NULL,
dev_eui bytea NOT NULL,
created_at timestamp with time zone NOT NULL,
f_port smallint NOT NULL,
confirmed boolean NOT NULL,
data bytea NOT NULL,
is_pending boolean NOT NULL,
f_cnt_down bigint,
timeout_after timestamp with time zone,
is_encrypted boolean NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “gateway” already exists
Command was: CREATE TABLE public.gateway (
gateway_id bytea NOT NULL,
tenant_id uuid NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL,
last_seen_at timestamp with time zone,
name character varying(100) NOT NULL,
description text NOT NULL,
latitude double precision NOT NULL,
longitude double precision NOT NULL,
altitude real NOT NULL,
stats_interval_secs integer NOT NULL,
tls_certificate bytea,
tags jsonb NOT NULL,
properties jsonb NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “multicast_group” already exists
Command was: CREATE TABLE public.multicast_group (
id uuid NOT NULL,
application_id uuid NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL,
name character varying(100) NOT NULL,
region character varying(10) NOT NULL,
mc_addr bytea NOT NULL,
mc_nwk_s_key bytea NOT NULL,
mc_app_s_key bytea NOT NULL,
f_cnt bigint NOT NULL,
group_type character(1) NOT NULL,
dr smallint NOT NULL,
frequency bigint NOT NULL,
class_b_ping_slot_period integer NOT NULL,
class_c_scheduling_type character varying(20) NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “multicast_group_device” already exists
Command was: CREATE TABLE public.multicast_group_device (
multicast_group_id uuid NOT NULL,
dev_eui bytea NOT NULL,
created_at timestamp with time zone NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “multicast_group_gateway” already exists
Command was: CREATE TABLE public.multicast_group_gateway (
multicast_group_id uuid NOT NULL,
gateway_id bytea NOT NULL,
created_at timestamp with time zone NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “multicast_group_queue_item” already exists
Command was: CREATE TABLE public.multicast_group_queue_item (
id uuid NOT NULL,
created_at timestamp with time zone NOT NULL,
scheduler_run_after timestamp with time zone NOT NULL,
multicast_group_id uuid NOT NULL,
gateway_id bytea NOT NULL,
f_cnt bigint NOT NULL,
f_port smallint NOT NULL,
data bytea NOT NULL,
emit_at_time_since_gps_epoch bigint
);

pg_restore: error: could not execute query: ERROR: relation “relay_device” already exists
Command was: CREATE TABLE public.relay_device (
relay_dev_eui bytea NOT NULL,
dev_eui bytea NOT NULL,
created_at timestamp with time zone NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “tenant” already exists
Command was: CREATE TABLE public.tenant (
id uuid NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL,
name character varying(100) NOT NULL,
description text NOT NULL,
can_have_gateways boolean NOT NULL,
max_device_count integer NOT NULL,
max_gateway_count integer NOT NULL,
private_gateways_up boolean NOT NULL,
private_gateways_down boolean NOT NULL,
tags jsonb NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “tenant_user” already exists
Command was: CREATE TABLE public.tenant_user (
tenant_id uuid NOT NULL,
user_id uuid NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL,
is_admin boolean NOT NULL,
is_device_admin boolean NOT NULL,
is_gateway_admin boolean NOT NULL
);

pg_restore: error: could not execute query: ERROR: relation “user” already exists
Command was: CREATE TABLE public.“user” (
id uuid NOT NULL,
external_id text,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL,
is_admin boolean NOT NULL,
is_active boolean NOT NULL,
email text NOT NULL,
email_verified boolean NOT NULL,
password_hash character varying(200) NOT NULL,
note text NOT NULL
);

pg_restore: error: COPY failed for table “__diesel_schema_migrations”: ERROR: duplicate key value violates unique constraint “__diesel_schema_migrations_pkey”
DETAIL: Key (version)=(00000000000000) already exists.
CONTEXT: COPY __diesel_schema_migrations, line 1
pg_restore: error: COPY failed for table “tenant”: ERROR: duplicate key value violates unique constraint “tenant_pkey”
DETAIL: Key (id)=(52f14cd4-c6f1-4fbd-8f87-4025e1d49242) already exists.
CONTEXT: COPY tenant, line 1
pg_restore: error: COPY failed for table “user”: ERROR: duplicate key value violates unique constraint “user_pkey”
DETAIL: Key (id)=(05244f12-6daf-4e1f-8315-c66783a0ab56) already exists.
CONTEXT: COPY user, line 1
pg_restore: error: could not execute query: ERROR: multiple primary keys for table “__diesel_schema_migrations” are not allowed
Command was: ALTER TABLE ONLY public.__diesel_schema_migrations
ADD CONSTRAINT __diesel_schema_migrations_pkey PRIMARY KEY (version);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “api_key” are not allowed
Command was: ALTER TABLE ONLY public.api_key
ADD CONSTRAINT api_key_pkey PRIMARY KEY (id);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “application_integration” are not allowed
Command was: ALTER TABLE ONLY public.application_integration
ADD CONSTRAINT application_integration_pkey PRIMARY KEY (application_id, kind);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “application” are not allowed
Command was: ALTER TABLE ONLY public.application
ADD CONSTRAINT application_pkey PRIMARY KEY (id);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “device_keys” are not allowed
Command was: ALTER TABLE ONLY public.device_keys
ADD CONSTRAINT device_keys_pkey PRIMARY KEY (dev_eui);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “device” are not allowed
Command was: ALTER TABLE ONLY public.device
ADD CONSTRAINT device_pkey PRIMARY KEY (dev_eui);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “device_profile” are not allowed
Command was: ALTER TABLE ONLY public.device_profile
ADD CONSTRAINT device_profile_pkey PRIMARY KEY (id);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “device_profile_template” are not allowed
Command was: ALTER TABLE ONLY public.device_profile_template
ADD CONSTRAINT device_profile_template_pkey PRIMARY KEY (id);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “device_queue_item” are not allowed
Command was: ALTER TABLE ONLY public.device_queue_item
ADD CONSTRAINT device_queue_item_pkey PRIMARY KEY (id);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “gateway” are not allowed
Command was: ALTER TABLE ONLY public.gateway
ADD CONSTRAINT gateway_pkey PRIMARY KEY (gateway_id);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “multicast_group_device” are not allowed
Command was: ALTER TABLE ONLY public.multicast_group_device
ADD CONSTRAINT multicast_group_device_pkey PRIMARY KEY (multicast_group_id, dev_eui);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “multicast_group_gateway” are not allowed
Command was: ALTER TABLE ONLY public.multicast_group_gateway
ADD CONSTRAINT multicast_group_gateway_pkey PRIMARY KEY (multicast_group_id, gateway_id);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “multicast_group” are not allowed
Command was: ALTER TABLE ONLY public.multicast_group
ADD CONSTRAINT multicast_group_pkey PRIMARY KEY (id);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “multicast_group_queue_item” are not allowed
Command was: ALTER TABLE ONLY public.multicast_group_queue_item
ADD CONSTRAINT multicast_group_queue_item_pkey PRIMARY KEY (id);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “relay_device” are not allowed
Command was: ALTER TABLE ONLY public.relay_device
ADD CONSTRAINT relay_device_pkey PRIMARY KEY (relay_dev_eui, dev_eui);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “tenant” are not allowed
Command was: ALTER TABLE ONLY public.tenant
ADD CONSTRAINT tenant_pkey PRIMARY KEY (id);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “tenant_user” are not allowed
Command was: ALTER TABLE ONLY public.tenant_user
ADD CONSTRAINT tenant_user_pkey PRIMARY KEY (tenant_id, user_id);

pg_restore: error: could not execute query: ERROR: multiple primary keys for table “user” are not allowed
Command was: ALTER TABLE ONLY public.“user”
ADD CONSTRAINT user_pkey PRIMARY KEY (id);

pg_restore: error: could not execute query: ERROR: relation “idx_api_key_tenant_id” already exists
Command was: CREATE INDEX idx_api_key_tenant_id ON public.api_key USING btree (tenant_id);

pg_restore: error: could not execute query: ERROR: relation “idx_application_name_trgm” already exists
Command was: CREATE INDEX idx_application_name_trgm ON public.application USING gin (name public.gin_trgm_ops);

pg_restore: error: could not execute query: ERROR: relation “idx_application_tags” already exists
Command was: CREATE INDEX idx_application_tags ON public.application USING gin (tags);

pg_restore: error: could not execute query: ERROR: relation “idx_application_tenant_id” already exists
Command was: CREATE INDEX idx_application_tenant_id ON public.application USING btree (tenant_id);

pg_restore: error: could not execute query: ERROR: relation “idx_device_application_id” already exists
Command was: CREATE INDEX idx_device_application_id ON public.device USING btree (application_id);

pg_restore: error: could not execute query: ERROR: relation “idx_device_dev_addr_trgm” already exists
Command was: CREATE INDEX idx_device_dev_addr_trgm ON public.device USING gin (encode(dev_addr, ‘hex’::text) public.gin_trgm_ops);

pg_restore: error: could not execute query: ERROR: relation “idx_device_dev_eui_trgm” already exists
Command was: CREATE INDEX idx_device_dev_eui_trgm ON public.device USING gin (encode(dev_eui, ‘hex’::text) public.gin_trgm_ops);

pg_restore: error: could not execute query: ERROR: relation “idx_device_device_profile_id” already exists
Command was: CREATE INDEX idx_device_device_profile_id ON public.device USING btree (device_profile_id);

pg_restore: error: could not execute query: ERROR: relation “idx_device_name_trgm” already exists
Command was: CREATE INDEX idx_device_name_trgm ON public.device USING gin (name public.gin_trgm_ops);

pg_restore: error: could not execute query: ERROR: relation “idx_device_profile_name_trgm” already exists
Command was: CREATE INDEX idx_device_profile_name_trgm ON public.device_profile USING gin (name public.gin_trgm_ops);

pg_restore: error: could not execute query: ERROR: relation “idx_device_profile_tags” already exists
Command was: CREATE INDEX idx_device_profile_tags ON public.device_profile USING gin (tags);

pg_restore: error: could not execute query: ERROR: relation “idx_device_profile_tenant_id” already exists
Command was: CREATE INDEX idx_device_profile_tenant_id ON public.device_profile USING btree (tenant_id);

pg_restore: error: could not execute query: ERROR: relation “idx_device_queue_item_created_at” already exists
Command was: CREATE INDEX idx_device_queue_item_created_at ON public.device_queue_item USING btree (created_at);

pg_restore: error: could not execute query: ERROR: relation “idx_device_queue_item_dev_eui” already exists
Command was: CREATE INDEX idx_device_queue_item_dev_eui ON public.device_queue_item USING btree (dev_eui);

pg_restore: error: could not execute query: ERROR: relation “idx_device_queue_item_timeout_after” already exists
Command was: CREATE INDEX idx_device_queue_item_timeout_after ON public.device_queue_item USING btree (timeout_after);

pg_restore: error: could not execute query: ERROR: relation “idx_device_tags” already exists
Command was: CREATE INDEX idx_device_tags ON public.device USING gin (tags);

pg_restore: error: could not execute query: ERROR: relation “idx_gateway_id_trgm” already exists
Command was: CREATE INDEX idx_gateway_id_trgm ON public.gateway USING gin (encode(gateway_id, ‘hex’::text) public.gin_trgm_ops);

pg_restore: error: could not execute query: ERROR: relation “idx_gateway_name_trgm” already exists
Command was: CREATE INDEX idx_gateway_name_trgm ON public.gateway USING gin (name public.gin_trgm_ops);

pg_restore: error: could not execute query: ERROR: relation “idx_gateway_tags” already exists
Command was: CREATE INDEX idx_gateway_tags ON public.gateway USING gin (tags);

pg_restore: error: could not execute query: ERROR: relation “idx_gateway_tenant_id” already exists
Command was: CREATE INDEX idx_gateway_tenant_id ON public.gateway USING btree (tenant_id);

pg_restore: error: could not execute query: ERROR: relation “idx_multicast_group_application_id” already exists
Command was: CREATE INDEX idx_multicast_group_application_id ON public.multicast_group USING btree (application_id);

pg_restore: error: could not execute query: ERROR: relation “idx_multicast_group_name_trgm” already exists
Command was: CREATE INDEX idx_multicast_group_name_trgm ON public.multicast_group USING gin (name public.gin_trgm_ops);

pg_restore: error: could not execute query: ERROR: relation “idx_multicast_group_queue_item_multicast_group_id” already exists
Command was: CREATE INDEX idx_multicast_group_queue_item_multicast_group_id ON public.multicast_group_queue_item USING btree (multicast_group_id);

pg_restore: error: could not execute query: ERROR: relation “idx_multicast_group_queue_item_scheduler_run_after” already exists
Command was: CREATE INDEX idx_multicast_group_queue_item_scheduler_run_after ON public.multicast_group_queue_item USING btree (scheduler_run_after);

pg_restore: error: could not execute query: ERROR: relation “idx_tenant_name_trgm” already exists
Command was: CREATE INDEX idx_tenant_name_trgm ON public.tenant USING gin (name public.gin_trgm_ops);

pg_restore: error: could not execute query: ERROR: relation “idx_tenant_tags” already exists
Command was: CREATE INDEX idx_tenant_tags ON public.tenant USING gin (tags);

pg_restore: error: could not execute query: ERROR: relation “idx_tenant_user_user_id” already exists
Command was: CREATE INDEX idx_tenant_user_user_id ON public.tenant_user USING btree (user_id);

pg_restore: error: could not execute query: ERROR: relation “idx_user_email” already exists
Command was: CREATE UNIQUE INDEX idx_user_email ON public.“user” USING btree (email);

pg_restore: error: could not execute query: ERROR: relation “idx_user_external_id” already exists
Command was: CREATE UNIQUE INDEX idx_user_external_id ON public.“user” USING btree (external_id);

pg_restore: error: could not execute query: ERROR: constraint “api_key_tenant_id_fkey” for relation “api_key” already exists
Command was: ALTER TABLE ONLY public.api_key
ADD CONSTRAINT api_key_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenant(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “application_integration_application_id_fkey” for relation “application_integration” already exists
Command was: ALTER TABLE ONLY public.application_integration
ADD CONSTRAINT application_integration_application_id_fkey FOREIGN KEY (application_id) REFERENCES public.application(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “application_tenant_id_fkey” for relation “application” already exists
Command was: ALTER TABLE ONLY public.application
ADD CONSTRAINT application_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenant(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “device_application_id_fkey” for relation “device” already exists
Command was: ALTER TABLE ONLY public.device
ADD CONSTRAINT device_application_id_fkey FOREIGN KEY (application_id) REFERENCES public.application(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “device_device_profile_id_fkey” for relation “device” already exists
Command was: ALTER TABLE ONLY public.device
ADD CONSTRAINT device_device_profile_id_fkey FOREIGN KEY (device_profile_id) REFERENCES public.device_profile(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “device_keys_dev_eui_fkey” for relation “device_keys” already exists
Command was: ALTER TABLE ONLY public.device_keys
ADD CONSTRAINT device_keys_dev_eui_fkey FOREIGN KEY (dev_eui) REFERENCES public.device(dev_eui) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “device_profile_tenant_id_fkey” for relation “device_profile” already exists
Command was: ALTER TABLE ONLY public.device_profile
ADD CONSTRAINT device_profile_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenant(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “device_queue_item_dev_eui_fkey” for relation “device_queue_item” already exists
Command was: ALTER TABLE ONLY public.device_queue_item
ADD CONSTRAINT device_queue_item_dev_eui_fkey FOREIGN KEY (dev_eui) REFERENCES public.device(dev_eui) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “gateway_tenant_id_fkey” for relation “gateway” already exists
Command was: ALTER TABLE ONLY public.gateway
ADD CONSTRAINT gateway_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenant(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “multicast_group_application_id_fkey” for relation “multicast_group” already exists
Command was: ALTER TABLE ONLY public.multicast_group
ADD CONSTRAINT multicast_group_application_id_fkey FOREIGN KEY (application_id) REFERENCES public.application(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “multicast_group_device_dev_eui_fkey” for relation “multicast_group_device” already exists
Command was: ALTER TABLE ONLY public.multicast_group_device
ADD CONSTRAINT multicast_group_device_dev_eui_fkey FOREIGN KEY (dev_eui) REFERENCES public.device(dev_eui) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “multicast_group_device_multicast_group_id_fkey” for relation “multicast_group_device” already exists
Command was: ALTER TABLE ONLY public.multicast_group_device
ADD CONSTRAINT multicast_group_device_multicast_group_id_fkey FOREIGN KEY (multicast_group_id) REFERENCES public.multicast_group(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “multicast_group_gateway_gateway_id_fkey” for relation “multicast_group_gateway” already exists
Command was: ALTER TABLE ONLY public.multicast_group_gateway
ADD CONSTRAINT multicast_group_gateway_gateway_id_fkey FOREIGN KEY (gateway_id) REFERENCES public.gateway(gateway_id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “multicast_group_gateway_multicast_group_id_fkey” for relation “multicast_group_gateway” already exists
Command was: ALTER TABLE ONLY public.multicast_group_gateway
ADD CONSTRAINT multicast_group_gateway_multicast_group_id_fkey FOREIGN KEY (multicast_group_id) REFERENCES public.multicast_group(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “multicast_group_queue_item_gateway_id_fkey” for relation “multicast_group_queue_item” already exists
Command was: ALTER TABLE ONLY public.multicast_group_queue_item
ADD CONSTRAINT multicast_group_queue_item_gateway_id_fkey FOREIGN KEY (gateway_id) REFERENCES public.gateway(gateway_id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “multicast_group_queue_item_multicast_group_id_fkey” for relation “multicast_group_queue_item” already exists
Command was: ALTER TABLE ONLY public.multicast_group_queue_item
ADD CONSTRAINT multicast_group_queue_item_multicast_group_id_fkey FOREIGN KEY (multicast_group_id) REFERENCES public.multicast_group(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “relay_device_dev_eui_fkey” for relation “relay_device” already exists
Command was: ALTER TABLE ONLY public.relay_device
ADD CONSTRAINT relay_device_dev_eui_fkey FOREIGN KEY (dev_eui) REFERENCES public.device(dev_eui) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “relay_device_relay_dev_eui_fkey” for relation “relay_device” already exists
Command was: ALTER TABLE ONLY public.relay_device
ADD CONSTRAINT relay_device_relay_dev_eui_fkey FOREIGN KEY (relay_dev_eui) REFERENCES public.device(dev_eui) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “tenant_user_tenant_id_fkey” for relation “tenant_user” already exists
Command was: ALTER TABLE ONLY public.tenant_user
ADD CONSTRAINT tenant_user_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenant(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: constraint “tenant_user_user_id_fkey” for relation “tenant_user” already exists
Command was: ALTER TABLE ONLY public.tenant_user
ADD CONSTRAINT tenant_user_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.“user”(id) ON DELETE CASCADE;

pg_restore: warning: errors ignored on restore: 88
ln: /var/lib/redis/redis: File exists

brocaar · May 13, 2024, 9:39am

I think the issue is that the restore script assumes the target database is empty, which is not the case in your situation. That is why you are seeing so many:

ERROR: constraint “XXXX_fkey” for relation “XXXX” already exists.

E.g. it tries to insert a record that already exist. What you could do is manually drop all columns from the database, and then rerun the backup command.

I will also try to reproduce your steps to see how the backup / restore process can be improved