I am on a team that is building a Drupal website. All of the settings, including the migration scripts from the old site, will be in code, so in theory all of our work can be replicated just from a
git clone. But it is convenient to have backups of the database at various milestones so we don't have to wait on that process every time.
So I want to use pg_dump to make a backup of my database and share it with my team. They would use pg_restore to set up their databases, and life is good. Except that there are always errors or warnings from pg_restore. Whenever I try to fix one, another takes its place.
First, I used
-O so that my team doesn't have to use the same user I do. This option works the best so far, but they see warnings that they don't own PL/pgSQL or the SCHEMA "public".
I can use
-n to specify the schema, but then I have to be careful that the user owns "public", and not postgres. Maybe I should specify a different schema. Can I export the search path with pg_dump?
When developers switch branches to work on different features, some tables and rows may become invalid, so restoring to a backup should involve dropping the database, or at least the schema.
-c -C looks good, but really only works if you're a superuser, I think. This isn't immediately a terrible idea, because developers work on their own machines, but deploying to production will make that a pain, I'm sure.
Is there a best practice I can follow here? Am I over-complicating things?