I want to backup postgresql data,
right now I use disable insert,update,delete in my application level, become readonly mode.

But I'm wondering what happen if insert,update,delete during dump database,
how to prevent it with postgresql function?
does dump automatic lock(insert,update,delete) table,
or is there any method do this, lock(insert,update,delete) and dump in same query?

my dump query:
pg_dump -U postgres -f backup/dbname.sql dbname

  • 5
    You don't need to "disable insert/update/delete". pg_dump will take a (transactional) consistent snapshot of the database from that moment when the dump started Jan 23 '17 at 7:01
  • @a_horse_with_no_name thanks for reply, wondering why stackoverflow need a readonly mode ? imgur.com/e81ns0Y Jan 23 '17 at 13:58
  • 1
    I have no idea how that is related to you question. For one SO is using SQL Server, not Postgres. And that message says nothing about backups being taken, only "maintenance" which could mean anything. From patching the operating system to deploying a new version of the application. Jan 23 '17 at 13:59
  • ohh I got it , I misunderstand that means. Jan 23 '17 at 14:01

You don't need to disable writes to the database while pg_dump is running. It's designed to create consistent backups by default. Quoting the manual:

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).


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.