Обсуждение: Slony and local machine slave..(supernewbie question)
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. Locally, we can then use the server for testing against almost the real DB (including testing future versions of PG), then when we need to upgrade on the server, use this as a master and live one as a slave, etc? Also serves as a nice backup. 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". Thanks for any thoughts.
On Thu, Jul 30, 2009 at 7:47 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
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.
Remember, slave is read-ony
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.
Well, it depends. Remember, if someone logs in and types 'Delete from table;' your 'delete' will be replicated across the link.
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
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:
Exactly right.
--Scott
On Thu, Jul 30, 2009 at 8:31 PM, Scott Mead<scott.lists@enterprisedb.com> wrote:
...snip...Thanks. This was useful. So much for my brainwave.
> 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
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