Install Pgbouncer in Linux

Environment - Amazon Linux/RHEL

Pre-Requiste:

Should have PG installed.( Please refer - http://postgresql-open-source.blogspot.in/2017/12/postgresql-installation-script-source.html)

Installation Steps-

1. Download and install libevent from 'http://libevent.org/'(latest release) which is necessary for pgbouncer

sudo su -
yum install wget
wget https://github.com/libevent/libevent/releases/download/release-2.1.8-stable/libevent-2.1.8-stable.tar.gz
tar -xzf libevent-2.1.8-stable.tar.gz
cd libevent-2.1.8-stable
yum install gcc
./configure
make
make install

2. Download pgbouncer from this link 'http://pgfoundry.org/frs/?group_id=1000258'

wget http://pgfoundry.org/frs/download.php/3393/pgbouncer-1.5.4.tar.gz
tar -xzf pgbouncer-1.5.4.tar.gz
cd pgbouncer-1.5.4
./configure --prefix=/usr/local
make
make install

3. Enter the lib file location into '/etc/ld.so.conf.d/libevent-i386.conf' and save the file and load

vi /etc/ld.so.conf.d/libevent-i386.conf and enter /usr/local/lib/ and save the file and load
ldconfig

4. Create log directory

mkdir -p /var/log/pgbouncer
mkdir -p /var/run/pgbouncer

5. Change the ownership to 'postgres' for both the directory
chown postgres:postgres /var/log/pgbouncer
chown postgres:postgres /var/run/pgbouncer


6. Change the ownership to 'postgres' for pgbouncer binary

cd /usr/local/bin/
chown -R postgres:postgres pgbouncer


7. copy all the roles and passoword of postgres

[postgres@ip-172-31-74-77 pgsql10]$ psql

postgres=# \o /usr/local/pgsql10/pgbouncer.auth
postgres=# select '"'||rolname||'"'||' "'||rolpassword||'"' from pg_authid;
postgres=# \o
postgres=# \! cat /usr/local/pgsql10/pgbouncer.auth
                     ?column?                   
--------------------------------------------------
 "new_user" "md500084d8780bcdf83b7345e04c09e0584"
 "postgres" "md532e12f215ba27cb750c9e093ce4b5127"
(7 rows)


8. Edit the 'pgbouncer.auth' file to have only 'role' and 'password'

vi /usr/local/pgsql10/pgbouncer.auth

"new_user" "md500084d8780bcdf83b7345e04c09e0584"
"postgres" "md532e12f215ba27cb750c9e093ce4b5127"


9. Edit the Configuration File

vi /etc/pgbouncer.ini

[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /tmp
#auth_type = any
auth_type = trust
auth_file = /usr/local/pgsql10/pgbouncer.auth
admin_users = postgres
stats_users = postgres
pool_mode = transaction
server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10
max_client_conn = 1000
default_pool_size = 20
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1



10. Start pgbouncer

[postgres@ip-172-31-74-77 bin]$ /usr/local/bin/pgbouncer -d /etc/pgbouncer.ini
2017-12-15 04:15:34.821 4794 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 1000, max fds possible: 1010


11. Check if pgbouncer is installed successfully -

[postgres@ip-172-31-74-77 pgbouncer]$ psql -p 6432 -U postgres -d pgbouncer
psql (10.1, server 1.5.4/bouncer)
Type "help" for help.

pgbouncer=# show help;
NOTICE:  Console usage
DETAIL: 
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SET key = arg
RELOAD
PAUSE [<db>]
RESUME [<db>]
KILL <db>
SUSPEND
SHUTDOWN
SHOW
pgbouncer=#

PostgreSQL Installation Script - Source Installation

Just copy the below lines and create a file. Your linux system will be ready with custom PostgreSQL installation. You can modify the script based on your need. You can consider the below lines as a framework. Cheers !!

Environment - Amazon Linux

#!/bin/bash

set -x

sudo su - <<EOF

/usr/bin/yum -y install gcc

/usr/bin/yum -y install readline-devel

/usr/bin/yum -y install zlib-devel

echo "Adding User for Postgres server"

/usr/sbin/useradd -d /home/postgres -m postgres

read -s -p "Enter password for postgres: " password

echo $password |passwd --stdin postgres

chage postgres -M -1

#/usr/sbin/useradd -d /home/postgres -m postgres

#passwd postgres

#read -p "New password:" postgres

#read -p "Retype new password " postgres

/usr/bin/wget https://ftp.postgresql.org/pub/source/v10.1/postgresql-10.1.tar.gz

/bin/tar -xzf postgresql-10.1.tar.gz

cd postgresql-10.1

/root/postgresql-10.1/configure --prefix=/usr/local/pgsql10/

make

make install

/bin/mkdir /usr/local/pgsql10/data

/bin/chown postgres:postgres -R /usr/local/pgsql10

EOF

sudo su - postgres <<EOF

cd /usr/local/pgsql10/bin

/usr/local/pgsql10/bin/initdb -D /usr/local/pgsql10/data

/usr/local/pgsql10/bin/pg_ctl -D /usr/local/pgsql10/data start

EOF

sudo su - <<EOF

/bin/ln -s /usr/local/pgsql10/bin/psql /usr/bin

EOF

sudo su - postgres <<EOF

/usr/bin/perl -i -p -e "s/\#listen_addresses = 'localhost'/listen_addresses = '*'/;" /usr/local/pgsql10/data/postgresql.conf

/usr/bin/perl -i -p -e "s/shared_buffers = 128MB/shared_buffers = 2GB/;" /usr/local/pgsql10/data/postgresql.conf

/usr/bin/perl -i -p -e "s/\#port = 5432/port = 5433/;" /usr/local/pgsql10/data/postgresql.conf

/usr/local/pgsql10/bin/pg_ctl -D /usr/local/pgsql10/data restart

EOF

Figure out Hidden used space in Linux

First Option:
root@devops-test:/ lsof | grep -i delete | sort –k8 | awk '{for(i=1;i<=6;i++){printf "%s ", $i}; print
$7/1048576 "MB" " "$8" "$9 }

Second Option:
Another situation was garbage file which was in /mnt/var/lib/mysql/garbage.file which again decreased disk usage to 23% upon removing the garbage.file
Tried to mount such files from root to /mnt using mount –bind / /mnt

Create EBS Volume and attach it to client

To Create an extra EBS Volume I ran the below command.
root@devops-test:~# aws ec2 create-volume --size 1 --region eu-central-1 --availability-zone eu-
central-1a --volume-type gp2

Found out availability zone and other details using below command:
root@devops-test:~# curl http://169.254.169.254/latest/meta-data/ adding appropriate keywords at the end of the command. For example, if I need to find instance-id, I will query
using curl http://169.254.169.254/latest/meta-data/instance-id

Attaching volume to AWS Client:
root@devops-test:~# aws ec2-attach-volume --volume-id vol-0b991fe06085ee22a --region eu-
central-1 --instance-id i-047c048c06938669e --device /dev/xvde

Why should you be worried about somebody executing this at the shell prompt? :(){ :|:& };:

It’s a fork bomb, crashes the system.

How do we shrink ibdata?

Below are the steps performed to shrink ibdata1

1. Backup MySQL DB and drop the DB
2. Stop MySQL service and add innodb_file_per_table=1 in my.cnf
3. Remove ibdata1, ib_logfile0 and ib_logfile1
4. Start MySQL service and restore the database

Converting ibdata to .ibd files per table

First enabling innodb_file_per_table and then when we optimize the tables, we can see that it creates .ibd files per table. Optimize table drops and recreates table so new .ibd files are created. Below are the steps:

Mysql> set global innodb_file_per_table=1;
Mysql> optimize table table_name;

How to recover MySQL Password without restart?

There is option to recover root access without restarting MySQL Service. But that needs creating another instance without InnoDB engine. Once we have another instance. We can copy the .frm, .myi & .myd files to new instance and change/recover root user password as we normally do in new instance and copy them back to original instance.

How to Recover MySQL Password ?

We need to skip grant tables and start MySQL service. It helps to login MySQL without
password. Once we login MySQL CMD, we can change the password of root accordingly.

root@devops-test:/var/lib# mysqld_safe --skip-grant-tables &
root@devops-test:/var/lib# mysql -u root -D mysql -Bse "update user set password=PASSWORD("***") where User='root'"
root@devops-test:/var/lib# mysql -u root -D mysql -Bse "flush privileges"