How to install PGPool II on PostgreSQL Servers in master-slave architecture + PGPoolAdmin web managment

General Information

PGPool can run on same server along with PostgreSQL DB or on stand alone server(recommended). In this article we will install PGPool on stand alone server, but the only difference is connection ports on PGPool and PostgreSQL.

We will install PGPool II 3.1 on PostgreSQL 9.1.

Basic architecture:

┏───────────────┓

│                │

│    pgpool-1    │

│  pgpool server │

│                │

┗───────────────┛

//             \\

//               \\

//                 \\

\\//                \\//

\/                  \/

┏───────────────┓           ┏───────────────┓

│                │           │                │

│    pgsql-1     │ streaming │     pgsql-2    │

│  pgsql master  │══════════>│  pgsql slave   │

│     server     │replication│     server     │

│                │           │                │

┗───────────────┛           ┗───────────────┛

Fail cases:

Slave fails

In case slave server will fail PGPool will start failover.sh script and will mark server as Down (state 3). It'll reconnect all open connections to this server to the master server, at this case users that have been on this connection will be disconnection.

When the server will be fixed and started up you will need manually start recovery process. To do this the PostgreSQL need to be turned off on the server. Recovery process will take backup from the master and restore it on slave. When it will finish restore process PGPool will connect it back. Streaming replication process will restore all the data inserted since the backup. No data lost.

Master fails

In case master server will fail PGPool will start failover.sh script, that will notice that the failed server is master and set trigger to slave server. When slave server notice the trigger it will promote itself to master. All the connections to master will be reconnected to slave and user will be disconnected. All the data inserted to master and not yet replicated to slave will be lost. When the server will be fixed and started you will need manually start recovery process. To do this PostgreSQL need to be turned off on the server. Recovery process will take backup from the master and restore it on slave. When it will finish restore process PGPool will connect it back. Streaming replication process will restore all the data inserted since the backup.

PGPool fails

PGPool is SPOF and if it fails there will be no connection to client. To fix this problem you need to create PGPool HA cluster. This can be done with cluster software like Pacemaker or Linux Heartbeat.

PGPool II setup

PGPool can be installed from source of from OS packages, I recommended installing from source, as we will need some files from the tar package. Source files can be downloaded from PGPool official site: http://pgpool.net/mediawiki/index.php/Downloads#pgpool-II

Before compiling PGPool you might need to install missing packages:

• For Debian/Ubuntu: libpq-dev
• For RH/CentOS: postgresql-libs

Download needed version of PGPool, open and install:

$ tar xfz /some/where/pgpool-II-3.1.1.tar.gz

$ cd pgpool-II-3.1.1

$ ./configure

$ make

$ make install

You can configure PGPool install path using –prefix flag, in this article we will use default configurations, which will install PGPool configurations to /usr/local/share/etc

If you installing from OS software manager you can't configure the path, but you won't need installing missing packages. Default path will for configuration will be /etc/pgpool2/
After installing PGPool we need to do some basic configuration, here's the sample pgpool.conf file with basic configuration needed to run PGPool in master-slave with streaming replication:
link for configuration

Make sure you changing this configuration to ones you need:

• PGPool client connections port

port = 5432

• PostgreSQL Master server

backend_hostname0 = 'pgpool-1'

• PostgreSQL connection port

backend_port0 = 5432

• Load balancing weight

backend_weight0 = 0

• PostgreSQL data path(on PostgreSQL server)

backend_data_directory0 = '/var/lib/postgresql/9.1/main'

• PostgreSQL username and password (super user)

sr_check_user = 'postgres'

sr_check_password = ''

• Health check for PostgreSQL nodes period in seconds

health_check_period = 10

• PostgreSQL username and password (super user)

health_check_user = 'postgres'

health_check_password = ''

• User to run recovery script on PostgreSQL server

recovery_user = 'postgres'

recovery_password = ''

Next we will setup user for PGPool Administration tool called PCP(later can be used in PGPoolAdmin). We need to encrypt user password to md5:

$ pg_md5 password

Edit pcp.conf and insert the new password along with username to the end of the file:

username:5f4dcc3b5aa765d61d8327deb882cf99

Next we need to add script for failover, that already configured in pgpool.conf. This script will check if the PostgreSQL server that failed is master or slave. In case of master fail it will put trigger on the slave and it will promote itself to master. Put this script at same location as all configuration files /usr/local/share/etc and give it execute permissions:

link to failover.sh

Create necessary directories

$ mkdir /var/run/pgpool

$ mkdir /var/log/pgpool

Set permissions to this directories for user that will run PGPool.

Now PGPool is fully configured and can be started. To start it just run PGPool command. By default it will run at same shell as your user and will print the log to STDOUT. Here is sample of command how to start PGPool as deamon and save the log to file:

$ pgpool -n -d > /tmp/pgpool.log 2>&1 &

You may not start the PGPool now if you want to install PGPoolAdmin.

PGPoolAdmin setup(optional)

PGPoolAdmin allows to manage PGPool: start/stop PGPool, edit configurations, add/remove/ recovery/promote PostgreSQL nodes.

To install PGPoolAdmin you will need to install first Apache service to the server(I hope you know how to do it).

Download PGPoolAdmin from http://pgpool.net/mediawiki/index.php/Downloads#pgpoolAdmin.

Open the tar file and put it content into Apache data directory:

$ tar xfz /some/where/pgpoolAdmin-3.1.1.tar.gz

$ mv /some/where/pgpoolAdmin-3.1.1/pgpooladmin /var/www/html/

Now we need to set permissions to apache user to edit configuration files

$ chown apache /var/run/pgpool

$ chown apache /var/log/pgpool

$ chown apache /share/local/etc/pgpool.conf

$ chown apache /share/local/etc/pool_hba.conf

$ chown -R apache /var/www/html/pgpooladmin

PGPool recovery script must login to PostgreSQL server and create trigger file, to do it must login to server via ssh from user apache. To increase security you might want to set firewall to allow to connection only from PGPool server.

PGPoolAdmin is ready to installed and used. Open in you browser http://serverIP/pgpooladmin/install/index.php follow the steps. At the end PGPoolAdmin will start PGPool on the server. To login use PCP user and password configured before.

PostgreSQL setup

At this point we have PGPool running and accepting connections, but it might not work properly as PostgreSQL might not accept those connections. At this step we will re-configure your PostgreSQL server. This step must be done to all new node you want to add to PGPool, before running recovery process.

Edit postgresql.conf configuration file with following variables:

listen_address = '*'

hot_standby = on

wal_level = hot_standby

max_wal_senders = 1

Edit pg_hba.conf configuration file:

host all all 0.0.0.0/0 trust

host replication postgres 0.0.0.0/0 trust

This mean that the server will accept connections from all IP's without authentication for any user. It's possible to put your subnet mask or IP of PGPool server. Note, md5 authentication is not working with PGPool master-slave configuration.

Second allows to do streaming replication between any server without authentication for user postgres. This can be changed for needed subnet or IP of second server and if you want to use other user for replication(User must have REPLICATION or SUPERUSER permission on the second server).

Now we need to setup some PGPool recovery functions. If you have installed PGPool from source you have them inside tar file. If you installed it from software manager download the source file and follow this step.

Inside the source file you have sql directory. You need to compile it each one of them and run the compiled SQL file on postgres DB and template1. If you installed PostgreSQL for software manager you will need to install additional package:

• For Debian/Ubuntu: postgresql-server-dev
• For RH/CentOS: postgresql-devel

Run in each directory:

$ make install

$ psql -f pgpool-*****.sql postgres

$ pgsql -f pgpool-*****.sql template1

PGPool recovery process is using 2 scripts, one on master and second on slave. First script called basebackup.sh, it's executed from the master and creates backup, then it sends it to the slave. This script need to be in PostgreSQL data directory(default /var/lib/postgresql/9.1/main) with execute permissions Next user that will run recovery process on the nodes need to be able to the login without password to the each other(again, I hope you can do it by yourself).

Second script allows PGPool to start PostgreSQL service. This script uses pg_ctl command, check that the path is correct. If you don't have pg_ctl change the path to /etc/init.d/postgresql and remove from the command “-w -D $DESTDIR”.

Create trigger directory and give postgres permissions on it

$ mkdir -p /var/log/pgpool/trigger

$ chown postgres /var/log/pgpool/trigger

Finishing the setup

Now you should have running PGPool and configured PostgreSQL master server. Last step is to configure the slave and test everything. Do all the configuration on the slave same as on the master and at the end stop the PostgreSQL service. If you installed PGPoolAdmin login into the interface, go to “Edit pgpool.conf” and then to “Backends” and press “Add”. Fill all the info for new backend and change “backend_weight” on both backends to 1. Save the configuration and reload PGPool. When PGPool will discover new node press “Recovery” near it. After recovery

Have questions? Just contact us right away and we will be happy to assist

Powered by 123ContactForm | Report abuse

Making a service autostart on Ubuntu, Debian, Oralce Linux, CentOs and Red Hat (Fedora and RHEL)

For RedHat based distributions (CentOS, Oracle Linux, Fedora) execute (as root):

chkconfig servicename on


For Debian based distributions (Ubuntu) execute (as root):

update-rc.d servicename defaults

Provided by: Forthscale systems, Linux support team

Log aggregation with Logstash, Elasticsearch, Graylog 2 and more Part One

Setup, problem and solution design.
Purpose of log aggregation is to develop single point of access for servers data (in our case nginx web servers).
We have a lot of web servers writing off huge amount of log and no real way to understand what is going on there. Initial solution was to have each systems write a local log file with a Munin agent with custom Perl parser transferring data to Munin server there it was displayed as an RRDtool graph. It worked, however servers themselves generated a lot of logged data making it impossible to parse close to real time forcing us to drop out significant amount of data.

After making a small internet research and due to budget constrains we decided to go with open source tools only. Those applications however still had to be high volume, high load, scalable and big data supporting.
We have decided to setup a dedicated loghost and ship all the data to it parsing it on spot to a needed results. Another thing our proposed solution took into consideration was future log indexing for both technical and BI search ability /readability.


Proposed solution consisted of:

Logstash - Tool for managing events and logs. You can use it to collect logs, parse them, and store them for later use (like, for searching) .
Elasticsearch -  Distributed, RESTful, Search Engine
Graylog 2 - Software to run analytics, alerting, monitoring and powerful searches over your whole log.
As a bonus, Logstash gave us possibility to export events to a monitoring system or support shift management.

next: implementation of log aggregation


Munin monitoring tool
Logstash
Elasticsearch
Graylog 2

Provided by: ForthScale systems, scalable infrastructure experts

list processes running in MySQL

login to mysql as a root:
mysql -uroot -p
end execute:
mysql> show processlist;
will show you list of processes running in MySQL
and using  \G delimiter will present processes in more readable format.
mysql> show processlist \G

Provided by: ForthScale systems, scalable infrastructure experts

Setting up Amazon AWS EC2 ftp server with Linux and VSFTP:

Install vsftp (example for Ubuntu / Debian)

apt-get -y install vsftpd

Edit configuration file (in our example with local authentication and no guest user)

vi /etc/vsftpd.conf

write_enable=YES
anonymous_enable=NO
local_umask=022
local_enable=YES

#to add passive ftp:
pasv_enable=YES
pasv_max_port=12100
pasv_min_port=12000
port_enable=YES
pasv_address="your external instance ip or address"

and open inbound port range 20-21 and 12000-12100 in your security groups

Provided by: ForthScale systems, scalable infrastructure experts

Debian / Ubuntu: purge removed packages with apt

Removing packages with aptitude or apt-get keeps some configuration and temp files on disk.
to purge them (to get rid of these configuration files) execute:

dpkg -l |awk ‘/^rc/ {print $2}’ |xargs sudo dpkg --purge

Powered by 123ContactForm | Report abuse

Provided by: ForthScale systems, scalable infrastructure experts

fixing E: Archive directory /var/cache/apt/archives/

During apt-get install of a package you might receive message :
E: Archive directory /var/cache/apt/archives/

to fix it you need to create a directory and repair deb packages database:

mkdir -p /var/cache/apt/archives/partial
apt-get autoclean

you should get response output similar to:
Reading package lists... Done
Building dependency tree
Reading state information... Done

then feel free to install you package with apt-get or aptitude


Powered by 123ContactForm | Report abuse

Force ssl in nginx with redirect

Just edit your site configuration file make sure you have a site setup for HTTPS - SSL (port 443)
edit the portion for HTTP:


server {
    listen      80;
    server_name server.yourdomain.tld;
    rewrite     ^   https://$server_name$request_uri? permanent;
}

save and restart nginx
Provided by: ForthScale systems, scalable infrastructure experts

Adding ( passing) linux system variable to Tomcat.

Sometimes you need to pass system variables to applications running in tomcat environment. those could b for example configuration files.
Trying to pass them to start-up script as variables such as 

export TOMCAT_OPTS=-Your.var=foo
will set it in the system shell and will not pass it to the tomcat on it`s start since it runs as a different user.
Java`s System.getEnv("Your.var")
Will return: NULL value.
You need to edit relevant tomcat.conf (tomcat6.conf for example in /etc)
and add:
export Your.var=foo
to the end of file
You can check that variable s set with System.getEnv("Your.var") in you java code.



Provided by: ForthScale systems, scalable infrastructure experts

Fixing libdbus in 64 bit RPM based linux (Centos, SL, Fedora)

Issue affects Firefox, Thunderbird, gEdit etc.
You will see an error:

error while loading shared libraries: libdbus-1.so.3: cannot open shared object file: No such file or directory

most likly you have a 32 bit version of dbus libraries installed.
To fix that you need to install 64 version as well:

yum install dbus-libs.x86_64

Provided by: ForthScale systems, Cloud experts

Instalation of Nomachine NX server or VNC server on Centos 5

Both tools are great to enable external GUI remote access on Linux.

To install NX Free server:
Note. Installation of NX Server requires installation of all three packages: client, node and server due to tools and libraries dependencies.
Download the packages from Nomachine:
wget http://64.34.161.181/download/3.5.0/Linux/nxclient-3.5.0-7.x86_64.rpm
wget http://64.34.161.181/download/3.5.0/Linux/nxnode-3.5.0-9.x86_64.rpm
wget http://64.34.161.181/download/3.5.0/Linux/FE/nxserver-3.5.0-11.x86_64.rpm

Install the packages:
rpm -i nxclient-3.5.0-7.x86_64.rpm
rpm -i nxnode-3.5.0-9.x86_64.rpm
rpm -i nxserver-3.5.0-11.x86_64.rpm

VNC server installation:

yum install vnc-server
yum install xorg* (you need to install those for VNC fonts)

Provided by: ForthScale systems, Cloud experts

Adding KDE Environment to Centos 5

List installation packs on your CentOS 5 machine:
yum grouplist

Install KDE
yum groupinstall "KDE (K Desktop Environment)"

Now you might want to enable GUI remote access with either:
VNC server or Nomachine NX.

Provided by: Forthscale systems, Cloud experts

adding support for TCP socket proxy with Nginx

Install needed prerequisites:

apt-get install build-essential checkinstall
apt-get build-dep nginx
apt-get source nginx

Check out TCP sockets patch for nginx from git repository:

git clone  https://github.com/yaoweibin/nginx_tcp_proxy_module.git


Patch your nginx source code:

cd nginx-1.1.19/
patch -p1 < ../nginx_tcp_proxy_module/tcp.patch

Now configure and build a new package:

./configure \
--add-module=../nginx_tcp_proxy_module/  \
--conf-path=/etc/nginx/nginx.conf  \
--error-log-path=/var/log/nginx/error.log  \
--pid-path=/var/run/nginx.pid  \
--lock-path=/var/lock/nginx.lock  \
--http-log-path=/var/log/nginx/access.log  \
--with-http_dav_module  \
--http-client-body-temp-path=/var/lib/nginx/body  \
--with-http_ssl_module  \
--http-proxy-temp-path=/var/lib/nginx/proxy  \
--with-http_stub_status_module  \
--http-fastcgi-temp-path=/var/lib/nginx/fastcgi  \
--with-debug  \
 --with-http_flv_module \
--prefix=/usr \
--sbin-path=/usr/sbin/nginx 

make

you will end with a package for your CPU architecture similar to:

/root/nginx-1.1.19/nginx_1.1.19-1_amd64.deb





Force install it:

dpkg -i --force-all /root/nginx-1.1.19/nginx_1.1.19-1_amd64.deb

Provided by: Forthscale, Cloud experts

Fixing SSH Daemon - Authentication refused: bad ownership or modes for directory

Then you are unable to connect to your ssh server with your PEM (or PPK) key with connection refused message check the /var/log/auth.log file.
If you see something similar to:
sshd: Authentication refused: bad ownership or modes for directory /Your/Home/Path
You have a home directory permission problem.
change to that user if not already one and execute:
chmod go-w ~/
chmod 700 ~/.ssh
chmod 600 ~/.ssh/authorized_keys 
It will restore your permissions and you should be able to connect.

 Provided by: SiQ systems, Cloud experts

Couple of simple steps to debug munin plugins
First check if munin recognize the plugin.
Execute munin plugin in regular mode:
munin-run plugin_name
you will get output in format of:
some.value XX

then execute munin plugin in configuration mode:
munin-run plugin_name config

you will get output in format of:

graph_title Great Plugin
graph_args --upper-limit 100 -l 0
graph_vlabel %
graph_category some_category
graph_info This is the best munin plugin ever.
something.label LABEL

If you have any problems they can be related to the plugin having a permission problem.

Next step is to test the plugin connection via port 4949
Run a telnet on munin node port 4949

telnet munin-node.example.com 4949

You will get output in format of:

Trying munin-node.example.com...
Connected to munin-node.example.com.
Escape character is '^]'.
# munin node at munin-node.example.com

then type in console:

fetch plugin_name
or
fetch plugin_name config

It will output something similar to munin-run.

Then plugin works with munin-run command but not through telnet execution, you  most likely to have a local PATH problem. Tip: Set env.PATH for the plugin in the plugin's environment file.


Provided by: SiQ systems, Cloud experts

List open ports and listening services in Linux

To list open network ports (sockets) and the processes that run them on any Linux distribution use netstat. You might need to install it via yum / apt-get.
Syntax:
netstat -lnptu

Powered by 123ContactForm | Report abuse

Provided by: SiQ systems, Cloud experts

Amazon AWS EC2 storage types

You can connect different storage types to Amazon EC2 instances, two of them provided naively by Amazon platform and the rest are either provided by external sources or tweaks.  In any EC2 instance (except micro) you have instance storage included in a package. You can also use elastic block storage (EBS) and have an option connecting different 3rd party storage over the network.

 

Instance storage is a fast non-persistent storage provided by Amazon. It means it will revert to it`s original state after any system shutdown, erasing any changes you have applied to the file-system. It is very useful for running “dumb” servers that do not store data locally or as an additional storage for temporary files.

 

Elastic block storage (EBS) is a persistent storage provided by Amazon. All and any data stored on it is available after instance shutdown and can be manipulated with on device level. For example you can detach an EBS volume from one instance and attach it to another. However EBS can not be attached to more then once instance at the same time.

 

Using S3 as a file system. S3 is a storage infrastructure provided by Amazon as a service, it is not a part of EC2 (Elastic cloud) but can be used to store and retrieve any amount of data from anywhere at any time. Because S3 infrastructure is fully managed and scaled by Amazon it is very useful for large scale web projects, backup media and large volume data transfers. Using S3 as a file system is done via FUSE in Linux or as a mapped network drive in MS Windows. We are providing a tutorial on Linux implementation of S3 as a file system .

 

There are also few companies out there providing iSCSI storage arrays for AWS, one of them is Zadara storage, company providing Virtual Private Storage Arrays and currently in beta stage. iSCSI as any other network attached storage systems will incorporate persistence and availability of EBS at much faster speed.

Taken from SiQ systems cloud glossary.

Setting Filezilla Server on Amazon EC2 instance with passive ftp

If you want to set Filezilla  ftp server to handle passive connections on your AWS EC2 instances, you should do the following.

Select non used TCP ports, for example 9024-9048 range

Configure firewalls:

In your AWS EC2 security group, allow the incoming connections on chosen ports:

tcp port 20
tcp port 21
tcp port 9024-9048

If using Windows firewall  on you instance, allow connections on same ports.

Now configure Filezilla to use specific port range on Passive connections:

Open Filezilla management console.

Got to: Edit > Settings > Passive Mode Settings

'External Server IP Address for passive mode transfers'

If you use AWS Elastic IP, enter it in "Use the following IP",
if not - use Filezilla provided web service with "Retrieve external IP address from" option.

Check 'Don't use external IP for local connections'

Check 'Use custom port range'

Enter chosen values (in our example) 9024 - 9048 for custom port range.

Powered by 123ContactForm | Report abuse

Provided by:SiQ systems, Cloud experts

Adding global DNS servers

Sometimes you need to preconfigure your servers for dns resolution and you are not sure what local DNS addresses will be or just to use backup dns servers for resolution.
Here is small list of globally accessible DNS servers:

nameserver 8.8.8.8
nameserver 8.8.4.4
nameserver 210.80.60.1
nameserver 210.80.60.2
nameserver 208.67.222.222
nameserver 208.67.220.220

NTP servers for Israel

For Israeli NTP servers you can use: il.pool.ntp.org

Since there are not enough servers in this zone, we recommend you buck up by use the Asia zone (asia.pool.ntp.org):

server 0.asia.pool.ntp.org
server 1.asia.pool.ntp.org
server 2.asia.pool.ntp.org
server 3.asia.pool.ntp.org

Provided by:SiQ systems, Cloud experts