0

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

Improve this question
4
  • 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
    – user1822
    Commented Jan 23, 2017 at 7:01
  • @a_horse_with_no_name thanks for reply, wondering why stackoverflow need a readonly mode ? imgur.com/e81ns0Y
    – user1775888
    Commented Jan 23, 2017 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.
    – user1822
    Commented Jan 23, 2017 at 13:59
  • ohh I got it , I misunderstand that means.
    – user1775888
    Commented Jan 23, 2017 at 14:01

1 Answer 1

Reset to default
4

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).

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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