Error when restoring the official dump file in dev environment

[nicolas-heigvd]

Hello,

I met an error during the database restore process:

api-db-1  | 2024-10-11 08:39:39.382 UTC [59] ERROR:  schema "topology" already exists
api-db-1  | 2024-10-11 08:39:39.382 UTC [59] STATEMENT:  CREATE SCHEMA topology;
api-db-1  | psql:/tmp/dump.sql:32: ERROR:  schema "topology" already exists

This is not not blocking but I think that the plain text dump as currently made is not ideal for several reasons.
Therefore, I'm wondering if we couldn't take advantage of a custom dump instead?

 12G oct 11 05:10 PostgreSQL.sql                 <---- the official dump
4.1G oct 10 12:49 20241010103350_smapshot.dump   <---- a dump I made

Because the plain text one not only is it really fat, but it also poses a few tricky problems:

• it can't be accessed without being root on the server,
• must be unpacked 2x manually with two additional tools (tar and gzip),
• raises the error mentioned above during restore,
• restore with psql is slow and inflexible; even PostgreSQL developers don't necessarily recommend doing it that way

Working on it....

[slecorne]

How did you create your custom dump? It would be nice if we had a lighter dump that was faster to restore. We could also have a way to create lighter development dumps.
Other points are not critical. The backup on the server is meant to restore production in case of issues (it is also backed up in the cloud), and it contains sensitive data (emails), so I think it is logical to keep it with only root access.