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.

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.

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

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

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.

[image] Jay Pfaffman: You think if I remove it from the database it’ll break something? Yes exactly this [image] Jay Pfaffman: so modifying the database in order to restore it on staging doesn’t make sense. 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

[image] Richard - Communiteq: What MIGHT work is that you try to rename the function in the target database instance just prior to restoring. 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.

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.

Restore failed `entity2char already exists' RDS Postgres 13.7

Product Support Self-hosting

RGJ (Richard - Communiteq) November 17, 2022, 10:31pm 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?

Topic		Replies	Views
Error when restore DB backup Self-hosting	25	2268	August 23, 2018
Restore fails with function "post_replies_sync_reply_id" already exists with same argument types Product Support	0	574	July 4, 2020
Restore failing with missing chat_mention function Bug fixed	23	737	October 22, 2024
Error Restoring Backup on Migration Bug	40	5357	January 14, 2020
Restore failing- possible Data Explorer issue Self-hosting	21	2577	December 9, 2019

Restore failed `entity2char already exists' RDS Postgres 13.7

Related topics