pg_dump on heavily written production db causing queries to hang



  • Trying to migrate our existing DB. There are no followers or existing backups that are available to us (they are on our cloud solution, but no available for download). So our only option is to run pg_dump on our prod table.

    It is very heavily written to and is roughly 700GB in size.

    pg_dump -d ${DB_URL} -Fc -Z 9 -f db.bak
    

    Running the following will begin the dump, but seems to cause every other query being run to hang after awhile. There are no schema changes occurring during the dump, only heavy writes (which I thought wasn't any issue). I cannot stop any of the jobs(listeners just inserting data) as it would throw production out of sync for the entirety of the time the backup was occurring.

    Are there any ways to optimize this?



  • We ended up just forking the database on the service for a single day, backing up on that as , and then shutting it down.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2