Postgresql 9.1 Streaming replication on ubuntu 12.04 part 1

Setting up PostgreSQL Replication

I have a need to set up a PostgreSQL Replication Database.   In Postgres 9.0+  they have the ability to have a read only replicated database that mirrors the live database, it can lag slightly behind the live database (although in my simple test it was spot on).  The Replication DB can be read from but not written to.  In my particular case I just want to use it as a failover DB that can be brought up as a live DB if ever needed.

Here is set up.  I have two Ubuntu 12.04 LTS 64 bit servers with postgresl 9.1 installed.  One of these servers will be the primary (master) PostgreSQL server and the other will be the replication (slave) postgres server.  

Set up a test Database :

To do a full test I am going to log into my PRIMARY server and create a new database.  The purpose of the database is for testing. Useful in the last phase.

Log into the DB

> sudo su postgres         >   psql -d postgres -U postgres

Create a test user

>    CREATE USER navneet WITH PASSWORD ‘rathi’;

Create the Database

>    CREATE DATBASE nanddb;

Make sure it was created by running \l  

Grant navneet privileges to this database

>    GRANT ALL PRIVILEGES ON DATABASE nand to navneet;

Now let’s test it quit out of postgres and try

>    \q         >    psql -d nand -U navneet

And I get an error 

 The authentication is peer not (md5/trust)

I am trying to log in as navneet but I am logged in as the postgres user. It’s a setting in “/etc/postgresql/9.1/main/pg_hba.conf “. I failed to edit

Edit the file

>    sudo vi   /etc/postgresql/9.1/main/pg_hba.conf

Change

# "local" is for Unix domain socket connections only local   all             all                                     peer

To

# "local" is for Unix domain socket connections only local   all             all                                     trust

It will allow all local user to login without password .if you try to login from outside it will ask for password if you create a ssh tunnel no password is needed.

And restart PostgreSQL to take the effect of new settings.

>    sudo /etc/init.d/postgresql  restart  or  >  service postgresql restart

And then log back in

>    psql  -U navneet nand

This time it worked!

Now connect to the database.

>    \c nand

Create a simple table that I can send updates to.  The purpose of this table is to test the set-up of the replication server.  The live server should be able to continue getting updates while the replication server is being set up, then it will “catch up”

>    CREATE TABLE data(         >      id serial primary key not null,         >      time timestamp not null default CURRENT_TIMESTAMP,         >      number integer not null         >  );

This creates a table with a primary key that auto increments, and a time field which will default to the current_timestamp

Run the following command to confirm the table has been created and is working as intended.

>    \dt         >    select * from data;         >    INSERT INTO data (number)         >    VALUES (34);         >    select * from data;

OK now that I have set up I need to create a simple program to insert data into this table on some kind of loop.

Writing a simple python program

First I had to install some libraries that python depends on for talking to PostgreSQL

>    sudo apt-get install python-psycopg2         >    vi insertDB.py

Here is my python code

#!/usr/bin/python # # #  Simple Script to insert data # import psycopg2 import sys import time con = None try:     # First get the current maximum     con = psycopg2.connect(database='nand', user='navneet', password='rathi')     cur = con.cursor()     cur.execute('select MAX(number) from data')     x = cur.fetchone()[0]     if x is None:        x = 0     while (True):         x = x + 1         cur.execute('INSERT INTO data(number) VALUES (' + str(x) + ')')         con.commit()         print 'inserting ' + str(x)         time.sleep(1) except psycopg2.DatabaseError, e:     print 'Error %s' % e     sys.exit(1) finally:     if con:         con.close()

It will connect, locally to my PostgreSQL database and query the data table to find the largest integer in the number field. The program then increments it by one and inserts the new values (always pausing 1 second and incrementing by 1).

The idea is this is constantly adding data to the database, so that I can confirm that the replication I create works correctly.

WAL (write ahead log) Files

Postgres uses write ahead logging

“WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage.”

First let’s go find the WAL files. /etc/postgresql/9.1/main/postgresql.conf file under the data_directory field.  In 9.1 its typically /var/lib/postgresql/9.1/main At any rate the WAL files are located in the pg_xlog folder within the DB folder

>    cd  /var/lib/postgresql/9.1/main/pg_xlog         >    ls -alh

Here you should see several files that are 16MiB in size with numbers for names.

These are the WAL files.   In the /etc/postgresql/9.1/main/postgresql.conf file there is a wal_keep_segments field which is typically set to 0.  If it is set to 0 it means that at least 0 WAL files must be kept in the queue.  More typically are here as the DB needed.   This setting must be increased to assure that the replication database has access to them.

Primary (Master) DB settings

First let’s set up the master DB to be able to allow replications DBs to get the updates they need

postgresql.conf

First we need to edit /etc/postgresql/9.1/main/postgresql.conf

>    sudo vi  /etc/postgresql/9.1/main/postgresql.conf

The following needs to be edited (the approx @line is to show where the typical line number is for this setting)

@line 61 listen_addresses = '*'          # what IP address(es) to listen on; @line 155 wal_level = hot_standby                  # minimal, archive, or hot_standby @line 198 max_wal_senders = 5                # max number of walsender processes @line 201 wal_keep_segments = 128         # in logfile segments, 16MB each;

Here is the “why” for each as best as I have researched.

listen_addresses = '*'       

Listen to any incoming connection. 

wal_level = hot_standby

This must be set to archive or hot_standy .  The default minimal does not put enough information in the WAL files to truly reconstruct the Database.

max_wal_senders = 5              

Sets number of concurrent streaming backup connections.   I think you could get by with 1 if you only have 1 failover server; I just set to 5 because all the other were doing it. 

wal_keep_segments = 128

This will keep at a minimum 128 WAL files.  At the default size of 16MiB that totals 2 GiB.  This is a lot but I want to make sure I do not lose anything on the transfer.

pga_hba.conf

Now edit /etc/postgresql/9.1/main/pga_hba.conf

>    sudo vi  /etc/postgresql/9.1/main/pg_hba.conf

Add this line to the bottom

host     replication    postgres        172.16.2.208/32        trust

This just says to trust the server at 172.16.2.208, which is my replication server.

Replication Role (First check & do it if Required)

I made a mistake when I first did this I did not realize there is a replication role in the database that must be designated.

When I simply tried to use the postgres user I got the following error.

2012-04-21 08:26:01 MDT FATAL:  could not connect to the primary server: FATAL:  must be replication role to start walsender

I am using the postgres user as the replication user.   To see what the postgres user has run the following command from the postgres database.

>    \du

Here you can see that my postgres user does not have the “Replication” Role.

So I added the role to my postgres user, most sites I found suggest creating a new user that only handles replication.   I decided to just go with reusing the postgres user.

From the postgres database run this command

>    ALTER ROLE postgres WITH REPLICATION;

Now run the \du command again

>    \du postgres

And you should see this

Now it has the Replication role!

Restart the Primary database

From the command line

>    sudo /etc/init.d/postgresql restart or service postgresql restart

After it has been restarted I kick off my python script. 

>    sudo /etc/init.d/postgresql restart

I left it running in its own terminal

Then I logged into the Database and did a few quick checks to make sure that data is being constantly loaded into the database.

>    psql -d nand –U navneet

Then from psql

>    select count(*) from data;

I ran this a few times to confirm its growing in size.

Copy the Database over to the Replication (Slave) server

My first go at this I screwed it up! .  I thought I could do a simple pg_dumpall from the primary server to the replication server.  I logged into the Replication server and ran these commands.

> sudo su postgres          > time pg_dumpall -h 172.16.2.207 -U postgres | psql -d postgres -U postgres

(I added the time command to see how long it runs)

And then after setting up the replication server and restarting it (which I will get into later) the postgres would not start up and I got this error.

FATAL:  hot standby is not possible because wal_level was not set to "hot_standby" on the master server

But I did set it!  So what is going on?

So here is the correct way of doing it.

First shut off postgres on the replication server

>    sudo su /etc/init.d/postgresql stop

Next log into the primary server and run the following commands

>    sudo su postgres         >    psql -d postgres -U postgres

You are logged into the database

Now tell the database you are going to start a backup.  You can still keep using your database as you normally would, there will be no interruption to incoming data.

>  SELECT pg_start_backup('mybackup_label', true);

From the command line copy your data directory folder from the primary server to the replication server.  In my case the data directory is “/var/lib/postgresql/9.1/main/” (this is set in the postgresql.conf file).  I kept the data directory the same on both primary and replication server.

Here is my command adjust it according to where you data directory is located.

>  time scp -r  /var/lib/postgresql/9.1/main/*   root@172.16.2.208:/var/lib/postgresql/9.1/main/

After the database has been copied over run the following command from the psql, this will say the backup is done on the master server.

>  SELECT pg_stop_backup();

I got this error.  “NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup”

I did not set up WAL archiving but I did set up wal_keep_segments, I think this is only a problem if you do not have enough wal segments.   I think I am OK.  Let’s see…

Replication (Slave) DB server settings

Now that we have backed up the database to the slave/replication database, we need to change it to an actual replication database.

postgresql.conf

First we need to edit /etc/postgresql/9.1/main/postgresql.conf

>    sudo vi  /etc/postgresql/9.1/main/postgresql.conf

The following needs to be edited (the @line is to show where the typical line number is for this setting)

@line 59 listen_addresses = '*'          # what IP address(es) to listen on; @line 210 hot_standby = on                        # "on" allows queries during recovery

Here is the “why” for each as best as I have researched.

listen_addresses = '*'       

Listen to any incoming connection. 

hot_standy = on

Allows you to query, but not update the replication DB

recovery.conf

Now the recovery.conf file must be created.   I am using Ubuntu 12.04 with a postgres 9.1 .The recovery.conf file needs to be in the data directory, as defined in postgresql.conf.  In my case that is “/var/lib/postgresql/9.1/main/”

So I copied the /usr/share/postgresql/9.1/recovery.conf.sample file to /var/lib/postgresql/9.1/main/recovery.conf and open it for editing.

>    cd  /usr/share/postgresql/9.1/         >    cp recovery.conf.sample  /var/lib/postgresql/9.1/main/recovery.         >    sudo vi /database/postgresqldata/recovery.conf

The following needs to be edited (the @line is to show where the typical line number is for this setting)

@line 108 standby_mode = on @line 110 primary_conninfo ='host=172.16.2.209 port=5432 user=postgres' @line 124 trigger_file = '/home/nrathi/failover'

Here is an explanation of each of these

standby_mode = on

Just sets the standby mode to on

primary_conninfo

All the information for the replication(slave) server to connect to the primary (master) server.

trigger_file

if this file exists the server will stop being a replication server and start being a primary server.  It checks periodically for this file.

Fix ownership of files. (be very careful the chown command fire it as it is  )

>  sudo chown -R postgres:postgres /var/lib/postgresql/9.1/main/         >  sudo chmod 700 /var/lib/postgresql/9.1/main/recovery.

Now start the postgres database on the replication (slave) server

>    sudo /etc/init.d/postgresql start

Now log into the database

>    sudo su postgres         >    psql -d postgres –U postgres

Then from postgres I ran the following commands

>    \c nand         >    select count(*) from data;         >    select pg_last_xlog_receive_location();

And I can see that it is getting live data!  So that is it you now have a replication server.  

Setup the Quota for internet User.

Using a Simple iptables command we can set up a quota for the internet user

Step 1
Take a Linux machine which will act as a gateway for you and route all the traffic from

All we want to have is the following: packets arriving from the local net with a receipient's IP address somewhere in the internet have to be modified such that the sender's address is equal to the router's address. For further command examples let us assume that the first interface 'eth0' is connected to the local net and that the router is connected to the internet via the second interface 'eth1'. The command for a shared internet connection then simply is:

# Connect a LAN to the internet gt; 
iptables -t nat -A POSTROUTING -o eth1 -j MASQUERADE

#Lets say we have a user for which i have to set a bandwidth of  13 GB/month 
iptables -A INPUT -p tcp -s 192.168.0.2 -m quota --quota 13958643712 -j ACCEPT
iptables -A OUTPUT -p tcp --dport 80 -j DROP
or
iptables -A INPUT -p tcp -j CLASSIFY --set-class 1:12

So in the above rule will allow the user (192.168.0.2)  to user the internet service up to 13 GB

after a month you need to fire
iptables -F
 and run the above command again or even you can schedule a cron job for this .and we are all set the Shell script will look like this.


#!/bin/bash
 # step 1
iptables -F

# Step 2
# Connect a LAN to the internet gt; 
iptables -t nat -A POSTROUTING -o eth1 -j MASQUERADE

 #Step 3
#Lets say we have a user for which i have to set a bandwidth of  13 GB/month 
iptables -A INPUT -p tcp -s 192.168.0.2 -m quota --quota 13958643712 -j ACCEPT

 #Step 4
iptables -A OUTPUT -p tcp --dport 80 -j DROP
or
iptables -A INPUT -p tcp -j CLASSIFY --set-class 1:12
#
#choose one from step 4 as per your requirement
#
#Create a shell script name quota.sh

chmod 777 quota.sh

crontab -e

*        *        1        *        *       /bin/bash  /path to/quota.sh

then save the cron by following keys 'esc' then ': wq'

and we are ready to go.

Compailing a linux on debian system

There are many ways to do it in Debian you can compile from source or directly upgrade the operating system it will upgrade the kernel version automatically  but its the kernel binaries which are available with the archives . I will tell you to compile the kernel from the source and how to apply patch to the kernel.

Each distribution has some specific tools to build a custom kernel from the sources. This article is about compiling a kernel on a Debian Etch system. It describes how to build a custom kernel using the latest unmodified kernel sources from www.kernel.org  (vanilla kernel) so that you are independent from the kernels supplied by your distribution. It also shows how to patch the kernel sources if you need features that are not in there.

1 Preliminary Note

I will describe two ways of compiling a new kernel. Using the first method, you will end up with a kernel .deb package that can be installed on the system, and that you can share with others and install on other Debian Etch systems.

The second method is to compile a kernel the "traditional" way. This way works on any Linux distribution, but of course you don't end up with a kernel .deb package.

2 Building A Kernel .deb Package

This chapter shows how to build a kernel and end up with a .deb package that you can install and share with others.

2.1 Install Required Packages For Kernel Compilation

First we update our package database:

sudo apt-get update

Then we install all needed packages like this:

sudo apt-get install kernel-package libncurses5-dev fakeroot wget bzip2 build-essential

2.2 Download The Kernel Sources

Next we download our desired kernel to /usr/src. Go to www.kernel.org and select the kernel you want to install, e.g. linux-3.8.tar.bz2 (you can find all 2.6 kernels here: https://www.kernel.org/pub/linux/kernel/v3.x/). Then you can download it to /usr/src like this:

cd /usr/src
wget https://www.kernel.org/pub/linux/kernel/v3.x/linux-3.8.tar.bz2

Then we unpack the kernel sources and create a symlink linux to the kernel sources directory:

tar xjf linux-3.8.tar.bz2
ln -s linux-3.8.tar.bz2 linux
cd /usr/src/linux

2.3 Apply Patches To The Kernel Sources (Optional)

Sometimes you need drivers for hardware that isn't supported by the new kernel by default, or you need support for virtualization techniques or some other bleeding-edge technology that hasn't made it to the kernel yet. In all these cases you have to patch the kernel sources (provided there is a patch available...).

Now let's assume you have downloaded the needed patch (I call it patch.bz2 in this example) to /usr/src. This is how you apply it to your kernel sources (you must still be in the /usr/src/linux directory):

bzip2 -dc /usr/src/patch.bz2 | patch -p1 --dry-run
bzip2 -dc /usr/src/patch.bz2 | patch -p1

The first command is just a test, it does nothing to your sources. If it doesn't show errors, you can run the second command which actually applies the patch. Don't do it if the first command shows errors!

2.4 Configure The Kernel

It's a good idea to use the configuration of your current working kernel as a basis for your new kernel. Therefore we copy the existing configuration to /usr/src/linux:

make clean && make mrproper
cp /boot/config-`uname -r` ./.config

Then we run

make menuconfig

which brings up the kernel configuration menu. Go to Load an Alternate Configuration File and choose .config (which contains the configuration of your current working kernel) as the configuration file:

2.5 Build The Kernel

To build the kernel, execute these two commands:

make-kpkg clean
fakeroot make-kpkg --initrd --append-to-version=-custom kernel_image kernel_headers

After --append-to-version= you can write any string that helps you identify the kernel, but it must begin with a minus (-) and must not contain whitespace.

Now be patient, the kernel compilation can take some hours, depending on your kernel configuration and your processor speed.

2.6 Install The New Kernel

After the successful kernel build, you can find two .deb packages in the /usr/src directory.

cd /usr/src
ls -l

On my test system they were called linux-image-3.8.2.5-custom_3.8.2.5-custom-10.00.Custom_i386.deb (which contains the actual kernel) and linux-headers-3.8.2.5-custom_3.8.2.5-custom-10.00.Custom_i386.deb (which contains files needed if you want to compile additional kernel modules later on). I install them like this:

dpkg -i linux-image-3.8.2.5-custom_3.8.2.5-custom-10.00.Custom_i386.deb
dpkg -i linux-headers-3.8.2.5-custom_3.8.2.5-custom-10.00.Custom_i386.deb

(You can now even transfer the two .deb files to other Debian Etch systems and install them there exactly the same way, which means you don't have to compile the kernel there again.)
That's it. The GRUB bootloader configuration file /boot/grub/menu.lst has been modified automatically, and a ramdisk for the new kernel has been create in /boot.
Now reboot the system:

shutdown -r now

At the boot prompt, select your new kernel

If everything goes well, it should come up with the new kernel. You can check if it's really using your new kernel by running

uname -r