Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are dumping a 4TB postgres database using pg_dump. The source is on a copy-on-write snapshot filesystem, and the target is an external drive.

While the pg_dump is running we get about 4-5MB/sec writes on the filesystem where postgresql data is (iostat), and specifically inside the base/ directory.

Since the only query running on this DB is the COPY initiated from pg_dump which only reads data, who is writing data to postgres datafiles?

We need to find a way to make postgres stop writing because the source copy-on-write filesystem gets filled up before the dump is finished.

Postgresql version is 8.4, vacuum is not running (autovacuum = off).

UPDATE: I recompiled pg_dump to use READ UNCOMMITTED transactions, but that didn't change anything.

share|improve this question
    
When experiencing this, do you have any files in the pg_xlog directory? –  dezso Dec 2 '13 at 16:47
    
xlog dir is not empty. This was good thinking, but after 3 days of pg_dump and 1TB of data modified in the base/ directory I wouldn't think it's the xlog that causes the writes. Moreover there are no reads on the xlog partition (it's a different partition so I can measure easily). –  sivann Dec 3 '13 at 15:04
    
The (exact) call might reveal something ... –  Erwin Brandstetter Feb 1 at 0:14
    
@ErwinBrandstetter it's just a pg_dump, no special options. –  sivann Feb 2 at 11:38

1 Answer 1

DISCLAIMER : Not a PostgreSQL guy

According to the PostgreSQL 8.4 Documentation

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

If no other processes are using the database, then it could be that pg_dump is using a temporary directory within the base folder.

Another member of this forum, Erwin Brandstetter, answered a question back on Nov 3, 2013 about changing the temp folder during a pg_dump. If you do change it to a different disk volume, it should alleviate your writes to the DB during the backup.

Give it a Try !!!

share|improve this answer
    
we do not use --format-tar, and those files written have an oid corresponding to a real table. –  sivann Dec 3 '13 at 15:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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