POSTGRESQL 9.1.9 Database Recovery.

Errors thrown in postgresql log file.
2013-09-04 14:06:36 IST 14922 [local] ERROR:  missing chunk number 0 for toast value 76775 in pg_toast_1255
2013-09-04 14:05:07 IST 14922 [local] ERROR:  missing chunk number 0 for toast value 76775 in pg_toast_1255
2013-09-04 14:03:59 IST 17909 [local] ERROR:  missing chunk number 0 for toast value 76775 in pg_toast_1255
2013-09-04 13:54:18 IST 17847 59.99.224.120 ERROR:  missing chunk number 1 for toast value 100134 in pg_toast_2618
2013-09-04 13:50:36 IST 17847 59.99.224.120 ERROR:  missing chunk number 0 for toast value 76775 in pg_toast_1255
2013-09-04 13:48:30 IST 17909 [local] ERROR:  missing chunk number 0 for toast value 76775 in pg_toast_1255
2013-09-04 13:41:35 IST 17847 59.99.224.120 ERROR:  missing chunk number 0 for toast value 76775 in pg_toast_1255
2013-09-04 13:29:40 IST 14942 59.99.224.120 ERROR:  missing chunk number 0 for toast value 87623 in pg_toast_2618
...
2013-09-04 11:31:00 IST 12499 127.0.0.1 ERROR:  missing chunk number 0 for toast value 96044 in pg_toast_2619
2013-09-04 11:30:55 IST 12481 127.0.0.1 ERROR:  missing chunk number 0 for toast value 96044 in pg_toast_2619
2013-09-04 11:30:55 IST 12479 127.0.0.1 ERROR:  missing chunk number 0 for toast value 96044 in pg_toast_2619
2013-09-04 11:30:28 IST 12392 127.0.0.1 ERROR:  missing chunk number 0 for toast value 96044 in pg_toast_2619
...
2013-09-04 00:32:57 IST 15985 [local] DETAIL:  Could not open file "pg_clog/0005": No such file or directory.
2013-09-04 00:32:08 IST 15961 [local] DETAIL:  Could not open file "pg_clog/0004": No such file or directory.
2013-09-04 00:21:59 IST 15833 [local] DETAIL:  Could not open file "pg_clog/0000": No such file or directory.
2013-09-04 00:17:25 IST 15698 [local] DETAIL:  Could not open file "pg_clog/0000": No such file or directory.
2013-09-04 00:11:59 IST 15673 [local] DETAIL:  Could not open file "pg_clog/0031": No such file or directory.
2013-09-04 00:11:20 IST 15664 [local] DETAIL:  Could not open file "pg_clog/0027": No such file or directory.
2013-09-04 00:10:47 IST 15650 [local] DETAIL:  Could not open file "pg_clog/0026": No such file or directory.
2013-09-04 00:10:03 IST 15631 [local] DETAIL:  Could not open file "pg_clog/002B": No such file or directory.
2013-09-04 00:09:32 IST 15624 [local] DETAIL:  Could not open file "pg_clog/0025": No such file or directory.
2013-09-04 00:08:55 IST 15592 [local] DETAIL:  Could not open file "pg_clog/0029": No such file or directory.
2013-09-04 00:08:19 IST 15586 [local] DETAIL:  Could not open file "pg_clog/0035": No such file or directory.
2013-09-04 00:07:44 IST 15579 [local] DETAIL:  Could not open file "pg_clog/002C": No such file or directory.
2013-09-04 00:05:42 IST 15243 [local] DETAIL:  Could not open file "pg_clog/002A": No such file or directory.
2013-09-04 00:03:21 IST 15129 [local] DETAIL:  Could not open file "pg_clog/002A": No such file or directory.
2013-09-04 00:01:28 IST 15005 [local] DETAIL:  Could not open file "pg_clog/0022": No such file or directory.
...
2013-09-03 23:17:06 IST 13266 117.213.89.49 ERROR:  missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:06 IST 13266 117.213.89.49 ERROR:  missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:06 IST 13266 117.213.89.49 ERROR:  missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:06 IST 13266 117.213.89.49 ERROR:  missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:06 IST 13266 117.213.89.49 ERROR:  missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:05 IST 13266 117.213.89.49 ERROR:  missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:05 IST 13266 117.213.89.49 ERROR:  missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:05 IST 13266 117.213.89.49 ERROR:  missing chunk number 0 for toast value 105498 in pg_toast_2619
...
2013-09-03 17:44:53 IST 2456 192.168.1.112 DETAIL:  Could not open file "pg_clog/0039": No such file or directory.
2013-09-03 17:44:53 IST 2456 192.168.1.112 DETAIL:  Could not open file "pg_clog/0039": No such file or directory.

The errors were very complicated and the data in the database(~10GB) were very critical.
Following steps cleared the errors and the DB was restored.

1) TO FIX missing pg_clog/XXXX files.

Used the following commands to create empty logfiles in pg_clog directory.

#dd if=/dev/zero of=<filename> bs=256k count=1
#chown postgres.postgres <filename>
#chmod 600 <filename>

Around 20 files were created.

2) TO FIX "missing chunk number 0 for toast value XXXXX in pg_toast_2619"

   Used the following command to find table name which is corrupted.
 
   courier=#select relname from pg_class where reltoastrelid='pg_toast.pg_toast_2619'::regclass;
 
   It was 'pg_statistic' table which got corrupted.
 
   AS pg_statistic is only a statistic file, deleted all rows in the table.
 
   courier=#DELETE FROM pg_statistic;
 
   *** Note: DB has to create fresh statistic records. Perfromance of initial queries may be slow.
 
 
3) TO FIX "missing chunk number 0 for toast value 87623 in pg_toast_2618"

   Used the following command to find table name which got corrupted.

   courier=#select relname from pg_class where reltoastrelid='pg_toast.pg_toast_2618'::regclass;
 
   It was 'pg_rewrite' table which got corrupted.
 
   pg_rewrite has rules for views and tables.
 
   To find out the corrupted rows, used the following command
 
   courier=#SELECT * FROM pg_rewrite limit 1 offset 27;
 
   Found rules missing for views.

1. weight_mismatch_view.
2. sync_log_baseview.
3. sync_log_view.
4. fen_deliveries.
5. fen_delivery.

 
   These views have column 'relhasrules' in pg_class set to TRUE.
 
   Changed the relhasrules value to FALSE,
   Deleted the views and
   Recreated views from schema definitions in backup.
 
   courier=#UPDATE pg_class SET relhasrules=false where oid=69526;
   courier=#DROP VIEW sync_log_view;
   courier=#CREATE VIEW ................;
 
 
3) TO FIX "missing chunk number 0 for toast value 76775 in pg_toast_1255"

   Used the following command to find table name which got corrupted.
 
   courier=#select relname from pg_class where reltoastrelid='pg_toast.pg_toast_1255'::regclass;
 
   It was 'pg_proc' table which stores the procedures and functions.
 
   To find out the corrupted row, used the following command.
 
   courier=#select proname,prosrc FROM pg_proc order by proname limit 1 offset 562;
 
   Function delivery_update was corrupted. Especially column 'prosrc' procedure source node got corrupted.
 
   Set the prosrc to NULL,
   Droped the function and
   Recreated the function from schema backup.
 
   courier=#UPDATE pg_proc SET prosrc=NULL where proname='delivery_update';
   courier=#DROP FUNCTION delivery_update(date);
   courier=#CREATE OR REPLACE FUNCTION delivery_update(date) ............................;
 
4) Restarted DB. Everything went smooth.

Internet Connection through Idea NetSetter(Huawei E620) from Ubuntu 11.04

Internet connection through USB sticks works out of box in Ubuntu with usb-modeswitch. But Idea NetSetter - Huawei E620, requires a tweak.

1) gedit /lib/udev/rules.d/40-usb_modeswitch.rules file. 

2) Add -H switch (Huawei Mode) in the line under Huawei E169, which will be as follows after the change.

ATTRS{idVendor}=="12d1", ATTRS{idProduct}=="1001", RUN+="usb_modeswitch -H '%b/%k'"

Save the file and restart udev service.

In the Mobile Broadband Configuration Change the Number in the Basic section to #99***1#.

Now, the internet connection can be established through Idea NetSetter (Huawei E620) from Ubuntu 11.04.

How to block admagnet.net advertisements?


When we view the railway related information in http://www.indianrailway.gov.in web site, there will be flood of advertisements from *.admagnet.net. This happens even when the pop-ups are blocked and javascript is disabled in the Chrome preferences.

These advertisements are embedded as flash files just before the body tag. We do not know whether Indian Railway is purposely doing it OR these advertisement lines are inserted on the fly in the en-route. However these advertisements are irritating.

To prevent such ads, just block the plugins(in this case 'Flash') from this web site.

This procedure is Google Chrome specific.

1) Select Settings->preferences.
2) Choose 'Under the hood', then 'Content Settings'.
3) Click Manage Exceptions under 'Plug-ins'.
4) Enter admagnet.net and choose 'Block'. Press Enter to add.


This disables the Flash advertisements.

Also prevent JavaScript from admagnet.net.

That's it, the ads will be blocked in the http://www.indianrailway.gov.in web site. Enjoy browsing Indian Railway Website without irritating ads.

PostgreSQL 'C' Function to execute shell command

How to run a shell command from a PostgreSQL function?

A typical example is to send sms through 'gnokii' with AT drivers.

1) Install gnokii with the following command
sudo apt-get install gnokii

2) Gnokii config shall be in /etc/gnokiirc with appropriate contents. I have used the following for Nokia 6681 connected to server by USB cable. Most of the mobiles will work with this configuration.

[global]

port = /dev/ttyACM0

model = AT

connection = serial

3) For compiling the PostgreSQL 'C' function, install the server development package by issuing the command

sudo apt-get install postgresql-server-dev-8.4

4) Create a shell script to Send SMS through gnokii, make it executable and place it in the common PATH preferably in /usr/local/bin/
  Contents of send_sms.sh:
#!/bin/bash

echo $2 | /usr/bin/gnokii --config /etc/gnokiirc --sendsms $1

if [ $? -eq 0 ]  # Test exit status of "gnokii" command.

then

  exit 0

else  

  exit -1

fi

5) Create a PostgreSQL C function to execute any shell command:
   Contents of exec_shell_command.c

/* POSTGRESQL C Function to execute shell command */
#include <string.h>
#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

/* by value */

PG_FUNCTION_INFO_V1(exec_shell_command);

Datum
exec_shell_command(PG_FUNCTION_ARGS)
{
    text *p1 = PG_GETARG_TEXT_P(0); 
    char *command = VARDATA(p1);
    int retVal = system(command);
    PG_RETURN_INT32(retVal);
}

6) Compile the above c file to generate object code.

gcc -c -fpic -I/usr/include/postgresql/8.4/server exec_shell_command.c -o exec_shell_command.o

The above code is a single line command.

7) Generate shared library from the object code generated in step 6.

gcc -shared -o exec_shell_command.so exec_shell_command.o 

The above code is a single line command.

8) Copy the shared object to PostgreSQL library path.

sudo cp -f exec_shell_command.so /usr/lib/postgresql/8.4/lib/

The above code is a single line command.

9) Add the user 'postgres' to the group 'dialout' to get permission to access USB serialport

sudo usermod  --groups dialout --append postgres

10) Create a function in Postgresql to use the 'C' function just we have created.

CREATE OR REPLACE FUNCTION exec_shell_command(text)

  RETURNS integer AS

'$libdir/exec_shell_command.so', 'exec_shell_command'

  LANGUAGE c STRICT;

Test the function by running the following command in PostgreSQL shell.

SELECT exec_shell_command('send_sms.sh 9894835581 "Hello Rampro, How are you?"');

OR Any other shell command.

That's it.  This function can be called from any procedure to execute any arbitrary shell command.

User Friendly vs User Reliable

On various occasions, I heard some people saying 'Linux is not user friendly'.

I asked them which Linux distribution they used.

Most of them said that they have not at all used Linux. Then how could they come to the conclusion that 'Linux is not user friendly'?

Only because of gossips by their peers.

I took some of them to see the Desktop of latest UBUNTU (10.4 to 11.10) versions. There were impressed on the desktop features and application collection.

I took one of them to one of my customer place, who runs Mandriva Linux version 10, since 2004. For the past seven years he was using it without any interruption, without any virus, without any reformatting of hard disk partitions. From the words of my customer, the gossiper came to know , how reliable Linux is!

I told him Linux is not only 'USER FRIENDLY' but also 'USER RELIABLE'.

Just a beginning

On 1st October 2004,  we started developing and deploying Linux based desktop solutions to various business domains. Initially we wanted to provide a solution to local grocery stores.

In Super Markets and Malls, the consumers are allowed to pick the commodity according to their choice and finally the bill is prepared. Bar code system is helping here much for speedy billing.

But in traditional grocery stores, first the bill is prepared and then the corresponding commodities are picked and delivered by the staff of the shop.

Normally more than 2000 commodities are available in a medium to higher end  grocery store. If a busy grocery shop wants to use computers for billing, selection of the required item among the available items is a key factor in deciding the bill preparation speed.

Also the staff of the grocery shop are not well educated to read English. So, the computers in grocery store shall use local language. The printer shall be able to print in local language at high speed.

Some of the consumers may not understand local language, so there need to be English printing also.

Keeping the above points in mind, we had created a completely Bi-Lingual application on Linux having interfaces and printing facility in both Tamil & English.

We created a mapping for the Unicode Tamil font in Linux to ISCII Tamil fonts for TVSE 450/455 Champion Dot Matrix Printers. This facilitated our application to print in Tamil at the speed of printing English.

Also we have provided multiple selection methods for easily selecting the items required by the customer.

We installed the software in one of a reputable customer in Dharmapuri, Tamil Nadu. We followed up the requirements for the next six months and incorporated the changes needed. Then most of the reputed stores in and around Dharmapuri installed our application and reaped its benefits.

And we had created an awareness about Linux and its strength in our area.