Question/help on exports in LB scenario

[DanSantimore]

Hey there,

Fully aware there's a ton of options here on things I might have screwed up. Running the docker version (0.11.12) of this app in AWS, behind an ALB. Works great except for a couple of functions. For example, I can export to CSV, but not to SQL. Trying to figure out what the difference is.

Here's the URL that gets generated with export to SQL:

https://{host}/db-access/api/export?table=%22public%22.%22build_specs%22&_session_id=a7ef563e-a563-a610-7e18-26e5393516f6

This appears to be correct, and since CSV works, it appears to be analagous. Response is a 400, but there is a content disposition for a gzip set which surprises me.

Basically trying to figure out whether i focus on something in the LB or something closer to the container.

Container log is not giving out much:

2022/12/08 23:03:25 Request params: map[] [GIN] 2022/12/08 - 23:03:26 | 400 | 202.241676ms | 152.186.131.14 | GET "/db-access/api/export?table=%22public%22.%22experiments%22&_session_id=a7ef563e-a563-a610-7e18-26e5393516f6"

[sosedoff]

@the-dansan it could be an issue with pg_dump on the machine that runs pgweb process. SQL export uses the standard postgres dump subprocess to export data, so if its not installed, you will get this cryptic error (or a similar one).

I will def improve the error logging for the sql dump as a lot of folks have run into this exact issue.

[sosedoff]

But since you're running dockerized version, im suspecting there's pg server/client version mismatch. You could try adding --debug for troubleshooting.

[DanSantimore]

Thanks for the idea. This is aurora, which I believe has no problem with pg_dump but who knows. It is using postgres protocol 12.8.

So, is pg_dump included in the docker hub image you publish? Otherwise that would be an obvious thing I missed :)

[sosedoff]

Pgweb 0.11.12 docker image has pg_dump (PostgreSQL) 11.16 (Debian 11.16-0+deb10u1) installed so that will cause the dumps to not work.

Here's an example:

~$ curl -i 'http://0.0.0.0:8081/api/export?table=%22public%22.%22city%22&_session_id=xxx'
HTTP/1.1 400 Bad Request
Content-Disposition: attachment; filename="xxx.city.sql.gz"
Content-Type: application/json; charset=utf-8
Date: Fri, 09 Dec 2022 17:20:07 GMT
Content-Length: 189

{
  "error": "error: exit status 1. output: pg_dump: server version: 15.0; pg_dump version: 11.16 (Debian 11.16-0+deb10u1)\npg_dump: aborting because of server version mismatch\n",
  "status": 400
}

New docker image will have the latest postgres tools installed, but if you need to get your fix rolling sooner, you can build your own docker image (based on ubuntu or whatever) and run that until 0.12 is out.

[sosedoff]

Basically, pg_dump's version should always be the same or greater than the server's.

[sosedoff]

This should be fixed in latest 0.12.0 release.