[pgpool-general: 2110] Re: pgpool-regclass No such file or directory

Mistina Michal Michal.Mistina at virte.sk
Fri Sep 6 21:19:12 JST 2013


Hi Nozomi.
Thank you for retesting.

> It seems that your postgres is not /usr/pgsql-9.2 but another one. How did 
> you start postgres?
I have cluster management software called pacemaker for managing services 
start/stop. While I was testing I tried to avoid to use the pacemaker. I 
started postgres with command "service postgresql-9.2 start".

If I understood correctly you are telling me that the postgres could be 
compiled with wrong /lib location. I cannot remember where I downloaded 
postgres RPM packages from. Therefore I re-downloaded them and installed 
everything on the clean system. It seems it works. I will test it further on 
the faulty system if it helps.

Thank you very much!!

I'm attaching whole installation steps what I did. Maybe it will help 
somebody.
Please see the results below:

[root at node02 ~]# wget 
http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm
[root at node02 ~]# rpm -ivH pgdg-redhat92-9.2-7.noarch.rpm
[root at node02 ~]# yum install yum-downloadonly
[root at node02 ~]# yum install 
postgresql92 -y --downloadonly --downloaddir=/root/postgres
[root at node02 ~]# yum install 
postgresql92-server -y --downloadonly --downloaddir=/root/postgres
[root at node02 postgres]# rpm -ivH 
postgresql92-libs-9.2.4-1PGDG.rhel6.x86_64.rpm
[root at node02 postgres]# rpm -ivH postgresql92-9.2.4-1PGDG.rhel6.x86_64.rpm
[root at node02 postgres]# rpm -ivH 
postgresql92-server-9.2.4-1PGDG.rhel6.x86_64.rpm
[root at node02 postgres]# updatedb
[root at node02 postgres]# locate bin/pg_ctl
/usr/pgsql-9.2/bin/pg_ctl
[root at node02 ~]# ll /usr/pgsql-9.2/share/extension/pgpool-regclass.sql
ls: cannot access /usr/pgsql-9.2/share/extension/pgpool-regclass.sql: No such 
file or directory
[root at node02 ~]# rpm -ivH pgpool-II-3.3.0-1.pgdg.x86_64.rpm
[root at node02 ~]# ll /usr/pgsql-9.2/share/extension/pgpool-regclass.sql
-rw-r--r-- 1 root root 142 Aug  5 04:17 
/usr/pgsql-9.2/share/extension/pgpool-regclass.sql
[root at node02 ~]# mount -t xfs -o noatime,nodiratime,attr2 
/dev/vg_drbd/lv_pgsql /var/lib/pgsql/9.2/data
[root at node02 ~]# ll /var/lib/pgsql/9.2/
total 4
drwx------ 2 postgres postgres 4096 Apr  2 01:41 backups
drwxr-xr-x 2 root     root        6 Sep  5 16:34 data
[root at node02 ~]# chown postgres:postgres /var/lib/pgsql/9.2/data
[root at node02 ~]# chmod 0700 /var/lib/pgsql/9.2/data
[root at node02 ~]# service postgresql-9.2 initdb
[root at node02 ~]# echo "host  all   all   0.0.0.0/0   trust" >> 
/var/lib/pgsql/9.2/data/pg_hba.conf
[root at node02 ~]# vim /var/lib/pgsql/9.2/data/postgresql.conf
<edited postgresql.conf >
[root at node02 ~]# service postgresql-9.2 start
Starting postgresql-9.2 service:                           [  OK  ]
[root at node02 ~]# pg_md5 --md5auth -p -u postgres
Password:
[root at node02 ~]# chown postgres:postgres /etc/pgpool-II/pool_passwd
[root at node02 ~]# vim /etc/pgpool-II/pgpool.conf
<edited pgpool.conf>
[root at node02 ~]# echo "host  all   all   0.0.0.0/0   trust" >> 
/etc/pgpool-II/pool_hba.conf
[root at node02 ~]# mkdir -p /var/log/pgpool
[root at node02 ~]# chown postgres:postgres /var/log/pgpool
[root at node02 ~]# service pgpool start
Starting pgpool service:                                   [  OK  ]
[root at node02 ~]# su - postgres
-bash-4.1$ /usr/pgsql-9.2/bin/createdb test
-bash-4.1$ /usr/pgsql-9.2/bin/psql test < 
/usr/pgsql-9.2/share/extension/pgpool-regclass.sql
CREATE FUNCTION
-bash-4.1$ psql -p 9999 -d test
test=# CREATE TABLE testtable(col1 varchar(40) );
CREATE TABLE
test=# INSERT INTO testtable VALUES ('test1');
INSERT 0 1
test=# SELECT * FROM testtable;
 col1
-------
 test1
(1 row)


Best regards,
Michal Mistina
-----Original Message-----
From: pgpool-general-bounces at pgpool.net 
[mailto:pgpool-general-bounces at pgpool.net] On Behalf Of Nozomi Anzai
Sent: Thursday, September 5, 2013 3:35 AM
To: pgpool-general at pgpool.net
Subject: [pgpool-general: 2108] Re: pgpool-regclass No such file or directory

Hi,

> I tried this:
> [root at node01 ~]# su - postgres
> -bash-4.1$ pg_config --libdir
> /usr/pgsql-9.2/lib
> -bash-4.1$ ll /usr/pgsql-9.2/lib | grep pgpool-
> -rwxr-xr-x 1 root root   36686 Aug  5 04:17 pgpool-recovery.so
> -rwxr-xr-x 1 root root   23214 Aug  5 04:17 pgpool-regclass.so
>
> I also manually applied pgpool-recovery.sql and pgpool-regclass.sql as
> it is required step in the installation guide:
> su - postgres
> cp /usr/pgsql-9.2/share/extension/pgpool-regclass.sql .
> psql -f pgpool-regclass.sql template1
> psql -f pgpool-regclass.sql <database>
>
> If I try to access the <database> I applied sql script with pgAdmin I
> got aforementioned error. I also got this error if I try to access it
> from PostgreSQL shell.
>
> Doesn't pgpool-regclass.sql create that user function which pgpool is
> looking for? Or is it a file  /usr/pgsql-9.2/lib/pgpool-regclass? How
> can I create the file and why it is not created by Pgpool-II rpm 
> installation?

It seems that your postgres is not /usr/pgsql-9.2 but another one. How did you 
start postgres?


I tested as follows:

---------------------------------------------------------------------------

1. I have 2 of postgres: /usr/local/pgsql (which is V.9.2 installed by
   source codes) and /usr/pgsql-9.2/lib/ (which is V.9.2 installed by RPM).
   The former doen't have pgpool libraries, the latter haves them.

[root at osspc16 ~]# updatedb
[root at osspc16 ~]# locate bin/pg_ctl
/usr/local/pgsql/bin/pg_ctl
/usr/pgsql-9.2/bin/pg_ctl

[postgres at osspc16 ~]$ /usr/local/pgsql/bin/pg_config --libdir 
/usr/local/pgsql/lib
[postgres at osspc16 ~]$ ls /usr/local/pgsql/lib/ | grep pgpool

[postgres at osspc16 ~]$ /usr/pgsql-9.2/bin/pg_config --libdir /usr/pgsql-9.2/lib
[postgres at osspc16 ~]$ ls /usr/pgsql-9.2/lib/ | grep pgpool pgpool-recovery.so 
pgpool-regclass.so

--------------------------------------------------------------------------

2. Postgres without pgpool libraries cannot create pgpool_regclass().
   (I think this is the same situation as your problem.)

[postgres at osspc16 ~]$ /usr/local/pgsql/bin/pg_ctl start -D data3/
[postgres at osspc16 ~]$ /usr/local/pgsql/bin/createdb test
[postgres at osspc16 ~]$ /usr/local/pgsql/bin/psql test < 
/usr/pgsql-9.2/share/extension/pgpool-regclass.sql ERROR:  could not access 
file "$libdir/pgpool-regclass": No such file or directory
STATEMENT:  CREATE OR REPLACE FUNCTION pgpool_regclass(cstring) RETURNS oid
        AS '$libdir/pgpool-regclass', 'pgpool_regclass'
        LANGUAGE C STRICT;
ERROR:  could not access file "$libdir/pgpool-regclass": No such file or 
directory

---------------------------------------------------------------------------

3. Postgres with pgpool libraries can create functions.

[postgres at osspc16 ~]$ /usr/pgsql-9.2/bin/pg_ctl stop -D data3/
[postgres at osspc16 ~]$ /usr/pgsql-9.2/bin/pg_ctl start -D data3/

[postgres at osspc16 ~]$ /usr/local/pgsql/bin/psql test < 
/usr/pgsql-9.2/share/extension/pgpool-regclass.sql CREATE FUNCTION

---------------------------------------------------------------------------

4. Copying libralies is also fine.

[root at osspc16 ~]# cd /usr/local/pgsql/lib
[root at osspc16 lib]# ln -s /usr/pgsql-9.2/lib/pgpool-* .
[root at osspc16 lib]# su - postgres

[postgres at osspc16 ~]$ /usr/pgsql-9.2/bin/pg_ctl stop -D data3/
[postgres at osspc16 ~]$ /usr/local/pgsql/bin/pg_ctl start -D data3/

[postgres at osspc16 ~]$ /usr/local/pgsql/bin/psql test psql (9.2.3) Type "help" 
for help.

test=# select pgpool_regclass('aaa');
 pgpool_regclass
-----------------
               0
(1 row)

---------------------------------------------------------------------------

> >> Hi Tatsuo.
> >>
> >> I am experiencing following message in the pgpool.log file:
> >>
> >> do_query: error message from backend: could not access file
> >> "$libdir/pgpool-regclass": No such file or directory. Exit this session.
> >>
> >>
> >>
> >> I know the Pgool cannot find regclass libraries or cannot access it.
> >> How can I determine where does Pgpool-II expect to have these libraries?
>
> >pgpool-regclass is a user defined PostgreSQL function and "$libdir"
> >is
> controled by your PostgreSQL installtion, not by pgpool-II. To find
> out where $libdir actually points to, you can use:
>
> >pg_config --libdir
>
> Thank you for reply Tatsuo.
>
> I installed Pgpool-II from the package
> pgpool-II-3.3.0-1.pgdg.x86_64.rpm downloaded from Pgpool download page.
>
> I tried this:
> [root at node01 ~]# su - postgres
> -bash-4.1$ pg_config --libdir
> /usr/pgsql-9.2/lib
> -bash-4.1$ ll /usr/pgsql-9.2/lib | grep pgpool-
> -rwxr-xr-x 1 root root   36686 Aug  5 04:17 pgpool-recovery.so
> -rwxr-xr-x 1 root root   23214 Aug  5 04:17 pgpool-regclass.so
>
> I also manually applied pgpool-recovery.sql and pgpool-regclass.sql as
> it is required step in the installation guide:
> su - postgres
> cp /usr/pgsql-9.2/share/extension/pgpool-regclass.sql .
> psql -f pgpool-regclass.sql template1
> psql -f pgpool-regclass.sql <database>
>
> If I try to access the <database> I applied sql script with pgAdmin I
> got aforementioned error. I also got this error if I try to access it
> from PostgreSQL shell.
>
> Doesn't pgpool-regclass.sql create that user function which pgpool is
> looking for? Or is it a file  /usr/pgsql-9.2/lib/pgpool-regclass? How
> can I create the file and why it is not created by Pgpool-II rpm 
> installation?
>
> Best regards,
> Michal Mistina
>


--
Nozomi Anzai
SRA OSS, Inc. Japan
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 3076 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130906/426deff7/attachment.p7s>


More information about the pgpool-general mailing list