Error Restoring Database

[aaronntw]

I am selfhosting Split-Pro as a custom app on Truenas Scale V25.04.2.3. I followed the guide to backup the database and encounter error to restore database. Appreciate help to restore previous added activity. Error as below:

root@TRUENAS-SCALE:/mnt/pool_apps/splitpro# cat splitpro_backup.sql | docker exec -i splitpro-db psql -U postgres
SET
SET
SET
ERROR:  database "splitpro" is being accessed by other users
DETAIL:  There is 1 other session using the database.
ERROR:  current user cannot be dropped
ERROR:  role "postgres" already exists
ALTER ROLE
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
UPDATE 1
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
COMMENT
ALTER DATABASE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
REVOKE
GRANT
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
COMMENT
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
ERROR:  database "splitpro" already exists
ALTER DATABASE
You are now connected to database "splitpro" as user "postgres".
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
ERROR:  type "SplitType" already exists
ALTER TYPE
SET
SET
ERROR:  relation "Account" already exists
ALTER TABLE
ERROR:  relation "Balance" already exists
ALTER TABLE
ERROR:  relation "Expense" already exists
ALTER TABLE
ERROR:  relation "ExpenseNote" already exists
ALTER TABLE
ERROR:  relation "ExpenseParticipant" already exists
ALTER TABLE
ERROR:  relation "Group" already exists
ALTER TABLE
ERROR:  relation "GroupBalance" already exists
ALTER TABLE
ERROR:  relation "GroupUser" already exists
ALTER TABLE
ERROR:  relation "Group_id_seq" already exists
ALTER SEQUENCE
ALTER SEQUENCE
ERROR:  relation "PushNotification" already exists
ALTER TABLE
ERROR:  relation "Session" already exists
ALTER TABLE
ERROR:  relation "User" already exists
ALTER TABLE
ERROR:  relation "User_id_seq" already exists
ALTER SEQUENCE
ALTER SEQUENCE
ERROR:  relation "VerificationToken" already exists
ALTER TABLE
ERROR:  relation "_prisma_migrations" already exists
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR:  insert or update on table "Account" violates foreign key constraint "Account_userId_fkey"
DETAIL:  Key (userId)=(1) is not present in table "User".
ERROR:  insert or update on table "Balance" violates foreign key constraint "Balance_userId_fkey"
DETAIL:  Key (userId)=(3) is not present in table "User".
ERROR:  insert or update on table "Expense" violates foreign key constraint "Expense_groupId_fkey"
DETAIL:  Key (groupId)=(1) is not present in table "Group".
COPY 0
ERROR:  insert or update on table "ExpenseParticipant" violates foreign key constraint "ExpenseParticipant_userId_fkey"
DETAIL:  Key (userId)=(2) is not present in table "User".
ERROR:  insert or update on table "Group" violates foreign key constraint "Group_userId_fkey"
DETAIL:  Key (userId)=(1) is not present in table "User".
ERROR:  insert or update on table "GroupBalance" violates foreign key constraint "GroupBalance_groupId_fkey"
DETAIL:  Key (groupId)=(1) is not present in table "Group".
ERROR:  insert or update on table "GroupUser" violates foreign key constraint "GroupUser_userId_fkey"
DETAIL:  Key (userId)=(1) is not present in table "User".
COPY 0
ERROR:  insert or update on table "Session" violates foreign key constraint "Session_userId_fkey"
DETAIL:  Key (userId)=(1) is not present in table "User".
COPY 4
COPY 1
COPY 11
 setval 
--------
      2
(1 row)

 setval 
--------
      4
(1 row)

ERROR:  multiple primary keys for table "Account" are not allowed
ERROR:  multiple primary keys for table "Balance" are not allowed
ERROR:  multiple primary keys for table "ExpenseNote" are not allowed
ERROR:  multiple primary keys for table "ExpenseParticipant" are not allowed
ERROR:  multiple primary keys for table "Expense" are not allowed
ERROR:  multiple primary keys for table "GroupBalance" are not allowed
ERROR:  multiple primary keys for table "GroupUser" are not allowed
ERROR:  multiple primary keys for table "Group" are not allowed
ERROR:  multiple primary keys for table "PushNotification" are not allowed
ERROR:  multiple primary keys for table "Session" are not allowed
ERROR:  multiple primary keys for table "User" are not allowed
ERROR:  multiple primary keys for table "_prisma_migrations" are not allowed
ERROR:  relation "Account_provider_providerAccountId_key" already exists
ERROR:  relation "Expense_groupId_idx" already exists
ERROR:  relation "Expense_paidBy_idx" already exists
ERROR:  relation "Group_publicId_key" already exists
ERROR:  relation "Group_splitwiseGroupId_key" already exists
ERROR:  relation "Session_sessionToken_key" already exists
ERROR:  relation "User_email_key" already exists
ERROR:  relation "VerificationToken_identifier_token_key" already exists
ERROR:  relation "VerificationToken_token_key" already exists
ERROR:  constraint "Account_userId_fkey" for relation "Account" already exists
ERROR:  constraint "Balance_friendId_fkey" for relation "Balance" already exists
ERROR:  constraint "Balance_userId_fkey" for relation "Balance" already exists
ERROR:  constraint "ExpenseNote_createdById_fkey" for relation "ExpenseNote" already exists
ERROR:  constraint "ExpenseNote_expenseId_fkey" for relation "ExpenseNote" already exists
ERROR:  constraint "ExpenseParticipant_expenseId_fkey" for relation "ExpenseParticipant" already exists
ERROR:  constraint "ExpenseParticipant_userId_fkey" for relation "ExpenseParticipant" already exists
ERROR:  constraint "Expense_addedBy_fkey" for relation "Expense" already exists
ERROR:  constraint "Expense_deletedBy_fkey" for relation "Expense" already exists
ERROR:  constraint "Expense_groupId_fkey" for relation "Expense" already exists
ERROR:  constraint "Expense_paidBy_fkey" for relation "Expense" already exists
ERROR:  constraint "Expense_updatedBy_fkey" for relation "Expense" already exists
ERROR:  constraint "GroupBalance_firendId_fkey" for relation "GroupBalance" already exists
ERROR:  constraint "GroupBalance_groupId_fkey" for relation "GroupBalance" already exists
ERROR:  constraint "GroupBalance_userId_fkey" for relation "GroupBalance" already exists
ERROR:  constraint "GroupUser_groupId_fkey" for relation "GroupUser" already exists
ERROR:  constraint "GroupUser_userId_fkey" for relation "GroupUser" already exists
ERROR:  constraint "Group_userId_fkey" for relation "Group" already exists
ERROR:  constraint "Session_userId_fkey" for relation "Session" already exists

[krokosik]

When restoring the database, make sure you are doing it on a fresh postgres instance.

1. Stop the splitpro container
2. Remove the DB container AND its volume
3. Recreate the DB container ONLY and then run the restore command
4. Start the splitpro container