Postgresql replication book review

http://www.packtpub.com/postgresql-replication/book

Recently I got book about postgresql replication  for review .
This book is great.
It describes different replication methods available in postgres in very easy way.
Besides that, it describes how replication mechanisms work, what you should know about designing database and many other aspects related to databases.
The book contains examples which can be used to one's practice.
In this book there are usefull things not only for database administrator but also for regular user who wants to know better how it works. 

Read linux dmesg with date

Simple script to read dmesg from linux (contains real date no miliseconds)
----------------------------------------------------------------------------------------------------------
#!/usr/bin/python
import sys
import re
import time
import datetime
import fileinput
import os

try:
  uptime = open('/proc/uptime','r')
except IOError as (errno, strerror):
  print "Wystapil blad otwarcia pliku :\n %s\n" % (strerror)

czas = time.time()

uptime_seconds = czas-float(uptime.readline().split()[0])

p = os.popen('dmesg')

for line in p.readlines():

  re1='.*?'
  re2='(\\d+)'
  re3='(\\.)'
  re4='(\\d+)'

  rg = re.compile(re1+re2+re3+re4,re.IGNORECASE|re.DOTALL)
  m = rg.search(line)
  if m:
      int1=float(m.group(1)+m.group(2)+m.group(3))
      szukaj=(m.group(1)+m.group(2)+m.group(3))
      czas = uptime_seconds + int1
      czas = datetime.datetime.fromtimestamp(int(czas)).strftime('%Y-%m-%d %H:%M:%S')
   
      zwrot = re.sub(szukaj,czas,line.rstrip(),1)

      print zwrot

MongoDB aggregation

MongoDB aggregation examples: db.posts.aggregate({$unwind:"$comments"},{$group:{_id:"$comments.author",count:{$sum:1}}},{$sort:{count:1}}) db.zip.aggregate([{$match:{$or:[{state:"CA"},{state:"NY"}]}}, {$group:{_id:{city:"$city",state:"$state"}, pop:{$sum:"$pop"}}}, {$match:{pop:{$gt:25000}}}, {$group:{_id:"$state", average:{$avg:"$pop"}}}]) db.grades.aggregate({$unwind:"$scores"} , {$match:{"scores.type":{$nin:["quiz"]}}},{$group:{_id:{student_id:"$student_id",class_id:"$class_id"},avg:{$avg:"$scores.score"}}},{$group:{_id:"$_id.class_id",average:{$avg:"$avg"}}}) db.zip.aggregate( [ {$project : {city:{$substr:["$city",0,1]},pop:1}}, {$sort : {city : 1}}, {$match: {city: /^\d.*$/}} , {$group:{_id:1,pop:{$sum:"$pop"}}}])

Create "internal" VLAN for Xen Virtual Machines

1) Add the dummy interface driver to the auto-load modules:

echo dummy >> /etc/modules

2) Configure network interface  ( /etc/network/interfaces)

auto dummy0
iface dummy0 inet static
address 192.168.100.1
netmask 255.255.255.0

3) Bring up the dummy interface

ifup dummy0

4) Add line in : /etc/xen/xend-config.sxp

(network-script 'network-bridge netdev=dummy0')

5) Run the command:

/etc/xen/scripts/network-bridge start netdev=dummy0 antispoof=no

6) Check if pdummy interface is present:

ifconfig pdummy0

pdummy0   Link encap:Ethernet  HWaddr b6:a5:b4:35:a7:1a 
          inet6 addr: fe80::b4a5:b4ff:fe35:a71a/64 Scope:Link
          UP BROADCAST RUNNING NOARP PROMISC  MTU:1500  Metric:1
          RX packets:0 errors:0 dropped:0 overruns:0 frame:0
          TX packets:532 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:0 (0.0 B)  TX bytes:28928 (28.2 KiB)

8) Attach new interface to guest domain :

xm network-attach  example.domain.guest bridge=dummy0 script=vif-bridge mac=00:16:3E:40:22:12

9) Log in to guest domain console and add new interface:

root@debian-xen:/home/shamrock# xm console example.domain.guest


Debian GNU/Linux 6.0 debian hvc0

debian login: root
Password:

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
root@debian:~# ifconfig eth1 192.168.100.200 netmask 255.255.255.0
root@debian:~# ifconfig eth1
eth1      Link encap:Ethernet  HWaddr 00:16:3E:40:22:12 
          inet addr:192.168.100.200  Bcast:192.168.100.255  Mask:255.255.255.0
          inet6 addr: fe80::216:3eff:fe40:2212/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:10 errors:0 dropped:0 overruns:0 frame:0
          TX packets:7 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:608 (608.0 B)  TX bytes:510 (510.0 B)
          Interrupt:244

And add this to /etc/network/interfaces

auto eth1
iface eth1 inet static
 address 192.168.100.200
 gateway 192.168.100.1
 netmask 255.255.255.0

10) In Dom0 edit DomU config and add ew interface:

i.e.: /etc/xen/example.domain.guest

vif         = [ 'ip=212.85.34.12,mac=00:16:3E:0B:0A:83,bridge=eth0', 'ip=192.168.100.100,mac=00:16:3E:D7:9C:F6,bridge=dummy0' ]

11) Last thing is to set the ip_forwarding in the kernel of Dom0 and create NAT rules.

root@debian-xen:/# echo 1 >/proc/sys/net/ipv4/ip_forward
root@debian-xen:/# iptables -t nat -A POSTROUTING -s 192.168.100.0/24 -d 0.0.0.0/0 -j MASQUERADE

FortiMail - diagnozowanie tcpdump

Do diagnozowania problemów związanych z siecią na serwerach z unixem służą wszelkiego rodzaju narzędzia pokroju tcpdumpa czy wiresharka.
Także Forti posiada takie narzędzie na poziomie CLI.

Składnia polecenia:

diagnose sniffer packet {interface | all}  ‘net z.z.z.z/p and/or host x.x.x.x and/or port yyy’  [options]

W celu zawężenia poszukiwań można użyć 

net/prefix : bierze pod uwagę cały blok IP
host          : bierze pod uwagę tylko danego hosta
port          : wypisuje tylko połączenia dla danego portu
options     : dodatkowe opcje

 

Pole

Dodatkowe opcje podawane są na końcu w postaci cyfry:The Options field at the end are as follow:
1: wypisuje nagłówek pakietu
2: wypisuje nagłówek pakietu i dane zwrotne z IP
3: wypisuje nagłówek i dane z pakietu ethernetowego ( jeżęli jest dostępny)
4: wypisuje nagłówek z nazwą interface
5: wypisuje nagłówek i dane z IP wraz z nazwą interface
6: wypisuje nagłówek i dane z pakietu ethernetowego ( jeżęli jest dostępny) wraz z nazwą interface

diagnose sniffer packet any ‘net 192.168.1.0/24 and host 172.16.11.12 and port 3878′

Software raid

Today I connected SDD hard drive to my new server. Because it contains 4 memory drives I decided to use software raid to use it as one huge free space.

root@alucard:/home/shamrock# mdadm --create /dev/md0 \
-f -v -l 0 -n 4 /dev/dm-1 /dev/dm-2 /dev/dm-3 /dev/dm-4

mdadm: chunk size defaults to 512K
mdadm: /dev/sdb appears to be part of a raid array:
    level=raid0 devices=0 ctime=Thu Jan  1 00:00:00 1970
mdadm: partition table exists on /dev/sdb but will be lost or
       meaningless after creating array
mdadm: /dev/sdc appears to be part of a raid array:
    level=raid0 devices=0 ctime=Thu Jan  1 00:00:00 1970
mdadm: partition table exists on /dev/sdc but will be lost or
       meaningless after creating array
mdadm: /dev/sdd appears to be part of a raid array:
    level=raid0 devices=0 ctime=Thu Jan  1 00:00:00 1970
mdadm: partition table exists on /dev/sdd but will be lost or
       meaningless after creating array
mdadm: /dev/sde appears to be part of a raid array:
    level=raid0 devices=0 ctime=Thu Jan  1 00:00:00 1970
mdadm: partition table exists on /dev/sde but will be lost or
       meaningless after creating array
Continue creating array? y
mdadm: Defaulting to version 1.2 metadata
mdadm: array /dev/md0 started.
root@alucard:/home/shamrock# mkfs.xfs /dev/md0 
log stripe unit (524288 bytes) is too large (maximum is 256KiB)
log stripe unit adjusted to 32KiB
meta-data=/dev/md0               isize=256    agcount=32, agsize=9157376 blks
         =                       sectsz=512   attr=2, projid32bit=0
data     =                       bsize=4096   blocks=293036032, imaxpct=5
         =                       sunit=128    swidth=512 blks
naming   =version 2              bsize=4096   ascii-ci=0
log      =internal log           bsize=4096   blocks=143088, version=2
         =                       sectsz=512   sunit=8 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0


root@alucard:/home/shamrock# df -h
Filesystem            Size  Used Avail Use% Mounted on
rootfs                 12G  2.6G  9.5G  22% /
udev                   95G  132K   95G   1% /dev
tmpfs                  19G  2.3M   19G   1% /run
/dev/mapper/lvm0-dom0
                       12G  2.6G  9.5G  22% /
tmpfs                 5.0M     0  5.0M   0% /run/lock
tmpfs                  38G   10M   38G   1% /tmp
tmpfs                  38G  4.0K   38G   1% /run/shm
/dev/mapper/lvm0-base
                      2.6T  2.4T  238G  91% /base
/dev/md0              1.1T   34M  1.1T   1% /media/md0

How to obtain postgresql database size

If you want to find the size of a dataase , the simples method is use query : 

SELECT pg_database_size(‘databasename’);

postgres=# SELECT pg_database_size('aad');
 pg_database_size
------------------
       4582268508
(1 row)

For more human readable format please use:

SELECT pg_size_pretty(pg_database_size(‘databasename’));

postgres=# SELECT pg_size_pretty(pg_database_size('aad'));
 pg_size_pretty
----------------
 4370 MB
(1 row)

To get the size of all databases with alphabetical order:
 
SELECT datname,pg_size_pretty(pg_database_size(pg_database.datname)) FROM  pg_database ORDER BY datname;

postgres=# SELECT datname,pg_size_pretty(pg_database_size(pg_database.datname)) FROM  pg_database ORDER BY datname;
      datname      | pg_size_pretty
-------------------+----------------
 aad              | 4370 MB
 bae     | 4797 MB
 maad    | 534 MB
 pjvi            | 11 MB
 template0         | 4248 kB
 template1         | 4248 kB
 (6 rows)