[pgpool-general: 2111] Re: pgpool-regclass No such file or directory
Mistina Michal
Michal.Mistina at virte.sk
Mon Sep 9 16:19:47 JST 2013
Hi.
Regarding my issue, I've found that the new PostgreSQL RPMs didn't help to
solve it. Actually booting with sample "clean" pgpool config helped after I
installed everything on the clean system.
I've been using adjusted pgpool.conf and if I tried to start with this
config the issue with the pgpool-regclass occurred.
1. I replaced pgpool.conf with the sample delivered with the Pgpool release.
2. I started Pgpool service.
3. I replaced pgpool.conf with adjusted pgpool.conf.
4. Restarted service. -> everything is OK.
I don't know what could cause this weird behaviour, but something in the
sample pgpool.conf corrected it. Maybe wrong permissions. I am not able to
reproduce the issue any more, so I cannot test the cause of issue.
Hopefully it will help somebody.
Regards,
Michal Mistina
-----Original Message-----
From: pgpool-general-bounces at pgpool.net
[mailto:pgpool-general-bounces at pgpool.net] On Behalf Of Mistina Michal
Sent: Friday, September 6, 2013 2:19 PM
To: Nozomi Anzai; pgpool-general at pgpool.net
Subject: [pgpool-general: 2110] Re: pgpool-regclass No such file or
directory
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.noarc
h.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/20130909/b348fd54/attachment.p7s>
More information about the pgpool-general
mailing list