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

Hi Jeff,
autovacuum is off on the DB running on the filesystem snapshot.
What "hint bits" do you suppose it is setting? It's running only one COPY
command for days. Do you have any suggestions to make it more "read only" ?

On 2 December 2013 18:14, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

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