pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSetting up streaming replication w/ a big ole database
6+ messages / 5 participants
[nested] [flat]
* Setting up streaming replication w/ a big ole database
@ 2012-04-10 18:34 Wells Oliver <wellsoliver@gmail.com>
2012-04-10 18:38 ` Re: Setting up streaming replication w/ a big ole database Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Wells Oliver @ 2012-04-10 18:34 UTC (permalink / raw)
To: pgsql-admin
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.
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.
--
Wells Oliver
wellsoliver@gmail.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Setting up streaming replication w/ a big ole database
2012-04-10 18:34 Setting up streaming replication w/ a big ole database Wells Oliver <wellsoliver@gmail.com>
@ 2012-04-10 18:38 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-04-10 18:43 ` Re: Setting up streaming replication w/ a big ole database Wells Oliver <wellsoliver@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2012-04-10 18:38 UTC (permalink / raw)
To: Wells Oliver <wellsoliver@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Setting up streaming replication w/ a big ole database
2012-04-10 18:34 Setting up streaming replication w/ a big ole database Wells Oliver <wellsoliver@gmail.com>
2012-04-10 18:38 ` Re: Setting up streaming replication w/ a big ole database Scott Marlowe <scott.marlowe@gmail.com>
@ 2012-04-10 18:43 ` Wells Oliver <wellsoliver@gmail.com>
2012-04-10 18:49 ` Re: Setting up streaming replication w/ a big ole database Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-04-10 18:56 ` Re: Setting up streaming replication w/ a big ole database Robert Burgholzer <rburghol@vt.edu>
0 siblings, 2 replies; 6+ messages in thread
From: Wells Oliver @ 2012-04-10 18:43 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Setting up streaming replication w/ a big ole database
2012-04-10 18:34 Setting up streaming replication w/ a big ole database Wells Oliver <wellsoliver@gmail.com>
2012-04-10 18:38 ` Re: Setting up streaming replication w/ a big ole database Scott Marlowe <scott.marlowe@gmail.com>
2012-04-10 18:43 ` Re: Setting up streaming replication w/ a big ole database Wells Oliver <wellsoliver@gmail.com>
@ 2012-04-10 18:49 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-04-10 19:11 ` Re: Setting up streaming replication w/ a big ole database Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2012-04-10 18:49 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; Wells Oliver <wellsoliver@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Setting up streaming replication w/ a big ole database
2012-04-10 18:34 Setting up streaming replication w/ a big ole database Wells Oliver <wellsoliver@gmail.com>
2012-04-10 18:38 ` Re: Setting up streaming replication w/ a big ole database Scott Marlowe <scott.marlowe@gmail.com>
2012-04-10 18:43 ` Re: Setting up streaming replication w/ a big ole database Wells Oliver <wellsoliver@gmail.com>
2012-04-10 18:49 ` Re: Setting up streaming replication w/ a big ole database Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2012-04-10 19:11 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2012-04-10 19:11 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Wells Oliver <wellsoliver@gmail.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Setting up streaming replication w/ a big ole database
2012-04-10 18:34 Setting up streaming replication w/ a big ole database Wells Oliver <wellsoliver@gmail.com>
2012-04-10 18:38 ` Re: Setting up streaming replication w/ a big ole database Scott Marlowe <scott.marlowe@gmail.com>
2012-04-10 18:43 ` Re: Setting up streaming replication w/ a big ole database Wells Oliver <wellsoliver@gmail.com>
@ 2012-04-10 18:56 ` Robert Burgholzer <rburghol@vt.edu>
1 sibling, 0 replies; 6+ messages in thread
From: Robert Burgholzer @ 2012-04-10 18:56 UTC (permalink / raw)
To: Wells Oliver <wellsoliver@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-04-10 19:11 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-04-10 18:34 Setting up streaming replication w/ a big ole database Wells Oliver <wellsoliver@gmail.com>
2012-04-10 18:38 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-04-10 18:43 ` Wells Oliver <wellsoliver@gmail.com>
2012-04-10 18:49 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-04-10 19:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-04-10 18:56 ` Robert Burgholzer <rburghol@vt.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox