Обсуждение: 8.2 "real-time" recovery
Hello, I have this scenario: 1. 8.2.3 production server (32 bit) with WAL archive active 2. 8.2.17 backup server (32bit running on 64bit) that should keep updating hourly the db cluster based on WAL files received from production server The problem is that after the first recovery the backup server has a new timeline (2 instead of 1), so future recovery doesn't work as the WAL files from the production server still have timeline id no 1. What should I do? 1. modify postgresql in order to bypass creating a new timeline id after each recovery, thus allowing me to start - recover - stop hourly 2. do something else (but what)? I modified src/bin/pg_resetxlog/pg_resetxlog.c in order to save (-l 0x1,0xYY,0xZZ) the timeline but pg_control is invalid as there is no checkpoint valid: LOG: invalid primary checkpoint record LOG: invalid secondary checkpoint link in control file PANIC: could not locate a valid checkpoint record Should I try to create a valid checkpoint in pg_resetxlog.c? (how to do that?) Thank you -- Best regards, Dragos Moinescu
Dragos Valentin Moinescu <dragos.moinescu@gmail.com> wrote: > 8.2.3 production server http://www.postgresql.org/support/versioning http://www.postgresql.org/docs/8.2/static/release.html > backup server ... that should keep updating hourly the db cluster > based on WAL files received from production server http://www.postgresql.org/docs/8.2/interactive/warm-standby.html Starting with the 9.0 release (which is expected to be out next month) you will be able to stream WAL data in near real-time and run read-only queries on the standby. -Kevin
Thanx for your reply. Still, we are talking about 80G databases which are heavily used. I cannot wait for 9.0 (which is still in beta). I really need a solution ASAP. Where can I find information about how to modify the postgresql server in order to allow this in 8.2.17? Thank you On Wed, Aug 25, 2010 at 4:58 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Dragos Valentin Moinescu <dragos.moinescu@gmail.com> wrote: > >> 8.2.3 production server > > http://www.postgresql.org/support/versioning > http://www.postgresql.org/docs/8.2/static/release.html > >> backup server ... that should keep updating hourly the db cluster >> based on WAL files received from production server > > http://www.postgresql.org/docs/8.2/interactive/warm-standby.html > > Starting with the 9.0 release (which is expected to be out next > month) you will be able to stream WAL data in near real-time and run > read-only queries on the standby. > > -Kevin > -- Best regards, Dragos Moinescu
Dragos Valentin Moinescu <dragos.moinescu@gmail.com> wrote: > Where can I find information about how to modify the postgresql > server in order to allow this in 8.2.17? You haven't exactly said what "this" is, but I am inferring that you want to bring your standby up to run read-only queries once per hour, and then resume replication? If so, you could either consider one of the many replication solutions: http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling or you could follow your current practice, but get a fresh base backup each time, and start the warm standby over again. If you use rsync with a daemon you will probably be surprised how quickly you can acquire the new base backup. -Kevin
I used rsync to create a base backup. Though I have 10 huge tables (min 1GB each) that are modified several times a second, thus creating a base backup hourly means I have to sync arround 10G each time (which is pretty time consuming). I do not want to use the standby server. This is why I am pretty happy with stop - start + recover. I cannot base backup anymore :( Is there any other possibility? I cannot use slony or buccardo as I don't control the number of databases or tables. I think the best approach is pgpool2 but there seems to be issues with sequences and those tables depend upon these sequences. Thank you On Wed, Aug 25, 2010 at 5:14 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Dragos Valentin Moinescu <dragos.moinescu@gmail.com> wrote: > >> Where can I find information about how to modify the postgresql >> server in order to allow this in 8.2.17? > > You haven't exactly said what "this" is, but I am inferring that you > want to bring your standby up to run read-only queries once per > hour, and then resume replication? If so, you could either consider > one of the many replication solutions: > > http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling > > or you could follow your current practice, but get a fresh base > backup each time, and start the warm standby over again. If you use > rsync with a daemon you will probably be surprised how quickly you > can acquire the new base backup. > > -Kevin > -- Best regards, Dragos Moinescu
[Rearranged somewhat. Please don't top-post, but put responses at the appropriate point in-line. I've had to guess a bit at what was responding to what; apologies for any misinterpretation.] Dragos Valentin Moinescu <dragos.moinescu@gmail.com> wrote: > Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > I used rsync to create a base backup. Though I have 10 huge tables > (min 1GB each) that are modified several times a second, thus > creating a base backup hourly means I have to sync arround 10G > each time (which is pretty time consuming). Are you sure you used a rsync daemon, rather than having rsync on one end look directly at the files on the other end? That can make a big difference in the performance for a situation like this. >> I am inferring that you want to bring your standby up to run >> read-only queries once per hour, and then resume replication? > I do not want to use the standby server. This is why I am pretty > happy with stop - start + recover. I'm not sure I understand you -- you don't need to run any read-only queries on the standby server? Why are you stopping it once per hour? Perhaps you just haven't properly implemented warm standby? (A warm standby accepts new WAL files as they arrive, to stay relatively up-to-date -- they never reach a "recovery completed" state unless told to do so, because the recovery script waits for the next file instead of failing.) > I cannot base backup anymore :( What do you mean by that? -Kevin
On Wed, Aug 25, 2010 at 6:11 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > [Rearranged somewhat. Please don't top-post, but put responses at > the appropriate point in-line. I've had to guess a bit at what was > responding to what; apologies for any misinterpretation.] Sorry about that:) > > Dragos Valentin Moinescu <dragos.moinescu@gmail.com> wrote: >> Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > >> I used rsync to create a base backup. Though I have 10 huge tables >> (min 1GB each) that are modified several times a second, thus >> creating a base backup hourly means I have to sync arround 10G >> each time (which is pretty time consuming). > > Are you sure you used a rsync daemon, rather than having rsync on > one end look directly at the files on the other end? That can make > a big difference in the performance for a situation like this. I did not use rsyncd. I used "rsync -avz user@host:/pgsqldata /localpgdata". Will read about rsyncd > >>> I am inferring that you want to bring your standby up to run >>> read-only queries once per hour, and then resume replication? > >> I do not want to use the standby server. This is why I am pretty >> happy with stop - start + recover. > > I'm not sure I understand you -- you don't need to run any read-only > queries on the standby server? Why are you stopping it once per > hour? Perhaps you just haven't properly implemented warm standby? I do not need a read-only server. I just want a server that is almost a mirror to the other server. > (A warm standby accepts new WAL files as they arrive, to stay > relatively up-to-date -- they never reach a "recovery completed" > state unless told to do so, because the recovery script waits for > the next file instead of failing.) > What I did in terms of warm standby: 1. base backup using rsync 2. rsync all wal files 3. edited recoverfy.conf with a recovery_command="cp /wal-archive/%f %p" So I need to write a new recovery_command that actually process any files from there, but waits for other files to arrive. "recovery-command.sh %f %p" Thank you >> I cannot base backup anymore :( > > What do you mean by that? > > -Kevin > -- Best regards, Dragos Moinescu
On Wed, 2010-08-25 at 22:39 +0300, Dragos Valentin Moinescu wrote: > What I did in terms of warm standby: > 1. base backup using rsync > 2. rsync all wal files > 3. edited recoverfy.conf with a recovery_command="cp /wal-archive/%f %p" > > So I need to write a new recovery_command that actually process any > files from there, but waits for other files to arrive. > "recovery-command.sh %f %p" Just use PITRTools. Your life will be easier. http://github.com/commandprompt/PITRTools JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
Dragos Valentin Moinescu <dragos.moinescu@gmail.com> wrote: > I did not use rsyncd. I used "rsync -avz user@host:/pgsqldata > /localpgdata". Will read about rsyncd You don't need to run the rsyncd service to get a daemon -- we use the remote shell technique, through ssh. If you're using the :: syntax (versus :/), you are using a daemon. -Kevin
On Wed, 2010-08-25 at 22:39 +0300, Dragos Valentin Moinescu wrote: > What I did in terms of warm standby: > 1. base backup using rsync > 2. rsync all wal files > 3. edited recoverfy.conf with a recovery_command="cp /wal-archive/%f %p" > > So I need to write a new recovery_command that actually process any > files from there, but waits for other files to arrive. > "recovery-command.sh %f %p" Just use PITRTools. Your life will be easier. http://github.com/commandprompt/PITRTools JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
thank you very much for your feedback.
I succeded in creating a warm standby server:
1. wal archive mirroring script (put it in a crontab to run):
#!/bin/sh
rsync user@remote.host:/from/ /to
2. recovery.conf contains only:
restore_command = 'sh /pgsql-data/restore-postgres.sh %f %p'
3. /pgsql-data/restore-postgres.sh contains:
#!/bin/sh
while true; do
if [ -f "/to/$1"] ; then
cp $1 $2
# we have found the requested file - exit with 0 to tell postgres it
has the file
# postgres will request the next file in sequence after it processes
the current file
exit 0
else
# else we are waiting for the file to become available: wait 30 seconds
sleep 30
# after sleeping for 30 seconds, the loop will continue to test the next file
fi
done
Thanx again.
On Wed, Aug 25, 2010 at 10:49 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Dragos Valentin Moinescu <dragos.moinescu@gmail.com> wrote:
>
>> I did not use rsyncd. I used "rsync -avz user@host:/pgsqldata
>> /localpgdata". Will read about rsyncd
>
> You don't need to run the rsyncd service to get a daemon -- we use
> the remote shell technique, through ssh. If you're using the ::
> syntax (versus :/), you are using a daemon.
>
> -Kevin
>
--
Best regards,
Dragos Moinescu
Dragos Valentin Moinescu <dragos.moinescu@gmail.com> wrote: > I succeded in creating a warm standby server: > > [custom scripting] That's gotta be nicer than what you were doing before. :-) That said, I fear there are a few unusual conditions you are not handling in your script. You might want to take a look at some packaged solutions which deal with these gracefully. pg_standby and pitrtools come to mind, although I might be forgetting something. In the meantime, keep an eye on the log files from both the source and standby databases to make sure transactions are flowing. You might also want to run pg_controldata and make sure that "Database cluster state" is "in archive recovery" and "Time of latest checkpoint" hasn't gotten too stale. -Kevin