Thread: pg_upgrade improvements
Hi all,
I've written a pg_upgrade wrapper for upgrading our users (heroku) to postgres 9.1. In the process I encountered a specific issue that could easily be improved. We've had this process work consistently for many users both internal and external, with the exception of just a few for whom the process fails and required manual handholding.
Before it performs the upgrade, the pg_upgrade program starts the old cluster, does various checks, and then attempts to stop it. On occasion stopping the cluster fails - I've posted command output on a gist [1]. Manually running the pg_upgrade shortly afterwards succeeds. We believe stopping the cluster times out because there are other connections to the cluster that are established in that small window. There could be incoming connections for a number of reasons: either the user or the user's applications are reestablishing connections, or something like collectd on the localhost attempts to connect during that small window.
Possible workarounds on the current version:
* Add an iptables rule to temporarily reject connections from the outside. This is not viable because in a multitenant environment a process may write an iptables rule, and meanwhile another process may permanently save rules, including the temporary one. We can defend against that, but it does add a lot of complexity.
* Rewrite pg_hba.conf temporarily while the pg_upgrade script runs to disallow any other connections.
A possible solution for pg_upgrade is for it to make pg_upgrade use the --force flag when stopping the cluster to kick connections out. There is no reason to be polite in this case. Another idea that was kicked around with my colleagues was to start the cluster in single-user mode, or only allow unix socket connections somewhere in /tmp. Anything that rejects other connections would be helpful.
It would also be nice if the invocation of pg_ctl didn't pipe its output to /dev/null. I'm sure it would contain information that would directly point at the root cause and could've saved some debugging and hand waving time.
Finally, just a note that while we haven't performed a huge number of upgrades yet, we have upgraded a few production systems and for the most part it has worked great.
Regards,
-Harold
Harold,
* Harold Giménez (harold.gimenez@gmail.com) wrote:
> Possible workarounds on the current version:
This has actually been discussed before and unfortunately there aren't
any trivial solutions.
> * Rewrite pg_hba.conf temporarily while the pg_upgrade script runs to
> disallow any other connections.
This is probably my favorite, from a 'practical' and 'effective'
standpoint. I wonder if there'd be a way to specify the pg_hba.conf
to use on the command-line or in some other way, to avoid having to
actually modify the existing one (which would have to be 'unmodified'
after, of course..).
The other options would work, of course. Perhaps my second favorite
option (second because I think it'd be more challenging and invasive) is
making the PG daemon only listens on a unix socket (which is not where
the default unix socket is).
The single-user option *sounds* viable, but, iirc, it actually isn't due
to the limitations on what can be done in that mode.
> It would also be nice if the invocation of pg_ctl didn't pipe its output to
> /dev/null. I'm sure it would contain information that would directly point
> at the root cause and could've saved some debugging and hand waving time.
Agreed.
> Finally, just a note that while we haven't performed a huge number of
> upgrades yet, we have upgraded a few production systems and for the most
> part it has worked great.
Great!
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
> The single-user option *sounds* viable, but, iirc, it actually isn't due
> to the limitations on what can be done in that mode.
Yeah. IMO the right long-term fix is to be able to run pg_dump and psql
talking to a standalone backend, but nobody's gotten round to making
that possible.
regards, tom lane
On Wed, Apr 4, 2012 at 11:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Stephen Frost <sfrost@snowman.net> writes: >> The single-user option *sounds* viable, but, iirc, it actually isn't due >> to the limitations on what can be done in that mode. > > Yeah. IMO the right long-term fix is to be able to run pg_dump and psql > talking to a standalone backend, but nobody's gotten round to making > that possible. Are you thinking about something like postgres --single --port=PORT_NUMBER_OR_SOCKET_DIRECTORY? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Apr 4, 2012 at 11:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah. IMO the right long-term fix is to be able to run pg_dump and psql
>> talking to a standalone backend, but nobody's gotten round to making
>> that possible.
> Are you thinking about something like postgres --single
> --port=PORT_NUMBER_OR_SOCKET_DIRECTORY?
No, opening up a port is exactly what we *don't* want it to do.
Otherwise you're right back to worrying about how to make sure that
unwanted connections don't get in. Notions like private socket
directories don't solve this because we don't have that option
available on Windows.
The vague idea I had was for libpq to have a connection option that
would cause it to spawn a standalone backend as a child process and
communicate with that over two pipes, just like any popen'd process.
The backend would see this exactly like standalone mode now, except for
speaking FE/BE protocol over its stdin/stdout rather than the existing
ad-hoc user interface for standalone mode.
regards, tom lane
On Thursday, April 05, 2012 03:46:54 PM Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: > > On Wed, Apr 4, 2012 at 11:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> Yeah. IMO the right long-term fix is to be able to run pg_dump and psql > >> talking to a standalone backend, but nobody's gotten round to making > >> that possible. > > > > Are you thinking about something like postgres --single > > --port=PORT_NUMBER_OR_SOCKET_DIRECTORY? > > No, opening up a port is exactly what we *don't* want it to do. > Otherwise you're right back to worrying about how to make sure that > unwanted connections don't get in. Notions like private socket > directories don't solve this because we don't have that option > available on Windows. I wonder if it wouldn't be better to pass a named pipe under windows and use a AF_UNIX socket everwhere else. Both should be pretty easily usable with the existing code. PG already seems to use named pipes under windows, so... Andres
* Andres Freund (andres@anarazel.de) wrote:
> I wonder if it wouldn't be better to pass a named pipe under windows and use a
> AF_UNIX socket everwhere else. Both should be pretty easily usable with the
> existing code. PG already seems to use named pipes under windows, so...
I didn't think Tom's suggestion was really all that difficult to
implement and sounds like a more-generally-useful change anyway (which
you might want to use outside of this specific use case).
Thanks,
Stephen
On Thursday, April 05, 2012 04:44:11 PM Stephen Frost wrote: > * Andres Freund (andres@anarazel.de) wrote: > > I wonder if it wouldn't be better to pass a named pipe under windows and > > use a AF_UNIX socket everwhere else. Both should be pretty easily usable > > with the existing code. PG already seems to use named pipes under > > windows, so... > > I didn't think Tom's suggestion was really all that difficult to > implement and sounds like a more-generally-useful change anyway (which > you might want to use outside of this specific use case). Hm. Changing libpq to use two pipes at the same time sounds considerably more invasive than basically just changing the socket creation and some minor details. Why would pipes be more useful? Its not like you could build useful pipelines with them. Also, it might open a window for implementing AF_UNIX like connections on windows... Andres
* Andres Freund (andres@anarazel.de) wrote: > Hm. Changing libpq to use two pipes at the same time sounds considerably more > invasive than basically just changing the socket creation and some minor > details. It's not something we'd back-patch, but I don't believe it'd be very difficult to implement.. > Why would pipes be more useful? Its not like you could build useful pipelines > with them. The point is to avoid the risk that someone else could connect to the database at the same time you're doing work on it. > Also, it might open a window for implementing AF_UNIX like connections on > windows... That's an unrelated discussion, imv.. Stephen
Hi, Not sure if were just missing each others point? On Thursday, April 05, 2012 05:20:04 PM Stephen Frost wrote: > > Why would pipes be more useful? Its not like you could build useful > > pipelines with them. > > The point is to avoid the risk that someone else could connect to the > database at the same time you're doing work on it. I got that. I just fail to see what the advantage of using two pipes instead of one socket as every other plain connection would be? Using named pipes solves that tidbit from Tom: > Notions like private socket directories don't solve this because we don't > have that option available on Windows. If you have named pipes or AF_UNIX sockets you can solve that by either just passing the fd to your child and not allowing any access to it (no problem on either platform) or by using a private directory. Andres
Stephen Frost <sfrost@snowman.net> writes:
> * Andres Freund (andres@anarazel.de) wrote:
>> Why would pipes be more useful? Its not like you could build useful pipelines
>> with them.
> The point is to avoid the risk that someone else could connect to the
> database at the same time you're doing work on it.
Right. Unless I misunderstand the semantics of named pipes on Windows,
we don't want that because in principle some unrelated process could
connect to it as soon as you set it up.
regards, tom lane
Andres Freund <andres@anarazel.de> writes:
>> The point is to avoid the risk that someone else could connect to the
>> database at the same time you're doing work on it.
> I got that. I just fail to see what the advantage of using two pipes instead
> of one socket as every other plain connection would be?
Yeah, that would be a small pain in the neck, but it eliminates a huge
pile of practical difficulties, like your blithe assumption that you can
find a "private directory" somewhere (wrong) or disallow access to other
people (also wrong, if they are using the same account as you).
The short answer is that sockets and named pipes are *meant* to be
publicly accessible. Guaranteeing that they are not is a difficult
task full of possibilities for security holes.
regards, tom lane
On Thursday, April 05, 2012 05:39:19 PM Tom Lane wrote: > Andres Freund <andres@anarazel.de> writes: > >> The point is to avoid the risk that someone else could connect to the > >> database at the same time you're doing work on it. > > > > I got that. I just fail to see what the advantage of using two pipes > > instead of one socket as every other plain connection would be? > > Yeah, that would be a small pain in the neck, but it eliminates a huge > pile of practical difficulties, like your blithe assumption that you can > find a "private directory" somewhere (wrong) or disallow access to other > people (also wrong, if they are using the same account as you). I don't think this needs to protect against malicious intent of a user running with the *same* privileges as the postmaster. That one can simply delete the whole cluster anyway. For everybody else you can just create a directory in PGDATA and revoke all permissions on it for everybody but the owner. For named pipes you could just create a random name with permissions only for the current user (thats possible in the same call) and be done with it. Andres
Andres Freund <andres@anarazel.de> writes:
> On Thursday, April 05, 2012 05:39:19 PM Tom Lane wrote:
>> Yeah, that would be a small pain in the neck, but it eliminates a huge
>> pile of practical difficulties, like your blithe assumption that you can
>> find a "private directory" somewhere (wrong) or disallow access to other
>> people (also wrong, if they are using the same account as you).
> I don't think this needs to protect against malicious intent of a user running
> with the *same* privileges as the postmaster.
Who said anything about malicious intent? Please re-read the original
gripe in this thread. There's nothing un-legitimate about, eg, clients
trying to connect to the database during your maintenance window.
What we want is to be sure that nobody can connect to the database
except the person running the standalone instance. To my mind "sure"
means "sure"; it does not include qualifiers like "unless some
other process tries to do the same thing at about the same time".
regards, tom lane
On Thursday, April 05, 2012 06:12:48 PM Tom Lane wrote: > Andres Freund <andres@anarazel.de> writes: > > On Thursday, April 05, 2012 05:39:19 PM Tom Lane wrote: > >> Yeah, that would be a small pain in the neck, but it eliminates a huge > >> pile of practical difficulties, like your blithe assumption that you can > >> find a "private directory" somewhere (wrong) or disallow access to other > >> people (also wrong, if they are using the same account as you). > > > > I don't think this needs to protect against malicious intent of a user > > running with the *same* privileges as the postmaster. > > Who said anything about malicious intent? Please re-read the original > gripe in this thread. There's nothing un-legitimate about, eg, clients > trying to connect to the database during your maintenance window. Yes, there is not. But those clients won't connect to a socket in some directory thats created extra for this purpose which they don't even know the name of. Scanning the directory tree for that would require malicious intent. > What we want is to be sure that nobody can connect to the database > except the person running the standalone instance. To my mind "sure" > means "sure"; it does not include qualifiers like "unless some > other process tries to do the same thing at about the same time". Its not like creating a file/directory/pipename with a random name and retrying if it already exists is an especially hard thing. That does make *sure* it does not happen by accident. Beside the fact that single run backend should already make sure were not running concurrently. So even a *fixed* atomically created filename name should be enough if its not the regular name and in a place thats not accessible from the outside. Anyway, I don't think those reasons are sensible, but I don't care enough to argue further. Andres
On Thu, Apr 5, 2012 at 17:34, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Stephen Frost <sfrost@snowman.net> writes: >> * Andres Freund (andres@anarazel.de) wrote: >>> Why would pipes be more useful? Its not like you could build useful pipelines >>> with them. > >> The point is to avoid the risk that someone else could connect to the >> database at the same time you're doing work on it. > > Right. Unless I misunderstand the semantics of named pipes on Windows, > we don't want that because in principle some unrelated process could > connect to it as soon as you set it up. By default it does, and that's what we're using it for (pg_ctl kill). That can be handled with permissions and counters, I think. But more importantly in this case, you cannot communicate across a named pipe on windows with the normal socket calls send() and recv(). You have to use the Windows API file calls like ReadFile() and WriteFile(). So it would require some rather major surgery to use that. This is why the pgpipe implementation we had used a socketpair instead of named pipes. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On ons, 2012-04-04 at 19:26 -0700, Harold Giménez wrote: > It would also be nice if the invocation of pg_ctl didn't pipe its > output to /dev/null. I'm sure it would contain information that would > directly point at the root cause and could've saved some debugging and > hand waving time. This aspect has been reworked in 9.2devel. Check it out to see if it works better for you.
On Sat, Apr 07, 2012 at 01:13:23PM +0300, Peter Eisentraut wrote: > On ons, 2012-04-04 at 19:26 -0700, Harold Giménez wrote: > > It would also be nice if the invocation of pg_ctl didn't pipe its > > output to /dev/null. I'm sure it would contain information that would > > directly point at the root cause and could've saved some debugging and > > hand waving time. > > This aspect has been reworked in 9.2devel. Check it out to see if it > works better for you. Yes, we now spit out 5 log files if pg_upgrade fails, and tell you which one to look at. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Wed, Apr 04, 2012 at 07:26:58PM -0700, Harold Giménez wrote: > Hi all, > > I've written a pg_upgrade wrapper for upgrading our users (heroku) > to postgres 9.1. In the process I encountered a specific issue that > could easily be improved. We've had this process work consistently > for many users both internal and external, with the exception of just > a few for whom the process fails and required manual handholding. > > Before it performs the upgrade, the pg_upgrade program starts the > old cluster, does various checks, and then attempts to stop it. On > occasion stopping the cluster fails - I've posted command output > on a gist [1]. Manually running the pg_upgrade shortly afterwards > succeeds. We believe stopping the cluster times out because there > are other connections to the cluster that are established in that > small window. There could be incoming connections for a number of > reasons: either the user or the user's applications are reestablishing > connections, or something like collectd on the localhost attempts to > connect during that small window. Well, we did address this in PG 9.2 by having pg_upgrade use a non-default port number when starting servers, 50432. You can do that too in PG 9.1 by just specifying non-default port numbers when you run pg_upgrade. We do start the server with a special --binary-upgrade mode, and we could do all sorts of connection limits in that mode, but having the port number be different seemed the clearest solution. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Wed, Apr 11, 2012 at 5:40 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Wed, Apr 04, 2012 at 07:26:58PM -0700, Harold Giménez wrote:Well, we did address this in PG 9.2 by having pg_upgrade use a
> There could be incoming connections for a number of
> reasons: either the user or the user's applications are reestablishing
> connections, or something like collectd on the localhost attempts to
> connect during that small window.
non-default port number when starting servers, 50432. You can do that
too in PG 9.1 by just specifying non-default port numbers when you run
pg_upgrade. We do start the server with a special --binary-upgrade
mode, and we could do all sorts of connection limits in that mode, but
having the port number be different seemed the clearest solution.
The non-default port number is a good solution, better than modifying pg_hba.
Thanks for pointing that out!
-Harold