Re: [GENERAL] Slony and local machine slave..(supernewbie question)

On Thu, Jul 30, 2009 at 7:47 AM, Phoenix Kiula <phoenix.kiula@xxxxxxxxx> wrote:

I use a Mac OSX at work. And finally have a running PG install.

So I'm thinking: can I use some mechanism to have my local PG server
(in our premises) as a slave mirror of the main live website server
(at our data center). 

Would Slony be the solution to look at? Is this a dumb thought to
begin with or can this be done with no to minimal loss of the live DB,
by just using some small part of the sys resources on the live server
to keep sending some updates back to our local server.

    You could use slony for this, but:

    *) You have to install triggers on your production server
           (read: performance hit on the master usually ~ 5 - 20% (depending on your db))

    *) Your slave server will be read-only, so if you only want it for reporting, that should be okay

    *) You can't run 'alter table' commands anymore on the database, you'll need to push them through the slonik engine:
             http://www.slony.info/documentation/ddlchanges.html
   
If I were you, I would want to look at how much throughput your production database is getting, how much your network is currently utilized and what all of this will do to those numbers.

Slony is a great way to replicate data, especially when a geographic separation is involved.
 

Locally, we can then use the server for testing against almost the
real DB (including testing future versions of PG),

    Remember, slave is read-ony
 

then when we need
to upgrade on the server, use this as a master and live one as a
slave, etc?

  
   Well, you could do that, but it would probably make more sense to do the upgrade within your data-center instead of having the temporary master off site.

 

Also serves as a nice backup.

   Well, it depends.  Remember, if someone logs in and types 'Delete from table;' your 'delete' will be replicated across the link.

 

Or am I dreaming?

The reason I feel this may be possible, presuming Slony allows
something like this, is that I have 24/7 broadband connectivity at
about 100 mbps. And I hardly ever shutdown my OSX, just shut down the
monitor or let the system "sleep".

  Well, be careful, putting the system into 'sleep' mode will make it unresponsive if the disks go offline.  This means that all the changes will be getting queued up on the master server until you wake up your OSx box, then you'll get a flood of changes (invovles system resources on both sides).


   If I were you, I wouldn't offer to use my workstation for this.  If it does work, you'll end up with every developer / gunslinger in the office shootin' to get time on your machine.
 

  It may make more sense to setup a dedicated PITR slave in your office, and refresh it every now and then.

http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html

--Scott