Thread: Syncing DBs prior to RServ replication
I'm looking at syncronizing two database servers with RServ (production / "hot standby"), and I'm trying to figure out a way to get the initial database on the slave server syncronized with the master. Normally, I would 1) turn off all clients, 2) dump the database from the master, 3) restore it into the slave, 4) turn clients back on, and 5) replicate regularly. Unfortunately I'm running the master in a production environment where anything more than 5 minutes of downtime is a really "Bad Thing®". The database dump is about 5G, and so leaving the master down for that entire time isn't possible. Is there a recommended way for performing such a "sync" without any lengthy downtime? I'm guessing I could start my dump, and then immediately afterward create the replication tables in the master. Since the dump is transactional (is it?), it shouldn't include the replication tables or the replication "snapshot" information in it's dump, and by the time I finally finish importing the dump in the new server, I should be able to perform the replication and pick up all the new / changed records since I started the DB dump. Will this work? Is there a better recommended way? Thanks. -- Michael A Nachbaur <mike@nachbaur.com>
"Michael A Nachbaur" <mike@nachbaur.com> wrote: > I'm looking at syncronizing two database servers with RServ (production / "hot > standby"), and I'm trying to figure out a way to get the initial database on > the slave server syncronized with the master. > > Normally, I would 1) turn off all clients, 2) dump the database from the > master, 3) restore it into the slave, 4) turn clients back on, and 5) > replicate regularly. > > Unfortunately I'm running the master in a production environment where > anything more than 5 minutes of downtime is a really "Bad Thing®". The > database dump is about 5G, and so leaving the master down for that entire > time isn't possible. Is there a recommended way for performing such a "sync" > without any lengthy downtime? > > I'm guessing I could start my dump, and then immediately afterward create the > replication tables in the master. Since the dump is transactional (is it?), > it shouldn't include the replication tables or the replication "snapshot" > information in it's dump, and by the time I finally finish importing the dump > in the new server, I should be able to perform the replication and pick up > all the new / changed records since I started the DB dump. > > Will this work? Is there a better recommended way? Thanks. I think you can completely solve your problem with 7.4 that should have the "point-in-time recovery" Regards Gaetano Mendola
I think you'd benefit from a two-stage Rsync (this was discussed on this
list in the past few months):
1. Rsync the $PGDATA cluster from the "hot" server to the "standby" server,
with the postmaster running
2. Stop the postmaster and perform the same rsync again (this will take only
a few seconds on how much time elapses between the completion of the first
stage and postmaster shutdown; 5G of data should take less than 5 minutes to
Rsync over if you're running Fast Ethernet or better).
Start the postmaster on both servers, and do your Rserv thing. (I don't use
Rserv, so I haven't tested this, but it sounds like you don't need the
servers to have identical states, so ...)
Cheers,
Murthy
>-----Original Message-----
>From: Michael A Nachbaur [mailto:mike@nachbaur.com]
>Sent: Monday, June 09, 2003 18:25
>To: pgsql-admin@postgresql.org
>Subject: [ADMIN] Syncing DBs prior to RServ replication
>
>
>I'm looking at syncronizing two database servers with RServ
>(production / "hot
>standby"), and I'm trying to figure out a way to get the
>initial database on
>the slave server syncronized with the master.
>
>Normally, I would 1) turn off all clients, 2) dump the
>database from the
>master, 3) restore it into the slave, 4) turn clients back on, and 5)
>replicate regularly.
>
>Unfortunately I'm running the master in a production environment where
>anything more than 5 minutes of downtime is a really "Bad
>Thing®". The
>database dump is about 5G, and so leaving the master down for
>that entire
>time isn't possible. Is there a recommended way for
>performing such a "sync"
>without any lengthy downtime?
>
>I'm guessing I could start my dump, and then immediately
>afterward create the
>replication tables in the master. Since the dump is
>transactional (is it?),
>it shouldn't include the replication tables or the replication
>"snapshot"
>information in it's dump, and by the time I finally finish
>importing the dump
>in the new server, I should be able to perform the replication
>and pick up
>all the new / changed records since I started the DB dump.
>
>Will this work? Is there a better recommended way? Thanks.
>
>--
>Michael A Nachbaur <mike@nachbaur.com>
>
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
On Mon, Jun 09, 2003 at 03:24:32PM -0700, Michael A Nachbaur wrote:
> I'm looking at syncronizing two database servers with RServ (production / "hot
> standby"), and I'm trying to figure out a way to get the initial database on
> the slave server syncronized with the master.
>
> Normally, I would 1) turn off all clients, 2) dump the database from the
> master, 3) restore it into the slave, 4) turn clients back on, and 5)
> replicate regularly.
>
> Unfortunately I'm running the master in a production environment where
> anything more than 5 minutes of downtime is a really "Bad Thing?". The
> database dump is about 5G, and so leaving the master down for that entire
> time isn't possible. Is there a recommended way for performing such a "sync"
> without any lengthy downtime?
This works-ish for eRServer, and should work for rserv too:
1. Set up replication on the master && pg_dump [master] | psql [slave]
2. Drop master bits on new slave and install slave bits
3. Start replicating.
Note that at (3) there is the risk of running into tuples on the
slave which are already there. Depending on your load, you may need
manually to remove those tuples in order to get the replication to
complete. (Solving that problem is one of the things the eRServer
version does, thanks to a patch from one of our developers some time
ago.)
> I'm guessing I could start my dump, and then immediately afterward create the
> replication tables in the master. Since the dump is transactional (is it?),
You might miss something this way.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Just a note to everyone. I finally managed to scrape enough time out of my tasklist (by staying late) so I could try this out. Besides some difficulty with having a RedHat 7.1 / PGSQL 7.2 box on one end and a Mandrake 9.1 / PGSQL 7.3 box on the other end, everything worked without a hitch. Thanks for the suggestion! On Wednesday 11 June 2003 07:51 am, Murthy Kambhampaty wrote: > I think you'd benefit from a two-stage Rsync (this was discussed on this > list in the past few months): > > 1. Rsync the $PGDATA cluster from the "hot" server to the "standby" server, > with the postmaster running > 2. Stop the postmaster and perform the same rsync again (this will take > only a few seconds on how much time elapses between the completion of the > first stage and postmaster shutdown; 5G of data should take less than 5 > minutes to Rsync over if you're running Fast Ethernet or better). > > Start the postmaster on both servers, and do your Rserv thing. (I don't use > Rserv, so I haven't tested this, but it sounds like you don't need the > servers to have identical states, so ...) > > Cheers, > Murthy > > >-----Original Message----- > > From: Michael A Nachbaur [mailto:mike@nachbaur.com] > > >Sent: Monday, June 09, 2003 18:25 > >To: pgsql-admin@postgresql.org > >Subject: [ADMIN] Syncing DBs prior to RServ replication > > > > > >I'm looking at syncronizing two database servers with RServ > >(production / "hot > >standby"), and I'm trying to figure out a way to get the > >initial database on > >the slave server syncronized with the master. > > > >Normally, I would 1) turn off all clients, 2) dump the > >database from the > >master, 3) restore it into the slave, 4) turn clients back on, and 5) > >replicate regularly. > > > >Unfortunately I'm running the master in a production environment where > >anything more than 5 minutes of downtime is a really "Bad > >Thing®". The > >database dump is about 5G, and so leaving the master down for > >that entire > >time isn't possible. Is there a recommended way for > >performing such a "sync" > >without any lengthy downtime? > > > >I'm guessing I could start my dump, and then immediately > >afterward create the > >replication tables in the master. Since the dump is > >transactional (is it?), > >it shouldn't include the replication tables or the replication > >"snapshot" > >information in it's dump, and by the time I finally finish > >importing the dump > >in the new server, I should be able to perform the replication > >and pick up > >all the new / changed records since I started the DB dump. > > > >Will this work? Is there a better recommended way? Thanks. > > > >-- > >Michael A Nachbaur <mike@nachbaur.com> > > > > > >---------------------------(end of > >broadcast)--------------------------- > >TIP 3: if posting/reading through Usenet, please send an appropriate > >subscribe-nomail command to majordomo@postgresql.org so that your > >message can get through to the mailing list cleanly > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Michael A Nachbaur <mike@nachbaur.com>