Обсуждение: Replicating PostgreSQL DB to lightweight system
<div class="bgcolor" style="background-color: ;"><div><div>Hi,</div><div> </div><div>like to know if I can replicate a PGdb 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, accessingthem thru' the small-footprint db system. Would be glad to hear if there are examples of existing commercial/OSSproducts to serve the purpose. Appreciate any advice. Thanks.</div><div> </div><div>Regards</div><div>pgdb</div></div></div><div>________________________________</div><div>Free POP3Email 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>
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/
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@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 > Skype: davidfetter Remember to vote!
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!!
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
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.
Вложения
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 if there > are examples of existing commercial/OSS products to serve the > purpose. Appreciate any advice. Thanks. -- Adrian Klaver aklaver@comcast.net