Re: Replicating PostgreSQL DB to lightweight system

On Wednesday 03 May 2006 04:31, pgdb wrote:

>I like to know if I can replicate a PG db to another
>lightweight platform like SQLite? The objective is to "push"
>updates to remote devices(laptops, PDAs etc) from a central
>server, so that the data can be used offline. These devices are
>not expected to modify any data, accessing them thru' the
>small-footprint db system. Would be glad to hear if there
>are examples of existing commercial/OSS products to serve the
>purpose. Appreciate any advice. Thanks.

Please don't post in HTML. In order to reply to this, I had to trim off
a lot of coding. I didn't even notice your msg the first time around as
HTML mail in plain text format is mostly unreadable.

I had essentially the same problem as my production database is in
PostgreSQL, and my Web presentation database is in MySQL. Additionally,
for historical as well as for performance reasons, the presentation db
has a flatter and more denormalized table structure. I wrote my own
dump routine in Python using the psycopg module. The denormalization is
mainly accomplished by reading from special views. Most of it was
written in a couple of hours, and it's working excellently.

Here's an abbreviated version, writing only one table, which should give
you a general idea:

#! /usr/bin/env python
# ss_dump.py - leifbk 2005
# dumps pgslekt to mysql tables for solumslekt.org

import psycopg
from time import strptime, strftime
from re import sub
import sys, os
sys.setappdefaultencoding('utf-8')

connection = psycopg.connect("dbname=pgslekt")
sql = connection.cursor()

def enc(s):
    return s.encode('latin-1')

def add_slashes(s):
    return sub('''(['"])''', r'\\\1', s)

def gender_convert(x):
    if x == 1:
        return 'M'
    elif x == 2:
        return 'F'
    else:
        return '?'

def bool_convert(x):
    if x:
        return 'T'
    else:
        return 'F'

def is_public(p):
    sql.execute("SELECT is_public(%s)" % p)
    return sql.fetchone()[0]

#      *** persons ***

ss_persons_ddl = """
-- create table ss_persons

DROP TABLE IF EXISTS ss_persons;
CREATE TABLE ss_persons (
    person_id MEDIUMINT UNSIGNED NOT NULL,
    father_id MEDIUMINT UNSIGNED NOT NULL DEFAULT '0',
    mother_id MEDIUMINT UNSIGNED NOT NULL DEFAULT '0',
    last_edit DATE NOT NULL DEFAULT '0000-00-00',
    pbd CHAR(18) NOT NULL DEFAULT '000000003000000001',
    pdd CHAR(18) NOT NULL DEFAULT '000000003000000001',
    gender ENUM('?','M','F') NOT NULL DEFAULT '?',
    living ENUM('?','Y','N') NOT NULL DEFAULT '?',
    PRIMARY KEY(person_id)
);

"""

print "Writing ss_persons.sql ..."
output = open('ss_persons.sql', 'w')
output.write(ss_persons_ddl)
sql.execute("SELECT * FROM tmg_persons WHERE is_public(person_id) ORDER
BY person_id")
result=sql.fetchall()

for row in result:
    person_id = int(row[0])
    if is_public(int(row[1])):
        father_id = int(row[1])
    else:
        father_id = 0
    if is_public(int(row[2])):
        mother_id = int(row[2])
    else:
        mother_id = 0
    last_edit = str(row[3])[:10]
    pbd       = row[4]
    pdd       = row[5]
    gender    = gender_convert(row[6])
    living    = row[7]
    line = "insert into ss_persons values (%d,%d,
%d,'%s','%s','%s','%s','%s');\n" % \
        (person_id, father_id, mother_id, last_edit, pbd, pdd, gender,
living)
    output.write(line)
output.close()

--
Leif Biberg Kristensen :: Registered Linux User #338009
http://solumslekt.org/ :: Cruising with Gentoo/KDE