Restore failed `entity2char already exists' RDS Postgres 13.7

Installation
1

I’m trying to restore a database created on "Discourse 2.9.0.beta10 - https://github.com/discourse/discourse version 8d3fe3ddc5d38ab02a8f5c6aed7ebf7a6bcfbfec">
and restore to Discourse 2.9.0.beta12 - https://github.com/discourse/discourse version d4371a9ffcc6c727105a880b4b70c715ed0283cb">. Both are using RDS Postgres 13.7. The uncompressed database is 126GB, so after spending a couple of days believing that I need a 200GB disk to run this restore of the 30GB compressed database, I get this:

...
SET
CREATE TYPE
ERROR:  function "entity2char" already exists with same argument types
EXCEPTION: psql failed: ERROR:  function "entity2char" already exists with same argument types
/var/www/discourse/lib/backup_restore/database_restorer.rb:92:in `restore_dump'
/var/www/discourse/lib/backup_restore/database_restorer.rb:26:in `restore'
/var/www/discourse/lib/backup_restore/restorer.rb:51:in `run'
...

Searching "entity2char" already exists finds nothing and I don’t see entity2char in core or any of the plugins. And I don’t see it in retort either, which I was hoping to blame, but removing it didn’t help.

Here are the plugins:

         - git clone https://github.com/discourse/discourse-adplugin.git
         - git clone https://github.com/discourse/discourse-affiliate.git
         - git clone https://github.com/discourse/discourse-bbcode.git
         - git clone https://github.com/discourse/discourse-canned-replies.git
         - git clone https://github.com/discourse/discourse-data-explorer.git
         - git clone https://github.com/discourse/discourse-prometheus.git
         - git clone https://github.com/discourse/discourse-push-notifications.git
         - git clone https://github.com/discourse/discourse-signatures.git
         - git clone https://github.com/discourse/discourse-user-notes.git
         - git clone https://github.com/discourse/discourse-spoiler-alert.git
         - git clone https://github.com/discourse/discourse-cakeday.git
         - git clone https://github.com/discourse/discourse-checklist.git
         - git clone https://github.com/discourse/discourse-calendar.git
         - git clone https://github.com/discourse/discourse-reactions.git

And I tried to look for a entity2char function in the database where I made the backup and wasn’t able to find one.

2

What’s the history of this forum? On which postgres and Discourse version did it start? Was it ever on Bitnami or one of those sketchy hosts?

1 Like
3

It looks like the database dump contains a reference to a entity2char function for some reason. Removing it should make it work unless there’s other stuff in the dump that shouldn’t be there.

1 Like
4

Do you have a hint how to remove it from the existing database? My first Google hint didn’t work.

5

Don’t remove it from the database, remove it from the backup.

6

I was seconds away from deleting it from the database, as it seems much easier to do whatever SQL has for

   drop function entity2char;

I was able to list them it \df

You think if I remove it from the database it’ll break something?

Wait. . . . And the point of this exercise of restoring the production database to the new image I’d like to launch is to make sure that discourse will work with the existing production database when I install the latest Discourse and migrate the database, so modifying the database in order to restore it on staging doesn’t make sense.

7

Yes exactly this

and this :wink:

What MIGHT work is that you try to rename the function in the target database instance just prior to restoring. But I have not tested that.

So (on the target db)

  • ALTER FUNCTION entity2char RENAME TO e2c_outoftheway
  • restore
  • ALTER FUNCTION entity2char RENAME TO e2c_from_backup
  • ALTER FUNCTION e2c_outoftheway RENAME TO entity2char
1 Like
8

Oh! So the issue is that the staging database has that function already, and the backup also has that function and it’s too stupid to understand that it’s the same function.

Maybe I should just drop and create the database on the staging/target site?

But your idea seems a bit easier.

EDIT: OK, I renamed the function and am doing the restore now. In about an hour I can see if it worked.

And the concern is that somehow the entity2char function that gets carried along in the backup will somehow not work with the existing database? Maybe I should just create a fresh new database and restore to it. I’m not a real fan of the name xx-discourse-database-5 anyway.

EDIT: Well, it looks like there are some more moles I will need to whack.

ERROR:  function "replace_mentions" already exists with same argument types

Maybe starting with a new database is the way to go.

9

The solution was to drop, create, and migrate the database before doing the restore. I should have tried skipping the migrate step.

Thanks for your help, @RGJ and @gerhard. Your clues were helpful in my figuring this out.

I guess the reason not to always drop the database first is that you can’t then back out when it fails.

1 Like