Skip to main content

::ksdwrt in Oracle and workarround in PostgreSQL::

dbms_system.ksdwrt
================
In Oracle we do have ksdwrt for writing the customized alters into alert_log file as well trace files.
These customized log information is very usefull to DBA while monitoringthe servers.

Step 1:-
======
exec dbms_system.ksdwrt(2,CURRENT_DATE||'::NOTIFY:: '||' ****** Disk Space IS Very Less ... 90% Full
******');

PL/SQL procedure successfully completed.

Step 2:-
=======
SQL> select name,value from v$parameter where name='background_dump_dest';

NAME
--------------------------------------------------
VALUE
--------------------------------------------------
background_dump_dest
D:\ORACLE\PRODUCT\10.2.0\ADMIN\DINESH\BDUMP


Step 3:-
=======
From alertdinesh_log

Fri Nov 11 02:33:07 2011
11-NOV-11::NOTIFY:: ****** Disk Space IS Very Less ... 90% Full ******



In PostgreSQL, we don't have that feature. So,please find the below work arround for this in PostgreSQL.
=======================================================================

Step 1:-
=====
CREATE OR REPLACE FUNCTION PG_LOG_WRITE(MESSAGE TEXT) RETURNS BIGINT
AS
$$
select pg_file_write('pg_log/'||filename,$1,true)
from
(
SELECT
pg_ls_dir AS filename,
pg_size_pretty((pg_stat_file('pg_log/' || pg_ls_dir)).size),
(pg_stat_file('pg_log/' || pg_ls_dir)).modification
FROM
pg_ls_dir('pg_log')
WHERE
(pg_stat_file('pg_log/' || pg_ls_dir)).isdir = false
ORDER BY
modification DESC LIMIT 1
) as sub;
$$
LANGUAGE SQL;

Step 2:-
=====
postgres=# SELECT pg_log_write((SELECT NOW())||' ::NOTIFY:: '||E' ****** Disk Space IS Very Less ... 90% Full ****** \n');

Step 3:-
======
$ tail -100f postgresql-2011-10-20_043319.log

2011-10-20 05:25:04.214368+05:30 ::NOTIFY:: ****** Disk Space IS Very Less ... 90% Full ******

Comments

  1. It works! Thanks a lot for this excellent article.
    Benny Rutten, Flanders, Belgium

    ReplyDelete

Post a Comment

Popular posts from this blog

pgBucket - A new concurrent job scheduler

Hi All,

I'm so excited to announce about my first contribution tool for postgresql. I have been working with PostgreSQL from 2011 and I'm really impressed with such a nice database.

I started few projects in last 2 years like pgHawk[A beautiful report generator for Openwatch] , pgOwlt [CUI monitoring. It is still under development, incase you are interested to see what it is, attaching the image here for you ],


pgBucket [Which I'm gonna talk about] and learned a lot and lot about PostgreSQL/Linux internals.

Using pgBucket we can schedule jobs easily and we can also maintain them using it's CLI options. We can update/insert/delete jobs at online. And here is its architecture which gives you a basic idea about how it works.


Yeah, I know there are other good job schedulers available for PostgreSQL. I haven't tested them and not comparing them with this, as I implemented it in my way.
Features are: OS/DB jobsCron style sytaxOnline job modificationsRequired cli options

Pgpool Configuration & Failback

I would like to share the pgpool configuration, and it's failback mechanism in this post.

Hope it will be helpful to you in creating pgpool and it's failback setup.

Pgpool Installation & Configuration

1. Download the pgpool from below link(Latest version is 3.2.1).
    http://www.pgpool.net/mediawiki/index.php/Downloads


2. Untart the pgpool-II-3.2.1.tar.gz and goto pgpool-II-3.2.1 directory.

3. Install the pgpool by executing the below commands:

./configure ­­prefix=/opt/PostgreSQL92/ ­­--with­-pgsql­-includedir=/opt/PostgreSQL92/include/ --with­-pgsql­-libdir=/opt/PostgreSQL92/lib/ make make install 4. You can see the pgpool files in /opt/PostgreSQL92/bin location.
/opt/PostgreSQL92/bin $ ls clusterdb   droplang  pcp_attach_node  pcp_proc_count pcp_systemdb_info  pg_controldata  pgpool pg_test_fsync pltcl_loadmod  reindexdb createdb    dropuser  pcp_detach_node  pcp_proc_info createlang  ecpg      pcp_node_count   pcp_promote_node oid2name  pcp_pool_status  pcp_stop_pgpool  …

N-Node Mutlimaster Replication With Bucardo...!

Our team recently got  a problem, which is to solve the N-Node multi master replication in PostgreSQL.

We all know that, there are some other db engines like Postgres-XC which works in this way. But, we don't have any tool available in PostgreSQL, except Bucardo.

Bucardo is the nice solution for 2-Nodes. Is there a way we can exceed this limitation from 2 to N..?

As an initial step on this, I have done with 3 Nodes, which I believe, we can extend this upto N. { I might be wrong here.}

Please follow the below steps to set up the 1 - 1 multi master replication.

1. Follow the below steps to get all the pre-requisites for the Bucardo.

yum install perl-DBIx-Safe or apt-get install libdbix-safe-perl Install the below components from CPAN. DBI DBD::Pg Test::Simple boolean (Bucardo 5.0 and higher) Download the latest tarball from here. tar xvfz Bucardo-4.4.8.tar.gz cd Bucardo-4.4.8 perl Makefile.PL make sudo make install 2. We need to create plperl extension in db. For this, download…