Restore Strapi postgres db backup fails (Docs say it should work)

Hi! I am using Render to deploy my strapi app which works good!

But when I try to restore one of the automatic backups of my postgres db the job fails. I am doing the exact steps recommended here in the docs PostgreSQL | Render · Cloud Hosting for Developers

  1. Download dump.sql.gz
  2. unzip it locally
  3. run this psql command: psql [connection_string] -f [backup_file_name].sql

Connection works and commands are executed but I get a bunch of errors and the data are not restored in the end…

The errors are of this kind: (Those are just some examples)

psql:2022-04-04T15_56Z.sql:2113: ERROR:  multiple primary keys for table "admin_permissions" are not allowed
psql:2022-04-04T15_56Z.sql:2697: ERROR:  constraint "admin_permissions_created_by_id_fk" for relation "admin_permissions" already exists
psql:2022-04-04T15_56Z.sql:96: ERROR:  must be owner of database template1
You are now connected to database "template1" as user "strapi_xxxx_user"
psql:2022-04-04T15_56Z.sql:192: ERROR:  relation "primarytable" already exists
psql:2022-04-04T15_56Z.sql:3106: ERROR:  must be member of role "postgres"

Hi there, happy to help. Quick question, when you are restoring the database are you restoring it to an empty database? I believe for the backup/restore process it needs to take place on a newly created database.

Hi @tyler ! Thanks for your comment and sorry for my late reply. I somehow got no notification about your message… :frowning:

No I did not create a new database or empty my old one before the import.
Creating a new one may cause other issues probably because maybe the generated username will change then?

What command would you recommend to empty the existing one before doing the import?

IMO the best solution would be if render could provide a postgres “pg_dump” with format “custom/tar/folder”. So we could use “pg_restore” on the existing database. without clearing it or creating a new one. I successfully tested this with my custom made pg_dump with format custom.

What do you think?

Hi N. H.,

My apologies for the delayed response here, when you create the new database you can specify the username to be the generated username from the previous database. Right now this is the easiest way to restore a database from backup. I will pass your suggestion along to the team!

1 Like

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.