Backup restoration fails

Ondersteuning Installatie
1

I’m having the following problem. I’ve been running a Discourse forum for over 10 years, and since I haven’t been able to install updates for some time now, I wanted to set up a new server:

Old server is running: 3.4.0.beta4-dev

New server: Latest version

The backup is already 673.2 MB in size as a .gz file, excluding uploads

Unfortunately, the restoration keeps aborting. The log file contains this error

[2026-06-16 07:54:52] ERROR:  could not create unique index “index_incoming_referers_on_path_and_incoming_domain_id”
[2026-06-16 07:54:52] DETAIL:  Key (path, incoming_domain_id)=(//, 5) is duplicated.
[2026-06-16 07:54:52] EXCEPTION: psql failed: DETAIL:  Key (path, incoming_domain_id)=(//, 5) is duplicated.

[2026-06-16 07:54:52] /var/www/discourse/lib/backup_restore/database_restorer.rb:93:in 'BackupRestore::DatabaseRestorer#restore_dump'
/var/www/discourse/lib/backup_restore/database_restorer.rb:26:in ‘BackupRestore::DatabaseRestorer#restore’
/var/www/discourse/lib/backup_restore/restorer.rb:61:in 'BackupRestore::Restorer#run'
/var/www/discourse/script/spawn_backup_restore.rb:20:in ‘Object#restore’
/var/www/discourse/script/spawn_backup_restore.rb:33:in ‘block in ’
/var/www/discourse/script/spawn_backup_restore.rb:4:in 'Kernel#fork'
/var/www/discourse/script/spawn_backup_restore.rb:4:in ‘’
[2026-06-16 07:54:52] Trying to rollback...

What can I do to fix this problem and not lose the last 10 years of data?

Thank you in advance for any help!

2

Hello and wow, welcome back after 9 years!

Pretty cool to see such a long running forum.

I think this is the same index problem as talked about here: Can't restore due to corrupt indexes (with some clues on how to deal with corrupt indexes)

It’s something like this:

The incoming_referers table tracks the URL paths that referred visitors to your forum. It has a unique index, meaning no two rows can have the same path + domain combination.

Your database has two rows with path='//' and incoming_domain_id=5. When it tries to rebuild this unique index during restore, it finds the duplicate and aborts the entire restore transaction.

So you’ll need to find and cleanup that duplicate incoming_referers and then make a new backup to restore on the new server.

I found this topic with instructions that might help you

3

Thank you very much for the quick response; I’ll give that a try!

4

(post deleted by author)

5

Sorry to bother you, but I seem to be doing something wrong—after running the command “discourse=# select * from incoming_referers where path LIKE ‘%/search/’ ORDER BY incoming_domain_id;”, I’m getting a syntax error.

6

Did you type “discourse=#”? If so, try again without that

7

Then I get the error message “bash: syntax error near unexpected token `from’”

For reference, I’m currently at: root@community-app:/var/www/discourse#

8

Ah , you’re not in the DB yet.

First run psql -U discourse discourse (I think)

And then the prompt should change to discourse=#

9

Okay, I’ve now also installed “apt install postgresql-client-common.” However, I’m still getting the error “Error: You must install at least one postgresql-client- package.”

10

Okay that I don’t know.

Maybe try directly via the rails console then: rails c

with

ActiveRecord::Base.connection.execute(<<~SQL)
  SELECT id, path, incoming_domain_id 
  FROM incoming_referers 
  WHERE path = '//' 
  AND incoming_domain_id = 5
SQL

That may give you the same without going through the database.

Does that work?

11

I’m really, really sorry for acting so stupid.

I’ve now installed Rails, and it seems to have worked. I also ran it with “rails c”. That seems to work too, but when I enter the command, it looks like this:

“root@community:/var/discourse# ActiveRecord::Base.connection.execute(<<~SQL)
-bash: syntax error near unexpected token `<<’
root@community:/var/discourse# SELECT id, path, incoming_domain_id
SELECT: command not found
root@community:/var/discourse# FROM incoming_referers
FROM: command not found
root@community:/var/discourse# WHERE path = ‘//’
WHERE: command not found
root@community:/var/discourse# AND incoming_domain_id = 5
AND: command not found
root@community:/var/discourse# SQL”

12

Don’t worry, maybe my instructions are just not that good :slight_smile:

I will say I’m confused when you say you just installed Rails and can’t access your DB. Maybe it’s all different because it’s an older version, but I think I’m out of my depth now too.

Anyway, once you’re in rails, the prompt should be something different, so from your output, it looks like you aren’t in the rails console yet.

I don’t even think you’re in the actual container. Have you done ./launcher enter app?

13

OK, yeah, that was the problem. The query ran successfully now, and I got this response:

#<PG::Result:0x00007fbde9732ef0 status=PGRES_TUPLES_OK ntuples=1 nfields=3 cmd_tuples=1>

14

So, ntuples=1 means only one row was found, while according to the error log there should be a duplicate… :woman_shrugging:

Sorry, I don’t know what to try next. I’d recommend browsing around on the forum, there seem to be a lot of similar cases (see below this topic in the related section), maybe you’ll find the next clue there.

(Or wait untill a more knowledgeable person breezes by ;))

15

It sounds like your old server isn’t a standard install. Anyway,… what happened in short: an index on your old server got broken over the years (this can happen after OS upgrades), so two identical rows slipped into the incoming_referers table. The backup copies them as-is, and the new server then refuses them. So we fix it on the old server first, then make a new backup.

On the OLD server, open the Rails console:

./launcher enter app
rails c

Then paste these lines, one after the other:

db = ActiveRecord::Base.connection.current_database
DB.exec("DELETE FROM incoming_referers a USING incoming_referers b WHERE a.id > b.id AND a.path = b.path AND a.incoming_domain_id = b.incoming_domain_id")
DB.exec("REINDEX DATABASE #{ActiveRecord::Base.connection.quote_table_name(db)}")

This removes the duplicate and rebuilds all indexes (in case other tables are affected too).

If REINDEX finishes without an error, type exit, make a fresh backup on the old server, and restore that new file. If it shows an error about another table, just paste it here.

20

Thank you very much. Unfortunately, I’m getting an error message on the last line:

“PG::InsufficientPrivilege: ERROR: must be owner of database discourse”

It should actually already be a “standard server.” The installation was performed by the “Discourse” team at the time for a one-time fee.

21

Ah, right. Let’s try this.

Leave the Rails console (type exit), and while still inside the container (./launcher enter app), run:

su postgres -c "reindexdb discourse"

When that finishes without an error, make a fresh backup on the old server and restore that new file. If it shows an error about a specific table, just paste it here.