Обсуждение: PG_STANDBY ISSUE
Hi experts -
I am running into issue with pg_standby. May be my understanding is not correct. Please help. here is what I did .
I am running into issue with pg_standby. May be my understanding is not correct. Please help. here is what I did .
- I made changes in the postgresql.conf ( archive_mode = on ,archive_command = 'cp -i %p /opt/postgres/archive/%f' , archive_timeout = 60s )
- pg_ctl start -D $PGDATA -l /opt/postgres/logfile
- postgres=# select pg_start_backup('BKP_LBL');
- I have done the base backup and I have used the following command for restore in the recovery.conf
- restore_command = 'pg_standby -d -s 3 -t /tmp/pg_standby.trigger.5432 /opt/postgres/archive/%f %p %r'
- postgres=# select pg_stop_backup();
- Now I have made some changes to the master database and I waited for 30 minutes
- I have stopped the master database
- I have started the slave. But I did not find the changes I have done after I issued the command elect pg_stop_backup();
- The log files have shipped to archive folder
Is there some thing wrong with the I am doing the procedure. Please help
Regards
Hi All -
Can you please help me with the issue when you get some time
regards
---------- Forwarded message ----------
From: akp geek <akpgeek@gmail.com>
Date: Fri, Nov 20, 2009 at 3:38 PM
Subject: PG_STANDBY ISSUE
To: pgsql-general@postgresql.org
Hi experts -
I am running into issue with pg_standby. May be my understanding is not correct. Please help. here is what I did .
From: akp geek <akpgeek@gmail.com>
Date: Fri, Nov 20, 2009 at 3:38 PM
Subject: PG_STANDBY ISSUE
To: pgsql-general@postgresql.org
Hi experts -
I am running into issue with pg_standby. May be my understanding is not correct. Please help. here is what I did .
- I made changes in the postgresql.conf ( archive_mode = on ,archive_command = 'cp -i %p /opt/postgres/archive/%f' , archive_timeout = 60s )
- pg_ctl start -D $PGDATA -l /opt/postgres/logfile
- postgres=# select pg_start_backup('BKP_LBL');
- I have done the base backup and I have used the following command for restore in the recovery.conf
- restore_command = 'pg_standby -d -s 3 -t /tmp/pg_standby.trigger.5432 /opt/postgres/archive/%f %p %r'
- postgres=# select pg_stop_backup();
- Now I have made some changes to the master database and I waited for 30 minutes
- I have stopped the master database
- I have started the slave. But I did not find the changes I have done after I issued the command elect pg_stop_backup();
- The log files have shipped to archive folder
Is there some thing wrong with the I am doing the procedure. Please help
Regards
akp geek wrote: > Hi experts - > > I am running into issue with pg_standby. May be my > understanding is not correct. Please help. here is what I did . > > > 1. I made changes in the postgresql.conf ( archive_mode = on > ,archive_command = 'cp -i %p /opt/postgres/archive/%f' , archive_timeout = > 60s ) > 2. pg_ctl start -D $PGDATA -l /opt/postgres/logfile > 3. postgres=# select pg_start_backup('BKP_LBL'); > 4. I have done the base backup and I have used the following command > for restore in the recovery.conf > 5. restore_command = 'pg_standby -d -s 3 -t > /tmp/pg_standby.trigger.5432 /opt/postgres/archive/%f %p %r' From memory, do you not want a space between ".../archive/" and "%f" - I think they're separate parameters. Also, you'll want to redirect STDERR to a file - add the following to the end of the command: 2>>/tmp/standby.log That way we'll be able to see what's happening. > 6. postgres=# select pg_stop_backup(); > 7. Now I have made some changes to the master database and I waited for > 30 minutes Or just generate lots of changes so WAL files get filled. > 8. I have stopped the master database > 9. I have started the slave. But I did not find the changes I have done > after I issued the command elect pg_stop_backup(); > 10. The log files have shipped to archive folder Good. That's a useful fact. Let's see if anything shows up in our standby logfile once we have it. -- Richard Huxton Archonet Ltd
Thanks a lot Richard. The culprit in my case is the restore command , I modified it as follows per your instructions , it is fine now
restore_command = 'pg_standby -d -s 5 -t /tmp/pg_standby.trigger.5432 /opt/postgres/archive %f %p %r 2>>/tmp/standby.log'
One small doubt I have is , do we have to keep the backup_label on the standby, I deleted it before I start the restore process , it still worked .. Just curious
appreicate your help
Regards
On Mon, Nov 23, 2009 at 1:04 PM, Richard Huxton <dev@archonet.com> wrote:
akp geek wrote:> 1. I made changes in the postgresql.conf ( archive_mode = on
> Hi experts -
>
> I am running into issue with pg_standby. May be my
> understanding is not correct. Please help. here is what I did .
>
>> ,archive_command = 'cp -i %p /opt/postgres/archive/%f' , archive_timeout => 2. pg_ctl start -D $PGDATA -l /opt/postgres/logfile
> 60s )
> 3. postgres=# select pg_start_backup('BKP_LBL');
> 4. I have done the base backup and I have used the following command> for restore in the recovery.conf> 5. restore_command = 'pg_standby -d -s 3 -t> /tmp/pg_standby.trigger.5432 /opt/postgres/archive/%f %p %r'From memory, do you not want a space between ".../archive/" and "%f" - I
think they're separate parameters.
Also, you'll want to redirect STDERR to a file - add the following to
the end of the command: 2>>/tmp/standby.log
That way we'll be able to see what's happening.
> 6. postgres=# select pg_stop_backup();
> 7. Now I have made some changes to the master database and I waited for
> 30 minutes
Or just generate lots of changes so WAL files get filled.
> 8. I have stopped the master database
> 9. I have started the slave. But I did not find the changes I have done> after I issued the command elect pg_stop_backup();> 10. The log files have shipped to archive folder
Good. That's a useful fact. Let's see if anything shows up in our
standby logfile once we have it.
--
Richard Huxton
Archonet Ltd
akp geek wrote: > One small doubt I have is , do we have to keep the backup_label on > the standby, I deleted it before I start the restore process , it > still worked .. Just curious The backup label is strictly for you to read, to help make sure you got all of the archived segments necessary for the backup to be useful. The standby doesn't actually use it for anything, it's more of a helper for a human trying to figure out what's going on. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com