Slony and local machine slave..(supernewbie question)

On Thu, Jul 30, 2009 at 7:47 AM, Phoenix Kiula <phoenix.kiula@gmail.com>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

On Thu, Jul 30, 2009 at 8:31 PM, Scott Mead<scott.lists@enterprisedb.com> wrote:

...snip...

  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

Thanks. This was useful. So much for my brainwave.

I certainly don't wish to lose the ALTER TABLE option if I go for
Slony method. Am I right to understand that the PITR setup is separate
from the whole Slony shebang, and that PITR stuff can be setup all
with just the main install of PG itself?

Thanks.

On Thu, Jul 30, 2009 at 9:36 AM, Phoenix Kiula <phoenix.kiula@gmail.com>wrote:

On Thu, Jul 30, 2009 at 8:31 PM, Scott Mead<scott.lists@enterprisedb.com>
wrote:

...snip...

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

Thanks. This was useful. So much for my brainwave.

I certainly don't wish to lose the ALTER TABLE option if I go for
Slony method. Am I right to understand that the PITR setup is separate
from the whole Slony shebang, and that PITR stuff can be setup all
with just the main install of PG itself?

Exactly right.

--Scott