fxp0.org.ua -- Databases

PostgreSQL table partitioning and flow-export

To export data aggregated by flow-capture to Postgresql, similar table structure must be created:

After this, flow-capture must be launched as following:

#file: /etc/flow-tools/flow-capture.conf
-w /var/flowdata -n 95  -N 3 -R /usr/local/bin/flowdata-export.sh 0/0/555

This means that it will run /usr/local/bin/flowdata-export.sh script every time it rotates its log file. With the first argument as the flow file name after rotating. Here's is the simple script:

#!/bin/sh

LOGS=/var/flowdata
DB=dbuser:dbpass:dbhost:dbport:dbname:dbtable

if [ $# -lt 1 ]; then
    echo "USE: $0 <filename>"
    exit
fi

flow-export -f 5 -m UNIX_SECS,DOCTETS,SRCADDR,DSTADDR,SRCPORT,DSTPORT,PROT -u $DB < $LOGS/$1

There can be a lot of data in db, and it could be wise to split it into smaller chunks. This can be done using postgresql table partitioning. Here's how we can improve our database/table setup:

CREATE TABLE flowdata (
    unix_secs   bigint NOT NULL,
    doctets     bigint NOT NULL,
    srcaddr     inet NOT NULL,
    dstaddr     inet NOT NULL,
    srcport     integer NOT NULL,
    dstport     integer NOT NULL,
    prot        integer NOT NULL
);

CREATE TABLE flowdata_01 (
    CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 1 )
) INHERITS (flowdata);

CREATE TABLE flowdata_02 (
    CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 2 )
) INHERITS (flowdata);

CREATE TABLE flowdata_03 (
    CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 3 )
) INHERITS (flowdata);

CREATE TABLE flowdata_04 (
    CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 4 )
) INHERITS (flowdata);

CREATE TABLE flowdata_05 (
    CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 5 )
) INHERITS (flowdata);

CREATE TABLE flowdata_06 (
    CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 6 )
) INHERITS (flowdata);

CREATE TABLE flowdata_07 (
    CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 7 )
) INHERITS (flowdata);

CREATE TABLE flowdata_08 (
    CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 8 )
) INHERITS (flowdata);

CREATE TABLE flowdata_09 (
    CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 9 )
) INHERITS (flowdata);

CREATE TABLE flowdata_10 (
    CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 10 )
) INHERITS (flowdata);

CREATE TABLE flowdata_11 (
    CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 11 )
) INHERITS (flowdata);

CREATE TABLE flowdata_12 (
    CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 12 )
) INHERITS (flowdata);

CREATE INDEX flowdata_01_unix_secs ON flowdata_01 ( unix_secs );
CREATE INDEX flowdata_01_srcaddr ON flowdata_01 ( srcaddr );
CREATE INDEX flowdata_01_dstaddr ON flowdata_01 ( dstaddr );
CREATE INDEX flowdata_01_srcport ON flowdata_01 ( srcport );
CREATE INDEX flowdata_01_dstport ON flowdata_01 ( dstport );
CREATE INDEX flowdata_01_prot ON flowdata_01 ( prot );

CREATE INDEX flowdata_02_unix_secs ON flowdata_02 ( unix_secs );
CREATE INDEX flowdata_02_srcaddr ON flowdata_02 ( srcaddr );
CREATE INDEX flowdata_02_dstaddr ON flowdata_02 ( dstaddr );
CREATE INDEX flowdata_02_srcport ON flowdata_02 ( srcport );
CREATE INDEX flowdata_02_dstport ON flowdata_02 ( dstport );
CREATE INDEX flowdata_02_prot ON flowdata_02 ( prot );

CREATE INDEX flowdata_03_unix_secs ON flowdata_03 ( unix_secs );
CREATE INDEX flowdata_03_srcaddr ON flowdata_03 ( srcaddr );
CREATE INDEX flowdata_03_dstaddr ON flowdata_03 ( dstaddr );
CREATE INDEX flowdata_03_srcport ON flowdata_03 ( srcport );
CREATE INDEX flowdata_03_dstport ON flowdata_03 ( dstport );
CREATE INDEX flowdata_03_prot ON flowdata_03 ( prot );

CREATE INDEX flowdata_04_unix_secs ON flowdata_04 ( unix_secs );
CREATE INDEX flowdata_04_srcaddr ON flowdata_04 ( srcaddr );
CREATE INDEX flowdata_04_dstaddr ON flowdata_04 ( dstaddr );
CREATE INDEX flowdata_04_srcport ON flowdata_04 ( srcport );
CREATE INDEX flowdata_04_dstport ON flowdata_04 ( dstport );
CREATE INDEX flowdata_04_prot ON flowdata_04 ( prot );

CREATE INDEX flowdata_05_unix_secs ON flowdata_05 ( unix_secs );
CREATE INDEX flowdata_05_srcaddr ON flowdata_05 ( srcaddr );
CREATE INDEX flowdata_05_dstaddr ON flowdata_05 ( dstaddr );
CREATE INDEX flowdata_05_srcport ON flowdata_05 ( srcport );
CREATE INDEX flowdata_05_dstport ON flowdata_05 ( dstport );
CREATE INDEX flowdata_05_prot ON flowdata_05 ( prot );

CREATE INDEX flowdata_06_unix_secs ON flowdata_06 ( unix_secs );
CREATE INDEX flowdata_06_srcaddr ON flowdata_06 ( srcaddr );
CREATE INDEX flowdata_06_dstaddr ON flowdata_06 ( dstaddr );
CREATE INDEX flowdata_06_srcport ON flowdata_06 ( srcport );
CREATE INDEX flowdata_06_dstport ON flowdata_06 ( dstport );
CREATE INDEX flowdata_06_prot ON flowdata_06 ( prot );

CREATE INDEX flowdata_07_unix_secs ON flowdata_07 ( unix_secs );
CREATE INDEX flowdata_07_srcaddr ON flowdata_07 ( srcaddr );
CREATE INDEX flowdata_07_dstaddr ON flowdata_07 ( dstaddr );
CREATE INDEX flowdata_07_srcport ON flowdata_07 ( srcport );
CREATE INDEX flowdata_07_dstport ON flowdata_07 ( dstport );
CREATE INDEX flowdata_07_prot ON flowdata_07 ( prot );

CREATE INDEX flowdata_08_unix_secs ON flowdata_08 ( unix_secs );
CREATE INDEX flowdata_08_srcaddr ON flowdata_08 ( srcaddr );
CREATE INDEX flowdata_08_dstaddr ON flowdata_08 ( dstaddr );
CREATE INDEX flowdata_08_srcport ON flowdata_08 ( srcport );
CREATE INDEX flowdata_08_dstport ON flowdata_08 ( dstport );
CREATE INDEX flowdata_08_prot ON flowdata_08 ( prot );

CREATE INDEX flowdata_09_unix_secs ON flowdata_09 ( unix_secs );
CREATE INDEX flowdata_09_srcaddr ON flowdata_09 ( srcaddr );
CREATE INDEX flowdata_09_dstaddr ON flowdata_09 ( dstaddr );
CREATE INDEX flowdata_09_srcport ON flowdata_09 ( srcport );
CREATE INDEX flowdata_09_dstport ON flowdata_09 ( dstport );
CREATE INDEX flowdata_09_prot ON flowdata_09 ( prot );

CREATE INDEX flowdata_10_unix_secs ON flowdata_10 ( unix_secs );
CREATE INDEX flowdata_10_srcaddr ON flowdata_10 ( srcaddr );
CREATE INDEX flowdata_10_dstaddr ON flowdata_10 ( dstaddr );
CREATE INDEX flowdata_10_srcport ON flowdata_10 ( srcport );
CREATE INDEX flowdata_10_dstport ON flowdata_10 ( dstport );
CREATE INDEX flowdata_10_prot ON flowdata_10 ( prot );

CREATE INDEX flowdata_11_unix_secs ON flowdata_11 ( unix_secs );
CREATE INDEX flowdata_11_srcaddr ON flowdata_11 ( srcaddr );
CREATE INDEX flowdata_11_dstaddr ON flowdata_11 ( dstaddr );
CREATE INDEX flowdata_11_srcport ON flowdata_11 ( srcport );
CREATE INDEX flowdata_11_dstport ON flowdata_11 ( dstport );
CREATE INDEX flowdata_11_prot ON flowdata_11 ( prot );

CREATE INDEX flowdata_12_unix_secs ON flowdata_12 ( unix_secs );
CREATE INDEX flowdata_12_srcaddr ON flowdata_12 ( srcaddr );
CREATE INDEX flowdata_12_dstaddr ON flowdata_12 ( dstaddr );
CREATE INDEX flowdata_12_srcport ON flowdata_12 ( srcport );
CREATE INDEX flowdata_12_dstport ON flowdata_12 ( dstport );
CREATE INDEX flowdata_12_prot ON flowdata_12 ( prot );

CREATE OR REPLACE RULE flowdata_insert_01 AS
ON INSERT TO flowdata WHERE
    ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 1 )
DO INSTEAD
    INSERT INTO flowdata_01 VALUES ( NEW.unix_secs,
                                     NEW.doctets,
                                     NEW.srcaddr, 
                                     NEW.dstaddr,
                                     NEW.srcport,
                                     NEW.dstport,
                                                                         NEW.prot );

CREATE OR REPLACE RULE flowdata_insert_02 AS
ON INSERT TO flowdata WHERE
    ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 2 )
DO INSTEAD
    INSERT INTO flowdata_02 VALUES ( NEW.unix_secs,
                                     NEW.doctets,
                                     NEW.srcaddr, 
                                     NEW.dstaddr,
                                     NEW.srcport,
                                     NEW.dstport,
                                                                         NEW.prot );

CREATE OR REPLACE RULE flowdata_insert_03 AS
ON INSERT TO flowdata WHERE
    ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 3 )
DO INSTEAD
    INSERT INTO flowdata_03 VALUES ( NEW.unix_secs,
                                     NEW.doctets,
                                     NEW.srcaddr, 
                                     NEW.dstaddr,
                                     NEW.srcport,
                                     NEW.dstport,
                                                                         NEW.prot );

CREATE OR REPLACE RULE flowdata_insert_04 AS
ON INSERT TO flowdata WHERE
    ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 4 )
DO INSTEAD
    INSERT INTO flowdata_04 VALUES ( NEW.unix_secs,
                                     NEW.doctets,
                                     NEW.srcaddr, 
                                     NEW.dstaddr,
                                     NEW.srcport,
                                     NEW.dstport,
                                                                         NEW.prot );

CREATE OR REPLACE RULE flowdata_insert_05 AS
ON INSERT TO flowdata WHERE
    ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 5 )
DO INSTEAD
    INSERT INTO flowdata_05 VALUES ( NEW.unix_secs,
                                     NEW.doctets,
                                     NEW.srcaddr, 
                                     NEW.dstaddr,
                                     NEW.srcport,
                                     NEW.dstport,
                                                                         NEW.prot );

CREATE OR REPLACE RULE flowdata_insert_06 AS
ON INSERT TO flowdata WHERE
    ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 6 )
DO INSTEAD
    INSERT INTO flowdata_06 VALUES ( NEW.unix_secs,
                                     NEW.doctets,
                                     NEW.srcaddr, 
                                     NEW.dstaddr,
                                     NEW.srcport,
                                     NEW.dstport,
                                                                         NEW.prot );

CREATE OR REPLACE RULE flowdata_insert_07 AS
ON INSERT TO flowdata WHERE
    ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 7 )
DO INSTEAD
    INSERT INTO flowdata_07 VALUES ( NEW.unix_secs,
                                     NEW.doctets,
                                     NEW.srcaddr, 
                                     NEW.dstaddr,
                                     NEW.srcport,
                                     NEW.dstport,
                                                                         NEW.prot );

CREATE OR REPLACE RULE flowdata_insert_08 AS
ON INSERT TO flowdata WHERE
    ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 8 )
DO INSTEAD
    INSERT INTO flowdata_08 VALUES ( NEW.unix_secs,
                                     NEW.doctets,
                                     NEW.srcaddr, 
                                     NEW.dstaddr,
                                     NEW.srcport,
                                     NEW.dstport,
                                                                         NEW.prot );

CREATE OR REPLACE RULE flowdata_insert_09 AS
ON INSERT TO flowdata WHERE
    ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 9 )
DO INSTEAD
    INSERT INTO flowdata_09 VALUES ( NEW.unix_secs,
                                     NEW.doctets,
                                     NEW.srcaddr, 
                                     NEW.dstaddr,
                                     NEW.srcport,
                                     NEW.dstport,
                                                                         NEW.prot );

CREATE OR REPLACE RULE flowdata_insert_10 AS
ON INSERT TO flowdata WHERE
    ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 10 )
DO INSTEAD
    INSERT INTO flowdata_10 VALUES ( NEW.unix_secs,
                                     NEW.doctets,
                                     NEW.srcaddr, 
                                     NEW.dstaddr,
                                     NEW.srcport,
                                     NEW.dstport,
                                                                         NEW.prot );

CREATE OR REPLACE RULE flowdata_insert_11 AS
ON INSERT TO flowdata WHERE
    ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 11 )
DO INSTEAD
    INSERT INTO flowdata_11 VALUES ( NEW.unix_secs,
                                     NEW.doctets,
                                     NEW.srcaddr, 
                                     NEW.dstaddr,
                                     NEW.srcport,
                                     NEW.dstport,
                                                                         NEW.prot );

CREATE OR REPLACE RULE flowdata_insert_12 AS
ON INSERT TO flowdata WHERE
    ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 12 )
DO INSTEAD
    INSERT INTO flowdata_12 VALUES ( NEW.unix_secs,
                                     NEW.doctets,
                                     NEW.srcaddr, 
                                     NEW.dstaddr,
                                     NEW.srcport,
                                     NEW.dstport,
                                                                         NEW.prot );

After this, there will be a dedicated table for every month. Data can be insterted into flowdata table as before (as well as selected), though in fact it will reside in its corresponding table. Such structure is good for keeping logs for the last year. All we need to do is to flush current month table every first day of the month. Thus we'll always have data for the last 12 monthes.

Posted by denis on Thursday, September 27th, 2007 at 1:47 p.m. (10 months, 2 weeks ago), 0 comments added so far

PowerDNS, gmysql backend and Chicken and Egg Problem

Yesterday I had a hard time making PowerDNS (pdns) start working using gmysql backend. Like I figured out later, this was a «Chicken and Egg problem». To start working, pdns must connect to mysql, to send its queries. But mysql needs DNS to resolve pdns' ip in order to authenticate it.

To avoid such situations in future, i added skip-name-resolve into /etc/my.cnf and substituted all hostnames with corresponding ip's in mysq.user table and some config files.

Posted by denis on Thursday, July 19th, 2007 at 12:14 p.m. (1 year ago), 0 comments added so far

Setup of libpam-mysql, libnss-mysql-bg with Django admin application

When you have many servers to manage, it can be usefull to have some centrelized server for user authentication and accounting. Libpam-mysql and libnss-mysql-bg permit keep your data in mysql db and make it possible to use it by pam-aware services on your system.

From the other hand, it is goot to have some web interface to manage all these records. I decided to use excellent Admin application from Django framework for this purpose

Here are the simple steps:

$ django-admin startproject webadmin
$ cd webadmin
$ ./manage.py startapp pam

After this, costumize your settings.py and add similar models.py to the pam/ folder.

# pam/models.py
from django.db import models

SHELLS = (
    ('/bin/sh', 'Bash'),
    ('/bin/false', 'No Shell'),
)

class Group(models.Model):
    gid = models.AutoField(primary_key=True)
    name = models.CharField(maxlength=15, unique=True, db_index=True)
    password = models.CharField(maxlength=128, db_index=True, default='x')
    class Admin:
        pass
    
    def __str__(self):
        return self.name

class User(models.Model):
    uid = models.AutoField(primary_key=True)
    username = models.CharField(maxlength=15, unique=True, db_index=True)
    group = models.ForeignKey(Group, verbose_name='primary group', related_name='primary_group', db_column='gid')
    groups = models.ManyToManyField(Group, verbose_name='groups', blank=True)
    gecos = models.CharField(maxlength=128, blank=True)
    homedir = models.CharField(maxlength=128, default='/home/')
    shell = models.CharField(maxlength=128, default='/bin/sh', choices=SHELLS)
    password = models.CharField(maxlength=128, db_index=True, help_text='Plain text, will be crypted on saving')
    lstchg = models.PositiveIntegerField(default=1, editable=False, help_text='days since Jan 1, 1970 that password was last changed')
    min = models.PositiveIntegerField(default=0, help_text='days before password may be changed')
    max = models.PositiveIntegerField(default=99999, help_text='days after which password must be changed')
    warn = models.PositiveIntegerField(default=0, help_text='days before password is to expire that user is warned')
    expire = models.IntegerField(default=-1, help_text='days after password expires that account is disabled')
    inact = models.PositiveIntegerField(default=0, help_text='days since Jan 1, 1970 that account is disabled')
    flag = models.PositiveIntegerField(default=0, editable=False)
    is_active = models.BooleanField('active', default=True)
    class Admin:
        pass

def save(self):
        super(User, self).save()
        if self.uid:
            if self.password and not self.password.startswith('$1$'):
                import crypt,random
                salt = ('$1$%s') % ''.join(random.sample('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ./', 8))
                password = crypt.crypt(self.password, salt)
                self.password = password
                self.save()
                
    def __str__(self):
        return self.username

To prevent system and mysql uids/gids overlapping, it's probably a good idea to set Mysql sequence numbers to some reasonable value, like 2000 or similar.

# apt-get install libnss-mysql-bg libpap-mysql After this, make you config files look similar to this:

# /etc/libnss-mysql.cfg
getpwnam    SELECT username,'x',uid,gid,gecos,homedir,shell \
            FROM pam_user \
            WHERE username='%1$s' AND is_active=1 \
            LIMIT 1
getpwuid    SELECT username,'x',uid,gid,gecos,homedir,shell \
            FROM pam_user \
            WHERE uid='%1$u' \
            LIMIT 1
getspnam    SELECT username,password,lstchg,min,max,warn,inact,expire,flag \
            FROM pam_user \
            WHERE username='%1$s' \
            LIMIT 1
getpwent    SELECT username,'x',uid,gid,gecos,homedir,shell \
            FROM pam_user
getspent    SELECT username,password,lstchg,min,max,warn,inact,expire,flag \
            FROM pam_user
getgrnam    SELECT name,password,gid \
            FROM pam_group \
            WHERE name='%1$s' \
            LIMIT 1
getgrgid    SELECT name,password,gid \
            FROM pam_group \
            WHERE gid='%1$u' \
            LIMIT 1
getgrent    SELECT name,password,gid \
            FROM pam_group
memsbygid   SELECT pam_user.username \
            FROM pam_user,pam_user_groups \
            WHERE pam_user_groups.group_id='%1$u' AND pam_user.uid=pam_user_groups.user_id
gidsbymem   SELECT pam_user_groups.group_id \
            FROM pam_user,pam_user_groups \
            WHERE pam_user.username='%1$s' AND pam_user.uid=pam_user_groups.user_id

host        192.168.1.1
database    webadmin
username   webadmin
password    secret
timeout      3
compress    0

# /etc/libnss-mysql-root.cfg
username    nss-root
password    s3cr3t

After this, you need to add some data into db using Django admin interface, and tweak pam configuration. In Debian/Ubuntu this can be done like this:

# /etc/nsswitch.conf
passwd:         compat mysql
group:          compat mysql
shadow:         compat mysql

[...]

# /etc/pam.d/common-auth

auth    [success=1 default=ignore]      pam_unix.so
auth    required                        pam_mysql.so user=webadmin passwd=secret host=192.168.1.1 db=webadmin table=pam_user usercolumn=pam_user.username passwdcolumn=pam_user.password crypt=1 [where=pam_user.is_active="1"]
auth    required                        pam_permit.so

# /etc/pam.d/common-account

account [success=1 default=ignore]      pam_unix.so
account required                        pam_mysql.so user=webadmin passwd=secret host=192.168.1.1 db=webadmin table=pam_user usercolumn=pam_user.username passwdcolumn=pam_user.password crypt=1 [where=pam_user.is_active="1"]
account required                        pam_permit.so

# /etc/pam.d/common-password

password       sufficient      pam_mysql.so user=webadmin passwd=secret host=192.168.1.1 db=webadmin table=pam_user usercolumn=pam_user.username passwdcolumn=pam_user.password crypt=1 [where=pam_user.is_active="1"]
password       required        pam_unix.so nullok obscure min=4 max=8 md5 try_first_pass

# /etc/pam.d/common-session

session required    pam_mkhomedir.so skel=/etc/skel/ umask=0022
session required        pam_unix.so

That's all! Now things like id <some_mysql_user> should work.

P.S.
I had to do /etc/init.d/sshd reload to make it work for new mysql users.

Posted by denis on Wednesday, January 31st, 2007 at 11:31 a.m. (1 year, 6 months ago), 2 comments added so far

Pgcluster: error on balancer

I'm currently playing with Pgcluster 1.5.0rc9, which is a multi-master and synchronous replication system that supports load balancing for PostgreSQL. This version is based on PostgreSQL 8.1.4. There's no much documentation i could found, but everything was going smoothly untill i tried to lunch pglb. This daemon must be run on Pgcluster load balancer. I took default config pglb.conf and substituted hostnames with the right ones:

#============================================================
#          Load Balance Server configuration file
#-------------------------------------------------------------
# file: pglb.conf
#-------------------------------------------------------------
# This file controls:
#       o which hosts are db cluster server
#       o which port  use connect to db cluster server
#       o how many connections are allowed on each DB server
#============================================================
#-------------------------------------------------------------
# set cluster DB server information
#               o Host_Name : hostname of Cluster DB
#                             -- please write a host name by FQDN or IP address.
#               o Port : connection for postmaster
#               o Max_Connection : maximun number of connection to postmaster
#-------------------------------------------------------------
<Cluster_Server_Info>
    <Host_Name>     n1.example.com  </Host_Name>
    <Port>                 5432                   </Port>
    <Max_Connect>  0                          </Max_Connect>
</Cluster_Server_Info>
<Cluster_Server_Info>
    <Host_Name>     n2.example.com  </Host_Name>
    <Port>                 5432                   </Port>
    <Max_Connect>  0                         </Max_Connect>
</Cluster_Server_Info>
#-------------------------------------------------------------
# set Load Balance server information
#               o Host_Name : The host name of this load balance server.
#                             -- please write a host name by FQDN or IP address.
#               o Backend_Socket_Dir : Unix domain socket path for the backend
#               o Receive_Port : connection from client
#               o Recovery_Port : connection for recovery process
#               o Max_Cluster_Num : maximun number of cluster DB servers
#               o Use_Connection_Pooling : use connection pool [yes/no] 
#-------------------------------------------------------------
<Host_Name> b.example.com </Host_Name>
<Backend_Socket_Dir>    /tmp     </Backend_Socket_Dir>
<Receive_Port>               5432     </Receive_Port>
<Recovery_Port>             6001     </Recovery_Port>
<Max_Cluster_Num>        128      </Max_Cluster_Num>
<Use_Connection_Pooling>  yes     </Use_Connection_Pooling>

Configuration looked ok, but pglb failed to run:

2006-10-25 12:40:47 [5272] DEBUG:PGRset_status_on_cluster_tbl():host:n1.example.com port:5432 max:0 use:0 status2
2006-10-25 12:40:47 [5272] DEBUG:PGRset_status_on_cluster_tbl():host:n2.example.com port:5432 max:0 use:0 status2
2006-10-25 12:40:47 [5272] DEBUG:init_pglb():Child_Tbl size is[0]
2006-10-25 12:40:47 [5272] ERROR:init_pglb():ChildShm shmget() failed. (Invalid argument)

The reason of this was Max_Connect directive. In the config sample it was set to 0. And i thought that 0 in this context might mean unlimited number of connections to this node. But now it seems it meant something different. After i changed 0's to some other positive values, it began to work.

Posted by denis on Wednesday, October 25th, 2006 at 1:15 p.m. (1 year, 9 months ago), 3 comments added so far

multiple definition of `__i686.get_pc_thunk.bx'

I needed to compile postgresql from source on debian box. I obtained source/dsc/diff files and tried to build debian package:

$ apt-get source postgresql

$ cd postgresql-7.4.7

$ dpkg-buildpackage -rfakeroot -uc -us

And during compilation i got following error:

/usr/lib/libc_nonshared.a(elf-init.oS)(.gnu.linkonce.t.__i686.get_pc_thunk.bx+0x0): In function `__i686.get_pc_thunk.bx':
: multiple definition of `__i686.get_pc_thunk.bx'
../ecpglib/typename.o(.gnu.linkonce.t.__i686.get_pc_thunk.bx+0x0):
/tmp/postgres/postgresql-7.4.7/build-tree/postgresql-7.4.7/src/interfaces/ecpg/ecpglib/typename.c:18: first defined here
collect2: ld returned 1 exit status

In google i found a few links about such errors. It seemed like it was gcc related issue. From some maillist archive/forum:

« After a little research I tried upgrading from gcc-3.4 to gcc-3.5, which is the latest version currently available in debian stable. The error persisted. After that I tried upgrading to gcc-4.0 out of debian testing, which solved the issue. »

I was running gcc 3.3.5 on that box, so i decided to try with gcc 3.4.4, which is also available in Debian Sarge. The outcome was the same. I changed symlink /usr/bin/gcc from /usr/bin/gcc-3.3 to /usr/bin/gcc-3.4 - stil no luck. And after a dozen of failed compilations with different paramenters set/unset i noticed that it uses /usr/bin/i386-linux-gcc as a compiler. Which was a symlink to /usr/bin/i386-linux-gcc-3.3 (which was a symlink to /usr/bin/gcc-3.3)

I changed it to /usr/bin/i386-linux-gcc-3.4 and successfully compiled postgresql :)

Posted by denis on Wednesday, October 18th, 2006 at 2:14 p.m. (1 year, 9 months ago), 1 comments added so far

PostgreSQL table partitioning and flow-export

PowerDNS, gmysql backend and Chicken and Egg Problem

Setup of libpam-mysql, libnss-mysql-bg with Django admin application

Pgcluster: error on balancer

multiple definition of `__i686.get_pc_thunk.bx'

Tags

Archive

RSS Feeds