Обсуждение: Setting up streaming replication w/ a big ole database
Hey all, I have a question about streaming replication available in PG 9+
The general configuration I see is along the lines of this:
Where you do the file system backup and restore. However, our current server I wish to make master has a 75GB database. I can't take the server down, and I can't run rsync against the live DB.
What are my options? I don't care if it takes a day or two for the slave to get up to sync w/ the master: is there some way to have an empty DB read an entire log that has everything?
I could also do a pg_restore to the new slave from last night's DB backup, but I understand (at least, I seem to read it) that you can't bring a slave up using pg_restore.
Are there any other options I am missing? Thanks for any tips.
On Tue, Apr 10, 2012 at 12:34 PM, Wells Oliver <wellsoliver@gmail.com> wrote: > Hey all, I have a question about streaming replication available in PG 9+ > > The general configuration I see is along the lines of this: > > http://brandonkonkle.com/blog/2010/oct/20/postgres-9-streaming-replication-and-django-balanc/ > > Where you do the file system backup and restore. However, our current server > I wish to make master has a 75GB database. I can't take the server down, and > I can't run rsync against the live DB. You don't need to take the master down. But you do need to run rsync. Note that rsync has a --bwlimit flag to reduce its impact on the master.
I admit to being scared as crap of rsync'ing a live database to another server. Like chills are running down my spine even typing it. Is this an approved, safe thing?
--
Wells Oliver
wellsoliver@gmail.com
On Tue, Apr 10, 2012 at 11:38 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Tue, Apr 10, 2012 at 12:34 PM, Wells Oliver <wellsoliver@gmail.com> wrote:You don't need to take the master down. But you do need to run rsync.
> Hey all, I have a question about streaming replication available in PG 9+
>
> The general configuration I see is along the lines of this:
>
> http://brandonkonkle.com/blog/2010/oct/20/postgres-9-streaming-replication-and-django-balanc/
>
> Where you do the file system backup and restore. However, our current server
> I wish to make master has a 75GB database. I can't take the server down, and
> I can't run rsync against the live DB.
Note that rsync has a --bwlimit flag to reduce its impact on the
master.
Wells Oliver
wellsoliver@gmail.com
Wells Oliver <wellsoliver@gmail.com> wrote: > I admit to being scared as crap of rsync'ing a live database to > another server. Like chills are running down my spine even typing > it. Is this an approved, safe thing? It is fine, as long as you're doing it between the pg_start_backup() and pg_stop_backup() calls. We do it on 100 production databases every week. It is approved, documented, and safe. -Kevin
I've definitely done this, albeit with a 6-7 GB database. I had accomplished previous backups with pg_dump commands, that invariably had to be restarted and took DAYS to complete. The rsync method achieved the backup within hours the first time, and you can do subsequent backups in minutes (in case something happens to the normal xlog copying - which for me has happened from time to time). Fear is the mind killer - let it rip dude! r.b. On Tue, Apr 10, 2012 at 2:43 PM, Wells Oliver <wellsoliver@gmail.com> wrote: > I admit to being scared as crap of rsync'ing a live database to another > server. Like chills are running down my spine even typing it. Is this an > approved, safe thing? > > > On Tue, Apr 10, 2012 at 11:38 AM, Scott Marlowe <scott.marlowe@gmail.com> > wrote: >> >> On Tue, Apr 10, 2012 at 12:34 PM, Wells Oliver <wellsoliver@gmail.com> >> wrote: >> > Hey all, I have a question about streaming replication available in PG >> > 9+ >> > >> > The general configuration I see is along the lines of this: >> > >> > >> > http://brandonkonkle.com/blog/2010/oct/20/postgres-9-streaming-replication-and-django-balanc/ >> > >> > Where you do the file system backup and restore. However, our current >> > server >> > I wish to make master has a 75GB database. I can't take the server down, >> > and >> > I can't run rsync against the live DB. >> >> You don't need to take the master down. But you do need to run rsync. >> Note that rsync has a --bwlimit flag to reduce its impact on the >> master. > > > > > -- > Wells Oliver > wellsoliver@gmail.com -- -- Robert W. Burgholzer http://www.findingfreestyle.com/ On Facebook - http://www.facebook.com/pages/Finding-Freestyle/151918511505970 Twitter - http://www.twitter.com/findfreestyle What's a tweeted swim set? A Sweet? No, a #swaiku! Get them by following http://twitter.com/findfreestyle
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Wells Oliver <wellsoliver@gmail.com> wrote:
>> I admit to being scared as crap of rsync'ing a live database to
>> another server. Like chills are running down my spine even typing
>> it. Is this an approved, safe thing?
> It is fine, as long as you're doing it between the pg_start_backup()
> and pg_stop_backup() calls. We do it on 100 production databases
> every week. It is approved, documented, and safe.
Right. It is true that the rsync alone will not give you a consistent
copy if the database is actively being modified. The secret sauce is
in having a copy of all the WAL that was generated while the rsync
happened. Replaying that against the copied data will apply (or
reapply) any changes that occurred meanwhile, thus fixing the
inconsistencies.
(I find that people are much more likely to believe that this works
once they grasp the principle.)
regards, tom lane