Re: [ADMIN] Setting up streaming replication w/ a big ole database
On Tue, Apr 10, 2012 at 12:34 PM, Wells Oliver wellsoli...@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. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Setting up streaming replication w/ a big ole database
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.marl...@gmail.comwrote: On Tue, Apr 10, 2012 at 12:34 PM, Wells Oliver wellsoli...@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 wellsoli...@gmail.com
Re: [ADMIN] Setting up streaming replication w/ a big ole database
Wells Oliver wellsoli...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Setting up streaming replication w/ a big ole database
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 wellsoli...@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.marl...@gmail.com wrote: On Tue, Apr 10, 2012 at 12:34 PM, Wells Oliver wellsoli...@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 wellsoli...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Setting up streaming replication w/ a big ole database
Kevin Grittner kevin.gritt...@wicourts.gov writes: Wells Oliver wellsoli...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
