Skip to main content

OS Results From PostgreSQL


Hi ,

As of now, we are using "\i" or using "PL/SH" script for getting the OS command results from PostgreSQL. However, below is the one more option where we can get the required info.

Step 1
======
#include "postgres.h"
#include "fmgr.h"
#include <string.h>
#include <unistd.h>
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(shell_exec);
Datum
shell_exec(PG_FUNCTION_ARGS)
{
    text  *arg1 = PG_GETARG_TEXT_P(0);
    char  *Command=VARDATA(arg1);
    int32 result=system(Command);   
    PG_RETURN_TEXT_P(result);
}

Step 2
=======
-bash-3.2$ more Makefile
PG_CONFIG = /opt/PostgresPlus/9.0AS/bin/pg_config
MODULES = shell_exec
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Step 3
======
make and .so file to $PGHOME/lib  or $PGHOME/lib/postgres/

Step 4
======
postgres=# CREATE OR REPLACE FUNCTION shell_exec(text) RETURNS int                           AS 'shell_exec.so', 'shell_exec'  
LANGUAGE C STRICT;
CREATE FUNCTION

Step 5
======
postgres=# select shell_exec('vmstat 1 >/opt/PostgreSQL/9.0/data/output');
^CCancel request sent
postgres=# select pg_read_file('/opt/PostgreSQL/9.0/data/output',1,1000000);
                                       pg_read_file                                      
------------------------------------------------------------------------------------------
 rocs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----         +
  r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st        +
  0  0 2653376 654888  31832 248464    6   14    28   220    8   12  4  5 89  1  0       +
  0  0 2653376 654988  31832 248468    0    0     0    40  693 1310  0  1 99  0  0       +
  1  0 2653376 654856  31840 248468    0    0     0   116  704 1348  0  1 99  0  0       +
  0  0 2653376 654608  31840 248472    0    0     0     0  664 1494  0  1 100  0  0      +
  0  0 2653376 654740  31840 248472    0    0     0     0  631 1522  0  1 99  0  0       +
  0  0 2653376 654732  31840 248476    0    0     0     0  674 1519  0  1 99  0  0       +
  0  0 2653376 654756  31840 248476    0    0     0   156  616 1541  0  1 100  0  0      +
  0  0 2653376 654748  31848 248480    0    0     0    72  620 1331  0  1 99  0  0       +
  0  0 2653376 654748  31848 248480    0    0     0     0  599 1299  0  0 99  0  0       +
  0  0 2653376 654740  31848 248484    0    0     0     0  630 1292  0  1 99  0  0       +
  0  0 2653376 654972  31848 248484    0    0     0    32  664 1358  0  1 99  0
0       +

దినేష్ కుమార్
Dinesh Kumar

Comments

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…