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…
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.
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!