Re: While only running pg_dump, postgresql performs writes inside base/ directory. How can we stop this?

On Mon, Dec 2, 2013 at 6:22 AM, Spiros Ioannou <sivann(at)inaccess(dot)com> wrote:

> 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?
>

It is probably setting "hint bits" on the tuples it is visiting.

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

Vacuum the system before taking the snapshot.

>
> Postgresql version is 8.4, vacuum is not running (autovacuum = off), OS is
> debian 6.
>

Since autovacuum is off, what are you doing about vacuuming?

Cheers,

Jeff