Re: Max number of WAL files in pg_xlog directory for Postgres 9.2 version

[Raghavendra Rao J S V]

Hi John,

As you said, I have gone through the document. Which one is correct [(2 +
checkpoint_completion_target) * checkpoint_segments + 1 *or*
checkpoint_segments + wal_keep_segments

+
1 files] for 9.2 PostgreSQL?

In my environment we have kept *wal_keep_segments* and *checkpoint_segments*
as below. Will it cause any negative impact?

*checkpoint_segments = 128* # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
*#wal_keep_segments = 0* # in logfile segments, 16MB each; 0 disables

++

There will always be at least one WAL segment file, and will normally not
be more than (2 + checkpoint_completion_target) * checkpoint_segments + 1
or checkpoint_segments + wal_keep_segments

+
1 files. Each segment file is normally 16 MB (though this size can be
altered when building the server). You can use this to estimate space
requirements for WAL. Ordinarily, when old log segment files are no longer
needed, they are recycled (renamed to become the next segments in the
numbered sequence). If, due to a short-term peak of log output rate, there
are more than 3 * checkpoint_segments + 1 segment files, the unneeded
segment files will be deleted instead of recycled until the system gets
back under this limit.

++



Regards,
Raghavendra Rao


On Wed, 5 Sep 2018 at 23:23, Johnes Castro  wrote:

> 1 wal by default occupies 16MB.
> The parameter in version 9.2 that controls this is: wal_keep_segments
>
>
> By setting the parameter to 10, the maximum size of the US pg_xlog will be
> 160MB.
>
> Best Regards,
> Johnes Castro
>
>
> --
> *De:* Johnes Castro 
> *Enviado:* quarta-feira, 5 de setembro de 2018 15:48
> *Para:* Raghavendra Rao J S V; pgsql-general@lists.postgresql.org
> *Assunto:* RE: Max number of WAL files in pg_xlog directory for Postgres
> 9.2 version
>
> Hi,
>
> This page in the documentation can help you.
> https://www.postgresql.org/docs/9.2/static/wal-configuration.html
>
> Best Regards,
> Johnes Castro
> PostgreSQL: Documentation: 9.2: WAL Configuration
> 
> 29.4. WAL Configuration. There are several WAL-related configuration
> parameters that affect database performance.This section explains their
> use. Consult Chapter 18 for general information about setting server
> configuration parameters.. Checkpoints are points in the sequence of
> transactions at which it is guaranteed that the heap and index data files
> have been updated with all information ...
> www.postgresql.org
>
> --
> *De:* Raghavendra Rao J S V 
> *Enviado:* quarta-feira, 5 de setembro de 2018 15:39
> *Para:* pgsql-general@lists.postgresql.org
> *Assunto:* Max number of WAL files in pg_xlog directory for Postgres 9.2
> version
>
> Hi All,
>
> We are using postgres 9.2 verstion database.
>
> Please let me know, how many max number of wal files in pg_xlog directory?
>
> What is the formul. I am seeing different formulas. Could you provide me
> which decides number of max WAL files in PG_XLOG directory for Postgres 9.2
> Database,please?
>
>
> --
> Regards,
> Raghavendra Rao J S V
>
>

-- 
Regards,
Raghavendra Rao J S V
Mobile- 8861161425

Re: Max number of WAL files in pg_xlog directory for Postgres 9.2 version

[Andreas Kretschmer]

Am 05.09.2018 um 19:39 schrieb Raghavendra Rao J S V:

Hi All,

We are using postgres 9.2 verstion database.


9.2 is out of support. Please consider a upgrade. soon!



Please let me know, how many max number of wal files in pg_xlog directory?


depends on the workload and on several settings. There is no rule of thumb.


Regards, Andreas
--

2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Max number of WAL files in pg_xlog directory for Postgres 9.2 version

[Ron]

On 09/05/2018 12:39 PM, Raghavendra Rao J S V wrote:

Hi All,

We are using postgres 9.2 verstion database.

Please let me know, how many max number of wal files in pg_xlog directory?

What is the formul. I am seeing different formulas. Could you provide me 
which decides number of max WAL files in PG_XLOG directory for Postgres 
9.2 Database,please?


If you're doing WAL file replication, and -- for whatever reason -- the 
standby system isn't applying them fast enough, there could be tens of 
thousands of files in pg_xlog.  (It would drain pretty quickly when you 
resolve the problem, though.)



--
Angular momentum makes the world go 'round.

RE: Max number of WAL files in pg_xlog directory for Postgres 9.2 version

[Johnes Castro]

1 wal by default occupies 16MB.
The parameter in version 9.2 that controls this is: wal_keep_segments


By setting the parameter to 10, the maximum size of the US pg_xlog will be 
160MB.

Best Regards,
Johnes Castro



De: Johnes Castro 
Enviado: quarta-feira, 5 de setembro de 2018 15:48
Para: Raghavendra Rao J S V; pgsql-general@lists.postgresql.org
Assunto: RE: Max number of WAL files in pg_xlog directory for Postgres 9.2 
version

Hi,

This page in the documentation can help you.
https://www.postgresql.org/docs/9.2/static/wal-configuration.html

Best Regards,
Johnes Castro
PostgreSQL: Documentation: 9.2: WAL 
Configuration
29.4. WAL Configuration. There are several WAL-related configuration parameters 
that affect database performance.This section explains their use. Consult 
Chapter 18 for general information about setting server configuration 
parameters.. Checkpoints are points in the sequence of transactions at which it 
is guaranteed that the heap and index data files have been updated with all 
information ...
www.postgresql.org



De: Raghavendra Rao J S V 
Enviado: quarta-feira, 5 de setembro de 2018 15:39
Para: pgsql-general@lists.postgresql.org
Assunto: Max number of WAL files in pg_xlog directory for Postgres 9.2 version

Hi All,

We are using postgres 9.2 verstion database.

Please let me know, how many max number of wal files in pg_xlog directory?

What is the formul. I am seeing different formulas. Could you provide me which 
decides number of max WAL files in PG_XLOG directory for Postgres 9.2 
Database,please?


--
Regards,
Raghavendra Rao J S V

RE: Max number of WAL files in pg_xlog directory for Postgres 9.2 version

[Johnes Castro]

Hi,

This page in the documentation can help you.
https://www.postgresql.org/docs/9.2/static/wal-configuration.html

Best Regards,
Johnes Castro
PostgreSQL: Documentation: 9.2: WAL 
Configuration
29.4. WAL Configuration. There are several WAL-related configuration parameters 
that affect database performance.This section explains their use. Consult 
Chapter 18 for general information about setting server configuration 
parameters.. Checkpoints are points in the sequence of transactions at which it 
is guaranteed that the heap and index data files have been updated with all 
information ...
www.postgresql.org



De: Raghavendra Rao J S V 
Enviado: quarta-feira, 5 de setembro de 2018 15:39
Para: pgsql-general@lists.postgresql.org
Assunto: Max number of WAL files in pg_xlog directory for Postgres 9.2 version

Hi All,

We are using postgres 9.2 verstion database.

Please let me know, how many max number of wal files in pg_xlog directory?

What is the formul. I am seeing different formulas. Could you provide me which 
decides number of max WAL files in PG_XLOG directory for Postgres 9.2 
Database,please?


--
Regards,
Raghavendra Rao J S V

Max number of WAL files in pg_xlog directory for Postgres 9.2 version

[Raghavendra Rao J S V]

Hi All,

We are using postgres 9.2 verstion database.

Please let me know, how many max number of wal files in pg_xlog directory?

What is the formul. I am seeing different formulas. Could you provide me
which decides number of max WAL files in PG_XLOG directory for Postgres 9.2
Database,please?


-- 
Regards,
Raghavendra Rao J S V