This is the fourth part of the tutorial. Other parts are:
Start from the beginning (PostgreSQL HA with pgpool-II - Part 1)!
This part deals with another painful thing: installing pgpool-II. The ultimate resource for this is pgpool-II manual. Here I'll extract the essence, and present it in easier step-by-step way. Let's start.
4.1 Installing pgpool-II
For Ubuntu users (like myself), problems start immediately. The official Ubuntu repository still offers pgpool-II version 3.3, and again we want the latest (3.5 at the moment of this writing). Again we have a good reason to insist on the newest since pgpool-II versions 3.4 and 3.5 have introduced significant improvements in performances (as you can read here). How to obtain the latest version? Well, if you are using CentOS - you're lucky - pgpool maintains yum repository. But if you are Ubuntu guy, you need to compile from source. I'm not sure why pgpool ignores Debian family and apt repository, but they do. Luckily, the installation from source isn't too hard. Pain comes later, with configuration, so CentOS guys - don't celebrate too much. Also, even if you are using CentOS, read through the next section in order to sync with us, and to have understanding about files included in the install.
4.1.1 Installing from Source
Let's start with installing packages that'll be needed for compilation:
$ apt-get update
$ apt-get install libpq-dev make
Besides mentioned packages this will install all dependencies, of course. We'll also need postgresql-9.5-pgpool2
package, but if you've followed previous parts of this tutorial - it is already installed.
The next thing to do is to download and extract source tarball:
$ # Download the tarball:
$ wget http://www.pgpool.net/download.php?f=pgpool-II-3.5.2.tar.gz -O pgpool-II-3.5.2.tar.gz
$ # Extract the tarball:
$ tar -xzf pgpool-II-3.5.2.tar.gz
$ # Delete the tarball once extracted:
$ rm pgpool-II-3.5.2.tar.gz
$ # cd to source directory:
$ cd pgpool-II-3.5.2
After that we'll configure and compile the package. There are more configuration options, but I'll use only one that defines installation directory, and I'll set it to /usr/share/pgpool2/3.5.2
. You can change the installation directory to some other location if you want (i.e. another good pick would be /opt/pgpool2/3.5.2
). Still in source directory, execute the following:
$ ./configure --prefix=/usr/share/pgpool2/3.5.2
$ make
$ make install
This will compile and install binaries (and some other things) in the specified location. Next thing to do is to create and define configuration directory. Do the following:
-
Pick and create configuration directory. To be consistent with PostgreSQL, I pick my configuration directory to be
/etc/pgpool2/3.5.2
, so I'll create it there. -
Move content of
/usr/share/pgpool2/3.5.2/etc
directory to your new configuration directory. (Note that/usr/share/pgpool2/3.5.2
used here is actually installation directory you've selected in compilation process.) Content that will be moved are actually example configuration files (pcp.conf.sample
and few others). Example move command (do the same for all other files from the directory):$ mv /usr/share/pgpool2/3.5.2/etc/pcp.conf.sample /etc/pgpool2/3.5.2/
The files that need to be moved this way are:
-
pcp.conf.sample
-
pgpool.conf.sample
-
pgpool.conf.sample-master-slave
-
pgpool.conf.sample-replication
-
pgpool.conf.sample-stream
-
pool_hba.conf.sample
-
-
Copy, move, or link binary files from your installation directory to
/usr/sbin
. I prefer using symbolic links, but you can also move or copy the files. Here's an example for filepcp_attach_node
(you should do the same for all other files):$ # Create symbolic link: $ ln -s /usr/share/pgpool2/3.5.2/bin/pcp_attach_node /usr/sbin/pcp_attach_node $ # OR move: $ mv /usr/share/pgpool2/3.5.2/bin/pcp_attach_node /usr/sbin/ $ # OR copy: $ cp /usr/share/pgpool2/3.5.2b/bin/pcp_attach_node /usr/sbin/
The files that need to be linked / moved / copied this way are:
-
pcp_attach_node
-
pcp_detach_node
-
pcp_node_count
-
pcp_node_info
-
pcp_pool_status
-
pcp_proc_count
-
pcp_proc_info
-
pcp_promote_node
-
pcp_recovery_node
-
pcp_stop_pgpool
-
pcp_watchdog_info
-
pg_md5
-
pgpool
-
/usr/sbin
- please confirm that. Talking about configuration files - find where they are installed, but I don't recommend moving them (pgpool service installed is probably configured for this existing location).
4.1.1.1 Extensions and SQL Scripts
There's one thing I must admit before starting this section: not all scripts that we'll prepare here are needed in our scenario. You can check original documentation to see when these scripts and extensions are used. Nevertheless, for the sake of completeness of pgpool-II installation, I will cover all the scripts here, and it won't hurt too much if you also do so.
pgpool-II comes with some PostgreSQL extensions and SQL scripts. You can find these in source directory (extracted from tarball), in src/sql
subdirectory. Original pgpool-II documentation says that they should be compiled, but you shouldn't do this - it is already covered by installed package postgresql-9.5-pgpool2
. You only need to confirm that needed binaries (pgpool_adm
, pgpool-recovery
, and pgpool-regclass
) are already available in PostgreSQL library (/usr/lib/postgresql/9.5/lib
). But extensions and SQL scripts should be copied anyway.
Extensions should be copied to extension
subdirectory of PostgreSQL installation directory (/usr/share/postgresql/9.5/extension
). Each extension consists of two files: *.control
and *.sql
, and to copy an extension we need to copy them both. Besides extensions there are some SQL script files we would also want to copy, but we will copy them to sql
subdirectory of PostgreSQL configuration directory (/etc/postgresql/9.5/main/sql
). Again, both extensions and SQL script files are at the moment in src/sql
subdirectory of source directory (extracted from tarball). So let's start copying:
$ # Create SQL scripts directory:
$ mkdir /etc/postgresql/9.5/main/sql
$ # Navigate to source directory:
$ cd ~/pgpool-II-3.5.2
$ # Navigate to src/sql subdirectory:
$ cd src/sql
$ # While there let's copy the first script file:
$ cp insert_lock.sql /etc/postgresql/9.5/main/sql/
$ # Navigate to pgpool_adm (the first extension) subdirectory:
$ cd pgpool_adm
$ # Let's copy pgpool_adm extension:
$ cp pgpool_adm.control /usr/share/postgresql/9.5/extension/
$ cp pgpool_adm--1.0.sql /usr/share/postgresql/9.5/extension/
$ # While there let's copy SQL script file also (note that I'm changing extension of the file also):
$ cp pgpool_adm.sql.in /etc/postgresql/9.5/main/sql/pgpool_adm.sql
$ # Navigate up in order to select another extension:
$ cd ..
$ # Navigate to pgpool-recovery (the next extension) subdirectory:
$ cd pgpool-recovery
$ # Let's copy pgpool-recovery extension:
$ cp pgpool_recovery.control /usr/share/postgresql/9.5/extension/
$ cp pgpool_recovery--1.1.sql /usr/share/postgresql/9.5/extension/
$ # While there let's copy SQL script files also (note that I'm changing extension of the file also):
$ cp pgpool-recovery.sql.in /etc/postgresql/9.5/main/sql/pgpool-recovery.sql
$ cp uninstall_pgpool-recovery.sql /etc/postgresql/9.5/main/sql/
$ # Navigate up in order to select another extension:
$ cd ..
$ # Navigate to pgpool-regclass (the next extension) subdirectory:
$ cd pgpool-regclass
$ # Let's copy pgpool-regclass extension:
$ cp pgpool_regclass.control /usr/share/postgresql/9.5/extension/
$ cp pgpool_regclass--1.0.sql /usr/share/postgresql/9.5/extension/
$ # While there let's copy SQL script files also (note that I'm changing extension of the file also):
$ cp pgpool-regclass.sql.in /etc/postgresql/9.5/main/sql/pgpool-regclass.sql
$ cp uninstall_pgpool-regclass.sql /etc/postgresql/9.5/main/sql/
$ # Navigate to your home directory and you can delete pgpool source directory since we've copied everything we need:
$ cd ~
$ rm -r pgpool-II-3.5.2
Now we need to change copied SQL script files. Basically, we need to change MODULE_PATHNAME
to the appropriate module path. Also we need to change $libdir
with actual PostgreSQL library directory (/usr/lib/postgresql/9.5/lib
). MODULE_PATHNAME
is actually mentioned library path plus actual module name. As an example here I'll provide pgpool-recovery.sql
before and after the change:
CREATE OR REPLACE FUNCTION pgpool_recovery(text, text, text, text)
RETURNS bool
AS 'MODULE_PATHNAME', 'pgpool_recovery'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION pgpool_remote_start(text, text)
RETURNS bool
AS 'MODULE_PATHNAME', 'pgpool_remote_start'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION pgpool_pgctl(text, text)
RETURNS bool
AS '$libdir/pgpool-recovery', 'pgpool_pgctl'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION pgpool_switch_xlog(text)
RETURNS text
AS 'MODULE_PATHNAME', 'pgpool_switch_xlog'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION pgpool_recovery(text, text, text, text)
RETURNS bool
AS '/usr/lib/postgresql/9.5/lib/pgpool-recovery', 'pgpool_recovery'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION pgpool_remote_start(text, text)
RETURNS bool
AS '/usr/lib/postgresql/9.5/lib/pgpool-recovery', 'pgpool_remote_start'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION pgpool_pgctl(text, text)
RETURNS bool
AS '/usr/lib/postgresql/9.5/lib/pgpool-recovery', 'pgpool_pgctl'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION pgpool_switch_xlog(text)
RETURNS text
AS '/usr/lib/postgresql/9.5/lib/pgpool-recovery', 'pgpool_switch_xlog'
LANGUAGE C STRICT;
You need to do the same for all other SQL script files from /etc/postgresql/9.5/main/sql
. As a hint I'll tell you that module name that is used as a replacement always corresponds to name of the file you are changing (i.e. while changing file pgpool-recovery.sql
you'll use module name pgpool-recovery
). Another important thing to check is if actual module exists in PostgreSQL library (/usr/lib/postgresql/9.5/lib
).
Here I need to admit something: in some circumstances PostgreSQL is smart enough to automatically change MODULE_PATHNAME
before executing the script. For example, it happens while we are installing extensions, and thanks to that we don't need to change extension files also, although they also contain MODULE_PATHNAME
. For example, pgpool_recovery--1.1.sql
file we've previously copied to extension directory also contains MODULE_PATHNAME
, but we don't need to change it because PostgreSQL will do this automatically on execution. But with regular SQL script files I don't know how to accomplish this, and for this reason I've changed everything manually. Except for a bit more work, there's no harm in doing that.
It's probably not necessary, but I like to have all directories and files in /etc/postgresql
owned by postgres
user, so I'll execute:
$ chown postgres:postgres -R /etc/postgresql/9.5/main/sql
4.1.1.2 Service Script
We would like pgpool-II to run as service, of course, so we need to create and register service script. I've created one by modifying one that gets installed when you install pgpool-II v3.3
package from the official Ubuntu repository. Here I'll provide this script, but you can change it if you want. Actually there are two parts of this script: defaults file which contains default configuration options, and the script itself. I'll provide both files here. Note that if you've used different directories, you need to adjust the files appropriately.
Defaults file:
# Defaults for pgpool initscript
# sourced by /etc/init.d/pgpool2
# set to "yes" if you want to enable debugging messages to the log
PGPOOL_LOG_DEBUG=no
# config file
PGPOOL_CONFIG_FILE=/etc/pgpool2/3.5.2/pgpool.conf
# hba file
PGPOOL_HBA_CONFIG_FILE=/etc/pgpool2/3.5.2/pool_hba.conf
# pcp config file
PGPOOL_PCP_CONFIG_FILE=/etc/pgpool2/3.5.2/pcp.conf
# PID file. Must be the same as defined in pgpool.conf (pid_file_name)
PGPOOL_PID_FILE=/var/run/postgresql/pgpool.pid
Service script:
#! /bin/sh
### BEGIN INIT INFO
# Provides: pgpool2
# Required-Start: $remote_fs $syslog
# Required-Stop: $remote_fs $syslog
# Should-Start: postgresql
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: start pgpool-II
# Description: pgpool-II is a connection pool server and replication
# proxy for PostgreSQL.
### END INIT INFO
PATH=/sbin:/bin:/usr/sbin:/usr/bin
DAEMON=/usr/sbin/pgpool
test -x $DAEMON || exit 5
# Include pgpool defaults if available
if [ -f /etc/default/pgpool2 ] ; then
. /etc/default/pgpool2
fi
PIDFILE=${PGPOOL_PID_FILE:-/var/run/postgresql/pgpool.pid}
PGPOOL_CONFIG_FILE=${PGPOOL_CONFIG_FILE:-/etc/pgpool2/3.5.2/pgpool.conf}
if [ ! -f $PGPOOL_CONFIG_FILE ]; then
echo "Config file not found."
log_failure_msg "Config file not found."
exit 1
fi
if [ x"$PGPOOL_LOG_DEBUG" = x"yes" ]; then
OPTS="-d -f $PGPOOL_CONFIG_FILE"
else
OPTS="-f $PGPOOL_CONFIG_FILE"
fi
PGPOOL_PCP_CONFIG_FILE=${PGPOOL_PCP_CONFIG_FILE:-/etc/pgpool2/3.5.2/pcp.conf}
if [ -f $PGPOOL_PCP_CONFIG_FILE ]; then
OPTS="$OPTS -F $PGPOOL_PCP_CONFIG_FILE"
fi
STOPOPTS=$OPTS
PGPOOL_HBA_CONFIG_FILE=${PGPOOL_HBA_CONFIG_FILE:-/etc/pgpool2/3.5.2/pool_hba.conf}
if [ -f $PGPOOL_HBA_CONFIG_FILE ]; then
OPTS="$OPTS -a $PGPOOL_HBA_CONFIG_FILE"
fi
. /lib/lsb/init-functions
is_running() {
pidofproc -p $PIDFILE $DAEMON >/dev/null
}
d_start() {
if ! test -d /var/run/postgresql; then
install -d -m 2775 -o postgres -g postgres /var/run/postgresql
fi
if ! test -d /var/log/pgpool; then
install -d -m 2775 -o postgres -g postgres /var/log/pgpool
fi
if is_running; then
:
else
echo "FD - Starting pgpool-II by executing:"
echo "$DAEMON -n $OPTS >> /var/log/pgpool/pgpool.log 2>&1 &"
su -c "$DAEMON -n $OPTS >> /var/log/pgpool/pgpool.log 2>&1 &" - postgres
fi
}
d_stop() {
echo "FD - Starting pgpool-II by executing:"
echo "$DAEMON $STOPOPTS -m fast stop"
su -c "$DAEMON $STOPOPTS -m fast stop" - postgres
}
d_reload() {
echo "FD - Reloading pgpool-II by executing:"
echo "$DAEMON $OPTS reload"
su -c "$DAEMON $OPTS reload" - postgres
}
case "$1" in
start)
log_daemon_msg "Starting pgpool-II" pgpool
d_start
log_end_msg $?
;;
stop)
log_daemon_msg "Stopping pgpool-II" pgpool
d_stop
log_end_msg $?
;;
status)
is_running
status=$?
if [ $status -eq 0 ]; then
log_success_msg "pgpool-II is running."
else
log_failure_msg "pgpool-II is not running."
fi
exit $status
;;
restart|force-reload)
log_daemon_msg "Restarting pgpool-II" pgpool
d_stop && sleep 1 && d_start
log_end_msg $?
;;
try-restart)
if $0 status >/dev/null; then
$0 restart
else
exit 0
fi
;;
reload)
log_daemon_msg "Reloading pgpool-II" pgpool
d_reload
log_end_msg $?
;;
*)
log_failure_msg "Usage: $0 {start|stop|status|restart|try-restart|reload|force-reload}"
exit 2
;;
esac
Now we can register the service by executing:
$ update-rc.d pgpool2 defaults
But since we haven't configured pgpool-II yet, let's the service for now:
$ update-rc.d pgpool2 disable
postgres
user. I've done so because I believe that it'll make my life easier later since a lot of scripts need to be run as postgres
user. Nevertheless, if you install pgpool-II from package, you'll see that the original script also runs the service this way.
4.2 Where to Go Next?
I'm getting tired of this. Hopefully we'll get HA in the next part - PostgreSQL HA with pgpool-II - Part 5. (I've already mentioned that I'm going with this tutorial and my implementation in parallel, so I still don't have HA up and running.)
Comments
When I try to add the pgpool_adm extension I get the following errors:
```
psql:pgpool_adm.sql:45: ERROR: function result type must be integer because of OUT parameters
psql:pgpool_adm.sql:53: ERROR: function result type must be integer because of OUT parameters
```
the function itself looks good:
```
39 /**
40 * input parameters: host, port, username, password
41 */
42 CREATE FUNCTION pcp_node_count(text, integer, text, text, OUT node_count integer)
43 RETURNS record
44 AS '/usr/lib/postgresql/9.4/lib/pgpool_adm', '_pcp_node_count'
45 LANGUAGE C VOLATILE STRICT;
```
the function ```_pcp_node_count``` from the pgpool source seems to return an integer.
```
351 _pcp_node_count(PG_FUNCTION_ARGS)
352 {
353 »···char * host_or_srv = text_to_cstring(PG_GETARG_TEXT_PP(0));
354 »···int16 node_count = 0;
355
356 »···PCPConnInfo* pcpConnInfo;
357 »···PCPResultInfo* pcpResInfo;
358
359 »···if (PG_NARGS() == 4)
360 »···{
361 »···»···char *user, *pass;
362 »···»···int port;
363 »···»···port = PG_GETARG_INT16(1);
364 »···»···user = text_to_cstring(PG_GETARG_TEXT_PP(2));
365 »···»···pass = text_to_cstring(PG_GETARG_TEXT_PP(3));
366 »···»···pcpConnInfo = connect_to_server(host_or_srv,port,user,pass);
367 »···}
368 »···else if (PG_NARGS() == 1)
369 »···{
370 »···»···pcpConnInfo = connect_to_server_from_foreign_server(host_or_srv);
371 »···}
372 »···else
373 »···{
374 »···»···ereport(ERROR, (0, errmsg("Wrong number of argument.")));
375 »···}
376
377 »···pcpResInfo = pcp_node_count(pcpConnInfo);
378
379 »···if (pcpResInfo == NULL || PCPResultStatus(pcpResInfo) != PCP_RES_COMMAND_OK)
380 »···{
381 »···»···char *error = pcp_get_last_error(pcpConnInfo)? pstrdup(pcp_get_last_error(pcpConnInfo)):NULL;
382 »···»···pcp_disconnect(pcpConnInfo);
383 »···»···pcp_free_connection(pcpConnInfo);
384 »···»···ereport(ERROR,(0,
385 »···»···»···»···»··· errmsg("failed to get node count"),
386 »···»···»···»···»··· errdetail("%s\n",error?error:"unknown reason")));
387 »···}
388
389 »···node_count = pcp_get_int_data(pcpResInfo, 0);
390
391 »···pcp_disconnect(pcpConnInfo);
392 »···pcp_free_connection(pcpConnInfo);
393
394 »···PG_RETURN_INT16(node_count);
395 }
```
Any ideas?
`CREATE EXTENSION` worked! \o/
Add new comment
Anonymous comments require solving captcha, and waiting for administrator's approval.