Blog Posts
Want to see your pgAdmin blog post here? Email webmaster@pgadmin.org.
Customising your pgAdmin 4 workspace - Part 1: Browser
Author: Anil Sahoo, date: Aug. 24, 2023
If you are reading this then you are probably using pgAdmin as a tool to help in your day to day database management activities with PostgreSQL. But, did you know there are a variety of ways you can maximise your productivity by customising the pgAdmin workspace using the Preferences dialog’s various options? This article will show you some of the options for customising the workspace using the Browser node of the Preferences tree control.
pgAdmin User Management in Server Mode
Author: Aditya Toshniwal, date: Aug. 24, 2023
pgAdmin can be deployed as a web application by configuring the app to run in server mode. One can check out server deployment on how to run pgAdmin in server mode. In this blog, we will discuss how to manage the pgAdmin users when running in server mode.
pgAdmin CI/CD
Author: Dave Page, date: Aug. 24, 2023
Almost exactly three years ago I wrote a blog on my personal page entitled Testing pgAdmin which went into great detail discussing how we test pgAdmin prior to releases. Back then, all of the automated testing was performed using Jenkins, with a number of jobs that ran various test suites whenever new code was checked in. All of this infrastructure ran in a virtual private cloud on AWS consisting of a large number of virtual machines and other resources, hosted for the project by EDB. I recently undertook a project to move the testing infrastructure to our Github project, using Github Actions.
Configuring and Using Shared Storage in pgAdmin 4
Author: Nikhil Mohite, date: May 31, 2023
pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL. In this blog, I will walk you through how to configure and use shared storage in pgAdmin 4.
How to use pgAgent in pgAdmin 4
Author: Pravesh Sharma, date: May 10, 2023
pgAgent is a tool used for scheduling jobs for PostgreSQL databases. Each job consists of steps and schedules. In this blog we will look at how pgAdmin can be used to create scheduled jobs using pgAgent with PostgreSQL, with steps and schedules to achieve what you want, when you want.
Setup pgAdmin development environment
Author: Aditya Toshniwal, date: May 10, 2023
pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL. In this blog, I will walk you through how to set up the pgAdmin development environment on your machine. The code repository for pgAdmin 4 is: https://github.com/pgadmin-org/pgadmin4.
Google Cloud SQL - PostgreSQL Deployment with pgAdmin 4
Author: Yogesh Mahajan, date: May 2, 2023
pgAdmin 4 has added functionality to deploy cloud instances for PostgreSQL on AWS, Azure and BigAnimal. pgAdmin version 7.0 has introduced the new functionality for deploying a Google Cloud SQL PostgreSQL instance from pgAdmin. To launch a Google Cloud SQL PostgreSQL instance, the prerequisite is to create and download a client secret file containing the client ID, client secret, and other OAuth 2.0 parameters for google authentication. This feature enables you to quickly deploy the PostgreSQL instance to the desired region on Google Cloud, with custom configuration, and connect to it with pgAdmin.
pgAgent Setup
Author: Khushboo Vashi, date: March 21, 2023
pgAgent is a job scheduling agent for Postgres databases, capable of running multi-step batch or shell scripts and SQL tasks on complex schedules, which may be managed using pgAdmin.
pgAgent shipped as a separate application. This blog is for users/developers who specifically want to build from a source. Most users should use pre-built packages from the PostgreSQL APT/YUM repositories, or StackBuilder.
pgAdmin with Kerberos and Active Directory
Author: Khushboo Vashi, date: March 21, 2023
pgAdmin supports Kerberos authentication for user logins as well as connecting to databases. Kerberos is a popular authentication method but many people find it difficult to set up especially with Windows Active Directory. In this blog, I will walk through the steps to set up Kerberos with pgAdmin and Active Directory.
OAuth2 Enhancements in pgAdmin
Author: Khushboo Vashi, date: Feb. 3, 2023
pgAdmin supports multiple authentication methods including OAuth2 for login into the app in web mode. We have added support for OAuth2 in July 2021. After that, the development team enhanced the OAuth2 functionality.
Create an ERD in pgAdmin 4
Author: Aditya Toshniwal, date: Feb. 1, 2023
The Entity-Relationship Diagram (ERD) tool is a database design tool that provides a graphical representation of database tables, columns, and inter-relationships. An ERD can give sufficient information for the database administrator to follow when developing and maintaining the database.
How to setup Two Factor Authentication in pgAdmin 4
Author: Akshay Joshi, date: Dec. 7, 2022
Two-Factor Authentication (2FA) works by adding an additional layer of security to your online accounts. It requires an additional login credential - something you have - to gain account access, in addition to your password (something you know).
This feature is only available in Server Mode.pgAdmin 4 supports two types of 2FA:
- Email Authentication
- Authenticator App (e.g: Google Authenticator)
Psycopg2 to Psycopg3 migration in pgAdmin 4
Author: Khushboo Vashi, date: Dec. 7, 2022
Recently the Psycopg project released a major version, Psycopg3. pgAdmin 4 is immensely dependent on psycopg2 as it is being used as a database driver and the pgAdmin team decided to migrate to the new latest version.
Initially, we thought that it would be hardly 3 to 4 weeks work as it is just a migration but our assumption was incorrect. The reason is we haven’t used psycopg2 "out of the box", we have extended many things and one of them is cursor factory. So, when I started porting, I realized the way we have done that is not supported any more, so I have started digging into the code and found out that in the next release, that was supposed to be included. So, I cloned the repo, built it on my local and used it till 3.1 official release.
Psycopg3 is completely rewritten, so it’s a vast topic to cover, but we will look at some of the areas which affected pgAdmin.
How to use an external database for pgAdmin user settings
Author: Akshay Joshi, date: Nov. 18, 2022
The purpose of this blog is to illustrate how to save pgAdmin 4 configurations to an external database server. In addition, we covered examples that demonstrate how to use PostgreSQL server as an external database.
pgAdmin config files
Author: Pravesh Sharma, date: Nov. 14, 2022
This blog explains the purpose of the various configuration files used by pgAdmin, when they should be modified, and for what reason.
Import Export Servers in pgAdmin 4
Author: Akshay Joshi, date: Sept. 2, 2022
In this blog we look at how server definitions can be imported and exported from pgAdmin using the command line or the graphical user interface. This allows server definitions to be migrated between pgAdmin installations and shared with colleagues.
EDB BigAnimal Deployment through pgAdmin 4
Author: Khushboo Vashi, date: Sept. 2, 2022
In this blog we demonstrate how to deploy PostgreSQL or EDB Advanced Server in the EDB BigAnimal Cloud using pgAdmin.
AWS RDS PostgreSQL Deployment with pgAdmin 4
Author: Yogesh Mahajan, date: Aug. 17, 2022
This blog explains and provides a walkthrough of the pgAdmin AWS RDS deployment wizard which can be used to deploy a new PostgreSQL database in the Amazon AWS cloud and register it with pgAdmin so you can begin working with it immediately.
pgAdmin User Survey 2022
Author: Dave Page, date: Aug. 2, 2022
On Monday 11th July the pgAdmin Development Team opened the first pgAdmin user survey which we then ran for three weeks, closing it on Monday 1st August. The aim of the survey was to help us understand how users are using pgAdmin to help us shape and focus our future development efforts. We had a fantastic response with 278 people taking the time to complete the survey - far exceeding our expectations. Responses were generally positive as well, with a number of people expressing their appreciation for the work of the development team, which is always nice to hear. In this blog post I'll go through the high level topics of the survey and attempt to summarise what has been reported, and draw some initial conclusions. If you would like to take a look at the results yourself...
Autocomplete feature in Query Tool
Author: Akshay Joshi, date: June 10, 2022
To use autocomplete, begin typing your query; when you would like the query editor to suggest object names or commands that might be next in your query, press the Control + Space key combination. For example, type "SELECT * FROM " (without quotes, but with a trailing space), and then press the Control + Space key combination to select from a popup menu of autocomplete options.
pgAdmin 4 Architecture
Author: Yogesh Mahajan, date: May 27, 2022
pgAdmin 4 is the leading Open Source management tool for Postgres. pgAdmin is designed to monitor and manage multiple PostgreSQL and EDB Advanced Server database servers, both local and remote, through a single graphical interface that allows the easy creation and management of database objects, as well as a number of other tools for managing your databases.
pgAdmin can be installed in two modes: Desktop & Server mode. Desktop mode is installed as a standalone application being used by the same operating system user while server mode can be accessed over the network, allowing it to be used by multiple users.
Both mode implementations follow a 3 tier architecture approach. This blog explains the pgAdmin architecture and technologies used in both modes.
Query Tool vs View/Edit Data
Author: Nikhil Mohite, date: May 10, 2022
Ever wondered what the differences are between the Query Tool and View/Edit Data tool in pgAdmin? In this blog Nikhil explains those differences and when each tool should be used.
How to Configure OAuth 2.0 with Azure AD in pgAdmin4
Author: Asmita Thapliyal, date: April 29, 2022
pgAdmin user Asmita Thapliyal writes:
I struggled for few days to get OAuth 2.0 configuration works for Azure AD in pgAdmin4, which leads me to writing this blog post.
We were looking for a hosted web interface for running SELECT queries in AWS Aurora database from on premise environment. This interface would allow developers for database interaction and to perform initial troubleshooting without sharing the database credentials and whitelisting the connection from their laptops and vms.
Use of the Master Password in pgAdmin 4
Author: Akshay Joshi, date: April 22, 2022
pgAdmin 4 introduced the Master Password in order to secure and later unlock saved server passwords. Any password that is saved in the SQLite DB file - used for storing user preferences and other data - through the save password or save SSH tunnel password option will be encrypted and decrypted using the master password.
The feature was introduced in pgAdmin 4 version 4.7 and above. This is applicable to Desktop mode users as well as to authentication methods like OAuth, Kerberos, or Web Server where pgAdmin doesn’t have access to anything long-lived to form the encryption key whereas with pgAdmin internal or LDAP authentication methods, the user’s login password is used as an encryption key.
pgAdmin 4 Browser Tree
Author: Khushboo Vashi, date: April 6, 2022
This blog from pgAdmin developer Khushboo Vashi discusses the technical changes made to the rendering of the treeview in pgAdmin.
Feature Test Automation in pgAdmin 4
Author: Yogesh Mahajan, date: Feb. 23, 2022
pgAdmin follows an Agile development process with an iterative and incremental approach to development. Testing is a critical part of the development process. pgAdmin is regularly tested with a subset of the available versions, ensuring a high-quality product. The test suite includes API testing, UI Testing (referred as Feature Testing), unit testing, and "resql" testing, a pgAdmin-specific test framework for testing the reverse engineering of SQL.
To achieve better quality in the short release cycles, test automation is required. The Selenium WebDriver with Python is used for feature test automation. Feature test frameworks currently support parallel testing/cross-browser testing. The new era of continuous testing requires faster test execution. Parallel testing involves running independent tests in parallel to reduce overall test execution time by allowing multiple OS/browser combinations to be tested simultaneously. An extended test coverage minimizes risk and reduces the chances of releasing a product with regressions or defects.
Dashboards in pgAdmin 4
Author: Nikhil Mohite, date: Feb. 3, 2022
The dashboard tab in pgAdmin shows an analysis of the activity statistics for the selected server or database.
It also shows quick links to Add New Server, Configure pgAdmin, and Documentation if a Server Group node is selected from the browser tree.
SSH Tunneling in pgAdmin 4
Author: Rahul Shirsat, date: Jan. 6, 2022
SSH tunneling is a method of transporting arbitrary networking data over an encrypted SSH connection. It can be used to add encryption to legacy applications or implement VPNs (Virtual Private Networks). It is a way to secure the data traffic of any given application using port forwarding, basically tunneling any TCP/IP port over SSH.
pgAdmin 4 supports SSH Tunneling, i.e. it - through an intermediary proxy host - can be connected to a PostgreSQL server that resides on a network to which the client may not be able to connect directly.
This blog will guide you to setup SSH tunneling with pgAdmin 4.
Shared Servers in pgAdmin 4
Author: Pradip Parkale, date: Jan. 4, 2022
Shared servers allow the admin user to share servers with other users so that they can use them directly without adding them themselves.
Shared servers only apply when pgAdmin is running in server mode. Server mode is a multi-user mode whereas Desktop mode is single user mode. In server mode users must login before they can use pgAdmin. An initial superuser account is created when server mode is initialised, and this user can add additional superusers and non-superusers as required.
Reverse Proxying to pgAdmin with uWSGI and NGINX
Author: Yogesh Mahajan, date: Dec. 16, 2021
pgAdmin 4 is a free open source graphical management tool for PostgreSQL and derivative relational databases such as EnterpriseDB's EDB Advanced Server. pgAdmin can be installed in two modes: Server mode and Desktop mode. pgAdmin in server mode can be deployed behind different web servers like apache2, Nnginx etc.
A reverse proxy is a server that sits in front of web servers and forwards client (e.g. web browser) requests to those web servers. Reverse proxies are typically implemented to help increase security and performance.
This blog will guide you to reverse proxy to pgAdmin using Nginx and uWSGI on Debian or Ubuntu Linux. The process is the same on other Linux distributions, but file, directory, and service names may differ.
How To Configure Webserver Authentication in pgAdmin 4
Author: Yogesh Mahajan, date: Nov. 30, 2021
pgAdmin 4 supports multiple authentication methods through its pluggable architecture. In addition to the four existing authentication methods; Kerberos, LDAP, OAuth 2.0, and internal, pgAdmin4 now supports webserver authentication.
Web server authentication (HTTP authentication) is the most common application of third-party authentication. With web server authentication, the web server performs the authentication and the application trusts the web server.
To enable web server authentication, the web server must be configured for any authentication mechanism (such as HTTP Basic auth or Shibboleth) which sets either headers or environment variables which will be used in pgAdmin to identify the user.
This blog will guide you to set up the apache2 webserver authentication with HTTP BASIC auth in pgAdmin 4, on Debian or Ubuntu Linux. The process is the same on other Linux distributions, but file, directory, and service names may differ.
How to Use Logical Replication in pgAdmin4
Author: Pradip Parkale, date: Oct. 7, 2021
Logical replication follows a publish and subscribe model. In a publisher node, a publication is created, which is a set of changes from a table or a group of tables. In a subscriber node, a subscription is created, which can subscribe to one or more publications. But why logical replication? Well in traditional replication, the standby server is a bit for bit copy of the leader server but what if you want to copy only some data? Logical replication gives you that flexibility of what should be sent and where to send the data.
pgAdmin and Porting the Node Properties Dialogs to ReactJS
Author: Aditya Toshniwal, date: Oct. 6, 2021
In a previous blog post, The Reasons Why pgAdmin Chose ReactJS, I had discussed the rationale behind choosing ReactJS for porting away from Backbone/jQuery. Following the post, I want to share the experience and course of actions EDB's pgAdmin team followed to achieve our goal. To date, the pgAdmin team has successfully ported all the properties dialog of the browser nodes to React based forms and has reached the testing phase.
How To Configure OAuth 2.0 in pgAdmin 4
Author: Khushboo Vashi, date: Sept. 30, 2021
pgAdmin 4 supports multiple authentication methods through its pluggable architecture. Currently four methods are supported:
- Password based pgAdmin internal authentication (default)
- Kerberos
- LDAP
- OAuth 2.0
How to Get the Most Out of the Schema Diff Tool in pgAdmin 4
Author: Akshay Joshi, date: Sept. 6, 2021
The Schema Diff Tool in pgAdmin 4 allows you to compare objects between two databases or two schemas. It allows you to compare tables, views, functions, sequences, packages, procedures and other database objects between two schemas/databases. It will report any discrepancies between schemas such as missing or mismatching procedures, tables, triggers, columns, indexes and constraints. It also will detect column discrepancies such as data type, nullability and defaults.
How to Deploy pgAdmin in Kubernetes
Author: Dave Page, date: June 28, 2021
pgAdmin has long had a container distribution; however the development team rarely used it, except when testing releases. So virtually all of our experience has been using Docker. Recently, a user ran into an issue when running under Kubernetes that I was unable to reproduce in Docker, so I spent some time learning how a pgAdmin deployment would work in that environment—and ironically it worked just fine; I couldn't reproduce the bug! Regardless, I gained an understanding of how to deploy pgAdmin in Kubernetes, so here's how it works.
The Reasons Why pgAdmin Chose ReactJS
Author: Aditya Toshniwal, date: March 10, 2021
pgAdmin 4 is a management tool for PostgreSQL and EDB Postgres Advanced Server. pgAdmin 4 was re-written from pgAdmin 3 (a native desktop application). The application is designed to work on both the desktop and a web server. It is written in Python using Flask with the user interface written in JS/jQuery/Backbone, and can be deployed on any network. It is 2021, and one might argue about why pgAdmin chose jQuery/Backbone. The development work of pgAdmin 4 started in around 2014, and back then, Backbone.js was one of the most popular frameworks. Backbone.js was even more popular than React at the time.
Kerberos Support in pgAdmin 4
Author: Khushboo Vashi, date: March 1, 2021
The pgAdmin team has been receiving requests from users to support Kerberos Authentication for quite some time and as a result, we have decided to implement it. The work has been divided into 2 phases. The first phase adds pluggable Kerberos authentication in the pgAdmin Server (multi user) mode using GSSAPI and SPNEGO, similar to the existing LDAP support. The web browser and the pgAdmin web server negotiate Kerberos as a security mechanism through SPNEGO and exchange tickets as SPNEGO tokens over HTTPS and which will bypass the pgAdmin login page entirely if the Kerberos authentication succeeds.
Testing pgAdmin
Author: Dave Page, date: Aug. 25, 2020
Like any software, pgAdmin has bugs. At the time of writing there are 119 new or in-progress issues in the bug tracker, which is pretty good for a piece of software with so many moving parts, and regular-as-clockwork monthly releases. Over 10% of these issues are currently assigned to the reporter to gather further information, plus there will undoubtedly be some duplicates and support issues/feature requests in that number that haven’t yet been picked up in our weekly backlog refinement meeting and either closed or moved to the feature or support trackers.
pgAdmin, a comparable tool to PL/SQL Developer for PostgreSQL
Author: Shivam Dhapatkar, date: Feb. 9, 2020
This article explains how to get started using pgAdmin 4 as a management tool for PostgreSQL.
- How to download pgAdmin 4
- How to get started with pgAdmin 4
- Query Tool
- How to execute a query in pgAdmin 4
- Creating functions, procedures, and triggers using Query Tool
Reverse Proxying to pgAdmin
Author: Dave Page, date: July 10, 2019
Reverse proxying requests to a pgAdmin server is becoming more and more popular if posts to the mailing lists are to be taken as an indicative measure; more often than not when using pgAdmin in a container (of which there have now been over 10 million pulls)! Typically users will deploy a reverse proxy for a couple of reasons; to host multiple applications in different subdirectories under the same domain, or to add SSL/TLS support independently of the application. Because of the number of questions asked, I spent a little time over the last couple of days doing some testing and updating the documentation with some examples. Here's a blog-ified version of that work.