’
> /usr/local/bin/bundle:25:in `load' /usr/local/bin/bundle:25:in `’
> Trying to rollback…
> Rolling back…
> Cleaning stuff up…
> Dropping functions from the discourse\_functions schema…
> Removing tmp ‘/var/www/discourse/tmp/restores/default/2025-01-09-180715’ directory…
> Unpausing sidekiq…
> Marking restore as finished…
> Notifying ‘system’ of the end of the restore…
> Finished!
> [FAILED]
---
### Author:  [@Dmitry\_Krasnoperov](https://meta.discourse.org/u/Dmitry_Krasnoperov)
#### Post date: [January 10, 2025, 12:16pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/2 "2025-01-10T12:16:49Z")
How to fix an SQL file so that `db:migrate` runs successfully?
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [January 10, 2025, 1:45pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/3 "2025-01-10T13:45:28Z")
What commit is the old site on?
There have been a couple of regressions where a commit included a migration that it should not have. My guess is that this is one of those cases.
---
### Author:  [@RGJ](https://meta.discourse.org/u/RGJ)
#### Post date: [January 10, 2025, 1:54pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/4 "2025-01-10T13:54:59Z")
Do you have the exact same set of plugins on both instances?
On which commit is your existing Discourse, and on which commit of the chat plugin is it?
---
### Author:  [@Dmitry\_Krasnoperov](https://meta.discourse.org/u/Dmitry_Krasnoperov)
#### Post date: [January 10, 2025, 2:36pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/5 "2025-01-10T14:36:03Z")
Appreciate your kind involvement. SQL dump is 45GB, takes forever to work on.
Tried running GUI update on Dec’31 that made DB migration hung. Old site is gone since 06-Jan, all I have is SQL dump and media uploads saved. Trying to fire up fresh site and restore my DB there. Running only a few plugins in my YAML:
[https://github.com/discourse/discourse-vk-auth.git](https://github.com/discourse/discourse-vk-auth.git)
[https://github.com/discourse/discourse-whos-online.git](https://github.com/discourse/discourse-whos-online.git)
[https://github.com/discourse/discourse-categories-suppressed.git](https://github.com/discourse/discourse-categories-suppressed.git)
[GitHub - jannolii/discourse-topic-trade-buttons · GitHub](https://github.com/jannolii/discourse-topic-trade-buttons.git)"
---
### Author:  [@RGJ](https://meta.discourse.org/u/RGJ)
#### Post date: [January 10, 2025, 2:45pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/6 "2025-01-10T14:45:26Z")
What is the file name of your backup? (It contains information about the last migration that has been run)
---
### Author:  [@Dmitry\_Krasnoperov](https://meta.discourse.org/u/Dmitry_Krasnoperov)
#### Post date: [January 10, 2025, 2:50pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/7 "2025-01-10T14:50:30Z")
sitename-2025-01-06-033732-v20240912212253.sql.gz
When doing manual restore, following issues reported (might mean something too):
psql:/shared/backups/default/da-stereo-2025-01-06-033732-v20240912212253.sql:842596361: ERROR: could not create unique index “category\_featured\_topics\_pkey”
DETAIL: Key (id)=(14) is duplicated.
psql:/shared/backups/default/da-stereo-2025-01-06-033732-v20240912212253.sql:842596928: ERROR: could not create unique index “incoming\_links\_pkey”
DETAIL: Key (id)=(1) is duplicated.
psql:/shared/backups/default/da-stereo-2025-01-06-033732-v20240912212253.sql:842596937: ERROR: could not create unique index “incoming\_referers\_pkey”
DETAIL: Key (id)=(1) is duplicated.
psql:/shared/backups/default/da-stereo-2025-01-06-033732-v20240912212253.sql:842597117: ERROR: could not create unique index “post\_hotlinked\_media\_pkey”
DETAIL: Key (id)=(1) is duplicated.
psql:/shared/backups/default/da-stereo-2025-01-06-033732-v20240912212253.sql:842597378: ERROR: could not create unique index “sitemaps\_pkey”
DETAIL: Key (id)=(2) is duplicated.
psql:/shared/backups/default/da-stereo-2025-01-06-033732-v20240912212253.sql:842597693: ERROR: could not create unique index “upload\_references\_pkey”
DETAIL: Key (id)=(8) is duplicated.
---
### Author:  [@RGJ](https://meta.discourse.org/u/RGJ)
#### Post date: [January 10, 2025, 3:03pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/8 "2025-01-10T15:03:46Z")
So that’s interesting.
- those conflicts are on fairly trivial tables and the conflicting id’s are low
- your backup is from September and the `column "notification_level" of relation "user_chat_channel_memberships" already exists` migration is from October, so it has not been ran on your previous instance, and it is one of the first migrations that is done after the restore.
This sounds to me like you have a different problem. Is this a [standard install](https://meta.discourse.org/t/142537?silent=true)? And was your previous instance a [standard install](https://meta.discourse.org/t/142537?silent=true)? I’ve got a hunch that the backup is not restoring in the right schema and the migrations are running against a blank instance.
---
### Author:  [@Dmitry\_Krasnoperov](https://meta.discourse.org/u/Dmitry_Krasnoperov)
#### Post date: [January 10, 2025, 3:53pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/9 "2025-01-10T15:53:15Z")
Yes, it is a standard docker install and my original site been created using this manual back in 2015.
Anything I can pull from postgres or rails console to shed some light on what’s going on?
---
### Author:  [@Dmitry\_Krasnoperov](https://meta.discourse.org/u/Dmitry_Krasnoperov)
#### Post date: [January 11, 2025, 2:38pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/10 "2025-01-11T14:38:29Z")
Thanks,
Any hints, tips, or direction will be greatly appreciated.
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [January 11, 2025, 7:23pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/11 "2025-01-11T19:23:11Z")
> [@Dmitry\_Krasnoperov](#):
>
> all I have is SQL dump
Do you mean a sql-only Discourse backup or some SQL dump that you made some other way?
---
### Author:  [@Dmitry\_Krasnoperov](https://meta.discourse.org/u/Dmitry_Krasnoperov)
#### Post date: [January 12, 2025, 9:55am UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/12 "2025-01-12T09:55:29Z")
Discourse only backup.
At home, I set up PostgreSQL, and our data backup was restored almost without any issues.
It seems that when restoring the file in PostgreSQL (which is inside the Discourse container), the old database is not deleted, and this causes a conflict, preventing the backup from being deployed properly.
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [January 12, 2025, 12:35pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/13 "2025-01-12T12:35:53Z")
That sounds like what Richard was saying.
There were some commits around postgres recently, though I’m not sure they’ve been accepted. You might try rebuilding before you try to restore again.
---
### Author:  [@Dmitry\_Krasnoperov](https://meta.discourse.org/u/Dmitry_Krasnoperov)
#### Post date: [January 12, 2025, 1:54pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/14 "2025-01-12T13:54:59Z")
When restoring sql.gz archive I can see following:
…
Copying archive to tmp directory…
Extracting dump file…
Validating metadata…
Current version: 20241224191732
Restored version: 20240912212253
and obviously db:migration fails with the reported error in post #1
I can see that postgres DB “discourse” restored with all required tables/data in it:
psql (13.18 (Debian 13.18-1.pgdg120+1))
Type “help” for help.
```plaintext
discourse=> \dn+
List of schemas
Name | Owner | Access privileges | Description
---------------------+-----------+------------------------+------------------------
backup | discourse | |
discourse_functions | discourse | |
public | discourse | discourse=UC/discourse+| standard public schema
| | =UC/discourse |
(3 rows)
discourse=> discourse=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+------------------------
discourse | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | discourse=CTc/postgres
postgres | postgres | 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)
```
but rake repoting that is using wrong DB:
rake db:version
database: discourse
Current version: 20241224191732
How do I point rake towards 20240912212253 ?
Thanks
---
### Author:  [@RGJ](https://meta.discourse.org/u/RGJ)
#### Post date: [January 13, 2025, 5:00pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/15 "2025-01-13T17:00:52Z")
> [@Dmitry\_Krasnoperov](#):
>
> It seems that when restoring the file in PostgreSQL (which is inside the Discourse container), the old database is not deleted, and this causes a conflict, preventing the backup from being deployed properly
This is what happens when there is a permissions problem. Discourse juggles with schemas during restore and when it does not have permissions to enumerate the tables in the database, things go south.
Although I have never experienced that, this might also be an issue with available disk space?
---
### Author:  [@thoka](https://meta.discourse.org/u/thoka)
#### Post date: [January 13, 2025, 5:10pm UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/16 "2025-01-13T17:10:47Z")
Just a gut feeling: could there be a [timeout](https://www.bytebase.com/blog/postgres-timeout/) during table movements after a restore?
---
### Author:  [@Dmitry\_Krasnoperov](https://meta.discourse.org/u/Dmitry_Krasnoperov)
#### Post date: [January 14, 2025, 6:06am UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/17 "2025-01-14T06:06:37Z")
Thanks for hint.
Disk space is not an issue; I have 6-7 times more space than the DB size. I can try a manual import before running the migration and fix the permissions. The question is, what exactly must be granted?
---
### Author:  [@Dmitry\_Krasnoperov](https://meta.discourse.org/u/Dmitry_Krasnoperov)
#### Post date: [January 14, 2025, 11:33am UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/18 "2025-01-14T11:33:08Z")
I think you need to update manual script in this way:
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO discourse;
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS pg\_trgm;
END;
There is no need to CREATE SCHEMA public (and set owner), cause newer Pg creates schema from SQL backup dump.
However table permissions must be fixed to discourse user, by default they are postgres.
Now it works! Thank you all for ideas and support.
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [February 13, 2025, 11:33am UTC](https://meta.discourse.org/t/problem-with-restore-discourse-from-backup-3-4-quite-large-db/346497/19 "2025-02-13T11:33:20Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.