Re: Replicating PostgreSQL DB to lightweight system

Lists: pgsql-general
From: "pgdb" <pgdb(at)gawab(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Replicating PostgreSQL DB to lightweight system
Date: 2006-05-03 02:31:19
Message-ID: 20060503023119.1529.qmail@info6.gawab.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general


<HTML><DIV class=bgcolor STYLE="background-color: ;"><DIV>
<DIV>Hi,</DIV>
<DIV>&nbsp;</DIV>
<DIV>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&nbsp;if there
are examples of existing commercial/OSS products to serve the
purpose. Appreciate any advice. Thanks.</DIV>
<DIV>&nbsp;</DIV>
<DIV>Regards</DIV>
<DIV>pgdb</DIV></DIV></DIV></HTML>
<DIV>________________________________</DIV>
<DIV>Free POP3 Email from <a href="http://www.gawab.com"
target="_blank">www.gawab.com</a></DIV>
<DIV>Sign up NOW and get your account @gawab.com!!</DIV>

Attachment Content-Type Size
unknown_filename text/html 834 bytes
From: Chris <dmagick(at)gmail(dot)com>
To: pgdb <pgdb(at)gawab(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Replicating PostgreSQL DB to lightweight system
Date: 2006-05-03 02:58:31
Message-ID: 44581C57.7010501@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

pgdb wrote:
> Hi,
>
> 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.

I doubt you'll find any tools to replicate between database systems..
however using pg_dump as a starting point you might be able to create
your own sqlite database and then replicate/copy that around.

You might have issues with date/time fields (don't know how sqlite
handles these) amongst other things, so you might need a script of some
sort to do some conversions.

--
Postgresql & php tutorials
http://www.designmagick.com/

From: David Fetter <david(at)fetter(dot)org>
To: pgdb <pgdb(at)gawab(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Replicating PostgreSQL DB to lightweight system
Date: 2006-05-03 04:00:48
Message-ID: 20060503040048.GA24925@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

On Wed, May 03, 2006 at 02:31:19AM +0000, pgdb wrote:
> Hi,

> 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.

If they're big enough, you could use Slony and PostgreSQL on the
devices, which has the nice property of disallowing any attempts at
writing.

> Would be glad to hear if there are examples of existing
> commercial/OSS products to serve the purpose.

If you're not using PostgreSQL for the smaller systems, you might be
able to rig something up with pg_dump using the --inserts option. The
tough part will probably be getting the schema translated into SQLite
(or whatever system you choose) idiom.

You might also consider DBI-Link, but I'm pretty sure that's not a fit
for this case.

> Appreciate any advice. Thanks.

In future, please post in plain text, as many of us have trouble
reading HTML ;)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
> Skype: davidfetter

Remember to vote!

From: "pgdb" <pgdb(at)gawab(dot)com>
To: David Fetter <david(at)fetter(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Replicating PostgreSQL DB to lightweight system
Date: 2006-05-03 05:11:11
Message-ID: 20060503051111.22551.qmail@info6.gawab.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general


David Fetter writes:
David Fetter writes:
> On Wed, May 03, 2006 at 02:31:19AM +0000, pgdb wrote:
>> Hi,
>
>> 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.
>
>
> If they're big enough, you could use Slony and PostgreSQL on the
> devices, which has the nice property of disallowing any attempts at
> writing.

Agreed. I have been using Slony, it's definitely feasible with
all-PGSQL.

>
>> Would be glad to hear if there are examples of existing
>> commercial/OSS products to serve the purpose.
>
> If you're not using PostgreSQL for the smaller systems, you might be
> able to rig something up with pg_dump using the --inserts option. The
> tough part will probably be getting the schema translated into SQLite
> (or whatever system you choose) idiom.
>
> You might also consider DBI-Link, but I'm pretty sure that's not a fit
> for this case.

Of course you're sure, you developed it :)

>
>> Appreciate any advice. Thanks.
>
> In future, please post in plain text, as many of us have trouble
> reading HTML ;)

Apologies to all :) It's not the first time, guess the problem
could be with Gawab. I will subscribe with another provider if
there's no solution.

>
> Cheers,
> D
> --
> David Fetter http://fetter.org/
> phone: +1 415 235 3778 AIM: dfetter666
>> Skype: davidfetter
>
> Remember to vote!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
---------------------------------------------
Free POP3 Email from www.Gawab.com
Sign up NOW and get your account @gawab.com!!

From: "Leif B(dot) Kristensen" <leif(at)solumslekt(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Replicating PostgreSQL DB to lightweight system
Date: 2006-05-03 06:25:34
Message-ID: 200605030825.34725.leif@solumslekt.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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

From: Vivek Khera <vivek(at)khera(dot)org>
To: Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Re: Replicating PostgreSQL DB to lightweight system
Date: 2006-05-03 14:09:32
Message-ID: F004D99D-2CD5-4539-9842-91BF88C5EFDE@khera.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general


On May 3, 2006, at 12:00 AM, David Fetter wrote:

>> Would be glad to hear if there are examples of existing
>> commercial/OSS products to serve the purpose.
>
> If you're not using PostgreSQL for the smaller systems, you might be
> able to rig something up with pg_dump using the --inserts option. The
> tough part will probably be getting the schema translated into SQLite
> (or whatever system you choose) idiom.

What I'd do is use pg_dump --data-only generating insert statements
and have pre-defined sqlite schema that is compatible with the dump
file. Then just load the data that way and distribute the new sqlite
file to your clients.

It should be scriptable within a half day or so, if that much.

From: Adrian Klaver <aklaver(at)comcast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Cc: "pgdb" <pgdb(at)gawab(dot)com>
Subject: Re: Replicating PostgreSQL DB to lightweight system
Date: 2006-05-03 14:37:02
Message-ID: 200605030737.02208.aklaver@comcast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

You might want to look at Knoda (www.knoda.org). In particular the following
link-
http://hk-classes.sourceforge.net/tutorials/knodascriptingtutorial/bk01ch05s12.html
On Tuesday 02 May 2006 07:31 pm, pgdb wrote:
>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&nbsp;if there
> are examples of existing commercial/OSS products to serve the
> purpose. Appreciate any advice. Thanks.

--
Adrian Klaver
aklaver(at)comcast(dot)net