Re: [HACKERS] postgres 9 bind address for replication
On Mon, Jul 23, 2012 at 8:45 PM, Andrew Dunstan and...@dunslane.net wrote: On 07/23/2012 02:23 PM, Adam Crews wrote: On Mon, Jul 23, 2012 at 8:34 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Jul 21, 2012 at 1:24 AM, Adam Crews adam.cr...@gmail.com wrote: Hello, I'm sorry for cross-posting, however I originally posted this to pgsql-general list, but didnt get any replies. Then I posted to pgsql-cluster-hackers..., and now here. I’m using pg 9.1.3 on CentOS 5 and have a few slave databases setup using the built in streaming replication. On the slaves I set the “listen_addresses” config option to an ip address for a virtual alias on my network interfaces. The host has an address of 10.1.1.10, and there is a virtual alias of 10.1.1.40 that the slave postmaster binds to. When the slave makes it connection to the master to start replication the source address for the connection is the host address, not the virtual alias address. Connections appear to come from 10.1.1.10, instead of the slave postmaster address of 10.1.1.40. This seems like a bug to me. I could understand that if the postmaster is listening on all interfaces, then it should use whatever the IP is for the for the host, but in an instance where the postmaster has been configured to listen to a specific address it seems like the call to start the replication should be passed that address so connections come from the slave postmaster’s IP, instead of the host. Is there a config option that can be used to adjust this? I've looked in the docs, but haven't found one yet. Is this perhaps a bug, or lack of feature? I don't think it's a bug, because the behavior you're hoping for might not be what everyone would want in a similar situation. It might qualify as an unimplemented feature. This mailing list isn't heavily used and this seems a bit off-topic for it anyway; you might want to try a different one for further discussion of this issue. So, I think this, as Robert states, an unimplemented feature. For my situation it would be very useful to have an option to be able to specify the source address for replication. I discovered this because I bind the listen address for postgres to a single address even though the host system may have multiple addresses. I then use that single address in iptables rules on other systems. Since I expect the slave to be at a .40 address, but the replication comes from the primary address of the interface (in this case .10), my iptables rules were missing the access for the slave to connect to the master. This site http://linux-ip.net/html/routing-saddr-selection.html describes the behavior I'm seeing. How do I go about requesting a config option that would allow me to specify the source address for the replication connections? You just have :-) You could just add an iptables rule redirecting .10 packets on port 5432 (or whatever you're using) appropriately. We don't have any provision for binding the local end of any connection AFAIK. So the first question is Do we want to? and the second is If yes, when and how? I don't see that replication should be a special case - if this is worth providing for it should be applicable to all clients, ISTM. I have an ugly patch lying around that implemented this as a libpq connection option. It was just a quick hack to work around a situation just like this (though not for replication), and ISTM that's the proper place to put it. I'll stick it on my TODO to try to clean that one up and submit for 9.3.. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] postgres 9 bind address for replication
On Mon, Jul 23, 2012 at 8:34 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Jul 21, 2012 at 1:24 AM, Adam Crews adam.cr...@gmail.com wrote: Hello, I'm sorry for cross-posting, however I originally posted this to pgsql-general list, but didnt get any replies. Then I posted to pgsql-cluster-hackers..., and now here. I’m using pg 9.1.3 on CentOS 5 and have a few slave databases setup using the built in streaming replication. On the slaves I set the “listen_addresses” config option to an ip address for a virtual alias on my network interfaces. The host has an address of 10.1.1.10, and there is a virtual alias of 10.1.1.40 that the slave postmaster binds to. When the slave makes it connection to the master to start replication the source address for the connection is the host address, not the virtual alias address. Connections appear to come from 10.1.1.10, instead of the slave postmaster address of 10.1.1.40. This seems like a bug to me. I could understand that if the postmaster is listening on all interfaces, then it should use whatever the IP is for the for the host, but in an instance where the postmaster has been configured to listen to a specific address it seems like the call to start the replication should be passed that address so connections come from the slave postmaster’s IP, instead of the host. Is there a config option that can be used to adjust this? I've looked in the docs, but haven't found one yet. Is this perhaps a bug, or lack of feature? I don't think it's a bug, because the behavior you're hoping for might not be what everyone would want in a similar situation. It might qualify as an unimplemented feature. This mailing list isn't heavily used and this seems a bit off-topic for it anyway; you might want to try a different one for further discussion of this issue. So, I think this, as Robert states, an unimplemented feature. For my situation it would be very useful to have an option to be able to specify the source address for replication. I discovered this because I bind the listen address for postgres to a single address even though the host system may have multiple addresses. I then use that single address in iptables rules on other systems. Since I expect the slave to be at a .40 address, but the replication comes from the primary address of the interface (in this case .10), my iptables rules were missing the access for the slave to connect to the master. This site http://linux-ip.net/html/routing-saddr-selection.html describes the behavior I'm seeing. How do I go about requesting a config option that would allow me to specify the source address for the replication connections? Thanks -Adam -- 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] postgres 9 bind address for replication
On 07/23/2012 02:23 PM, Adam Crews wrote: On Mon, Jul 23, 2012 at 8:34 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Jul 21, 2012 at 1:24 AM, Adam Crews adam.cr...@gmail.com wrote: Hello, I'm sorry for cross-posting, however I originally posted this to pgsql-general list, but didnt get any replies. Then I posted to pgsql-cluster-hackers..., and now here. I’m using pg 9.1.3 on CentOS 5 and have a few slave databases setup using the built in streaming replication. On the slaves I set the “listen_addresses” config option to an ip address for a virtual alias on my network interfaces. The host has an address of 10.1.1.10, and there is a virtual alias of 10.1.1.40 that the slave postmaster binds to. When the slave makes it connection to the master to start replication the source address for the connection is the host address, not the virtual alias address. Connections appear to come from 10.1.1.10, instead of the slave postmaster address of 10.1.1.40. This seems like a bug to me. I could understand that if the postmaster is listening on all interfaces, then it should use whatever the IP is for the for the host, but in an instance where the postmaster has been configured to listen to a specific address it seems like the call to start the replication should be passed that address so connections come from the slave postmaster’s IP, instead of the host. Is there a config option that can be used to adjust this? I've looked in the docs, but haven't found one yet. Is this perhaps a bug, or lack of feature? I don't think it's a bug, because the behavior you're hoping for might not be what everyone would want in a similar situation. It might qualify as an unimplemented feature. This mailing list isn't heavily used and this seems a bit off-topic for it anyway; you might want to try a different one for further discussion of this issue. So, I think this, as Robert states, an unimplemented feature. For my situation it would be very useful to have an option to be able to specify the source address for replication. I discovered this because I bind the listen address for postgres to a single address even though the host system may have multiple addresses. I then use that single address in iptables rules on other systems. Since I expect the slave to be at a .40 address, but the replication comes from the primary address of the interface (in this case .10), my iptables rules were missing the access for the slave to connect to the master. This site http://linux-ip.net/html/routing-saddr-selection.html describes the behavior I'm seeing. How do I go about requesting a config option that would allow me to specify the source address for the replication connections? You just have :-) You could just add an iptables rule redirecting .10 packets on port 5432 (or whatever you're using) appropriately. We don't have any provision for binding the local end of any connection AFAIK. So the first question is Do we want to? and the second is If yes, when and how? I don't see that replication should be a special case - if this is worth providing for it should be applicable to all clients, ISTM. cheers andrew -- 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] postgres 9 bind address for replication
On Jul 23, 2012, at 11:45 AM, Andrew Dunstan and...@dunslane.net wrote: On 07/23/2012 02:23 PM, Adam Crews wrote: On Mon, Jul 23, 2012 at 8:34 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Jul 21, 2012 at 1:24 AM, Adam Crews adam.cr...@gmail.com wrote: Hello, I'm sorry for cross-posting, however I originally posted this to pgsql-general list, but didnt get any replies. Then I posted to pgsql-cluster-hackers..., and now here. I’m using pg 9.1.3 on CentOS 5 and have a few slave databases setup using the built in streaming replication. On the slaves I set the “listen_addresses” config option to an ip address for a virtual alias on my network interfaces. The host has an address of 10.1.1.10, and there is a virtual alias of 10.1.1.40 that the slave postmaster binds to. When the slave makes it connection to the master to start replication the source address for the connection is the host address, not the virtual alias address. Connections appear to come from 10.1.1.10, instead of the slave postmaster address of 10.1.1.40. This seems like a bug to me. I could understand that if the postmaster is listening on all interfaces, then it should use whatever the IP is for the for the host, but in an instance where the postmaster has been configured to listen to a specific address it seems like the call to start the replication should be passed that address so connections come from the slave postmaster’s IP, instead of the host. Is there a config option that can be used to adjust this? I've looked in the docs, but haven't found one yet. Is this perhaps a bug, or lack of feature? I don't think it's a bug, because the behavior you're hoping for might not be what everyone would want in a similar situation. It might qualify as an unimplemented feature. This mailing list isn't heavily used and this seems a bit off-topic for it anyway; you might want to try a different one for further discussion of this issue. So, I think this, as Robert states, an unimplemented feature. For my situation it would be very useful to have an option to be able to specify the source address for replication. I discovered this because I bind the listen address for postgres to a single address even though the host system may have multiple addresses. I then use that single address in iptables rules on other systems. Since I expect the slave to be at a .40 address, but the replication comes from the primary address of the interface (in this case .10), my iptables rules were missing the access for the slave to connect to the master. This site http://linux-ip.net/html/routing-saddr-selection.html describes the behavior I'm seeing. How do I go about requesting a config option that would allow me to specify the source address for the replication connections? You just have :-) You could just add an iptables rule redirecting .10 packets on port 5432 (or whatever you're using) appropriately. We don't have any provision for binding the local end of any connection AFAIK. So the first question is Do we want to? and the second is If yes, when and how? I don't see that replication should be a special case - if this is worth providing for it should be applicable to all clients, ISTM. cheers andrew I've worked around the issue for now by just allowing the host address instead of the postmaster ip address in my iptables. I agree that if implemented it should be an option available to all clients, not just the replicator. To give a little background, here's what I'm doing. In my environment we have dedicated ip addresses to specific postmasters, then sometimes run multiple postmasters on the same server. Our data files are stored on san storage and can be moved between different servers. With this setup I've abstracted my database from the underlying hardware and can move the postmasters between hosts with minimal downtime. (3 minutes to stop the db, unmount the storage, mount on the new host, start the postmaster). The ip travels to the new host as a virtual interface along with the database files. To make sure clients can't access a db they are not supposed to, I control access via iptables and pg_hba. I can now do system patching with little impact to the application. Thanks, -Adam -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
