<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial, helvetica, sans-serif;font-size:12pt"><DIV></DIV>
<DIV>
<DIV>> How many tables in the database?</DIV>
<DIV> 24<BR></DIV>
<DIV>> How big (in terms of rows) are the tables?</DIV>
<DIV> 8 tables have less than 10 rows</DIV>
<DIV> 2 tables have less than 1,000 rows</DIV>
<DIV> 5 tables have less than 10,000 rows</DIV>
<DIV> 4 tables have less than 50,000 rows</DIV>
<DIV> 4 tables have less than 1,000,000 rows</DIV>
<DIV> 1 table has just under 15,000,000 rows<BR></DIV>
<DIV>> How big (size in Mbytes) is the whole database?</DIV>
<DIV> Not sure exactly how to measure this. After installing <SPAN class=yshortcuts id=lw_1267045635_0 style="CURSOR: hand; BORDER-BOTTOM: #0066cc 1px dashed">PostgreSQL</SPAN> to a new machine, the 'data' directory is 32MB. I then restore my db from backup, the data directory is 1.31GB. However, the .backup file from which the db was restored is only 91MB.</DIV>
<DIV> </DIV>
<DIV>> What is the update rate (5 new records a second - or 5 new records a month)?</DIV>
<DIV> The table with the most rows (association table consisting of two integer fields per row) grows by up to 100K rows per day, but we publish up to 350 rows containing actual source code per day, sometimes up to a few KB each. These are added in spurts of publishing lasting up to a couple of minutes each, maybe 50 or so publishes per day. </DIV>
<DIV> </DIV>
<DIV>> How many "sites" need to get the updates? Is it only 1 laptop or 1,000 of them?<BR> Maybe up to a few dozen or so eventually.<BR><BR> </DIV></DIV>Dave Stevenson<BR>dave.stevenson@pacbell.net
<DIV><BR></DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial, helvetica, sans-serif"><BR>
<DIV style="FONT-SIZE: 10pt; FONT-FAMILY: arial, helvetica, sans-serif"><FONT face=Tahoma size=2>
<HR SIZE=1>
<B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Michael Squires <msquires@whitepages.com><BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> slony1-general@lists.slony.info<BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> Wed, February 24, 2010 10:26:50 AM<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B> Re: [Slony1-general] Is Slony for me?<BR></FONT><BR>Rather than nibbling around the edges on this ("slony would be good if <BR>your situation is X"), why don't you give us some sense of the scale you <BR>are talking about.<BR><BR>How many tables in the database?<BR>How big (in terms of rows) are the tables?<BR>How big (size in Mbytes) is the whole database?<BR>What is the update rate (5 new records a second - or 5 new records a month)?<BR>How many "sites" need to get the updates? Is it only 1 laptop or 1,000 <BR>of them?<BR><BR>The "best" potential solution for you might range from a single-file <BR>sqlite database that you
just copy to your laptop whenever you want to <BR>to a full slony with log-shipping to ....<BR><BR>Michael<BR><BR>On 2/24/10 10:19 AM, Kevin Kempter wrote:<BR>> On Wednesday 24 February 2010 11:13:19 Walter Coole wrote:<BR>>> Dave's points are valid, but I'd suggest a tweak that I've found useful:<BR>>> use one of pg_dump's ascii modes, keep the pg_dump output and use rsync<BR>>> to do the copy to the laptop. If the updates are rare and small, rsync<BR>>> (or a similar tool) will be much more efficient at doing the copy, at<BR>>> the cost of keeping an extra copy of the database on the laptop.<BR>>><BR>>><BR>>><BR>>> I found that, for my installation, this arrangement was easier to set up<BR>>> and maintain than Slony or any of the other replication schemes for<BR>>> Postgres.<BR>>><BR>>> Walter<BR>>><BR>>><BR>>><BR>>> From: <A
href="mailto:slony1-general-bounces@lists.slony.info" ymailto="mailto:slony1-general-bounces@lists.slony.info">slony1-general-bounces@lists.slony.info</A><BR>>> [mailto:<A href="mailto:slony1-general-bounces@lists.slony.info" ymailto="mailto:slony1-general-bounces@lists.slony.info">slony1-general-bounces@lists.slony.info</A>] On Behalf Of Melvin<BR>>> Davidson<BR>>> Sent: Wednesday, February 24, 2010 9:44 AM<BR>>> To: <A href="mailto:Slony1-general@lists.slony.info" ymailto="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</A>; Dave Stevenson<BR>>> Subject: Re: [Slony1-general] Is Slony for me?<BR>>><BR>>><BR>>><BR>>> --- On Wed, 2/24/10, Dave Stevenson<<A href="mailto:dave.stevenson@pacbell.net" ymailto="mailto:dave.stevenson@pacbell.net">dave.stevenson@pacbell.net</A>> wrote:<BR>>><BR>>> From: Dave Stevenson<<A
href="mailto:dave.stevenson@pacbell.net" ymailto="mailto:dave.stevenson@pacbell.net">dave.stevenson@pacbell.net</A>><BR>>> Subject: [Slony1-general] Is Slony for me?<BR>>> To: <A href="mailto:Slony1-general@lists.slony.info" ymailto="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</A><BR>>> Date: Wednesday, February 24, 2010, 11:28 AM<BR>>><BR>>> I'm looking for a replication solution, but I'm no DBA. My use case is<BR>>> probably similar to the following, listed in Slony's online doc as a<BR>>> poor match for Slony:<BR>>><BR>>> Replicating a pricing database from a central server to sales staff<BR>>> who connect periodically to grab updates.<BR>>><BR>>><BR>>><BR>>> I'm wondering what is it about Slony that makes that scenario a bad<BR>>> match, or what alternative might work better for
me.<BR>>><BR>>><BR>>><BR>>> I use Postgresql as a source code repository. However, the server is<BR>>> remote, and access is typically very slow. I'd like to have a read-only<BR>>> local copy for comparing, reconciling, loading, etc., and access the<BR>>> remote server only for writes (publish). Typical usage is lots of reads,<BR>>> few writes, very rare and limited updates, even more rare and limited<BR>>> deletes (few times per year?), and maybe once every 2-4 years a schema<BR>>> change.<BR>>><BR>>><BR>>><BR>>> If I have a local clone on my laptop, it will often be offline (at<BR>>> night). Is Slony a bad match because the slave would miss updates while<BR>>> offline?<BR>>><BR>>><BR>>><BR>>> Anyone know of a better fit for my use case?<BR>>><BR>>><BR>>><BR>>> Thanks,<BR>>><BR>>><BR>>> Dave
Stevenson<BR>>> <A href="mailto:dave.stevenson@pacbell.net" ymailto="mailto:dave.stevenson@pacbell.net">dave.stevenson@pacbell.net</A><BR>>><BR>>><BR>>> -----Inline Attachment Follows-----<BR>>><BR>>> _______________________________________________<BR>>> Slony1-general mailing list<BR>>> <A href="mailto:Slony1-general@lists.slony.info" ymailto="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</A><BR>>> <http://us.mc530.mail.yahoo.com/mc/compose?to=Slony1-general@lists.slony<BR>>> .info><BR>>> http://lists.slony.info/mailman/listinfo/slony1-general<BR>>><BR>>>> I'd like to have a read-only local copy for comparing, reconciling,<BR>>><BR>>> loading,<BR>>><BR>>>> etc.<BR>>><BR>>> Good news. Slony is excellent for that situation.<BR>>><BR>>>> If I have a local clone on my laptop, it will often be
offline (at<BR>>><BR>>> night). Is Slony a<BR>>><BR>>>> bad match because the slave would miss updates while offline?<BR>>><BR>>> Bad news. Unless there is only a small amount of updates (DML) that<BR>>> will be occurring while your laptop is offline, it will take a long time<BR>>> for slony to synchronize and catch up with transactions that occurred.<BR>>> If your database is small enough, you might be better off just doing a<BR>>> pg_dump of the master, and then copy it to the laptop, drop the old<BR>>> database, make a new db and then pg_restore the new copy.<BR>>><BR>>> Melvin Davidson<BR>>><BR>><BR>><BR>> This scenario is actually a perfect fit for SLONY log shipping which would<BR>> allow your laptop to act as a 'remote' or 'disconnected' subscriber thus<BR>> you'll be able to have your laptop apply the slon logs on any schedule
you<BR>> decide.<BR>><BR>><BR>> _______________________________________________<BR>> Slony1-general mailing list<BR>> <A href="mailto:Slony1-general@lists.slony.info" ymailto="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</A><BR>> <A href="http://lists.slony.info/mailman/listinfo/slony1-general" target=_blank>http://lists.slony.info/mailman/listinfo/slony1-general</A><BR>_______________________________________________<BR>Slony1-general mailing list<BR><A href="mailto:Slony1-general@lists.slony.info" ymailto="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</A><BR><A href="http://lists.slony.info/mailman/listinfo/slony1-general" target=_blank>http://lists.slony.info/mailman/listinfo/slony1-general</A><BR></DIV></DIV></div></body></html>