Re: [GENERAL] Monitor pg_xlog size via SQL with postgres 9.4

2016-09-22 Thread Sylvain Marechal
Thank you all, I thought there may be a specific posgresql stored procedure
that did the work of enumerating the files of the pg_xlog directory to get
its full  size, or may be one that gave the full size of the server
databases including pg_xlog.
Nevermind, this does the work.

Regards,
Sylvain
​

2016-09-22 16:05 GMT+02:00 hubert depesz lubaczewski :

> On Thu, Sep 22, 2016 at 02:23:20PM +0200, Sylvain Marechal wrote:
> > is there a way to monitor the size of the pg_xlog directory in SQL? The
>
> Assuming you have superuser privileges, it will most likely work:
>
> select sum( (f).size ) from (select pg_stat_file('pg_xlog/' || pg_ls_dir)
> as f from pg_ls_dir('pg_xlog/')) x where not (f).isdir;
>
> Best regards,
>
> depesz
>
>


Re: [GENERAL] Monitor pg_xlog size via SQL with postgres 9.4

2016-09-22 Thread hubert depesz lubaczewski
On Thu, Sep 22, 2016 at 02:23:20PM +0200, Sylvain Marechal wrote:
> is there a way to monitor the size of the pg_xlog directory in SQL? The

Assuming you have superuser privileges, it will most likely work:

select sum( (f).size ) from (select pg_stat_file('pg_xlog/' || pg_ls_dir) as f 
from pg_ls_dir('pg_xlog/')) x where not (f).isdir;

Best regards,

depesz



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Monitor pg_xlog size via SQL with postgres 9.4

2016-09-22 Thread Francisco Olarte
Hello:

On Thu, Sep 22, 2016 at 2:23 PM, Sylvain Marechal
 wrote:
> is there a way to monitor the size of the pg_xlog directory in SQL? The goal
> is to monitor the pg_xlog file without ressorting to a 'du' like solution
> that needs a direct access to the machine.

Well AFAIK SQL doesn't even have the concept of a directory (
functions callable from SQL provide it, but SQL is for databases &
AMOF you can have a perfectly valid SQL db without disks ), so I
assume you want some way to get at the machine disk usage without
opening a shell on it, from a db client. You could try one of the
functions in

https://www.postgresql.org/docs/9.5/static/functions-admin.html#FUNCTIONS-ADMIN-GENFILE

and many of the untrusted programming languages for postgres functions
( plperl, plpython, etc ) has methods of calling stat in the server.

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Monitor pg_xlog size via SQL with postgres 9.4

2016-09-22 Thread Jehan-Guillaume de Rorthais
On Thu, 22 Sep 2016 14:23:20 +0200
Sylvain Marechal  wrote:

> Hello all,
> 
> is there a way to monitor the size of the pg_xlog directory in SQL? The
> goal is to monitor the pg_xlog file without ressorting to a 'du' like
> solution that needs a direct access to the machine.

You might be inspired by the following query:

https://github.com/OPMDG/check_pgactivity/blob/master/check_pgactivity#L6156

Regards,


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[GENERAL] Monitor pg_xlog size via SQL with postgres 9.4

2016-09-22 Thread Sylvain Marechal
Hello all,

is there a way to monitor the size of the pg_xlog directory in SQL? The
goal is to monitor the pg_xlog file without ressorting to a 'du' like
solution that needs a direct access to the machine.

I know I can get the retained size for existing replication slots segment
in case there are some with the following query :
<<<
SELECT slot_name, database, active,
pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS
retained_bytes FROM pg_replication_slots  ;
>>>

but how could I monitor the real size of the pg_xlog directory and detect
it is growing (may be because the archive_command is becomed faulty)

I was thinking of using the last_archived_wal field of the pg_stat_archiver
table, but I am not sure it is accurate, and I do not find any postgres
stored procedure that converts the file name into an LSN (ie, a function
that does the opposite of pg_xlogfile_name())

Thanks and regards,
Sylvain