Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10
Thanks for all suggestions. based on the constraints I had with network, I could able to set up the warm stand by. I am seeing the following log file I don't know to how to handle. 2013-10-03 17:52:00 GMT [27636]: [457-1] user=,db=LOG: restored log file 000101F60003 from archive scp:/archive/000101F60004: No such file or directory *2013-10-03 17:52:02 GMT [27636]: [458-1] user=,db=LOG: unexpected pageaddr 1F5/3400 in log file 502, segment 4, offset 0* Regards On Thu, Oct 3, 2013 at 12:34 AM, Jaime Casanova ja...@2ndquadrant.comwrote: On Tue, Oct 1, 2013 at 10:48 PM, Sergey Konoplev gray...@gmail.com wrote: On Tue, Oct 1, 2013 at 6:15 PM, Jaime Casanova ja...@2ndquadrant.com wrote: but it would be a good idea to set hot_standby_feedback to on and max_standby_archive_delay to something larger than 30s Doesn't replica need a connection to master for hot_standby_feedback? doh! yes, it needs it... vacuum_defer_cleanup_age it's the one you should set if never do streaming... but, of course, that is not accurate enough -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157
Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10
On Tue, Oct 1, 2013 at 10:48 PM, Sergey Konoplev gray...@gmail.com wrote: On Tue, Oct 1, 2013 at 6:15 PM, Jaime Casanova ja...@2ndquadrant.com wrote: but it would be a good idea to set hot_standby_feedback to on and max_standby_archive_delay to something larger than 30s Doesn't replica need a connection to master for hot_standby_feedback? doh! yes, it needs it... vacuum_defer_cleanup_age it's the one you should set if never do streaming... but, of course, that is not accurate enough -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10
it is a firewall issue. they can't open the port that we requested it. so as you mentioned tunnel to the primary via tunnel. will give that a try regards On Mon, Sep 30, 2013 at 11:10 PM, Chris Travers chris.trav...@gmail.comwrote: On Mon, Sep 30, 2013 at 7:14 PM, akp geek akpg...@gmail.com wrote: Hi all - Currently we have set up one master one slave , which working fine. Now we need to replicate to an other slave. The problem we have , the port that we use on primary can not be reached from the new slave. We can't the change the primary port also, because many applications using it. I can't reach out to my primary using the recovery.conf on the new slave. Can you suggest how I can handle this? Appreciate your help. Why can't you reach it? Is it a firewall? if so basically you have two options. The first is you can configure your firewall to allow the connection. The second is you can tunnel through using another port/service like SSH or IPSec ESP. Best Wishes, Chris Travers Regards -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more.shtml
Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10
One more thing.. pardon me for being dumb I want to set the 2 nd slave as HOT STAND BY, not steaming .. What would be steps. on the primary I will have the archive_command on the slave in the recover.conf , restore_command. After I make my slave as exactly as master, How can the slave gets the files from master ( HOT STAND BY ).. master has trusted connection(ssh ) with slave. Thanks a lot for the help. On Tue, Oct 1, 2013 at 10:14 AM, akp geek akpg...@gmail.com wrote: it is a firewall issue. they can't open the port that we requested it. so as you mentioned tunnel to the primary via tunnel. will give that a try regards On Mon, Sep 30, 2013 at 11:10 PM, Chris Travers chris.trav...@gmail.comwrote: On Mon, Sep 30, 2013 at 7:14 PM, akp geek akpg...@gmail.com wrote: Hi all - Currently we have set up one master one slave , which working fine. Now we need to replicate to an other slave. The problem we have , the port that we use on primary can not be reached from the new slave. We can't the change the primary port also, because many applications using it. I can't reach out to my primary using the recovery.conf on the new slave. Can you suggest how I can handle this? Appreciate your help. Why can't you reach it? Is it a firewall? if so basically you have two options. The first is you can configure your firewall to allow the connection. The second is you can tunnel through using another port/service like SSH or IPSec ESP. Best Wishes, Chris Travers Regards -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more.shtml
Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10
On Tue, Oct 1, 2013 at 2:03 PM, akp geek akpg...@gmail.com wrote: One more thing.. pardon me for being dumb I want to set the 2 nd slave as HOT STAND BY, not steaming .. Hot standby assumes being streaming. You can not establish a hot standby without using streaming replication. What is the reason not to do it streaming? BTW, you will find the SSH tunnel instructions here http://code.google.com/p/pgcookbook/wiki/Streaming_Replication_Setup -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10
thanks. I can try this. Any idea for the message below. Thanks for the patience I tried tunneling this morning and it did not work. when tried the tunneling command in the url you mentioned getting following error. I will try to find what exactly this mean , but any help is appreciated. command-line: line 0: Bad configuration option: ExitOnForwardFailure command-line: line 0: Bad configuration option: ExitOnForwardFailure Regards On Tue, Oct 1, 2013 at 6:46 PM, Sergey Konoplev gray...@gmail.com wrote: On Tue, Oct 1, 2013 at 2:03 PM, akp geek akpg...@gmail.com wrote: One more thing.. pardon me for being dumb I want to set the 2 nd slave as HOT STAND BY, not steaming .. Hot standby assumes being streaming. You can not establish a hot standby without using streaming replication. What is the reason not to do it streaming? BTW, you will find the SSH tunnel instructions here http://code.google.com/p/pgcookbook/wiki/Streaming_Replication_Setup -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com
Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10
On Tue, Oct 1, 2013 at 5:46 PM, Sergey Konoplev gray...@gmail.com wrote: On Tue, Oct 1, 2013 at 2:03 PM, akp geek akpg...@gmail.com wrote: One more thing.. pardon me for being dumb I want to set the 2 nd slave as HOT STAND BY, not steaming .. Hot standby assumes being streaming. You can not establish a hot standby without using streaming replication. What is the reason not to do it streaming? Ah! why? you don't need to use streaming replication for a hot standby, it works perfectly well even if you replay everything from archive and never do streaming. but it would be a good idea to set hot_standby_feedback to on and max_standby_archive_delay to something larger than 30s -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10
On Tue, Oct 1, 2013 at 6:15 PM, Jaime Casanova ja...@2ndquadrant.com wrote: you don't need to use streaming replication for a hot standby, it works perfectly well even if you replay everything from archive and never do streaming. Right, I mixed up a with the terms a bit. but it would be a good idea to set hot_standby_feedback to on and max_standby_archive_delay to something larger than 30s Doesn't replica need a connection to master for hot_standby_feedback? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10
On Tue, Oct 1, 2013 at 5:30 PM, akp geek akpg...@gmail.com wrote: I tried tunneling this morning and it did not work. when tried the tunneling command in the url you mentioned getting following error. I will try to find what exactly this mean , but any help is appreciated. command-line: line 0: Bad configuration option: ExitOnForwardFailure command-line: line 0: Bad configuration option: ExitOnForwardFailure It looks like your SSH version or implementation doesn't support ExitOnForwardFailure. Try to find an alternative. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10
On Mon, Sep 30, 2013 at 7:14 PM, akp geek akpg...@gmail.com wrote: Hi all - Currently we have set up one master one slave , which working fine. Now we need to replicate to an other slave. The problem we have , the port that we use on primary can not be reached from the new slave. We can't the change the primary port also, because many applications using it. I can't reach out to my primary using the recovery.conf on the new slave. Can you suggest how I can handle this? Appreciate your help. Why can't you reach it? Is it a firewall? if so basically you have two options. The first is you can configure your firewall to allow the connection. The second is you can tunnel through using another port/service like SSH or IPSec ESP. Best Wishes, Chris Travers Regards -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more.shtml
Re: [GENERAL] Postgres Replication
Mageshwaran wrote: Hi , Is there any way to do replication in postgres.. Please give me any idea to do replication... entering postgresql replication into google brings up: www.commandprompt.com www.greenplum.com (kind of different but sure) And don't forget: www.slony.info And PITR. Lastly, perhaps the documentation would service you. Joshua D. Drake Regards J Mageshwaran ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres Replication
dcrespo wrote: Good question. The only concern that I have is the date of the last version (2005-3-7). You will find that their website has not been updated for a while. If you look in pgfoundry you will find that they have releases as recent as a few days ago. The different 1.x versions relate to a different postgres version (1.7.x is 8.2) (1.5.x is 8.1) (1.3.x is 8.0). http://pgfoundry.org/projects/pgcluster Do you or anybody know if this software (PGCluster) is stable and works fine? Please, give information on how it fits your needs. I haven't used it myself, just been looking around out of curiosity. Thank you! Daniel km wrote: On Tue, Jan 09, 2007 at 12:17:20PM -0600, Scott Marlowe wrote: Has anybody researched on this that can point me in the right direction? You could use possibly use pgpool as long as its caveats aren't a show stopper (can't insert with random, individual inserts with things like now() might be a little different, insert order might not be the same on both machines, etc... I haven't used daffodil, but have heard of it. There's also c-jdbc and a few others. what abt pgcluster ? how does it fare with SlonyI ? regards, KM ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres Replication
On Tue, Jan 09, 2007 at 12:17:20PM -0600, Scott Marlowe wrote: Has anybody researched on this that can point me in the right direction? You could use possibly use pgpool as long as its caveats aren't a show stopper (can't insert with random, individual inserts with things like now() might be a little different, insert order might not be the same on both machines, etc... I haven't used daffodil, but have heard of it. There's also c-jdbc and a few others. what abt pgcluster ? how does it fare with SlonyI ? regards, KM ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres Replication
Thank you, Ben, for your reply. I have read the FAQ of DRBD, but I'm still wondering how an application accessing a database server knows when to switch to the mirror (setting this one as the master). I think I should have an application that provides the connection transparently which determines where to connect. But for that, it must be running in another computer besides the cluster (the two computers). I'm a newbie, so maybe this was a newbie question message. Thanks Daniel Ben wrote: If you only want to use one database at a time you might look into using DRBD. It's a linux block-level package that is like raid-1 over the network. On Tue, 9 Jan 2007, dcrespo wrote: Hi everybody, I have two computers with a Postgres Database each. I want one of them to be the replica of the other one; let's say I want a Master to Master replication in order to use either one (but only one at a time) as the main database: in case of failure, switch. The ideal synchronization way would be Synchronous. However, these two computers are going to be next to each other, so the asynchronous synchronization would be fast enough (I don't really know. Can you tell so?) for the case synchronous sync is not available. What I have found so far is Daffodil and Slony-I. Daffodil's name doesn't even appear in Postgresql.org, which is not the case for Slony-I. So there's a big point in favor to Slony-I. Has anybody researched on this that can point me in the right direction? Thanks a lot, Daniel Crespo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres Replication
Good question. The only concern that I have is the date of the last version (2005-3-7). Do you or anybody know if this software (PGCluster) is stable and works fine? Please, give information on how it fits your needs. Thank you! Daniel km wrote: On Tue, Jan 09, 2007 at 12:17:20PM -0600, Scott Marlowe wrote: Has anybody researched on this that can point me in the right direction? You could use possibly use pgpool as long as its caveats aren't a show stopper (can't insert with random, individual inserts with things like now() might be a little different, insert order might not be the same on both machines, etc... I haven't used daffodil, but have heard of it. There's also c-jdbc and a few others. what abt pgcluster ? how does it fare with SlonyI ? regards, KM ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Postgres Replication
Look into heartbeat: http://www.linux-ha.org/HeartbeatProgram The idea is that you have a virtual address to be the database, and that the primary server configures itself for this address as well as whatever address it would normally have. Then, when you want to switch servers (maybe because the primary has died, or because you want to do some maintenance to keep it from dying) the second server takes over the database address with a bunch of ARP packets. Your application sees its postgres connections have died and so gracefully (right?) tries to reconnect, and as long as the primary server is no longer trying to regain control of that virtual address (which it usually isn't, because either you've configured it not to or because it's dead) then everything proceeds just fine on the backup server. On Wed, 10 Jan 2007, dcrespo wrote: Thank you, Ben, for your reply. I have read the FAQ of DRBD, but I'm still wondering how an application accessing a database server knows when to switch to the mirror (setting this one as the master). I think I should have an application that provides the connection transparently which determines where to connect. But for that, it must be running in another computer besides the cluster (the two computers). I'm a newbie, so maybe this was a newbie question message. Thanks Daniel Ben wrote: If you only want to use one database at a time you might look into using DRBD. It's a linux block-level package that is like raid-1 over the network. On Tue, 9 Jan 2007, dcrespo wrote: Hi everybody, I have two computers with a Postgres Database each. I want one of them to be the replica of the other one; let's say I want a Master to Master replication in order to use either one (but only one at a time) as the main database: in case of failure, switch. The ideal synchronization way would be Synchronous. However, these two computers are going to be next to each other, so the asynchronous synchronization would be fast enough (I don't really know. Can you tell so?) for the case synchronous sync is not available. What I have found so far is Daffodil and Slony-I. Daffodil's name doesn't even appear in Postgresql.org, which is not the case for Slony-I. So there's a big point in favor to Slony-I. Has anybody researched on this that can point me in the right direction? Thanks a lot, Daniel Crespo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Postgres Replication
On Tue, 2007-01-09 at 07:36, dcrespo wrote: Hi everybody, I have two computers with a Postgres Database each. I want one of them to be the replica of the other one; let's say I want a Master to Master replication in order to use either one (but only one at a time) as the main database: in case of failure, switch. The ideal synchronization way would be Synchronous. However, these two computers are going to be next to each other, so the asynchronous synchronization would be fast enough (I don't really know. Can you tell so?) for the case synchronous sync is not available. What I have found so far is Daffodil and Slony-I. Daffodil's name doesn't even appear in Postgresql.org, which is not the case for Slony-I. So there's a big point in favor to Slony-I. Has anybody researched on this that can point me in the right direction? Possibly. Depending on your biz requirements, you may be better served with a hot failover setup, where both machines can mount the same storage array and if the primary server fails, the secondary server mounts its partitions and starts up postgresql, and takes over its IPs etc... There are hazards with this kind of setup, because if two postmasters run on the same data store it will corrupt it beyond repair, etc... slony works well for what you're talking about, but you'll need to come up with a switchover plan that meets you needs. You could use possibly use pgpool as long as its caveats aren't a show stopper (can't insert with random, individual inserts with things like now() might be a little different, insert order might not be the same on both machines, etc... I haven't used daffodil, but have heard of it. There's also c-jdbc and a few others. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgres Replication
If you only want to use one database at a time you might look into using DRBD. It's a linux block-level package that is like raid-1 over the network. On Tue, 9 Jan 2007, dcrespo wrote: Hi everybody, I have two computers with a Postgres Database each. I want one of them to be the replica of the other one; let's say I want a Master to Master replication in order to use either one (but only one at a time) as the main database: in case of failure, switch. The ideal synchronization way would be Synchronous. However, these two computers are going to be next to each other, so the asynchronous synchronization would be fast enough (I don't really know. Can you tell so?) for the case synchronous sync is not available. What I have found so far is Daffodil and Slony-I. Daffodil's name doesn't even appear in Postgresql.org, which is not the case for Slony-I. So there's a big point in favor to Slony-I. Has anybody researched on this that can point me in the right direction? Thanks a lot, Daniel Crespo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgres replication only some datas
After a long battle with technology, Matthias Blohm [EMAIL PROTECTED], an earthling, wrote: Hello, a question about a tool or a possibility how could something work. following situation: we have a database which is full of very sensitive information and needed that db to use our online website. but now we move the website to a server outside our office and needed to replicate only some datas to the online db. with the tool slony i found out , that some tables could be replicated, but in some tables are some information, which we do not wont to replicate. so we need a tool or an idea how to do that. I though about a dump and deleting the sensitive datas, but the database is about a half gig and we need the changed entries directly on the online db within seconds. Anybody how could help? Is there some way you could separate out the sensitive material into a separate table so that you'd have: - Table public_stuff with the replicable data; - Table private_stuff with the data that shouldn't be replicated; - View all_stuff, which unifies the data when it needs to be combined. You might use a rule to decide which data goes where, or perhaps a stored procedure create_stuff(elements). -- select 'cbbrowne' || '@' || 'ntlug.org'; http://www.ntlug.org/~cbbrowne/nonrdbms.html Perhaps the purpose of categorical algebra is to show that that which is trivial, is trivially trivial. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
