Configure Postgres Hot Standby Server Using Log Shipping::

 Configure Postgres Hot Standby Server Using Log Shipping::
=============================================

Configure two identical database server machines. My version is (PostgreSQL) 9.6.2. Differences in database version can cause conflicts during log shipping.

Server 1 -  dbmaster.local - 192.168.1.1
Server 2 -  dbslave.local   - 192.168.1.2

* Created the user postgres on both machines.
* Enabled ssh key authentication for postgres user

Master configuration::
 
Edit postgresql.conf

wal_level = hotstandby
archive_mode = on
archive_command = 'scp  %p  postgres@dbslave.local:archive_logs/archive%f '

listen_addresses = '*'
archive_timeout = 120
max_wal_senders =1

Edit pg_hba.conf
host     all                      all             192.168.1.0/24        trust
host     replication     rep            192.168.1.2/24        trust

Iptables and Firewalld Configuration

Iptables Commands::

===============

Iptables contains 4 builtin tables ::

1. Filter Table

    INPUT chain – Incoming to firewall. For packets coming to the local server.
    OUTPUT chain – Outgoing from firewall. For packets generated locally and going out of the local server.
    FORWARD chain – Packet for another NIC on the local server. For packets routed through the local server.

2. NAT Table

    PREROUTING chain – Alters packets before routing. i.e Packet translation happens immediately after the packet comes to the system (and before routing). This helps to translate the destination ip address of the packets to something that matches the routing on the local server. This is used for DNAT (destination NAT).
    POSTROUTING chain – Alters packets after routing. i.e Packet translation happens when the packets are leaving the system. This helps to translate the source ip address of the packets to something that might match the routing on the desintation server. This is used for SNAT (source NAT).
    OUTPUT chain – NAT for locally generated packets on the firewall.

3. Mangle Table

    PREROUTING chain – Alters packets before routing. i.e Packet translation happens immediately after the packet comes to the system (and before routing). This helps to translate the destination ip address of the packets to something that matches the routing on the local server. This is used for DNAT (destination NAT).
    POSTROUTING chain – Alters packets after routing. i.e Packet translation happens when the packets are leaving the system. This helps to translate the source ip address of the packets to something that might match the routing on the desintation server. This is used for SNAT (source NAT).
    OUTPUT chain – NAT for locally generated packets on the firewall.

4. Raw Table

    PREROUTING chain
    OUTPUT chain

Some simple commands , Please try to understand the use of these commands.

# iptables -n -L -v --line-numbers   , where  -L : List rules, -v : Display detailed information., -n : Display IP address and port in numeric format.

To prevent accessing a website, for example linuxgeeknotes.blogspot.in::

# host -t a  linuxgeeknotes.blogspot.in gives ipaddress 67.123.116.0
# whois 67.123.116.0 | grep CIDR

# iptables -A OUTPUT -p tcp -d 67.123.116.0/17 -j DROP
# iptables -A OUTPUT -p tcp -d linuxgeeknotes.blogspot.in -j DROP



To log and block IP spoofing on public interface called eth1::

# iptables -A INPUT -i eth1 -s 10.0.0.0/8 -j LOG --log-prefix "IP_SPOOF A:"
# iptables -A INPUT -i eth1 -s 10.0.0.0/8 -j DROP


Drop and Accept traffic using mac address::

# iptables -A INPUT -m mac --mac-source 00:0F:EA:91:04:08 -j DROP
# iptables -A INPUT -p tcp --destination-port 22 -m mac --mac-source 00:0F:EA:91:04:07 -j ACCEPT


Block and allow ping requests::

# iptables -A INPUT -p icmp --icmp-type echo-request -j DROP
# iptables -A INPUT -i eth1 -p icmp --icmp-type echo-request -j DROP

To open a range of ports::

# iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 7000:7010 -j ACCEPT

To allow a range of ip to port 80

# iptables -A INPUT -p tcp --destination-port 80 -m iprange --src-range 192.168.1.100-192.168.1.200 -j ACCEPT


Replace ACCEPT with DROP to block port:
Open port ssh tcp port 22

# iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
# iptables -A INPUT -s 192.168.1.0/24 -m state --state NEW -p tcp --dport 22 -j ACCEPT 

Open cups (printing service) udp/tcp port 631 for LAN users ##

# iptables -A INPUT -s 192.168.1.0/24 -p udp -m udp --dport 631 -j ACCEPT
# iptables -A INPUT -s 192.168.1.0/24 -p tcp -m tcp --dport 631 -j ACCEPT   Allow time sync via NTP for lan users (open udp port 123) ##

# iptables -A INPUT -s 192.168.1.0/24 -m state --state NEW -p udp --dport 123 -j ACCEPT

Open tcp port 25 (smtp) for all ##
# iptables -A INPUT -m state --state NEW -p tcp --dport 25 -j ACCEPT

Open dns server ports for all ##
# iptables -A INPUT -m state --state NEW -p udp --dport 53 -j ACCEPT
# iptables -A INPUT -m state --state NEW -p tcp --dport 53 -j ACCEPT

Open http/https (Apache) server port to all ##
# iptables -A INPUT -m state --state NEW -p tcp --dport 80 -j ACCEPT # iptables -A INPUT -m state --state NEW -p tcp --dport 443 -j ACCEPT

Open tcp port 110 (pop3) for all ##
# iptables -A INPUT -m state --state NEW -p tcp --dport 110 -j ACCEPT

Open tcp port 143 (imap) for all ##
# iptables -A INPUT -m state --state NEW -p tcp --dport 143 -j ACCEPT

Open access to Samba file server for lan users only ##
# iptables -A INPUT -s 192.168.1.0/24 -m state --state NEW -p tcp --dport 137 -j ACCEPT
# iptables -A INPUT -s 192.168.1.0/24 -m state --state NEW -p tcp --dport 138 -j ACCEPT
# iptables -A INPUT -s 192.168.1.0/24 -m state --state NEW -p tcp --dport 139 -j ACCEPT
# iptables -A INPUT -s 192.168.1.0/24 -m state --state NEW -p tcp --dport 445 -j ACCEPT

## open access to proxy server for lan users only #
# iptables -A INPUT -s 192.168.1.0/24 -m state --state NEW -p tcp --dport 3128 -j ACCEPT

## open access to mysql server for lan users only #
# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

Limiting the number of connections for a particular service.

# iptables -A INPUT -p tcp --syn --dport 22 -m connlimit --connlimit-above 3 -j REJECT

# iptables -p tcp --syn --dport 80 -m connlimit --connlimit-above 20 --connlimit-mask 24 -j DROP

# iptables -F      , -F Deleting (flushing) all the rules. # iptables -X      , -X Delete chain. # iptables -t nat -F , -t table_name # iptables -t nat -X # iptables -t mangle -F

# iptables -t mangle -X



Firewalld Commands::
====================

The firewalld daemon manages groups of rules using entities called zones.

Firewalld uses zones and services instead of chain and rules.

Some of the firewalld commands are as follows. Please look into it and try to understand::

# systemctl start firewalld

# systemctl enable firewalld

# systemctl stop firewalld

# systemctl disable firewalld

# firewall-cmd --state

# systemctl status firewalld

# firewall-cmd --reload

# firewall-cmd --zone=public --add-service=http --permanent

# firewall-cmd --zone=public --remove-service=http --permanent

# firewall-cmd --zone=public --add-rich-rule 'rule family="ipv4" source address=192.168.0.14 accept'

# firewall-cmd --zone=public --add-port=12345/tcp --permanent

# firewall-cmd --zone=public --remove-port=12345/tcp --permanent

# firewall-cmd --zone="public" --add-forward-port=port=80:proto=tcp:toport=12345

# firewall-cmd --zone="public" --add-forward-port=port=80:proto=tcp:toport=8080:toaddr=123.456.78.9

# firewall-cmd --zone=public --add-rich-rule 'rule family="ipv4" source address=192.168.1.2 accept'

# firewall-cmd --zone=public --add-rich-rule 'rule family="ipv4" source address="192.168.1.2" port port=22 protocol=tcp reject'

# firewall-cmd --zone=public --add-rich-rule 'rule family="ipv4" source address="10.1.0.3" forward-port port=80 protocol=tcp to-port=6532'

389 Directory Server - Multi-Master Replication::

389 Directory Server - Multi-Master Replication::
==========================================
Setup as follows::
Directory Server 1 :  192.168.1.1 (dirs1.test.int)
Directory Server 2 :  192.168.1.2 (dirs2.test.int)

 Pre-requisites on both servers ::

# Add /etc/hosts entries on both  servers.
# Install apache webserver on both.
# Whitelist ports 389,9830 and 636 and both server ip's in the firewall.

# Add the following lines at the end  of  /etc/sysctl.conf  file.
net.ipv4.tcp_keepalive_time = 300
net.ipv4.ip_local_port_range = 1024 65000
fs.file-max = 64000

# Add the following lines at the end  of   /etc/security/limits.conf file
*               soft     nofile          8192  
*               hard     nofile          8192

# Set ulimit as follows and add it in /etc/profile
 ulimit -n 8192

Install the 389 Server packages on both the servers as follows::

# yum install 389-ds-base openldap-clients idm-console-framework 389-adminutil 389-admin 389-admin-console 389-console 389-ds-console

# setup-ds-admin.pl

You will be prompted for configuration questions. Configure accordingly for your use.

Start the services
#  systemctl start dirsrv.target
#  systemctl start dirsrv-admin.service
#  /httpd-2.4/bin/apachectl start

Multi-Master Replication Configuration:: 

Server 1 Configuration:

# Login to 389-console on server 1 using the credentials given during setup-admin.pl


Double Click "Directory Server" on the left pane--->Click Directory tab
Right Click "Config" menu on left pane  and Create new user there( This is the replication user which is used for replication)
In my case I created the user "rep"

Now go to Configuration Tab and Click replication.
Enable Changelog-->Select the path for the log and Click Save

Under Replication-->userRoot-->Enter replication settings
Replication Role : MultiMaster
Set Purge Delay as : Never
Enter New supplier DN as : uid-rep,cn=config (as my user is rep)
Click Save.

Now Right Click userRoot-->New Replication Agreement
Enter a name for the agreement and Click Next.

Enter the consumer details(Here in this server, Supplier is Server1 and Consumer is Server2)
Enter the host name and port details of Consumer.

Enter the replication username and password and Click Next to complete the replication.

Server 2 Configuration:

# Login to 389-console on server 2 using the credentials given during setup-admin.pl

Double Click "Directory Server" on the left pane--->Click Directory tab
Right Click "Config" menu on left pane  and Create new user there( This is the replication user which is used for replication)
In my case I created the user "rep"

Now go to Configuration Tab and Click replication.
Enable Changelog-->Select the path for the log and Click Save

Under Replication-->userRoot-->Enter replication settings
Replication Role : MultiMaster
Set Purge Delay as : Never
Enter New supplier DN as : uid-rep,cn=config (as my user is rep)
Click Save.

Now Right Click userRoot-->New Replication Agreement
Enter a name for the agreement and Click Next.

Enter the consumer details(Here in this server, Supplier is Server2 and Consumer is Server1)
Enter the host name and port details of Consumer.

Enter the replication username and password and Click Next to complete the replication.


Once done, Try creating ldap users via 389-console under the domains on both the servers. The users will be automatically replicated and listed on both the servers.

Import ssl certificate to Java Key Store::

Import SSL certificates to Java Key Store::
=================================
Download the certificate to the home directory of the domain and run the keytool utility to import the certificate to Java Key Store


===========

Command to convert .pfx certificate to .crt

#  openssl pkcs12 -in ind-vmn-vm3.win12vmn.test.int.pfx -clcerts -nokeys -out ind-vmn-vm3.win12vmn.test.int.crt

The self-signed certificate generated in windows is in  .pfx format.
===========


# keytool --import --file certificate.cer --keystore  name


# keytool -import -trustcacerts -alias testaliasname -file ind-vmn-vm3.win12vmn.test.int.crt -keystore /opt/java/jdk1.8.0_121/jre/lib/security/cacerts

To list the installed certificate:
# keytool -list -v -keystore /opt/java/jdk1.8.0_121/jre/lib/security/cacerts  | grep Alias | grep gigtest

It will be stored in /opt/username/jdk1.8.0_121/jre/lib/security/cacerts

Backup cacerts before running the java keytool.

Postgresql-9.6.1 Database Streamline Replication from Master to Slave

Postgresql-9.6.1 Database Streamline Replication from Master to Slave:
=====================================================
I have two VM's with Centos 7 and Ip's are as follows::

Master :  192.168.1.1
Slave   :  192.168.1.2

Master Configuration::

Create user postgres 
Download postgresql-9.6.1.tar.gz and untar it

# ./configure --prefix=/home/postgres/pgsql
# make
# make install


Initialize new database:

#  /home/postgres/pgsql/bin/initdb -D /home/postgres/pgsql/data

# /home/postgres/pgsql/bin/pg_ctl -D /home/postgres/pgsql/data/ -l logfile start
# /home/postgres/pgsql/bin/pg_ctl -D /home/postgres/pgsql/data/ -l logfile stop

Edit pg_hba.conf

Add entries for host ips to connect and the replications slave details as follows::

# host            all                                   all                          192.168.1.0/24                                        trust


Edit postgresql.conf

listen_addresses = '*'
wal_level = hot_standby
checkpoint_segments = 8
archive_mode = on
max_wal_senders = 3
wal_keep_segments = 8

# /home/postgres/pgsql/bin/psql -U 

Create replication user on master::

postgres=# CREATE ROLE rep WITH REPLICATION PASSWORD '********' LOGIN




Client Configuration ::

Create user postgres 
Download postgresql-9.6.1.tar.gz and untar it

# ./configure --prefix=/home/postgres/pgsql
# make
# make install

Initialize new database:

#  /home/postgres/pgsql/bin/initdb -D /home/postgres/pgsql/data

Rename the main directory in the slave as follows:
mv /home/postgres/pgsql/data  /home/postgres/pgsql/data.backup

pg_basebackup -h 192.168.1.1 -D /home/postgres/pgsql/data -U rep -v -P



Edit pg_hba.conf

Add entries for host ips to connect and the replications slave details as follows::

# host            all                                   all                          192.168.1.0/24                                        trust
# host            replication                    rep                        192.168.1.2/24                                        trust


Now create recovery.conf and add the entries as follows in slave


vi /home/postgres/pgsql/data/recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=192.168.1.1 port=5432 user=rep password=password'
trigger_file = '/tmp/postgresql.trigger.5432'

or  

standby_mode = 'on'
primary_conninfo = 'host=192.168.240.41 port=5432 user=rep'
trigger_file = '/tmp/postgresql.trigger.5432'

and create .pgpass file under the home directory of the postgres user as follows 

192.168.240.41:5432:*:rep:123456

Now configure pgadmin on your desktop and create tables on the master database server. 

It should be automatically replicated to the slave server.

High Availability Configuration and LoadBalancing for webservers using HAProxy::

High Availability  Configuration and LoadBalancing for webservers using HAProxy::
=============================================================
My scenario is as follows:

HA Proxy is installed on 192.168.1.100

I have one application hosted on four Tomcat instances as follows sharing the same database.
192.168.1.10:8084
192.168.1.10:8085
192.168.1.10:8086
192.168.1.10:8087

Database server 192.168.1.50

I have four Apache instances installed on another server which is connected to the tomcat instances mentioned above using mod_jk connector

192.168.1.20:8040 --> 192.168.1.10:8084
192.168.1.20:8050 --> 192.168.1.10:8085
192.168.1.20:8060 --> 192.168.1.10:8086
192.168.1.20:8070 --> 192.168.1.10:8087

So once configured, when I browse  192.168.1.100, I should get the any of the four tomcat pages

Please check the previous thread for apache-mod_jk configurations


HAproxy installation steps:-
# yum install haproxy

Edit /etc/haproxy/haproxy.cfg and add the frontend and backend entries at the bottom of the file.

Comment off any default entries.
========================

frontend http-service
    bind 192.168.1.100:80
    acl url path_beg /
    default_backend http-server
    mode http

backend http-server
    balance roundrobin
    option httpchk HEAD /
    server service1 192.168.1.20:8040 check
    server service2 192.168.1.20:8050 check
    server service3 192.168.1.20:8060 check
    server service4 192.168.1.20:8070 check

===========================
Disable  SELinux and  Restart the service.