Обсуждение: Hosting pgAdmin4 behind nginx and at /pgdmin, for mortals
Hi,
I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other things working (a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck getting pgAdmin4 to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to do this (I'm running the latest, v4.2, of pgAdmin4)?
I'm aware of threads such as https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local, and several others but not been able to come up with a clear approach:
One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port 5050 directly behind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I've tried both (modelled on what I have working, and various references). One combination I tried was:
- Creating a softlink from pgadmin4.py to pgAdmin4.wsgi
- Using gunicorn to run the pgadmin4.py to a unix domain socket like this:
$ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application
- Serving behind nginx like this:
location /pgadmin {
rewrite ^/pgadmin/(.*) /$1 break;
include proxy_params;
proxy_pass http://unix:/home/ubuntu/pgadmin.sock;
}
rewrite ^/pgadmin/(.*) /$1 break;
include proxy_params;
proxy_pass http://unix:/home/ubuntu/pgadmin.sock;
}
But all I get is a stubborn 404. Any pointers welcome...
Thanks, Shaheed
P.S. I considered just making pgAdmin4.py listen on 0.0.0.0:5050, but I do need to get everything behind HTTPS sooner rather than later.
OK, I got it working. This is how...
On Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote:
>
> Hi,
>
> I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other things working (a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck getting pgAdmin4 to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to do this (I'm running the latest, v4.2, of pgAdmin4)?
>
> I'm aware of threads such as https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local, and several others but not been able to come up with a clear approach:
>
> One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port 5050 directly behind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I've tried both (modelled on what I have working, and various references). One combination I tried was:
>
> - Creating a softlink from pgadmin4.py to pgAdmin4.wsgi
> - Using gunicorn to run the pgadmin4.py to a unix domain socket like this:
>
> $ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application
>
> - Serving behind nginx like this:
>
> location /pgadmin {
> rewrite ^/pgadmin/(.*) /$1 break;
> include proxy_params;
> proxy_pass http://unix:/home/ubuntu/pgadmin.sock;
> }
>
> But all I get is a stubborn 404. Any pointers welcome...
First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). The next problem was with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser looking for top level URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a piece of code in https://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hacking a small wodge of code into pgAdmin4.py.
The SCRIPT_NAME is set by an nginx fragment like this:
location /pgadmin {
rewrite ^/pgadmin/(.*)$ /\$1 break;
include proxy_params;
proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock;
proxy_set_header X-Script-Name /pgadmin;
}
Thanks, ShaheedOn Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote:
>
> Hi,
>
> I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other things working (a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck getting pgAdmin4 to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to do this (I'm running the latest, v4.2, of pgAdmin4)?
>
> I'm aware of threads such as https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local, and several others but not been able to come up with a clear approach:
>
> One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port 5050 directly behind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I've tried both (modelled on what I have working, and various references). One combination I tried was:
>
> - Creating a softlink from pgadmin4.py to pgAdmin4.wsgi
> - Using gunicorn to run the pgadmin4.py to a unix domain socket like this:
>
> $ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application
>
> - Serving behind nginx like this:
>
> location /pgadmin {
> rewrite ^/pgadmin/(.*) /$1 break;
> include proxy_params;
> proxy_pass http://unix:/home/ubuntu/pgadmin.sock;
> }
>
> But all I get is a stubborn 404. Any pointers welcome...
First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). The next problem was with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser looking for top level URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a piece of code in https://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hacking a small wodge of code into pgAdmin4.py.
The SCRIPT_NAME is set by an nginx fragment like this:
location /pgadmin {
rewrite ^/pgadmin/(.*)$ /\$1 break;
include proxy_params;
proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock;
proxy_set_header X-Script-Name /pgadmin;
}
In addition to that change, as previously noted, I needed to create a link to pgAdmin4.wsgi to allow gunicorn to pick it up. I change the name I used so I ended up with the link being "wsgi.py" -> "pgAdmin4.py", so the the corresponding gunicorn command is something like this:
gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application
Now, IIUC, the notion of SCRIPT_NAME is somewhat standard, and needed to solve this issue of running pgAdmin in server mode, but sharing the domain with other applications. Would there be interest in making the needed code an integral part of pgAdmin? If so, I'd be happy to file a feature request.
Hi
On Fri, Mar 1, 2019 at 9:30 AM Shaheed Haque <srhaque@theiet.org> wrote:
OK, I got it working. This is how...Thanks, Shaheed
On Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote:
>
> Hi,
>
> I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other things working (a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck getting pgAdmin4 to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to do this (I'm running the latest, v4.2, of pgAdmin4)?
>
> I'm aware of threads such as https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local, and several others but not been able to come up with a clear approach:
>
> One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port 5050 directly behind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I've tried both (modelled on what I have working, and various references). One combination I tried was:
>
> - Creating a softlink from pgadmin4.py to pgAdmin4.wsgi
> - Using gunicorn to run the pgadmin4.py to a unix domain socket like this:
>
> $ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application
>
> - Serving behind nginx like this:
>
> location /pgadmin {
> rewrite ^/pgadmin/(.*) /$1 break;
> include proxy_params;
> proxy_pass http://unix:/home/ubuntu/pgadmin.sock;
> }
>
> But all I get is a stubborn 404. Any pointers welcome...
First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). The next problem was with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser looking for top level URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a piece of code in https://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hacking a small wodge of code into pgAdmin4.py.
The SCRIPT_NAME is set by an nginx fragment like this:
location /pgadmin {
rewrite ^/pgadmin/(.*)$ /\$1 break;
include proxy_params;
proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock;
proxy_set_header X-Script-Name /pgadmin;
}In addition to that change, as previously noted, I needed to create a link to pgAdmin4.wsgi to allow gunicorn to pick it up. I change the name I used so I ended up with the link being "wsgi.py" -> "pgAdmin4.py", so the the corresponding gunicorn command is something like this:gunicorn ... --bind unix:/.../pgadmin.sock wsgi:applicationNow, IIUC, the notion of SCRIPT_NAME is somewhat standard, and needed to solve this issue of running pgAdmin in server mode, but sharing the domain with other applications. Would there be interest in making the needed code an integral part of pgAdmin? If so, I'd be happy to file a feature request.
Thanks for your work on this.
I've committed a change to add the reverse proxy code, and to put some more examples in the docs: https://redmine.postgresql.org/projects/pgadmin4/repository/revisions/f401def044c8b47974d58c71ff9e6f71f34ef41d
FWIW, I think the reason that this was an issue for so long is that you don't need the extra code if you use mod_wsgi or uWSGI - it's only needed with Gunicorn. I'll let you guess which of those technologies I'm most familiar with!
Unfortunately this commit won't make the 4.3 release later this week, but it will be in 4.4. The instructions will still be good for scenarios other than Gunicorn in a sub-directory though.
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Dave,
Thanks for taking this forward, I look forward to 4.4! I also noted with interest that we can use "pgAdmin4:app" rather than my hacky link to the pgAdmin4.wsgi, so that means a completely hack-free solution :-).
Shaheed
On Mon, 4 Mar 2019 at 16:33, Dave Page <dpage@pgadmin.org> wrote:
HiOn Fri, Mar 1, 2019 at 9:30 AM Shaheed Haque <srhaque@theiet.org> wrote:OK, I got it working. This is how...Thanks, Shaheed
On Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote:
>
> Hi,
>
> I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other things working (a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck getting pgAdmin4 to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to do this (I'm running the latest, v4.2, of pgAdmin4)?
>
> I'm aware of threads such as https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local, and several others but not been able to come up with a clear approach:
>
> One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port 5050 directly behind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I've tried both (modelled on what I have working, and various references). One combination I tried was:
>
> - Creating a softlink from pgadmin4.py to pgAdmin4.wsgi
> - Using gunicorn to run the pgadmin4.py to a unix domain socket like this:
>
> $ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application
>
> - Serving behind nginx like this:
>
> location /pgadmin {
> rewrite ^/pgadmin/(.*) /$1 break;
> include proxy_params;
> proxy_pass http://unix:/home/ubuntu/pgadmin.sock;
> }
>
> But all I get is a stubborn 404. Any pointers welcome...
First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). The next problem was with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser looking for top level URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a piece of code in https://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hacking a small wodge of code into pgAdmin4.py.
The SCRIPT_NAME is set by an nginx fragment like this:
location /pgadmin {
rewrite ^/pgadmin/(.*)$ /\$1 break;
include proxy_params;
proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock;
proxy_set_header X-Script-Name /pgadmin;
}In addition to that change, as previously noted, I needed to create a link to pgAdmin4.wsgi to allow gunicorn to pick it up. I change the name I used so I ended up with the link being "wsgi.py" -> "pgAdmin4.py", so the the corresponding gunicorn command is something like this:gunicorn ... --bind unix:/.../pgadmin.sock wsgi:applicationNow, IIUC, the notion of SCRIPT_NAME is somewhat standard, and needed to solve this issue of running pgAdmin in server mode, but sharing the domain with other applications. Would there be interest in making the needed code an integral part of pgAdmin? If so, I'd be happy to file a feature request.Thanks for your work on this.I've committed a change to add the reverse proxy code, and to put some more examples in the docs: https://redmine.postgresql.org/projects/pgadmin4/repository/revisions/f401def044c8b47974d58c71ff9e6f71f34ef41dFWIW, I think the reason that this was an issue for so long is that you don't need the extra code if you use mod_wsgi or uWSGI - it's only needed with Gunicorn. I'll let you guess which of those technologies I'm most familiar with!Unfortunately this commit won't make the 4.3 release later this week, but it will be in 4.4. The instructions will still be good for scenarios other than Gunicorn in a sub-directory though.--Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
You're welcome. Sorry it took so long to realise the main issue was only applicable to Gunicorn!
On Mon, Mar 4, 2019 at 6:29 PM Shaheed Haque <srhaque@theiet.org> wrote:
Dave,Thanks for taking this forward, I look forward to 4.4! I also noted with interest that we can use "pgAdmin4:app" rather than my hacky link to the pgAdmin4.wsgi, so that means a completely hack-free solution :-).ShaheedOn Mon, 4 Mar 2019 at 16:33, Dave Page <dpage@pgadmin.org> wrote:HiOn Fri, Mar 1, 2019 at 9:30 AM Shaheed Haque <srhaque@theiet.org> wrote:OK, I got it working. This is how...Thanks, Shaheed
On Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote:
>
> Hi,
>
> I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other things working (a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck getting pgAdmin4 to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to do this (I'm running the latest, v4.2, of pgAdmin4)?
>
> I'm aware of threads such as https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local, and several others but not been able to come up with a clear approach:
>
> One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port 5050 directly behind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I've tried both (modelled on what I have working, and various references). One combination I tried was:
>
> - Creating a softlink from pgadmin4.py to pgAdmin4.wsgi
> - Using gunicorn to run the pgadmin4.py to a unix domain socket like this:
>
> $ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application
>
> - Serving behind nginx like this:
>
> location /pgadmin {
> rewrite ^/pgadmin/(.*) /$1 break;
> include proxy_params;
> proxy_pass http://unix:/home/ubuntu/pgadmin.sock;
> }
>
> But all I get is a stubborn 404. Any pointers welcome...
First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). The next problem was with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser looking for top level URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a piece of code in https://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hacking a small wodge of code into pgAdmin4.py.
The SCRIPT_NAME is set by an nginx fragment like this:
location /pgadmin {
rewrite ^/pgadmin/(.*)$ /\$1 break;
include proxy_params;
proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock;
proxy_set_header X-Script-Name /pgadmin;
}In addition to that change, as previously noted, I needed to create a link to pgAdmin4.wsgi to allow gunicorn to pick it up. I change the name I used so I ended up with the link being "wsgi.py" -> "pgAdmin4.py", so the the corresponding gunicorn command is something like this:gunicorn ... --bind unix:/.../pgadmin.sock wsgi:applicationNow, IIUC, the notion of SCRIPT_NAME is somewhat standard, and needed to solve this issue of running pgAdmin in server mode, but sharing the domain with other applications. Would there be interest in making the needed code an integral part of pgAdmin? If so, I'd be happy to file a feature request.Thanks for your work on this.I've committed a change to add the reverse proxy code, and to put some more examples in the docs: https://redmine.postgresql.org/projects/pgadmin4/repository/revisions/f401def044c8b47974d58c71ff9e6f71f34ef41dFWIW, I think the reason that this was an issue for so long is that you don't need the extra code if you use mod_wsgi or uWSGI - it's only needed with Gunicorn. I'll let you guess which of those technologies I'm most familiar with!Unfortunately this commit won't make the 4.3 release later this week, but it will be in 4.4. The instructions will still be good for scenarios other than Gunicorn in a sub-directory though.--Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Hi Dave,
I'm just migrating to the fixes in 4.4. Can I just double check one
thing? In my original hack, I needed to create a link "wsgi.py" ->
"pgAdmin4.py", so the the corresponding gunicorn command is something
like this:
gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application
Now, in the documentation updates as part of this fix, the last
argument to that line reads "pgAdmin4:app" and not "wsgi:application".
The reason I though I needed to use the wsgi.py file is that it
contains these lines amongst other:
# Ensure the global server mode is set.
builtins.SERVER_MODE = True
I'm not sure if, by following the updated docs, I am missing a
necessary setting?
Thanks, Shaheed
On Tue, 5 Mar 2019 at 09:11, Dave Page <dpage@pgadmin.org> wrote:
>
> You're welcome. Sorry it took so long to realise the main issue was only applicable to Gunicorn!
>
> On Mon, Mar 4, 2019 at 6:29 PM Shaheed Haque <srhaque@theiet.org> wrote:
>>
>> Dave,
>>
>> Thanks for taking this forward, I look forward to 4.4! I also noted with interest that we can use "pgAdmin4:app"
ratherthan my hacky link to the pgAdmin4.wsgi, so that means a completely hack-free solution :-).
>>
>>
>> Shaheed
>>
>> On Mon, 4 Mar 2019 at 16:33, Dave Page <dpage@pgadmin.org> wrote:
>>>
>>> Hi
>>>
>>> On Fri, Mar 1, 2019 at 9:30 AM Shaheed Haque <srhaque@theiet.org> wrote:
>>>>
>>>> OK, I got it working. This is how...
>>>>
>>>> On Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote:
>>>> >
>>>> > Hi,
>>>> >
>>>> > I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other things
working(a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck getting
pgAdmin4to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to do this
(I'mrunning the latest, v4.2, of pgAdmin4)?
>>>> >
>>>> > I'm aware of threads such as
https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local,and several
othersbut not been able to come up with a clear approach:
>>>> >
>>>> > One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port
5050directly behind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I've
triedboth (modelled on what I have working, and various references). One combination I tried was:
>>>> >
>>>> > - Creating a softlink from pgadmin4.py to pgAdmin4.wsgi
>>>> > - Using gunicorn to run the pgadmin4.py to a unix domain socket like this:
>>>> >
>>>> > $ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application
>>>> >
>>>> > - Serving behind nginx like this:
>>>> >
>>>> > location /pgadmin {
>>>> > rewrite ^/pgadmin/(.*) /$1 break;
>>>> > include proxy_params;
>>>> > proxy_pass http://unix:/home/ubuntu/pgadmin.sock;
>>>> > }
>>>> >
>>>> > But all I get is a stubborn 404. Any pointers welcome...
>>>>
>>>> First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). The next
problemwas with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser looking for
toplevel URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a piece of code
inhttps://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hacking a small
wodgeof code into pgAdmin4.py.
>>>>
>>>> The SCRIPT_NAME is set by an nginx fragment like this:
>>>>
>>>> location /pgadmin {
>>>> rewrite ^/pgadmin/(.*)$ /\$1 break;
>>>> include proxy_params;
>>>> proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock;
>>>> proxy_set_header X-Script-Name /pgadmin;
>>>> }
>>>>
>>>> In addition to that change, as previously noted, I needed to create a link to pgAdmin4.wsgi to allow gunicorn to
pickit up. I change the name I used so I ended up with the link being "wsgi.py" -> "pgAdmin4.py", so the the
correspondinggunicorn command is something like this:
>>>>
>>>> gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application
>>>>
>>>> Now, IIUC, the notion of SCRIPT_NAME is somewhat standard, and needed to solve this issue of running pgAdmin in
servermode, but sharing the domain with other applications. Would there be interest in making the needed code an
integralpart of pgAdmin? If so, I'd be happy to file a feature request.
>>>>
>>>> Thanks, Shaheed
>>>
>>>
>>> Thanks for your work on this.
>>>
>>> I've committed a change to add the reverse proxy code, and to put some more examples in the docs:
https://redmine.postgresql.org/projects/pgadmin4/repository/revisions/f401def044c8b47974d58c71ff9e6f71f34ef41d
>>>
>>> FWIW, I think the reason that this was an issue for so long is that you don't need the extra code if you use
mod_wsgior uWSGI - it's only needed with Gunicorn. I'll let you guess which of those technologies I'm most familiar
with!
>>>
>>> Unfortunately this commit won't make the 4.3 release later this week, but it will be in 4.4. The instructions will
stillbe good for scenarios other than Gunicorn in a sub-directory though.
>>>
>>> --
>>> Dave Page
>>> Blog: http://pgsnake.blogspot.com
>>> Twitter: @pgsnake
>>>
>>> EnterpriseDB UK: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>
>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
Hi
It worked fine when I tested it. If you don't get any errors and you get a login prompt, it should all be good.
On Thu, Apr 4, 2019 at 3:01 PM Shaheed Haque <shaheedhaque@gmail.com> wrote:
Hi Dave,
I'm just migrating to the fixes in 4.4. Can I just double check one
thing? In my original hack, I needed to create a link "wsgi.py" ->
"pgAdmin4.py", so the the corresponding gunicorn command is something
like this:
gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application
Now, in the documentation updates as part of this fix, the last
argument to that line reads "pgAdmin4:app" and not "wsgi:application".
The reason I though I needed to use the wsgi.py file is that it
contains these lines amongst other:
# Ensure the global server mode is set.
builtins.SERVER_MODE = True
I'm not sure if, by following the updated docs, I am missing a
necessary setting?
Thanks, Shaheed
On Tue, 5 Mar 2019 at 09:11, Dave Page <dpage@pgadmin.org> wrote:
>
> You're welcome. Sorry it took so long to realise the main issue was only applicable to Gunicorn!
>
> On Mon, Mar 4, 2019 at 6:29 PM Shaheed Haque <srhaque@theiet.org> wrote:
>>
>> Dave,
>>
>> Thanks for taking this forward, I look forward to 4.4! I also noted with interest that we can use "pgAdmin4:app" rather than my hacky link to the pgAdmin4.wsgi, so that means a completely hack-free solution :-).
>>
>>
>> Shaheed
>>
>> On Mon, 4 Mar 2019 at 16:33, Dave Page <dpage@pgadmin.org> wrote:
>>>
>>> Hi
>>>
>>> On Fri, Mar 1, 2019 at 9:30 AM Shaheed Haque <srhaque@theiet.org> wrote:
>>>>
>>>> OK, I got it working. This is how...
>>>>
>>>> On Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote:
>>>> >
>>>> > Hi,
>>>> >
>>>> > I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other things working (a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck getting pgAdmin4 to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to do this (I'm running the latest, v4.2, of pgAdmin4)?
>>>> >
>>>> > I'm aware of threads such as https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local, and several others but not been able to come up with a clear approach:
>>>> >
>>>> > One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port 5050 directly behind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I've tried both (modelled on what I have working, and various references). One combination I tried was:
>>>> >
>>>> > - Creating a softlink from pgadmin4.py to pgAdmin4.wsgi
>>>> > - Using gunicorn to run the pgadmin4.py to a unix domain socket like this:
>>>> >
>>>> > $ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application
>>>> >
>>>> > - Serving behind nginx like this:
>>>> >
>>>> > location /pgadmin {
>>>> > rewrite ^/pgadmin/(.*) /$1 break;
>>>> > include proxy_params;
>>>> > proxy_pass http://unix:/home/ubuntu/pgadmin.sock;
>>>> > }
>>>> >
>>>> > But all I get is a stubborn 404. Any pointers welcome...
>>>>
>>>> First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). The next problem was with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser looking for top level URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a piece of code in https://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hacking a small wodge of code into pgAdmin4.py.
>>>>
>>>> The SCRIPT_NAME is set by an nginx fragment like this:
>>>>
>>>> location /pgadmin {
>>>> rewrite ^/pgadmin/(.*)$ /\$1 break;
>>>> include proxy_params;
>>>> proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock;
>>>> proxy_set_header X-Script-Name /pgadmin;
>>>> }
>>>>
>>>> In addition to that change, as previously noted, I needed to create a link to pgAdmin4.wsgi to allow gunicorn to pick it up. I change the name I used so I ended up with the link being "wsgi.py" -> "pgAdmin4.py", so the the corresponding gunicorn command is something like this:
>>>>
>>>> gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application
>>>>
>>>> Now, IIUC, the notion of SCRIPT_NAME is somewhat standard, and needed to solve this issue of running pgAdmin in server mode, but sharing the domain with other applications. Would there be interest in making the needed code an integral part of pgAdmin? If so, I'd be happy to file a feature request.
>>>>
>>>> Thanks, Shaheed
>>>
>>>
>>> Thanks for your work on this.
>>>
>>> I've committed a change to add the reverse proxy code, and to put some more examples in the docs: https://redmine.postgresql.org/projects/pgadmin4/repository/revisions/f401def044c8b47974d58c71ff9e6f71f34ef41d
>>>
>>> FWIW, I think the reason that this was an issue for so long is that you don't need the extra code if you use mod_wsgi or uWSGI - it's only needed with Gunicorn. I'll let you guess which of those technologies I'm most familiar with!
>>>
>>> Unfortunately this commit won't make the 4.3 release later this week, but it will be in 4.4. The instructions will still be good for scenarios other than Gunicorn in a sub-directory though.
>>>
>>> --
>>> Dave Page
>>> Blog: http://pgsnake.blogspot.com
>>> Twitter: @pgsnake
>>>
>>> EnterpriseDB UK: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>
>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
I can login, but when I click on "Servers", nothing happens on screen
and the error log gets this:
[2019-04-04 16:10:10 +0000] [5850] [INFO] Starting gunicorn 19.9.0
[2019-04-04 16:10:10 +0000] [5850] [INFO] Listening at:
unix:/home/ubuntu/pgadmin.sock (5850)
[2019-04-04 16:10:10 +0000] [5850] [INFO] Using worker: gthread
[2019-04-04 16:10:10 +0000] [5853] [INFO] Booting worker with pid: 5853
2019-04-04 16:10:25,732: ERROR flask.app:
'psycopg2.extensions.Column' object has no attribute '_asdict'
Traceback (most recent call last):
File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line
1813, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line
1799, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/usr/local/lib/python3.6/dist-packages/flask/views.py", line 88, in view
return self.dispatch_request(*args, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/utils.py",
line 259, in dispatch_request
return method(*args, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/utils.py",
line 309, in children
children.extend(module.get_nodes(*args, **kwargs))
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/server_groups/servers/__init__.py",
line 127, in get_nodes
in_recovery, wal_paused = recovery_state(conn, manager.version)
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/server_groups/servers/__init__.py",
line 52, in recovery_state
status, result = connection.execute_dict(recovery_check_sql)
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py",
line 1202, in execute_dict
desc.to_dict() for desc in cur.ordered_description()
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py",
line 1202, in <listcomp>
desc.to_dict() for desc in cur.ordered_description()
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/cursor.py",
line 94, in to_dict
ores = OrderedDict(self.orig_col._asdict())
AttributeError: 'psycopg2.extensions.Column' object has no attribute '_asdict'
It seems as though the version of psycopg2 is not what is expected,
rather than anything to do with "builtins.SERVER_MODE = True"? By way
of full disclosure, I do in fact have both psycopg2 AND
psycopg2-binary installed [1] but some quick experiments removing one
or other did not solve the problem.
Have I overlooked something?
Thanks, Shaheed
[1] this came up in another discussion recently, and it seems that I
ought to remove the psycopg2-binary though 4.2 was OK with this setup.
On Thu, 4 Apr 2019 at 15:04, Dave Page <dpage@pgadmin.org> wrote:
>
> Hi
>
> It worked fine when I tested it. If you don't get any errors and you get a login prompt, it should all be good.
>
> On Thu, Apr 4, 2019 at 3:01 PM Shaheed Haque <shaheedhaque@gmail.com> wrote:
>>
>> Hi Dave,
>>
>> I'm just migrating to the fixes in 4.4. Can I just double check one
>> thing? In my original hack, I needed to create a link "wsgi.py" ->
>> "pgAdmin4.py", so the the corresponding gunicorn command is something
>> like this:
>>
>> gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application
>>
>> Now, in the documentation updates as part of this fix, the last
>> argument to that line reads "pgAdmin4:app" and not "wsgi:application".
>> The reason I though I needed to use the wsgi.py file is that it
>> contains these lines amongst other:
>>
>> # Ensure the global server mode is set.
>> builtins.SERVER_MODE = True
>>
>> I'm not sure if, by following the updated docs, I am missing a
>> necessary setting?
>>
>> Thanks, Shaheed
>>
>> On Tue, 5 Mar 2019 at 09:11, Dave Page <dpage@pgadmin.org> wrote:
>> >
>> > You're welcome. Sorry it took so long to realise the main issue was only applicable to Gunicorn!
>> >
>> > On Mon, Mar 4, 2019 at 6:29 PM Shaheed Haque <srhaque@theiet.org> wrote:
>> >>
>> >> Dave,
>> >>
>> >> Thanks for taking this forward, I look forward to 4.4! I also noted with interest that we can use "pgAdmin4:app"
ratherthan my hacky link to the pgAdmin4.wsgi, so that means a completely hack-free solution :-).
>> >>
>> >>
>> >> Shaheed
>> >>
>> >> On Mon, 4 Mar 2019 at 16:33, Dave Page <dpage@pgadmin.org> wrote:
>> >>>
>> >>> Hi
>> >>>
>> >>> On Fri, Mar 1, 2019 at 9:30 AM Shaheed Haque <srhaque@theiet.org> wrote:
>> >>>>
>> >>>> OK, I got it working. This is how...
>> >>>>
>> >>>> On Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote:
>> >>>> >
>> >>>> > Hi,
>> >>>> >
>> >>>> > I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other
thingsworking (a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck
gettingpgAdmin4 to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to
dothis (I'm running the latest, v4.2, of pgAdmin4)?
>> >>>> >
>> >>>> > I'm aware of threads such as
https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local,and several
othersbut not been able to come up with a clear approach:
>> >>>> >
>> >>>> > One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port
5050directly behind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I've
triedboth (modelled on what I have working, and various references). One combination I tried was:
>> >>>> >
>> >>>> > - Creating a softlink from pgadmin4.py to pgAdmin4.wsgi
>> >>>> > - Using gunicorn to run the pgadmin4.py to a unix domain socket like this:
>> >>>> >
>> >>>> > $ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application
>> >>>> >
>> >>>> > - Serving behind nginx like this:
>> >>>> >
>> >>>> > location /pgadmin {
>> >>>> > rewrite ^/pgadmin/(.*) /$1 break;
>> >>>> > include proxy_params;
>> >>>> > proxy_pass http://unix:/home/ubuntu/pgadmin.sock;
>> >>>> > }
>> >>>> >
>> >>>> > But all I get is a stubborn 404. Any pointers welcome...
>> >>>>
>> >>>> First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). The
nextproblem was with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser looking
fortop level URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a piece of
codein https://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hacking a
smallwodge of code into pgAdmin4.py.
>> >>>>
>> >>>> The SCRIPT_NAME is set by an nginx fragment like this:
>> >>>>
>> >>>> location /pgadmin {
>> >>>> rewrite ^/pgadmin/(.*)$ /\$1 break;
>> >>>> include proxy_params;
>> >>>> proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock;
>> >>>> proxy_set_header X-Script-Name /pgadmin;
>> >>>> }
>> >>>>
>> >>>> In addition to that change, as previously noted, I needed to create a link to pgAdmin4.wsgi to allow gunicorn
topick it up. I change the name I used so I ended up with the link being "wsgi.py" -> "pgAdmin4.py", so the the
correspondinggunicorn command is something like this:
>> >>>>
>> >>>> gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application
>> >>>>
>> >>>> Now, IIUC, the notion of SCRIPT_NAME is somewhat standard, and needed to solve this issue of running pgAdmin in
servermode, but sharing the domain with other applications. Would there be interest in making the needed code an
integralpart of pgAdmin? If so, I'd be happy to file a feature request.
>> >>>>
>> >>>> Thanks, Shaheed
>> >>>
>> >>>
>> >>> Thanks for your work on this.
>> >>>
>> >>> I've committed a change to add the reverse proxy code, and to put some more examples in the docs:
https://redmine.postgresql.org/projects/pgadmin4/repository/revisions/f401def044c8b47974d58c71ff9e6f71f34ef41d
>> >>>
>> >>> FWIW, I think the reason that this was an issue for so long is that you don't need the extra code if you use
mod_wsgior uWSGI - it's only needed with Gunicorn. I'll let you guess which of those technologies I'm most familiar
with!
>> >>>
>> >>> Unfortunately this commit won't make the 4.3 release later this week, but it will be in 4.4. The instructions
willstill be good for scenarios other than Gunicorn in a sub-directory though.
>> >>>
>> >>> --
>> >>> Dave Page
>> >>> Blog: http://pgsnake.blogspot.com
>> >>> Twitter: @pgsnake
>> >>>
>> >>> EnterpriseDB UK: http://www.enterprisedb.com
>> >>> The Enterprise PostgreSQL Company
>> >
>> >
>> >
>> > --
>> > Dave Page
>> > Blog: http://pgsnake.blogspot.com
>> > Twitter: @pgsnake
>> >
>> > EnterpriseDB UK: http://www.enterprisedb.com
>> > The Enterprise PostgreSQL Company
>
>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
Woah, rabbithole time... On Thu, 4 Apr 2019 at 17:33, Shaheed Haque <shaheedhaque@gmail.com> wrote: > > I can login, but when I click on "Servers", nothing happens on screen > and the error log gets this: > > [2019-04-04 16:10:10 +0000] [5850] [INFO] Starting gunicorn 19.9.0 > [2019-04-04 16:10:10 +0000] [5850] [INFO] Listening at: > unix:/home/ubuntu/pgadmin.sock (5850) > [2019-04-04 16:10:10 +0000] [5850] [INFO] Using worker: gthread > [2019-04-04 16:10:10 +0000] [5853] [INFO] Booting worker with pid: 5853 > 2019-04-04 16:10:25,732: ERROR flask.app: > 'psycopg2.extensions.Column' object has no attribute '_asdict' > Traceback (most recent call last): > File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line > 1813, in full_dispatch_request > rv = self.dispatch_request() > File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line > 1799, in dispatch_request > return self.view_functions[rule.endpoint](**req.view_args) > File "/usr/local/lib/python3.6/dist-packages/flask/views.py", line 88, in view > return self.dispatch_request(*args, **kwargs) > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/utils.py", > line 259, in dispatch_request > return method(*args, **kwargs) > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/utils.py", > line 309, in children > children.extend(module.get_nodes(*args, **kwargs)) > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/server_groups/servers/__init__.py", > line 127, in get_nodes > in_recovery, wal_paused = recovery_state(conn, manager.version) > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/server_groups/servers/__init__.py", > line 52, in recovery_state > status, result = connection.execute_dict(recovery_check_sql) > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py", > line 1202, in execute_dict > desc.to_dict() for desc in cur.ordered_description() > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py", > line 1202, in <listcomp> > desc.to_dict() for desc in cur.ordered_description() > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/cursor.py", > line 94, in to_dict > ores = OrderedDict(self.orig_col._asdict()) > AttributeError: 'psycopg2.extensions.Column' object has no attribute '_asdict' > > It seems as though the version of psycopg2 is not what is expected, > rather than anything to do with "builtins.SERVER_MODE = True"? By way > of full disclosure, I do in fact have both psycopg2 AND > psycopg2-binary installed [1] but some quick experiments removing one > or other did not solve the problem. > > Have I overlooked something? I reverted to 4.2 and started seeing the same error. Then I remembered that I was prompted to upgrade to 4.4 by *another* issue which was that from today (as in, this was not happening yesterday) my install of the 4.2 wheel started failing with complaints about a missing libpq-dev. I had addressed that by simply "apt install libpq-dev", implying it is the header files in this library development support package that are the root of the problem. I'm unclear what to try next. Maybe get rid of the psycopg-binary/psycopg duplication will help? At any rate, I am dead in the water... Clues appreciated! Thanks, Shaheed > Thanks, Shaheed > > [1] this came up in another discussion recently, and it seems that I > ought to remove the psycopg2-binary though 4.2 was OK with this setup. > > On Thu, 4 Apr 2019 at 15:04, Dave Page <dpage@pgadmin.org> wrote: > > > > Hi > > > > It worked fine when I tested it. If you don't get any errors and you get a login prompt, it should all be good. > > > > On Thu, Apr 4, 2019 at 3:01 PM Shaheed Haque <shaheedhaque@gmail.com> wrote: > >> > >> Hi Dave, > >> > >> I'm just migrating to the fixes in 4.4. Can I just double check one > >> thing? In my original hack, I needed to create a link "wsgi.py" -> > >> "pgAdmin4.py", so the the corresponding gunicorn command is something > >> like this: > >> > >> gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application > >> > >> Now, in the documentation updates as part of this fix, the last > >> argument to that line reads "pgAdmin4:app" and not "wsgi:application". > >> The reason I though I needed to use the wsgi.py file is that it > >> contains these lines amongst other: > >> > >> # Ensure the global server mode is set. > >> builtins.SERVER_MODE = True > >> > >> I'm not sure if, by following the updated docs, I am missing a > >> necessary setting? > >> > >> Thanks, Shaheed > >> > >> On Tue, 5 Mar 2019 at 09:11, Dave Page <dpage@pgadmin.org> wrote: > >> > > >> > You're welcome. Sorry it took so long to realise the main issue was only applicable to Gunicorn! > >> > > >> > On Mon, Mar 4, 2019 at 6:29 PM Shaheed Haque <srhaque@theiet.org> wrote: > >> >> > >> >> Dave, > >> >> > >> >> Thanks for taking this forward, I look forward to 4.4! I also noted with interest that we can use "pgAdmin4:app"rather than my hacky link to the pgAdmin4.wsgi, so that means a completely hack-free solution :-). > >> >> > >> >> > >> >> Shaheed > >> >> > >> >> On Mon, 4 Mar 2019 at 16:33, Dave Page <dpage@pgadmin.org> wrote: > >> >>> > >> >>> Hi > >> >>> > >> >>> On Fri, Mar 1, 2019 at 9:30 AM Shaheed Haque <srhaque@theiet.org> wrote: > >> >>>> > >> >>>> OK, I got it working. This is how... > >> >>>> > >> >>>> On Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote: > >> >>>> > > >> >>>> > Hi, > >> >>>> > > >> >>>> > I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other thingsworking (a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck gettingpgAdmin4 to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to dothis (I'm running the latest, v4.2, of pgAdmin4)? > >> >>>> > > >> >>>> > I'm aware of threads such as https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local,and several othersbut not been able to come up with a clear approach: > >> >>>> > > >> >>>> > One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port5050 directly behind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I'vetried both (modelled on what I have working, and various references). One combination I tried was: > >> >>>> > > >> >>>> > - Creating a softlink from pgadmin4.py to pgAdmin4.wsgi > >> >>>> > - Using gunicorn to run the pgadmin4.py to a unix domain socket like this: > >> >>>> > > >> >>>> > $ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application > >> >>>> > > >> >>>> > - Serving behind nginx like this: > >> >>>> > > >> >>>> > location /pgadmin { > >> >>>> > rewrite ^/pgadmin/(.*) /$1 break; > >> >>>> > include proxy_params; > >> >>>> > proxy_pass http://unix:/home/ubuntu/pgadmin.sock; > >> >>>> > } > >> >>>> > > >> >>>> > But all I get is a stubborn 404. Any pointers welcome... > >> >>>> > >> >>>> First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). The nextproblem was with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser looking fortop level URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a piece of codein https://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hacking a smallwodge of code into pgAdmin4.py. > >> >>>> > >> >>>> The SCRIPT_NAME is set by an nginx fragment like this: > >> >>>> > >> >>>> location /pgadmin { > >> >>>> rewrite ^/pgadmin/(.*)$ /\$1 break; > >> >>>> include proxy_params; > >> >>>> proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock; > >> >>>> proxy_set_header X-Script-Name /pgadmin; > >> >>>> } > >> >>>> > >> >>>> In addition to that change, as previously noted, I needed to create a link to pgAdmin4.wsgi to allow gunicorn topick it up. I change the name I used so I ended up with the link being "wsgi.py" -> "pgAdmin4.py", so the the correspondinggunicorn command is something like this: > >> >>>> > >> >>>> gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application > >> >>>> > >> >>>> Now, IIUC, the notion of SCRIPT_NAME is somewhat standard, and needed to solve this issue of running pgAdmin inserver mode, but sharing the domain with other applications. Would there be interest in making the needed code an integralpart of pgAdmin? If so, I'd be happy to file a feature request. > >> >>>> > >> >>>> Thanks, Shaheed > >> >>> > >> >>> > >> >>> Thanks for your work on this. > >> >>> > >> >>> I've committed a change to add the reverse proxy code, and to put some more examples in the docs: https://redmine.postgresql.org/projects/pgadmin4/repository/revisions/f401def044c8b47974d58c71ff9e6f71f34ef41d > >> >>> > >> >>> FWIW, I think the reason that this was an issue for so long is that you don't need the extra code if you use mod_wsgior uWSGI - it's only needed with Gunicorn. I'll let you guess which of those technologies I'm most familiar with! > >> >>> > >> >>> Unfortunately this commit won't make the 4.3 release later this week, but it will be in 4.4. The instructions willstill be good for scenarios other than Gunicorn in a sub-directory though. > >> >>> > >> >>> -- > >> >>> Dave Page > >> >>> Blog: http://pgsnake.blogspot.com > >> >>> Twitter: @pgsnake > >> >>> > >> >>> EnterpriseDB UK: http://www.enterprisedb.com > >> >>> The Enterprise PostgreSQL Company > >> > > >> > > >> > > >> > -- > >> > Dave Page > >> > Blog: http://pgsnake.blogspot.com > >> > Twitter: @pgsnake > >> > > >> > EnterpriseDB UK: http://www.enterprisedb.com > >> > The Enterprise PostgreSQL Company > > > > > > > > -- > > Dave Page > > Blog: http://pgsnake.blogspot.com > > Twitter: @pgsnake > > > > EnterpriseDB UK: http://www.enterprisedb.com > > The Enterprise PostgreSQL Company
Downgrade psycopg2 to 2.7.7 (or whatever came before 2.8). Apparently they broke backwards compatibility:-(
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK:http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK:http://www.enterprisedb.com
The Enterprise PostgreSQL Company
I can login, but when I click on "Servers", nothing happens on screen
and the error log gets this:
[2019-04-04 16:10:10 +0000] [5850] [INFO] Starting gunicorn 19.9.0
[2019-04-04 16:10:10 +0000] [5850] [INFO] Listening at:
unix:/home/ubuntu/pgadmin.sock (5850)
[2019-04-04 16:10:10 +0000] [5850] [INFO] Using worker: gthread
[2019-04-04 16:10:10 +0000] [5853] [INFO] Booting worker with pid: 5853
2019-04-04 16:10:25,732: ERROR flask.app:
'psycopg2.extensions.Column' object has no attribute '_asdict'
Traceback (most recent call last):
File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line
1813, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line
1799, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/usr/local/lib/python3.6/dist-packages/flask/views.py", line 88, in view
return self.dispatch_request(*args, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/utils.py",
line 259, in dispatch_request
return method(*args, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/utils.py",
line 309, in children
children.extend(module.get_nodes(*args, **kwargs))
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/server_groups/servers/__init__.py",
line 127, in get_nodes
in_recovery, wal_paused = recovery_state(conn, manager.version)
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/server_groups/servers/__init__.py",
line 52, in recovery_state
status, result = connection.execute_dict(recovery_check_sql)
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py",
line 1202, in execute_dict
desc.to_dict() for desc in cur.ordered_description()
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py",
line 1202, in <listcomp>
desc.to_dict() for desc in cur.ordered_description()
File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/cursor.py",
line 94, in to_dict
ores = OrderedDict(self.orig_col._asdict())
AttributeError: 'psycopg2.extensions.Column' object has no attribute '_asdict'
It seems as though the version of psycopg2 is not what is expected,
rather than anything to do with "builtins.SERVER_MODE = True"? By way
of full disclosure, I do in fact have both psycopg2 AND
psycopg2-binary installed [1] but some quick experiments removing one
or other did not solve the problem.
Have I overlooked something?
Thanks, Shaheed
[1] this came up in another discussion recently, and it seems that I
ought to remove the psycopg2-binary though 4.2 was OK with this setup.
On Thu, 4 Apr 2019 at 15:04, Dave Page <dpage@pgadmin.org> wrote:HiIt worked fine when I tested it. If you don't get any errors and you get a login prompt, it should all be good.On Thu, Apr 4, 2019 at 3:01 PM Shaheed Haque <shaheedhaque@gmail.com> wrote:Hi Dave,I'm just migrating to the fixes in 4.4. Can I just double check onething? In my original hack, I needed to create a link "wsgi.py" ->"pgAdmin4.py", so the the corresponding gunicorn command is somethinglike this:gunicorn ... --bind unix:/.../pgadmin.sock wsgi:applicationNow, in the documentation updates as part of this fix, the lastargument to that line reads "pgAdmin4:app" and not "wsgi:application".The reason I though I needed to use the wsgi.py file is that itcontains these lines amongst other:# Ensure the global server mode is set.builtins.SERVER_MODE = TrueI'm not sure if, by following the updated docs, I am missing anecessary setting?Thanks, ShaheedOn Tue, 5 Mar 2019 at 09:11, Dave Page <dpage@pgadmin.org> wrote:You're welcome. Sorry it took so long to realise the main issue was only applicable to Gunicorn!On Mon, Mar 4, 2019 at 6:29 PM Shaheed Haque <srhaque@theiet.org> wrote:Dave,Thanks for taking this forward, I look forward to 4.4! I also noted with interest that we can use "pgAdmin4:app" rather than my hacky link to the pgAdmin4.wsgi, so that means a completely hack-free solution :-).ShaheedOn Mon, 4 Mar 2019 at 16:33, Dave Page <dpage@pgadmin.org> wrote:HiOn Fri, Mar 1, 2019 at 9:30 AM Shaheed Haque <srhaque@theiet.org> wrote:OK, I got it working. This is how...On Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote:Hi,I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other things working (a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck getting pgAdmin4 to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to do this (I'm running the latest, v4.2, of pgAdmin4)?I'm aware of threads such as https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local, and several others but not been able to come up with a clear approach:One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port 5050 directly behind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I've tried both (modelled on what I have working, and various references). One combination I tried was:- Creating a softlink from pgadmin4.py to pgAdmin4.wsgi- Using gunicorn to run the pgadmin4.py to a unix domain socket like this:$ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application- Serving behind nginx like this:location /pgadmin {rewrite ^/pgadmin/(.*) /$1 break;include proxy_params;proxy_pass http://unix:/home/ubuntu/pgadmin.sock;}But all I get is a stubborn 404. Any pointers welcome...First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). The next problem was with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser looking for top level URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a piece of code in https://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hacking a small wodge of code into pgAdmin4.py.The SCRIPT_NAME is set by an nginx fragment like this:location /pgadmin {rewrite ^/pgadmin/(.*)$ /\$1 break;include proxy_params;proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock;proxy_set_header X-Script-Name /pgadmin;}In addition to that change, as previously noted, I needed to create a link to pgAdmin4.wsgi to allow gunicorn to pick it up. I change the name I used so I ended up with the link being "wsgi.py" -> "pgAdmin4.py", so the the corresponding gunicorn command is something like this:gunicorn ... --bind unix:/.../pgadmin.sock wsgi:applicationNow, IIUC, the notion of SCRIPT_NAME is somewhat standard, and needed to solve this issue of running pgAdmin in server mode, but sharing the domain with other applications. Would there be interest in making the needed code an integral part of pgAdmin? If so, I'd be happy to file a feature request.Thanks, ShaheedThanks for your work on this.I've committed a change to add the reverse proxy code, and to put some more examples in the docs: https://redmine.postgresql.org/projects/pgadmin4/repository/revisions/f401def044c8b47974d58c71ff9e6f71f34ef41dFWIW, I think the reason that this was an issue for so long is that you don't need the extra code if you use mod_wsgi or uWSGI - it's only needed with Gunicorn. I'll let you guess which of those technologies I'm most familiar with!Unfortunately this commit won't make the 4.3 release later this week, but it will be in 4.4. The instructions will still be good for scenarios other than Gunicorn in a sub-directory though.--Dave PageBlog: http://pgsnake.blogspot.comTwitter: @pgsnakeEnterpriseDB UK: http://www.enterprisedb.comThe Enterprise PostgreSQL Company--Dave PageBlog: http://pgsnake.blogspot.comTwitter: @pgsnakeEnterpriseDB UK: http://www.enterprisedb.comThe Enterprise PostgreSQL Company--Dave PageBlog: http://pgsnake.blogspot.comTwitter: @pgsnakeEnterpriseDB UK: http://www.enterprisedb.comThe Enterprise PostgreSQL Company
On Thu, 4 Apr 2019 at 17:57, Shaheed Haque <shaheedhaque@gmail.com> wrote: > > Woah, rabbithole time... > > On Thu, 4 Apr 2019 at 17:33, Shaheed Haque <shaheedhaque@gmail.com> wrote: > > > > I can login, but when I click on "Servers", nothing happens on screen > > and the error log gets this: > > > > [2019-04-04 16:10:10 +0000] [5850] [INFO] Starting gunicorn 19.9.0 > > [2019-04-04 16:10:10 +0000] [5850] [INFO] Listening at: > > unix:/home/ubuntu/pgadmin.sock (5850) > > [2019-04-04 16:10:10 +0000] [5850] [INFO] Using worker: gthread > > [2019-04-04 16:10:10 +0000] [5853] [INFO] Booting worker with pid: 5853 > > 2019-04-04 16:10:25,732: ERROR flask.app: > > 'psycopg2.extensions.Column' object has no attribute '_asdict' > > Traceback (most recent call last): > > File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line > > 1813, in full_dispatch_request > > rv = self.dispatch_request() > > File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line > > 1799, in dispatch_request > > return self.view_functions[rule.endpoint](**req.view_args) > > File "/usr/local/lib/python3.6/dist-packages/flask/views.py", line 88, in view > > return self.dispatch_request(*args, **kwargs) > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/utils.py", > > line 259, in dispatch_request > > return method(*args, **kwargs) > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/utils.py", > > line 309, in children > > children.extend(module.get_nodes(*args, **kwargs)) > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/server_groups/servers/__init__.py", > > line 127, in get_nodes > > in_recovery, wal_paused = recovery_state(conn, manager.version) > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/server_groups/servers/__init__.py", > > line 52, in recovery_state > > status, result = connection.execute_dict(recovery_check_sql) > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py", > > line 1202, in execute_dict > > desc.to_dict() for desc in cur.ordered_description() > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py", > > line 1202, in <listcomp> > > desc.to_dict() for desc in cur.ordered_description() > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/cursor.py", > > line 94, in to_dict > > ores = OrderedDict(self.orig_col._asdict()) > > AttributeError: 'psycopg2.extensions.Column' object has no attribute '_asdict' > > > > It seems as though the version of psycopg2 is not what is expected, > > rather than anything to do with "builtins.SERVER_MODE = True"? By way > > of full disclosure, I do in fact have both psycopg2 AND > > psycopg2-binary installed [1] but some quick experiments removing one > > or other did not solve the problem. > > > > Have I overlooked something? > > I reverted to 4.2 and started seeing the same error. Then I remembered > that I was prompted to upgrade to 4.4 by *another* issue which was > that from today (as in, this was not happening yesterday) my install > of the 4.2 wheel started failing with complaints about a missing > libpq-dev. I had addressed that by simply "apt install libpq-dev", > implying it is the header files in this library development support > package that are the root of the problem. I was nearly right. The actual root cause seems to be that psycopg2 released 2.8 today, and that appears to be incompatible with pgAdmin 4.2 and 4.4. For now, I'll try a downgrade to psycopg2 2.7.7 (and get rid of the psycopg2-binary while I am at it). Hope that helps, Thanks, Shaheed > I'm unclear what to try next. Maybe get rid of the > psycopg-binary/psycopg duplication will help? At any rate, I am dead > in the water... > > Clues appreciated! > > Thanks, Shaheed > > > Thanks, Shaheed > > > > [1] this came up in another discussion recently, and it seems that I > > ought to remove the psycopg2-binary though 4.2 was OK with this setup. > > > > On Thu, 4 Apr 2019 at 15:04, Dave Page <dpage@pgadmin.org> wrote: > > > > > > Hi > > > > > > It worked fine when I tested it. If you don't get any errors and you get a login prompt, it should all be good. > > > > > > On Thu, Apr 4, 2019 at 3:01 PM Shaheed Haque <shaheedhaque@gmail.com> wrote: > > >> > > >> Hi Dave, > > >> > > >> I'm just migrating to the fixes in 4.4. Can I just double check one > > >> thing? In my original hack, I needed to create a link "wsgi.py" -> > > >> "pgAdmin4.py", so the the corresponding gunicorn command is something > > >> like this: > > >> > > >> gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application > > >> > > >> Now, in the documentation updates as part of this fix, the last > > >> argument to that line reads "pgAdmin4:app" and not "wsgi:application". > > >> The reason I though I needed to use the wsgi.py file is that it > > >> contains these lines amongst other: > > >> > > >> # Ensure the global server mode is set. > > >> builtins.SERVER_MODE = True > > >> > > >> I'm not sure if, by following the updated docs, I am missing a > > >> necessary setting? > > >> > > >> Thanks, Shaheed > > >> > > >> On Tue, 5 Mar 2019 at 09:11, Dave Page <dpage@pgadmin.org> wrote: > > >> > > > >> > You're welcome. Sorry it took so long to realise the main issue was only applicable to Gunicorn! > > >> > > > >> > On Mon, Mar 4, 2019 at 6:29 PM Shaheed Haque <srhaque@theiet.org> wrote: > > >> >> > > >> >> Dave, > > >> >> > > >> >> Thanks for taking this forward, I look forward to 4.4! I also noted with interest that we can use "pgAdmin4:app"rather than my hacky link to the pgAdmin4.wsgi, so that means a completely hack-free solution :-). > > >> >> > > >> >> > > >> >> Shaheed > > >> >> > > >> >> On Mon, 4 Mar 2019 at 16:33, Dave Page <dpage@pgadmin.org> wrote: > > >> >>> > > >> >>> Hi > > >> >>> > > >> >>> On Fri, Mar 1, 2019 at 9:30 AM Shaheed Haque <srhaque@theiet.org> wrote: > > >> >>>> > > >> >>>> OK, I got it working. This is how... > > >> >>>> > > >> >>>> On Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote: > > >> >>>> > > > >> >>>> > Hi, > > >> >>>> > > > >> >>>> > I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other thingsworking (a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck gettingpgAdmin4 to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to dothis (I'm running the latest, v4.2, of pgAdmin4)? > > >> >>>> > > > >> >>>> > I'm aware of threads such as https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local,and several othersbut not been able to come up with a clear approach: > > >> >>>> > > > >> >>>> > One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port5050 directly behind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I'vetried both (modelled on what I have working, and various references). One combination I tried was: > > >> >>>> > > > >> >>>> > - Creating a softlink from pgadmin4.py to pgAdmin4.wsgi > > >> >>>> > - Using gunicorn to run the pgadmin4.py to a unix domain socket like this: > > >> >>>> > > > >> >>>> > $ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application > > >> >>>> > > > >> >>>> > - Serving behind nginx like this: > > >> >>>> > > > >> >>>> > location /pgadmin { > > >> >>>> > rewrite ^/pgadmin/(.*) /$1 break; > > >> >>>> > include proxy_params; > > >> >>>> > proxy_pass http://unix:/home/ubuntu/pgadmin.sock; > > >> >>>> > } > > >> >>>> > > > >> >>>> > But all I get is a stubborn 404. Any pointers welcome... > > >> >>>> > > >> >>>> First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). Thenext problem was with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser lookingfor top level URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a pieceof code in https://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hackinga small wodge of code into pgAdmin4.py. > > >> >>>> > > >> >>>> The SCRIPT_NAME is set by an nginx fragment like this: > > >> >>>> > > >> >>>> location /pgadmin { > > >> >>>> rewrite ^/pgadmin/(.*)$ /\$1 break; > > >> >>>> include proxy_params; > > >> >>>> proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock; > > >> >>>> proxy_set_header X-Script-Name /pgadmin; > > >> >>>> } > > >> >>>> > > >> >>>> In addition to that change, as previously noted, I needed to create a link to pgAdmin4.wsgi to allow gunicornto pick it up. I change the name I used so I ended up with the link being "wsgi.py" -> "pgAdmin4.py", so the thecorresponding gunicorn command is something like this: > > >> >>>> > > >> >>>> gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application > > >> >>>> > > >> >>>> Now, IIUC, the notion of SCRIPT_NAME is somewhat standard, and needed to solve this issue of running pgAdminin server mode, but sharing the domain with other applications. Would there be interest in making the needed codean integral part of pgAdmin? If so, I'd be happy to file a feature request. > > >> >>>> > > >> >>>> Thanks, Shaheed > > >> >>> > > >> >>> > > >> >>> Thanks for your work on this. > > >> >>> > > >> >>> I've committed a change to add the reverse proxy code, and to put some more examples in the docs: https://redmine.postgresql.org/projects/pgadmin4/repository/revisions/f401def044c8b47974d58c71ff9e6f71f34ef41d > > >> >>> > > >> >>> FWIW, I think the reason that this was an issue for so long is that you don't need the extra code if you use mod_wsgior uWSGI - it's only needed with Gunicorn. I'll let you guess which of those technologies I'm most familiar with! > > >> >>> > > >> >>> Unfortunately this commit won't make the 4.3 release later this week, but it will be in 4.4. The instructionswill still be good for scenarios other than Gunicorn in a sub-directory though. > > >> >>> > > >> >>> -- > > >> >>> Dave Page > > >> >>> Blog: http://pgsnake.blogspot.com > > >> >>> Twitter: @pgsnake > > >> >>> > > >> >>> EnterpriseDB UK: http://www.enterprisedb.com > > >> >>> The Enterprise PostgreSQL Company > > >> > > > >> > > > >> > > > >> > -- > > >> > Dave Page > > >> > Blog: http://pgsnake.blogspot.com > > >> > Twitter: @pgsnake > > >> > > > >> > EnterpriseDB UK: http://www.enterprisedb.com > > >> > The Enterprise PostgreSQL Company > > > > > > > > > > > > -- > > > Dave Page > > > Blog: http://pgsnake.blogspot.com > > > Twitter: @pgsnake > > > > > > EnterpriseDB UK: http://www.enterprisedb.com > > > The Enterprise PostgreSQL Company
Thanks for the kind (and quick) replies. My last email crossed with the ones from Dave and Murtaza... On Thu, 4 Apr 2019 at 19:03, Dave Page <dpage@pgadmin.org> wrote: > > Downgrade psycopg2 to 2.7.7 (or whatever came before 2.8). Apparently they broke backwards compatibility:-( > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK:http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > On 4 Apr 2019, at 17:33, Shaheed Haque <shaheedhaque@gmail.com> wrote: > > I can login, but when I click on "Servers", nothing happens on screen > and the error log gets this: > > [2019-04-04 16:10:10 +0000] [5850] [INFO] Starting gunicorn 19.9.0 > [2019-04-04 16:10:10 +0000] [5850] [INFO] Listening at: > unix:/home/ubuntu/pgadmin.sock (5850) > [2019-04-04 16:10:10 +0000] [5850] [INFO] Using worker: gthread > [2019-04-04 16:10:10 +0000] [5853] [INFO] Booting worker with pid: 5853 > 2019-04-04 16:10:25,732: ERROR flask.app: > 'psycopg2.extensions.Column' object has no attribute '_asdict' > Traceback (most recent call last): > File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line > 1813, in full_dispatch_request > rv = self.dispatch_request() > File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line > 1799, in dispatch_request > return self.view_functions[rule.endpoint](**req.view_args) > File "/usr/local/lib/python3.6/dist-packages/flask/views.py", line 88, in view > return self.dispatch_request(*args, **kwargs) > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/utils.py", > line 259, in dispatch_request > return method(*args, **kwargs) > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/utils.py", > line 309, in children > children.extend(module.get_nodes(*args, **kwargs)) > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/server_groups/servers/__init__.py", > line 127, in get_nodes > in_recovery, wal_paused = recovery_state(conn, manager.version) > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/server_groups/servers/__init__.py", > line 52, in recovery_state > status, result = connection.execute_dict(recovery_check_sql) > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py", > line 1202, in execute_dict > desc.to_dict() for desc in cur.ordered_description() > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py", > line 1202, in <listcomp> > desc.to_dict() for desc in cur.ordered_description() > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/cursor.py", > line 94, in to_dict > ores = OrderedDict(self.orig_col._asdict()) > AttributeError: 'psycopg2.extensions.Column' object has no attribute '_asdict' > > It seems as though the version of psycopg2 is not what is expected, > rather than anything to do with "builtins.SERVER_MODE = True"? By way > of full disclosure, I do in fact have both psycopg2 AND > psycopg2-binary installed [1] but some quick experiments removing one > or other did not solve the problem. > > Have I overlooked something? > > Thanks, Shaheed > > [1] this came up in another discussion recently, and it seems that I > ought to remove the psycopg2-binary though 4.2 was OK with this setup. > > On Thu, 4 Apr 2019 at 15:04, Dave Page <dpage@pgadmin.org> wrote: > > > Hi > > > It worked fine when I tested it. If you don't get any errors and you get a login prompt, it should all be good. > > > On Thu, Apr 4, 2019 at 3:01 PM Shaheed Haque <shaheedhaque@gmail.com> wrote: > > > Hi Dave, > > > I'm just migrating to the fixes in 4.4. Can I just double check one > > thing? In my original hack, I needed to create a link "wsgi.py" -> > > "pgAdmin4.py", so the the corresponding gunicorn command is something > > like this: > > > gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application > > > Now, in the documentation updates as part of this fix, the last > > argument to that line reads "pgAdmin4:app" and not "wsgi:application". > > The reason I though I needed to use the wsgi.py file is that it > > contains these lines amongst other: > > > # Ensure the global server mode is set. > > builtins.SERVER_MODE = True > > > I'm not sure if, by following the updated docs, I am missing a > > necessary setting? > > > Thanks, Shaheed > > > On Tue, 5 Mar 2019 at 09:11, Dave Page <dpage@pgadmin.org> wrote: > > > You're welcome. Sorry it took so long to realise the main issue was only applicable to Gunicorn! > > > On Mon, Mar 4, 2019 at 6:29 PM Shaheed Haque <srhaque@theiet.org> wrote: > > > Dave, > > > Thanks for taking this forward, I look forward to 4.4! I also noted with interest that we can use "pgAdmin4:app" ratherthan my hacky link to the pgAdmin4.wsgi, so that means a completely hack-free solution :-). > > > > Shaheed > > > On Mon, 4 Mar 2019 at 16:33, Dave Page <dpage@pgadmin.org> wrote: > > > Hi > > > On Fri, Mar 1, 2019 at 9:30 AM Shaheed Haque <srhaque@theiet.org> wrote: > > > OK, I got it working. This is how... > > > On Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote: > > > Hi, > > > I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other things working(a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck getting pgAdmin4to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to do this (I'mrunning the latest, v4.2, of pgAdmin4)? > > > I'm aware of threads such as https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local,and several othersbut not been able to come up with a clear approach: > > > One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port 5050 directlybehind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I've tried both(modelled on what I have working, and various references). One combination I tried was: > > > - Creating a softlink from pgadmin4.py to pgAdmin4.wsgi > > - Using gunicorn to run the pgadmin4.py to a unix domain socket like this: > > > $ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application > > > - Serving behind nginx like this: > > > location /pgadmin { > > rewrite ^/pgadmin/(.*) /$1 break; > > include proxy_params; > > proxy_pass http://unix:/home/ubuntu/pgadmin.sock; > > } > > > But all I get is a stubborn 404. Any pointers welcome... > > > First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). The next problemwas with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser looking for toplevel URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a piece of code inhttps://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hacking a small wodgeof code into pgAdmin4.py. > > > The SCRIPT_NAME is set by an nginx fragment like this: > > > location /pgadmin { > > rewrite ^/pgadmin/(.*)$ /\$1 break; > > include proxy_params; > > proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock; > > proxy_set_header X-Script-Name /pgadmin; > > } > > > In addition to that change, as previously noted, I needed to create a link to pgAdmin4.wsgi to allow gunicorn to pick itup. I change the name I used so I ended up with the link being "wsgi.py" -> "pgAdmin4.py", so the the corresponding gunicorncommand is something like this: > > > gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application > > > Now, IIUC, the notion of SCRIPT_NAME is somewhat standard, and needed to solve this issue of running pgAdmin in servermode, but sharing the domain with other applications. Would there be interest in making the needed code an integralpart of pgAdmin? If so, I'd be happy to file a feature request. > > > Thanks, Shaheed > > > > Thanks for your work on this. > > > I've committed a change to add the reverse proxy code, and to put some more examples in the docs: https://redmine.postgresql.org/projects/pgadmin4/repository/revisions/f401def044c8b47974d58c71ff9e6f71f34ef41d > > > FWIW, I think the reason that this was an issue for so long is that you don't need the extra code if you use mod_wsgi oruWSGI - it's only needed with Gunicorn. I'll let you guess which of those technologies I'm most familiar with! > > > Unfortunately this commit won't make the 4.3 release later this week, but it will be in 4.4. The instructions will stillbe good for scenarios other than Gunicorn in a sub-directory though. > > > -- > > Dave Page > > Blog: http://pgsnake.blogspot.com > > Twitter: @pgsnake > > > EnterpriseDB UK: http://www.enterprisedb.com > > The Enterprise PostgreSQL Company > > > > > -- > > Dave Page > > Blog: http://pgsnake.blogspot.com > > Twitter: @pgsnake > > > EnterpriseDB UK: http://www.enterprisedb.com > > The Enterprise PostgreSQL Company > > > > > -- > > Dave Page > > Blog: http://pgsnake.blogspot.com > > Twitter: @pgsnake > > > EnterpriseDB UK: http://www.enterprisedb.com > > The Enterprise PostgreSQL Company
This issue is tracked here: https://redmine.postgresql.org/issues/4143. On Thu, 4 Apr 2019 at 19:06, Shaheed Haque <shaheedhaque@gmail.com> wrote: > > Thanks for the kind (and quick) replies. My last email crossed with > the ones from Dave and Murtaza... > > On Thu, 4 Apr 2019 at 19:03, Dave Page <dpage@pgadmin.org> wrote: > > > > Downgrade psycopg2 to 2.7.7 (or whatever came before 2.8). Apparently they broke backwards compatibility:-( > > > > -- > > Dave Page > > Blog: http://pgsnake.blogspot.com > > Twitter: @pgsnake > > > > EnterpriseDB UK:http://www.enterprisedb.com > > The Enterprise PostgreSQL Company > > > > On 4 Apr 2019, at 17:33, Shaheed Haque <shaheedhaque@gmail.com> wrote: > > > > I can login, but when I click on "Servers", nothing happens on screen > > and the error log gets this: > > > > [2019-04-04 16:10:10 +0000] [5850] [INFO] Starting gunicorn 19.9.0 > > [2019-04-04 16:10:10 +0000] [5850] [INFO] Listening at: > > unix:/home/ubuntu/pgadmin.sock (5850) > > [2019-04-04 16:10:10 +0000] [5850] [INFO] Using worker: gthread > > [2019-04-04 16:10:10 +0000] [5853] [INFO] Booting worker with pid: 5853 > > 2019-04-04 16:10:25,732: ERROR flask.app: > > 'psycopg2.extensions.Column' object has no attribute '_asdict' > > Traceback (most recent call last): > > File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line > > 1813, in full_dispatch_request > > rv = self.dispatch_request() > > File "/usr/local/lib/python3.6/dist-packages/flask/app.py", line > > 1799, in dispatch_request > > return self.view_functions[rule.endpoint](**req.view_args) > > File "/usr/local/lib/python3.6/dist-packages/flask/views.py", line 88, in view > > return self.dispatch_request(*args, **kwargs) > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/utils.py", > > line 259, in dispatch_request > > return method(*args, **kwargs) > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/utils.py", > > line 309, in children > > children.extend(module.get_nodes(*args, **kwargs)) > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/server_groups/servers/__init__.py", > > line 127, in get_nodes > > in_recovery, wal_paused = recovery_state(conn, manager.version) > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/browser/server_groups/servers/__init__.py", > > line 52, in recovery_state > > status, result = connection.execute_dict(recovery_check_sql) > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py", > > line 1202, in execute_dict > > desc.to_dict() for desc in cur.ordered_description() > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py", > > line 1202, in <listcomp> > > desc.to_dict() for desc in cur.ordered_description() > > File "/usr/local/lib/python3.6/dist-packages/pgadmin4/pgadmin/utils/driver/psycopg2/cursor.py", > > line 94, in to_dict > > ores = OrderedDict(self.orig_col._asdict()) > > AttributeError: 'psycopg2.extensions.Column' object has no attribute '_asdict' > > > > It seems as though the version of psycopg2 is not what is expected, > > rather than anything to do with "builtins.SERVER_MODE = True"? By way > > of full disclosure, I do in fact have both psycopg2 AND > > psycopg2-binary installed [1] but some quick experiments removing one > > or other did not solve the problem. > > > > Have I overlooked something? > > > > Thanks, Shaheed > > > > [1] this came up in another discussion recently, and it seems that I > > ought to remove the psycopg2-binary though 4.2 was OK with this setup. > > > > On Thu, 4 Apr 2019 at 15:04, Dave Page <dpage@pgadmin.org> wrote: > > > > > > Hi > > > > > > It worked fine when I tested it. If you don't get any errors and you get a login prompt, it should all be good. > > > > > > On Thu, Apr 4, 2019 at 3:01 PM Shaheed Haque <shaheedhaque@gmail.com> wrote: > > > > > > Hi Dave, > > > > > > I'm just migrating to the fixes in 4.4. Can I just double check one > > > > thing? In my original hack, I needed to create a link "wsgi.py" -> > > > > "pgAdmin4.py", so the the corresponding gunicorn command is something > > > > like this: > > > > > > gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application > > > > > > Now, in the documentation updates as part of this fix, the last > > > > argument to that line reads "pgAdmin4:app" and not "wsgi:application". > > > > The reason I though I needed to use the wsgi.py file is that it > > > > contains these lines amongst other: > > > > > > # Ensure the global server mode is set. > > > > builtins.SERVER_MODE = True > > > > > > I'm not sure if, by following the updated docs, I am missing a > > > > necessary setting? > > > > > > Thanks, Shaheed > > > > > > On Tue, 5 Mar 2019 at 09:11, Dave Page <dpage@pgadmin.org> wrote: > > > > > > You're welcome. Sorry it took so long to realise the main issue was only applicable to Gunicorn! > > > > > > On Mon, Mar 4, 2019 at 6:29 PM Shaheed Haque <srhaque@theiet.org> wrote: > > > > > > Dave, > > > > > > Thanks for taking this forward, I look forward to 4.4! I also noted with interest that we can use "pgAdmin4:app" ratherthan my hacky link to the pgAdmin4.wsgi, so that means a completely hack-free solution :-). > > > > > > > > Shaheed > > > > > > On Mon, 4 Mar 2019 at 16:33, Dave Page <dpage@pgadmin.org> wrote: > > > > > > Hi > > > > > > On Fri, Mar 1, 2019 at 9:30 AM Shaheed Haque <srhaque@theiet.org> wrote: > > > > > > OK, I got it working. This is how... > > > > > > On Mon, 25 Feb 2019 at 23:25, Shaheed Haque <srhaque@theiet.org> wrote: > > > > > > Hi, > > > > > > I'm a relative noob when it comes to the world of nginx, wsgi and so forth, but I do have several other things working(a Django app under gunicorn and the RabbitMQ web UI directly behind nginx). However, I'm rather stuck getting pgAdmin4to run at http://mydomain.com:80/pgadmin behind nginx. Is there a simple, up-to-date example of how to do this (I'mrunning the latest, v4.2, of pgAdmin4)? > > > > > > I'm aware of threads such as https://www.postgresql.org/message-id/2197768425D7F5479A0FFB3FEC212F7FF602B871%40aesmail.surcouf.local,and several othersbut not been able to come up with a clear approach: > > > > > > One of the several variables I'm struggling to understand is the choice of whether to run pgAdmin4.py on port 5050 directlybehind nginx, or as a WSGI app under gunicorn. I assume the latter should be easier to set up, but I've tried both(modelled on what I have working, and various references). One combination I tried was: > > > > > > - Creating a softlink from pgadmin4.py to pgAdmin4.wsgi > > > > - Using gunicorn to run the pgadmin4.py to a unix domain socket like this: > > > > > > $ /usr/local/bin/gunicorn -w 1 --bind unix:/home/ubuntu/pgadmin.sock pgadmin4:application > > > > > > - Serving behind nginx like this: > > > > > > location /pgadmin { > > > > rewrite ^/pgadmin/(.*) /$1 break; > > > > include proxy_params; > > > > proxy_pass http://unix:/home/ubuntu/pgadmin.sock; > > > > } > > > > > > But all I get is a stubborn 404. Any pointers welcome... > > > > > > First, I got over the 404s (caused, it seems, by me forgetting just how much my browser had cached :-0). The next problemwas with the nginx config fragment: as soon as pgAdmin responded, it of course started the browser looking for toplevel URLs such as /browser and /static which are obviously not under /pgadmin. The to this key was a piece of code inhttps://stackoverflow.com/a/50515636/6332554, which basically adds the concept of a SCRIPT_NAME by hacking a small wodgeof code into pgAdmin4.py. > > > > > > The SCRIPT_NAME is set by an nginx fragment like this: > > > > > > location /pgadmin { > > > > rewrite ^/pgadmin/(.*)$ /\$1 break; > > > > include proxy_params; > > > > proxy_pass http://unix:/home/$CLOUD_USER/pgadmin.sock; > > > > proxy_set_header X-Script-Name /pgadmin; > > > > } > > > > > > In addition to that change, as previously noted, I needed to create a link to pgAdmin4.wsgi to allow gunicorn to pickit up. I change the name I used so I ended up with the link being "wsgi.py" -> "pgAdmin4.py", so the the correspondinggunicorn command is something like this: > > > > > > gunicorn ... --bind unix:/.../pgadmin.sock wsgi:application > > > > > > Now, IIUC, the notion of SCRIPT_NAME is somewhat standard, and needed to solve this issue of running pgAdmin in servermode, but sharing the domain with other applications. Would there be interest in making the needed code an integralpart of pgAdmin? If so, I'd be happy to file a feature request. > > > > > > Thanks, Shaheed > > > > > > > > Thanks for your work on this. > > > > > > I've committed a change to add the reverse proxy code, and to put some more examples in the docs: https://redmine.postgresql.org/projects/pgadmin4/repository/revisions/f401def044c8b47974d58c71ff9e6f71f34ef41d > > > > > > FWIW, I think the reason that this was an issue for so long is that you don't need the extra code if you use mod_wsgior uWSGI - it's only needed with Gunicorn. I'll let you guess which of those technologies I'm most familiar with! > > > > > > Unfortunately this commit won't make the 4.3 release later this week, but it will be in 4.4. The instructions will stillbe good for scenarios other than Gunicorn in a sub-directory though. > > > > > > -- > > > > Dave Page > > > > Blog: http://pgsnake.blogspot.com > > > > Twitter: @pgsnake > > > > > > EnterpriseDB UK: http://www.enterprisedb.com > > > > The Enterprise PostgreSQL Company > > > > > > > > > > -- > > > > Dave Page > > > > Blog: http://pgsnake.blogspot.com > > > > Twitter: @pgsnake > > > > > > EnterpriseDB UK: http://www.enterprisedb.com > > > > The Enterprise PostgreSQL Company > > > > > > > > > > -- > > > > Dave Page > > > > Blog: http://pgsnake.blogspot.com > > > > Twitter: @pgsnake > > > > > > EnterpriseDB UK: http://www.enterprisedb.com > > > > The Enterprise PostgreSQL Company