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?


It turns out I have been trying to over-simplify things. Somehow, I didn't try using both -O and -n until today. Now pg_restore doesn't throw errors, and my CI server will continue with tests. And it will keep my team from seeing warnings when they shouldn't.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.