Обсуждение: Archive files growth!!!

paulo matadr wrote:
> How the best way to controling fast growth in my Database.

I'm going to assume, based on the config info you quoted and the subject
line, that you mean controlling the size of the archived WAL files on
the warm standby server. If that assumption is incorrect, you might want
to reply with a bit more detail about your problem.

If so, one option may be to use pg_clearxlogtail to zero the unused
portions of the backup copies of your WAL files then compress them with
(eg) gzip. Make sure to TEST YOUR BACKUPS.

--
Craig Ringer

paulo matadr wrote:
>
> Let me see, for example one insert type (inser into table2 (select *
> from table1), where table have size  26megas,make 226megas for archives
> files.
> (i made a test with parameter wal_bufffer before this is defaul value
> 64k and the same commando make 640megas of archives after modify to
> 1024k a take 226m)
> however continuous confused for me.
> how 26mega of insert generate 226mega of archive.

OK, that's interesting, since those should be full WAL archives, so your
problem isn't to do with unused WAL tails.

When you say that the table is 26 MB, how did you measure that? Is that
the size of a dump of the table using pg_dump ? If so, that is not the
real size of the table as it is actually stored by PostgreSQL.

The best way to find the size of the table is by asking PostgreSQL:

SELECT relname, reltuples, relpages, relpages*8 AS size_kb
FROM pg_class WHERE relname = 'tablename' ORDER BY relpages DESC ;

(I'm assuming that your database uses the default 8kb page size).

This will be quite a bit bigger than the size of the dump.

I am also fairly sure that the WAL contains a record of what is done to
the indexes as well as to the tables. If you have lots of indexes,
especially multi-column indexes, on the table you are interested in then
the WAL files generated by an INSERT will be a lot bigger than the
amount of data inserted.

It might help if you could upload some example data and SQL somewhere.
Right now it is not possible to see what you are doing, so there is a
lot of guesswork involved.

--
Craig Ringer

paulo matadr wrote:
>
> result for your query
>
> "cliente_fone" 341130 3345 26760k

OK. And what about indexes, logging triggers, etc?

Can you post the output of:

   \d client_fone

from psql?

(I can't really imagine how indexes alone could generate that much
logging data, though).

--
Craig Ringer