Postgresql: Table Replication Through Databases

SYNOPSYS

I want to replicate a few tables from one database to another.
I don’t need syncronised replication, hot swap or clustering capabilities.
I don’t want to add triggers to log events and then replay them periodicaly.
What I will do is :

  • create a new table, exact copy of the considered one including foreign constraints.
  • feed the new table from the distant one using dblink(…).
  • disable the foreign constraints referring the table.
  • swap the tables.
  • reactivate the foreign constraints.

You’ll find here an simpler solution to this.

PROCEDURE

show foreign constraints and build SQL to DROP/ADD them
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE VIEW v_constraints AS
SELECT
   c.conname AS constraint_name
  ,t.relname AS table_name
  ,ta.attname AS column_name
  ,ft.relname AS foreign_table_name
  ,fta.attname AS foreign_column_name
  , c.contype AS constraint_type
  ,c.confmatchtype AS match_type
  ,c.confupdtype AS on_update
  ,c.confdeltype AS on_delete
  ,format('ALTER TABLE ONLY %s DROP CONSTRAINT %s', t.relname, c.conname) AS constraint_drop
  ,format('ALTER TABLE %s ADD CONSTRAINT %s %s (%s) REFERENCES %s (%s) MATCH %s ON UPDATE %s ON DELETE %s', t.relname, c.conname
      ,(CASE c.contype WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' WHEN 't' THEN 'TRIGGER' WHEN 'x' THEN 'EXCLUSION' END)
      ,ta.attname, ft.relname, fta.attname
      ,(CASE c.confmatchtype WHEN 'f' THEN 'FULL' WHEN 'p' THEN 'PARTIAL' WHEN 'u' THEN 'SIMPLE' ELSE 'SIMPLE' END)
      ,(CASE c.confupdtype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END)
      ,(CASE c.confupdtype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END)
  ) AS constraint_add
FROM
  pg_constraint c
  LEFT JOIN pg_class t  ON (c.conrelid  = t.oid)
  LEFT JOIN pg_class ft ON (c.confrelid = ft.oid)
  LEFT JOIN pg_attribute ta ON (t.oid = ta.attrelid AND ta.attnum = ANY(c.conkey))
  LEFT JOIN pg_attribute fta ON (ft.oid = fta.attrelid AND fta.attnum = ANY(c.confkey))
;
create an empty table, duplicate of another, including foreign keys
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE FUNCTION create_table_like(source text, newtable text) RETURNS void AS $$
DECLARE
    _query text;
BEGIN
    EXECUTE format('create table %s (like %s including all)', newtable, source);
    for _query in SELECT format('alter table %s add constraint %s %s',
                                newtable,
                                replace(conname, source, newtable),
                                pg_get_constraintdef(oid))
                  FROM pg_constraint
                  WHERE contype = 'f' and conrelid = source::regclass
    LOOP
        EXECUTE _query;
    END LOOP;
END;
$$ LANGUAGE plpgsql;
view to access distant table using dblink(…)
1
2
3
4
5
6
7
CREATE VIEW v_sync_data AS
SELECT * FROM dblink('dbname=origin_db', 'select * from tbl_data')
AS t (
    f1  integer,
    f2  boolean,
    f3  text
);
function to do the dirty job
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE FUNCTION update_table_from(source text, dest text) RETURNS void AS $$
DECLARE
    cstr     RECORD;
BEGIN

    CREATE TEMP TABLE cstrs AS SELECT constraint_drop, constraint_add FROM v_constraints
    WHERE constraint_type='f' AND foreign_table_name=dest;

    DROP TABLE IF EXISTS new_table;
    EXECUTE format($x$select adm_create_table_like('%s', 'new_table')$x$, dest);
    EXECUTE format('insert into new_table (select * from %s)', source);

    FOR cstr IN SELECT * FROM cstrs LOOP
        EXECUTE cstr.constraint_drop;
    END LOOP;

    EXECUTE format('DROP TABLE IF EXISTS %s_old', dest);
    EXECUTE format('ALTER TABLE %s RENAME TO %s_old', dest, dest);
    EXECUTE format('ALTER TABLE new_table RENAME TO %s', dest);

    FOR cstr IN SELECT * FROM cstrs LOOP
        EXECUTE cstr.constraint_add;
    END LOOP;

    DROP TABLE cstrs;
END;
$$ LANGUAGE plpgsql;

ISSUES

  • when you use ‘ALTER TABLE foo RENAME TO bar’, views based on ‘foo’ will be updated too.
  • create_table_like() does not copy granted access, so they will be lost when switching the tables.

Login-notify

SYNOPSYS

I hope to know if someone logs into my server, or does a successful su, … lukcily pam has a pam_exec module

PROCEDURE

/etc/pam.d/common-session
1
2
...
session optional            pam_exec.so /usr/local/bin/notify-login
/usr/local/bin/notify-login
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#!/bin/sh
[ "$PAM_TYPE" = "open_session" ] || exit 0
WHITE_IPS="..."
RHOST_IP="${PAM_RHOST%%.*}"
for IP in $WHITE_IPS; do
    [ "x$IP" = "x$RHOST_IP" ] && exit 0
done
{
    echo "User: $PAM_USER"
    echo "Ruser: $PAM_RUSER"
    echo "Rhost: $PAM_RHOST"
    echo "Service: $PAM_SERVICE"
    echo "TTY: $PAM_TTY"
    echo "Date: `date`"
    echo "Server: `uname -a`"
} | mail -s "`hostname -s` $PAM_SERVICE login: $PAM_USER" root@localhost

Udev and Ethx

SYNOPSYS

If you have multiple network interfaces you may encounter index changing depending on driver initialisation order,
or maybe your network driver sets a weird interface names that is not really script friendly.
This shows you how to control your network interfaces naming with udev.

PROCEDURE

/etc/udev/rules.d/eth-names.rules
1
SUBSYSTEM=="net", ACTION=="add", ATTR{address}=="d4:be:d9:c9:81:4c", NAME="eth0"

Postfix and Virtuals

SYNOPSYS

I have used exim for a while, but wasn’t sure of what was going on, and to be honest, mail hassle is not … well (!)
So let’s give a try at postfix, coupled with dovecot and PostgreSQL

What I want is:

  • ssmtp service with per user password
  • imaps service with per user password
  • virtual domains
  • virtual uid per user

as usual this setup is made on a debian wheezy.

PROCEDURE

Start fist with dovecot and PostgreSQL
Design your DB as you wish and feed /etc/dovecot/dovecot-sql.conf as it should be.
What matters here is the use of peer authentication between dovecot and PostgreSQL using UNIX socket (that rocks).

/etc/postgresql/9.1/main/pg_hba.conf
1
2
# "local" is for Unix domain socket connections only
local  all  all peer
create a ‘secure’ user
1
adduser --system --no-create-home -uid XYZ secuser
/etc/dovecot/dovecot-sql.conf
1
2
3
4
5
6
# use unix:PostgreSQL
connect = host=/var/run/postgresql dbname=vmaildb
# SQL queries of your own
password_query = …
user_query = …

Setup your authentication services used by dovecot and postfix

/etc/dovecot/dovecot.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
auth default {
    mechanisms = login plain
    user = secuser
    userdb sql {
        args = /etc/dovecot/dovecot-sql.conf
    }
    passdb sql {
        args = /etc/dovecot/dovecot-sql.conf
    }
    socket listen {
        # contacted by /usr/lib/dovecot/deliver to know where the mailboxes are
        master {
            path = /var/run/dovecot/auth-master
            mode = 0600
            user = secuser
        }
        # contacted by postfix to know if a client can send an email (SASL)
        client {
            path = /var/spool/postfix/private/auth
            mode = 0600
            user = postfix
        }
    }
}
corresponding sockets
1
2
srw------- 1 secuser  root … /var/run/dovecot/auth-master
srw------- 1 postfix  root … /var/spool/postfix/private/auth

Explain to postfix how to use dovecot to deliver mails

postfix/master.cf
1
2
3
# Local Delivery Agent
 dovecot   unix  -       n       n       -       -       pipe
flags=DRhu user=secuser:dovecot argv=/usr/lib/dovecot/deliver -f ${sender} -d ${recipient}

multiple UID

To be able to deliver the mails with the virtual user UID,
2 options exists, setuid bit and sudo.
No sudo on servers for me if possible, but beware, if you update dovecot, your setuid bit will be lost forever.

deliver needs setuid to deliver to diffenet UID
1
2
3
chgrp dovecot /usr/lib/dovecot/deliver
chmod 04750 /usr/lib/dovecot/deliver
-rwsr-x--- 1 root dovecot … /usr/lib/dovecot/deliver

TO BE CONTINUED …

  • postfix basic setup
  • virtual aliases
  • drop wrong virtual destinations
update your postfix config and check it
1
2
3
4
5
6
7
postalias /etc/alaiases
postmap /etc/postfix/valiases
postmap -q "user@domain.fqdn" /etc/postfix/valiases
postfix reload
postconf -d (default)
mailq (-q)
postuser -d ALL deferred

Xkb Config

SYNOPSYS

I have a typematrix 2030 and has a fr_CH layout user, I choose the bépo layout.
But I have a few changes in mind, I WANT my own kbd definition.

RELATED

PROCEDURE

Find my patch.

/usr/share/X11/xkb/
1
2
3
4
5
6
compat          # translate certain key combinations into actions
geometry        # information on the physical layout
keycodes        # map a keyboard's scan codes onto useful symbolic keycodes
rules           # sets of rules that the end user will chose from to get it's config
symbols         # maps the symbolic key codes conto whatever symbols to produce
types           # information on the available levels for various keys and how to shift between the levels
/usr/share/X11/xkb/geometry/typematrix
1
2
3
4
5
6
7
8
9
10
11
xkb_geometry "tm2030USB-jeyzu" {
 include "typematrix(tm2030_shape)"
 include "typematrix(tm2030_MiscDiod_102)"
 include "typematrix(tm2030_MiscDiod_off)"

 description = "TypeMatrix EZ-Reach 2030 USB";

 include "typematrix(tm2030USB_func)"
 include "typematrix(tm2030USB_alpha)"
 include "typematrix(tm2030USB_ctrl)"
}; // geometry tm2030USB-jeyzu
/usr/share/X11/xkb/symbols/jp (fust for the flag)
1
2
3
4
5
6
7
8
9
10
11
partial alphanumeric_keys
 xkb_symbols "jeyzu" {
 include "fr(bepo)"
 name[Group1]="French (Bepo, ergonomic, Dvorak way, jeyzu)";
 // First row
 key <AE02> { type[group1] = "FOUR_LEVEL_SEMIALPHABETIC", [    less,      2,  guillemotleft,  leftdoublequotemark ] };
 key <AE03> { type[group1] = "FOUR_LEVEL_SEMIALPHABETIC", [ greater,      3, guillemotright, rightdoublequotemark ] };
 key <AE06> { type[group1] = "FOUR_LEVEL_SEMIALPHABETIC", [   equal,      6,       notequal                       ] };
 key <AE11> {                                             [      at, degree,    asciicircum,              minutes ] };
 key <SPCE> {                                             [   space, escape,     underscore,                U202F ] };
};
/usr/share/X11/xkb/rules/base
1
2
3
// TypeMatrix geometries
-! $tmgeometries = tm2020 tm2030PS2 tm2030USB tm2030USB-102 tm2030USB-106
+! $tmgeometries = tm2020 tm2030PS2 tm2030USB tm2030USB-jeyzu tm2030USB-102 tm2030USB-106
/usr/share/X11/xkb/rules/evdev
1
2
3
// TypeMatrix geometries
-! $tmgeometries = tm2020 tm2030PS2 tm2030USB tm2030USB-102 tm2030USB-106
+! $tmgeometries = tm2020 tm2030PS2 tm2030USB tm2030USB-jeyzu tm2030USB-102 tm2030USB-106
/usr/share/X11/xkb/rules/base.lst
1
2
+  tm2030USB-jeyzu TypeMatrix EZ-Reach 2030 USB (jeyzu)
+  jeyzu           jp: Japanese (Bepo)

Terminfo

SYNOPSYS

My beloved mail client mutt throws a ‘key not bound’ error at me when hit Backspace to scroll my message up a line.

UPDATES

  • 2013-11-13
    • I forgot to precise that I’m exclusively using terminology, and since this patch, that trick is no longer needed.

RELATED

PROCEDURE

what we want (sed or grep fun)
1
2
3
4
$ infocmp | grep -oE  'kbs=[^,]+'
kbs=\177
$ infocmp | sed -ne 's/.*\(kdch1=[^,]\+\).*/\1/p'
kdch1=\E[3~
how to fix
1
2
infocmp | sed 's/kbs=^H/kbs=\\177/' | sed 's/kdch1=\\177/kdch1=\\E[3~/' > terminfo
tic -x terminfo

Systemd 2 Networks

SYNOPSYS

eth0 is connected to a dhcp filtered LAN.
I want to use an open Wifi network to access the internet without impacting the LAN connection.

PROCEDURE

/etc/conf.d/open_wifi_wpa.conf
1
2
3
4
5
6
# generated with: wpa_passphrase openwrtSSID openwrt_secret
network={
    ssid="openwrtSSID"
    #psk="openwrt_secret"
    psk=85fb1efxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx02225dc
}
/etc/conf.d/open_wifi.conf
1
2
3
4
5
6
wpa_config=/etc/conf.d/open_wifi_wpa.conf
ethx=eth0
interface=wlan0
gateway=180.30.20.10
network=180.0.0.0
netmask=255.0.0.0
/usr/lib/systemd/system/open-wifi.service
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[Unit]
Description=OpenWrt Wifi
After=network.target
Wants=network.target

[Service]
Type=oneshot
RemainAfterExit=yes
EnvironmentFile=/etc/conf.d/open_wifi.conf
ExecStart=/usr/bin/ip route del default via ${gateway}
ExecStart=/usr/bin/ip route add ${network}/8 dev ${ethx}
ExecStart=/usr/bin/wpa_supplicant -Dwext -i ${interface} -c ${wpa_config} -B
ExecStart=/usr/bin/dhcpcd -q -C resolv.conf -C mtu ${interface}
ExecStop=/usr/bin/ip link set ${interface} down
ExecStop=/usr/bin/ip route add default via ${gateway}
ExecStop=/usr/bin/ip ro
ExecStop=/usr/bin/ip addr flush dev ${interface

[Install]
WantedBy=multi-user.target
original route -n
1
2
3
4
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
0.0.0.0         180.30.20.10    0.0.0.0         UG    0      0        0 eth0
180.30.20.0     0.0.0.0         255.255.255.0   U     202    0        0 eth0
route -n after systemctl start open-wifi.service
1
2
3
4
5
6
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
0.0.0.0         192.168.2.1     0.0.0.0         UG    303    0        0 wlan0
180.0.0.0       0.0.0.0         255.0.0.0       U     0      0        0 eth0
180.30.20.0     0.0.0.0         255.255.255.0   U     202    0        0 eth0
192.168.2.0     0.0.0.0         255.255.255.0   U     303    0        0 wlan0

Postgresql: Fix Serial Sequences

SYNOPSYS

When tickling your PostgreSQL with pgadmin you are more likely going to once broke your serial sequences.
This trick allows you to detect the messed sequences and correct them.

PROCEDURE

show sequences
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- psql -Atq -f show_sequences.sql -o tmp.sql && psql -Atq -f tmp.sql
SELECT
  'SELECT ' ||quote_literal(S.relname)|| ' as seq_name' ||
  ', (select last_value from ' ||S.relname|| ') as seq_val' ||
  ', (select MAX('||quote_ident(C.attname)|| ') FROM ' ||T.relname|| ') as max_id;'

FROM
  pg_class AS S
  ,pg_depend AS D
  ,pg_class AS T
  ,pg_attribute AS C
WHERE
  S.relkind = 'S'
  AND S.oid = D.objid
  AND D.refobjid = T.oid
  AND D.refobjid = C.attrelid
  AND D.refobjsubid = C.attnum
ORDER BY S.relname;
fix sequences
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- psql -Atq -f fix_sequences.sql -o tmp.sql && psql -Atq -f tmp.sql
SELECT
  'SELECT SETVAL(' ||quote_literal(S.relname)||
  ', MAX(' ||quote_ident(C.attname)||
  ') ) FROM ' ||quote_ident(T.relname)|| ';'
FROM
  pg_class AS S
  ,pg_depend AS D
  ,pg_class AS T
  ,pg_attribute AS C
WHERE
  S.relkind = 'S'
  AND S.oid = D.objid
  AND D.refobjid = T.oid
  AND D.refobjid = C.attrelid
  AND D.refobjsubid = C.attnum
ORDER BY S.relname;

Cgit

SYNOPSYS

I do love cgit. Since this the old repo seems less usefull, This setup uses nginx as a frontend to lighttpd,

PROCEDURE

  • clone cgit
1
user@srv$ git clone http://git.zx2c4.com/cgit
  • build cgit
1
user@srv$ make get-git cgit
  • install cgit (edit Makefile)
1
2
3
4
...
CGIT_SCRIPT_PATH = /var/www/cgi-bin
CGIT_DATA_PATH = /var/www/public/cgit
...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
...
# Specify some default clone prefixes
clone-prefix=git@srv:

# Use filters
source-filter=/usr/lib/cgit/filters/syntax-highlighting.sh
commiv-filter=/usr/lib/cgit/filters/commit-links.sh

# Build lovely urls
remove-suffix=1
virtual-root=/cgit

# Use gitolite projects list
project-list=/home/git/projects.list
scan-path=/home/git/repositories
enable-gitweb-owner=1
...
  • lighttpd
1
2
3
4
server.modules += ( "mod_cgi" )
$HTTP["url"] =~ "^/cgi-bin/" {
    cgi.assign = ( "" => "" )
}
  • nginx
1
2
3
4
location ~ ^/cgit {
    rewrite ^/cgit(.*) /cgi-bin/cgit$1 break;
    proxy_pass http://IP:port;
}
git

Gitdaemon

SYNOPSYS

When gitolite (see this post) is installed, time to open the gates of knowledge.

PROCEDURE

/etc/init.d/git-daemon

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
#!/bin/sh
### BEGIN INIT INFO
# Provides:          git-daemon
# Required-Start:    $remote_fs $syslog
# Required-Stop:     $remote_fs $syslog
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Git repo server daemon
### END INIT INFO
# /etc/init.d/git-daemon
#

set -e

. /lib/lsb/init-functions

DAEMON=/usr/lib/git-core/git-daemon
NAME=git-daemon
PATH=/sbin:/bin:/usr/sbin:/usr/bin
PID_FILE="/var/run/git-daemon.pid"
DAEMONOPTS="--detach --syslog --pid-file=$PID_FILE --user=git --base-path=/home/git/repositories --export-all"

test -x $DAEMON || exit 0

case "$1" in
  start)
    echo -n "Starting $NAME server"
    start-stop-daemon --start --background -m --pidfile $PID_FILE --exec $DAEMON -- $DAEMONOPTS
    echo "."
    ;;
  stop)
    echo -n "Stopping $NAME server"
    start-stop-daemon --stop --pidfile $PID_FILE --oknodo --exec $DAEMON
    rm -f $PID_FILE
    echo "."
    ;;
  status)
    status_of_proc -p $PID_FILE $DAEMON $NAME && exit 0 || exit $?
    ;;
  *)
    echo "Usage: /etc/init.d/$NAME {start|stop|status}"
    exit 1
    ;;
esac

exit 0
git