cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Exploring Pgweb: A cross platform client for PostgreSQL databases

Highlighted
Tanzanite

Exploring Pgweb: A cross platform client for PostgreSQL databases

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

 

Untitled picture.png

 

Accessing the pgweb over localhost:8081 via a web browser

 

Untitled picture.png

 

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.

 

Untitled picture.png

 

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

 

Untitled picture.png

 

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

 

Untitled picture.png

 

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.

 

Untitled picture.png