I am moving a schema from one server to another using pg_dump + pg_restore + drop schema cascade. I need to know that no applications are writing to it (or at least that the writes will fail) during this process to avoid losing data.

At the moment I am renaming the schema before starting the dump. Is that sufficient to prevent writes? What if a large batch insert is busy when I rename the schema? Will that still complete?


You can REVOKE access from that schema, but I am affraid the application will have some unwanted effects. PostgreSQL has fine tuned permission administration. And later you can GRANT access to that schema.

  • Can I do that (and restore access) as a 'normal' user? – David Tinker Nov 18 '14 at 13:35
  • You can if you are owner of the schema (creator), otherwise you need to be super user or admin. – Mladen Uzelac Nov 18 '14 at 14:02

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.