Pgweb is a light weight cross platform client for PostgreSQL DBs written in Go. It can connect to both local & remote PostgreSQL instances behind firewall using native SSH tunneling both with password or ssk keys
Due to no dependency on any other external library/utility all that is needed to run pgweb is to download it and run to connect to a working PostgreSQL server. Also works on RaspberryPi.
Note: It can also be used to connect to the CockroachDB instances, detailed in post Cockroach DB : An open source distributed SQL Database as external data store for ThingWorx
Getting Access
Download pre-compiled binaries here for different platforms.
Building pgweb from source
Use https://github.com/sosedoff/pgweb.git to clone the source and build it.
Additional Reads
Installing & Testing it
After downloading the pgweb_windows_amd64.exe for windows platform (used for testing in this blog), start the pgweb utility using: pgweb_windows_amd64.exe
Note starting the pgweb utility from the command prompt shows the port on which pgweb is accessible
e.g. This is when no flag is used to start up the pgweb utility, all default options used
Accessing the pgweb over localhost:8081 via a web browser
Connecting
Pgweb allows for connecting to the PostgreSQL DB in variety of ways - e.g. using something like JDBC connection string like postgres://user:password@host:port/db?sslmode=mode. Additionally users can connect using standard hostname, port , username & password connection w/o SSL or via SSH tunneling.
Navigating around pgweb UI
UI design is pretty straight forward and clean highlighting database name, all the tables available under that database and selected table's information on the left. To switch to different database running under the connected server click on the database name to see a drop down menu with names listed.
Selected table gets queried for all its data automatically displaying all its rows together with additional tabs for quickly reviewing the metadata about the table
Query Tab
This is particularly interesting esp. for getting quick info on how the SQL query is going to perform. This could be used to analyze and generate the query plan showing preliminary data on how is it going to cost to run a particular query, how many times its going to loop over the complete table, what's going to be the execution time, etc
To get this information simply type in the sql statement under the Query tab and hit Explain Query button to generate this data. For larger tables it could take a while to get this information generated
The queried data then can be exported in formats : JSON, CSV & XML
Exporting table data out of the tables
This is pretty handy feature allowing quick export of entire table's data set by right clicking on the table name and selecting one of the desired formats which includes JSON, CSV, etc.
