Re: [HACKERS] How to avoid base backup in automated failover
Hey Amitkapila, Thank you for the quick reply. How can implement this patch in windows, because I am using windows 9.1.1 postgreSQL application ?? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-avoid-base-backup-in-automated-failover-tp5711147p5728562.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to avoid base backup in automated failover
Hey Haas,What does the standby server have to wait for replication to catch up before promoting ?? Is there any parameter to configure this ??Few more questions on this part 1. How could we ensure the standby has received all transactions sent by primary till the point primary server is dead. (Meaning the dead primary and standby server are exactly same, so that the dead primary comes back it can be turned to standby without any issues).2. When the dead primary is turned to standby the streaming is not happening due to current_wal_location is ahead in the standby server is ahead of wal_sent_location. In this case how can I start streaming without taking a fresh base backup ??3. When the dead primary comes back the DB still accepts data and it goes to out of sync with the current primary and streaming won't start. Is there any solution for this case ??Reddy. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-avoid-base-backup-in-automated-failover-tp5711147p5728517.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] How to avoid base backup in automated failover
Hey Haas, What does the standby server have to wait for replication to catch up before promoting ?? Is there any parameter to configure this ?? Few more questions on this part 1. How could we ensure the standby has received all transactions sent by primary till the point primary server is dead. (Meaning the dead primary and standby server are exactly same, so that the dead primary comes back it can be turned to standby without any issues). 2. When the dead primary is turned to standby the streaming is not happening due to current_wal_location is ahead in the standby server is ahead of wal_sent_location. In this case how can I start streaming without taking a fresh base backup ?? 3. When the dead primary comes back the DB still accepts data and it goes to out of sync with the current primary and streaming won't start. Is there any solution for this case ?? Reddy. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-avoid-base-backup-in-automated-failover-tp5711147p5728518.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Windows Streaming replication -- Windows 2008 servers
Hi All, It would be helpful if someone tell me, how to verify the streaming replication started in standby server successfully, just after the service is started ?? Like knowing the exit code of the service started... I know we can do it by verifying the below functions after the service is started .. select pg_last_xlog_replay_location() select pg_last_xlog_current_location() select pg_last_xlog_receive_location() Regards, Reddy. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Windows-Streaming-replication-Windows-2008-servers-tp5718921.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
[HACKERS] How to avoid base backup in automated failover
Hi all, Recently I was writing an application to implement automated failover with env: Two 2008 R2 servers, Network area storage, asynchronous replication, WAL archive on primary enabled. Is there any way to avoid starting standby server always from base backup in automated failover. I see the database is growing huge. I can't keep doing base backup every day. Please suggest solution Regards, Reddy -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-avoid-base-backup-in-automated-failover-tp5711147.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hot standby PSQL 9.1 Windows 2008 Servers
Dear Kevin, Thank you for your reply. Yeah I am writing an application using powershell, it's true it is not trivial and especially a guy like me who has no idea on database. You raised all the cases which I am muddling with, But currently I am testing this setup: Always standby server is configured from base backup and restore from storage server then start streaming replication(asynchronous). Base backups are taken frequently. I am sure there is some data loss during switching. Still researching how to do it clean. suggest me if you have any good papers on this .. Reddy. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Re-hot-standby-PSQL-9-1-Windows-2008-Servers-tp5710824p5710830.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hot standby PSQL 9.1 Windows 2008 Servers
Sorry to mention, In my setup the primary and standby servers receive same traffic, so no issue with the network fault between the primary and the standby, but not between the primary and some of the clients updating it -- View this message in context: http://postgresql.1045698.n5.nabble.com/Re-hot-standby-PSQL-9-1-Windows-2008-Servers-tp5710824p5710832.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hot standby PSQL 9.1 Windows 2008 Servers
Dear Robert, Thank you very much for the reply. You mean when the primary which is going to switch its role to standby might not have sent all the WAL records to the standby and If it is switched to standby it has more WAL records than the standby which is now serves as primary. Is it ?? It is actually the standby server which has to be restored from archive when it is switching to primary right .. Not the primary which is switching to standby ?? Regards, Reddy. -- View this message in context: http://postgresql.1045698.n5.nabble.com/hot-standby-PSQL-9-1-Windows-2008-Servers-tp5708637p5709495.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] hot standby PSQL 9.1 Windows 2008 Servers
Hi All, I have implemented hot standby for PostgreSQL with a group of two Primary and Standby in windows 2008 servers. Currently below are the settings: 1. Archiving is enabled on primary, stored on network storage. 2. Asynchronous Streaming replication from primary to standby. wal-senders=5, wal_keep_segments=32. 3. I am checking heartbeat of primary from standby, when it is down i am restarting service by changing postgresql.conf and recovery.conf settings. I do base backup only first time on standby when it is going to be replicated. when ever primary goes down, standby becomes primary and primary becomes standby when primary comes up. When primary becomes standby I am restoring data from WAL archive and start postgres service streaming replication to connect to primary. This setup is working. I have tested for few days in my network with huge data input to the primary and restarting the servers multiple times. I have observed these below specific errors in standby server and standby server is not replicating in anymore. *1. Invalid primary and secondary checkpoints. Very rare But happend 2. contrecord is requested by 0/DF20 -- Major one 3. record with zero length at 0/DF78, invalid record length at 0/DF78* These errors are shown up when primary switching to standby by recovering data from archive and standby stops replicating. *I am not able to start the service for that I should kill all the process of postgres and then do base backup and then start service on stnadby.* I don't have any prior experience working with postgreSQL. Please tell me what are the most reliable settings for Hot standby with streaming replication to work ? two 2008 servers and 1 network storage available. How to avoid above errors ?? -- View this message in context: http://postgresql.1045698.n5.nabble.com/hot-standby-PSQL-9-1-Windows-2008-Servers-tp5708637.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
