pgAdmin Ubuntu

Introduction to pgAdmin

The tools of the graphical user interface have become a typical part of the tech workflows. pgAdmin is a go-to solution for customers if they need a GUI for PostgreSQL management. This environment is based on our web browser and permits us to visually negotiate with SQL database servers both remotely and locally.

It was developed to help the users of PostgreSQL to get the most out of their database. The aim is to offer a graphical administration tool for making it easier to manipulate data and schema in PostgreSQL (or even more than one installation). pgAdmin is a fully open-source and free tool to use with the installations of PostgreSQL servers, whether we host it locally, in containers, in the cloud, or in other environments.

pgAdmin is a layer on the high point of our database that aids us in interacting with it easily. While we certainly do not have to utilize pgAdmin for any database administration task, it is treated as highly valuable. For PostgreSQL users, its open-source nature and customization ease make it an accessible database management system.

What and when pgAdmin used for?

pgAdmin is the most leading open-source graphical user interface tool developed for the most leading relational database management tools. To understand why it is essential, let's take a look at its features.

The primary benefits and features of pgAdmin are mentioned below:

• It works around operating systems because it is compatible with Linux, Mac, and Windows.
• We can install pgAdmin everywhere that we are using PostgreSQL.
• It is compatible with every version of EDB Postgres Advanced Server and PostgreSQL.
• There is huge documentation on downloading, installing, and using pgAdmin.
• Unlock query tools offer faster debugging, data entry, and more.
• Deploy in server mode or desktop mode based on our requirements.
• Implement routine maintenance using tools to analyze, vacuum, restore, and back up.
• View, edit, and create every typical object in PostgreSQL.

While it is possible to manage these similar things without pgAdmin, this tool's value is that it offers a handy data administration interface for us to manage SQL maintenance, queries, and other important processes without applying command line prompts. pgAdmin offers monitoring tools that let us see the operation status at a glance, and it aids automate jobs using its scheduling agent.

Overall, pgAdmin is a great addition to most PostgreSQL user workflows.

With a lot of documentation, it is not hard to get started, even if we have never installed a graphical user interface tool on a database before. The second step is to ensure that pgAdmin is suitable for our PostgreSQL installation.

Version History of PgAdmin

• 1.0: The 1.0 version was published on 29 September 2016. It was the first major version of PgAdmin 4. This version contains the below features with a more modern feel and look:
  • Multibyte support
  • Build, view, and alter all basic PostgreSQL objects
  • Routine maintenance
  • Tools
  • Multiple models for deployment
  • Extensive documentation
  • Developed for multiple derivatives and versions of PostgreSQL
  • Multi-platform
• 1.1: The 1.1 version was published on 27 October 2016. This version includes several fixes and features reported from the PgAdmin4 1.0 release. Some features are as follows:
  • Added support for Python 3.5
  • Contain wait information on the dashboard activity tab

Bug fixes

• Issue #1155 is fixed to display the initial value if the user creates a sequence
• Issue #1531 is fixed for updating privileges for Materials Views and Views in which "string indices must be integers error" is shown.

• 1.2: The 1.2 version was published on 10 February 2017. This version includes several features reported from the PgAdmin4 1.1 release. Some features are as follows:
  • Shift the runtime from QtWebKit to QtWebEngine
  • Find and substitute functionality with group and regexp replacement
  • The data output panel's column width should be set to data
  • Supports setting the values of a field to "null"
  • macOS appbundle is unavailable PostgreSQL binaries
  • Remember finally used directory inside the file manager
• 1.3: The 1.3 version was published on 10 March 2017. This version includes several features reported from the PgAdmin4 1.2 release. Some features are as follows:
  • Syntax highlighting is more visible.
  • Improve syntax highlighting color visibility
  • Create a global Python wheel rather than per-python-version ones.
• 1.4: The 1.4 version was published on 13 April 2017. This version includes several features reported from the PgAdmin4 1.3 release. Some features are as follows:
  • Add the capability to disable/gray-out the option, i.e., "Save Password", when establishing a connection to any server
  • Show table DDL in the SQL tab for Greenplum
  • Included German translation
• 1.5: The 1.5 version was published on 19 May 2017. This version includes several features reported from the PgAdmin4 1.4 release. Some features are as follows:
  • Permit row and column selection inside a Query Tool
• 1.6: The 1.6 version was published on 13 July 2017. This version includes several features reported from the PgAdmin4 1.5 release. Some features are as follows:
  • Permit the Query Tool, web browser, and Developer tabs to be shifted to distinct monitors as wanted.
  • Set focus on the initially enabled field if a dialogue is unlocked.
  • Learn dialogues about F1 for help, Enter OK/Save, and Escape for Cancel.
  • Ask the user for saving dirty queries instead of deleting them for a natural workflow.
• 2.0: The 2.0 version was published on 13 July 2017. This version includes several features reported from the PgAdmin4 1.6 release. Some features are as follows:
  • Include a field in the Server Dialogue permitting users to describe a database subset they would like to view in the treeview.
  • Speed up application loading significantly
  • Permit for fast vs. slow connection failures
  • Default the file view of the browser to list
  • Permit queries to be discarded from the dashboard and show additional information inside the subnote control
  • Support SSL certificates usage for authentication
  • Support pgpass file usage
  • Update icons along with new designs and delete from menus to free up the user interface
• 2.1: The 2.1 version was published on 11 January 2018. This version includes several features reported from the PgAdmin4 2.0 release. Some features are as follows:
  • Permit connections to be highlighted in the Query Tool and treeview
  • Improve UI for selection query inside the Data Filter window
  • Develop data entry inside Query Tool
  • Permit configuration of the clipboard and CSV formatting of query outputs
  • Permit files to be launched by double clicking in Query Tool
• 3.0: The 3.0 version was published on 22 March 2018. This version includes several features reported from the PgAdmin4 2.1 release. Some features are as follows:
  • Permit sorting if editing/viewing data
  • Add the feature to activate/deactivate user interface animations
  • Add many options for keyboard navigation for the primary browser windows
  • Append keyboard navigation through Shift-Tab/Tab key in the Query Tool module
  • Provide keyboard navigation inside the debugger
  • Provide tab navigation inside the dialogs
  • Support configuration shortcut keys for several basic options inside the main window
• 3.1: The 3.1 version was published on 28 June 2018. This version includes several features reported from the PgAdmin4 3.0 release. Some features are as follows:
  • Supports SSH tunnelled connections
  • Supports the auto-complete keyword options in upper case
  • Supports NOTIFY/LISTEN inside the Query Tool
  • Permits sorting inside the file dialogue
  • Supports procedure and function of PG11
  • Permit the connection timeout to be set up on the peer-server basis
• 3.2: The 3.2 version was published on 9 August 2018. This version includes several features reported from the PgAdmin4 3.1 release. Some features are as follows:
  • Added release number for URLs to guarantee that files are just cached based on peer-version.
  • Supports SCRAM password modifications
  • Supports altering saved password
  • Supports JIT and Trigger stats inside the graphical query plan viewer
  • Supports triggers, indexes, unique key, foreign key, and primary key on partitioned tables for EPAS/PG 11.
  • Permit the user to describe a stable port number inside the runtime to help with cookie whitelisting etc.
  • Added a menu option to copy the URL of the appserver to the clipboard.
• 3.3: The 3.3 version was published on 6 September 2018. This version includes several features reported from the PgAdmin4 3.2 release. Some features are as follows:
  • Supports a geometry view that can provide PostGIS data on an empty canvas or several map sources.
  • Supports new backup/restore menus for PostgreSQL 11 and dump menus for 'pg_dumpall'.
  • Supports a Spanish translation
• 4.0: The 4.0 version was published on 1 October 2019. This version includes several features reported from the PgAdmin4 3.6 release. Some features are as follows:
  • New user interface design
  • Permits query plans to be loaded as the SVG file
  • Permit servers to be pre-installed into container deployments.
• 4.5: The 4.5 version was published on 10 October 2019. This version includes several features reported from the PgAdmin4 4.4 release. Some features are as follows:
  • Solved issue, i.e., 'Change Password' for SCRAM authentication
  • Guarantee that two succeeding SELECT statements should operate correctly
  • Supports suggested ESLinter checks
• 4.10: The 4.10 version was published on 4 July 2019. This version includes several features reported from the PgAdmin4 4.9 release. Some features are as follows:
  • Permit a few objects to be dropped/dragged into the Query Tool to enter their signature in the query text
  • Permit the path to be overridden to /pgadmin4/servers.json inside the container distribution
• 4.15: The 4.15 version was published on 14 November 2019. This version includes several features reported from the PgAdmin4 4.14 release. Some features are as follows:
  • Supports encrypted passwords for roles in reverse engineering SQL
  • Supports Beta (Dark) UI Theme
  • Added Enable Replica and Enable Always on triggers
  • Supports an option to appeal confirmation before resetting/canceling modifications on the Properties dialog
  • Supports custom theme selection and creation
• 4.20: The 4.20 version was published on 2 April 2020. This version includes several features reported from the PgAdmin4 4.19 release. Some features are as follows:
  • Supports FTS Template, FTS Parser, FTS Dictionary, FTS Configuration, and Collation for the Schema Diff
  • Supports Domain and its Types and Constraints for the Schema Diff
• 4.25: The 4.25 version was published on 20 August 2020. This version includes several features reported from the PgAdmin4 4.24 release. Some features are as follows:
  • Substitute the Flotr2 charting library to ChartJS with the help of React
  • Changed schema diff tool for comparing two databases rather than two schemas
  • Supports a -yes command line option to configure web.sh to permit non-interactive use
• 4.30: The 4.30 version was published on 28 January 2021. This version includes several features reported from the PgAdmin4 4.29 release. Some features are as follows:
  • Supports ERD diagram with basic DDL SQL generation, foreign key, primary key, and fields
  • Supports Kerberos authentication with SPNEGO to advance the Kerberos tickets from a browser
  • Supports Kerberos documentation
  • Supports ERD diagram documentation
• 5.0: The 5.0 version was published on 25 February 2021. This version includes several features reported from the PgAdmin4 4.30 release. Some features are as follows:
  • Make Dependencies, Dependents, and Statistics tabs closable, and anyone can add them with the option, i.e., 'Add panel'
  • Supports Logical Replication
  • Implemented runtime with NWjs to launch pgAdmin4 within a standalone window rather than the web browser and system tray
• 6.0: The 6.0 version was published on 7 October 2021. This version includes several features reported from the PgAdmin4 5.7 release. Some features are as follows:
  • Supports the clause, i.e., OWNED BY for sequences
• 6.5: The 6.5 version was published on 11 February 2022. This version includes several features reported from the PgAdmin4 6.4 release. Some features are as follows:
  • Supports open SQL help, online help, and Dialog help in an outside web browser
• 7.0: The 7.0 version was published on 13 April 2023. This version includes several features reported from the PgAdmin4 6.21 release. Some features are as follows:
  • Auto-extend row edit form if a row is newly added for Exclusion Constraint, Unique Constraints, Foreign Key, and Primary Key.
  • Supports mounting distributed storage within the server mode
  • Supports PostgreSQL server deployment on Google Cloud
  • Supports BigAnimal v3 API

Added option for building unique constraints using nulls, not distinct

pgAdmin vs. pgAgent

In very old pgAdmin versions, the tool is called pgAgent, which is used to shift as a package. However, we will find that pgAgent is available as an isolated download. We can select to use these tools independently or together, depending on our requirements.

The main aim of pgAgent is to schedule tasks within PostgreSQL. If we have both pgAgent and pgAdmin together, we can use pgAdmin to handle pgAgent. It can make our workflow convenient because of the graphical behavior of pgAdmin. Although, it is not essential.

pgAgent works on most of the PostgreSQL installations like pgAdmin. We will find every download link the same, except for Python and containerized versions, which aren't present for pgAgent.

Compatibility of pgAdmin

pgAdmin is a highly flexible tool created to work with every version of EDB Postgres Advanced Server and PostgreSQL.

Additionally, pgAdmin is suitable for Linux, Mac, and Microsoft Windows environments. Whether, we are hosting PostgreSQL in the cloud, locally, or with any other configuration, we can use pgAdmin.

Options to install and use pgAdmin are as follows:

• MacOS and Windows both have embedded download links for pgAgent and pgAdmin 4 on the pgAdmin website.
• Debian and Ubuntu users can download and install the APT package to start handling their PostgreSQL databases.
• Linux users of Fedora or REHL can download and install the RPM package to get began with database management.
• If we are using Python for our database development and management, we can install the package from pgAdmin.
• The PostgreSQL containerized installations are also compatible with pgAdmin. We need to download the container release (which offers the Docker logo) and then install it. Once we connect it, we can use pgAdmin in our browser.
• Finally, we need to download a source code for pgAdmin, which is available readily. It is helpful if we plan to change the previous code or start open-source development of this tool.

Features of pgAdmin

pgAdmin contains a huge variety of features, and the best way to check them all is by downloading a copy and trying it. Here is a short list to provide us with an idea of its abilities:

• Multi-platform
  • macOS
  • Linux
  • Microsoft Windows
• Developed for multiple PostgreSQL derivatives and versions
  • pgAdmin supports several versions of EDB Postgres Advanced Server and PostgreSQL that are in support during the publication of any given pgAdmin version.
• Huge documentation
  • PostgreSQL documentation links
  • pgAdmin application documentation
• Multiple deployment models
  • Server mode, supporting more than one user on the web
  • Desktop mode, with an aim built runtime
• Tools
  • ERD tool to design and document schemas
  • Schema tool to manage differences between schemas
  • Procedural language debugger (supporting pgsql/pl and edb-spl)
  • Grant wizard for fast updates to ACLs
  • Graphical query form display
  • Fast datagrid for entry/display of data
  • Strong query tool using color syntax highlighting
• Routine maintenance
  • Batch/shell/SQL job scheduling agent
  • Analyze, vacuum, restore, and backup on demand
  • Monitoring dashboard
  • Auto-vacuum management
• Establish, view, and change all basic PostgreSQL objects
  Objects are represented with a user-friendly property list and SQL definition. Dependency objects and dependent may be browsed also, and object statistics viewed where needed.
  • Constraints
  • Compound Triggers
  • Columns
  • Collations
  • Check Constraints
  • Catalogs
  • Catalog Objects
  • Casts
  • Domains
  • Domain Constraints
  • Databases
  • Extensions
  • Exclusion constraints
  • Event Triggers
  • Functions
  • Foreign Keys
  • FTS Configurations
  • Indexes
  • Languages
  • Materialized Views
  • Packages
  • Procedures
  • Resource Groups
  • Schemas
  • Steps
  • Synonyms
  • Types
  • Triggers
  • Tables
  • User mappings
  • Unique Constraint
  • Views
  • Variables
  • pgAgent Steps
  • pgAgent Schedules
  • pgAdmin Jobs
• Multibyte support
  Almost all server-side encodings of PostgreSQL are supported by pgAdmin. Some of them are listed below:
  • WIN
  • WIN874
  • TCVN
  • WIN1256
  • ALT
  • KOI8
  • MULE_INTERNAL
  • UNICODE/UTF-8
  • ISO_8859_8, ISO_8859_7, ISO_8859_6, ISO_8859_5
  • LATIN1, LATIN2, LATIN3, LATIN4, LATIN5, LATIN6, LATIN7, LATIN8, LATIN9, LATIN10
  • JOHAB
  • EUC_TW, EUC_KR, EUC_CN, EUC_JP
  • SQL_ASCII

pgAdmin development and their resources

• GIT access
  pgAdmin is an existed subject to the license terms. It is developed with several GIT repositories to keep the source code. The repository access is available from a web interface or with our favorite GIT client.
• Mailing list
  The mailing list, i.e., pgAdmin hackers, is wanted for developers who want to contribute to the pgAdmin project.
• TODO list
  It is a list of aspects that have been requested or are planned for addition in a future pgAdmin version. There is no assurance that any aspect will be worked in any specific release because pgAdmin is the volunteer project.
• Change log
  It is the notable change record made on pgAdmin by several developers. Manually, it is a maintained list and may not add information on all minor changes that have ever been made.

Styleguide of pgAdmin: Typography

pgAdmin mainly applies Google Roboto for the entire content, and Source Code Pro is utilized for SQL and other codes. The type arrangement involves letter spacing, line spacing, line lengths, point sizes, selecting typefaces, and space adjustment between letter pairs.

Styleguide of pgAdmin: Themes

• Color Palettes
• Dropdowns
• Input Fields
• Buttons
• Toggle Buttons
• Radio Buttons
• Checkboxes
• Alerts
• Accordings
• Tables
• Tab Sets
• Menus

Styleguide of pgAdmin: Color Palettes

pgAdmin utilizes standard color sets for style and consistency. Also, the team members of pgAdmin are committed to satisfying with standard contrast ratio (AA). To do it, select primary, secondary, and tertiary colors that provide support for usability by giving enough color contrast between components so that users with low vision can check and apply the interface.

pgAdmin utilizes the Bootstrap framework for the sensible layout and has personalized it with CSS based on particular requirements.

Styleguide of pgAdmin: Iconography

• Query Plans
• Tree View
• Custom Icons
• Font Awesome

Styleguide of pgAdmin: Font Awesome

Icons can be defined as visual representations of common actions, files, or commands. They should be legible, simple, and meaningful. pgAdmin applies Font Awesome 5 and custom icons.

Guidelines to select icons:

• Icons should be expressive of the relative functionality.
• All custom icons are used for Query explain and Tree view section. Font
• Awesome icons are used for every other file, action, and command.
• Use both outlined and filled icons depending on the needed legibility and context.
• Clickable icons should have a tooltip connected to let the customer know what happens when it's clicked.

pgAdmin Development Team

The project is handled and executed by the development team. The following document outlines the team structure and how it works.

Purpose

The development team of pgAdmin is available for two reasons:

• To make sure that pgAdmin remains available freely to everyone that wishes it.
• To maintain and manage the pgAdmin project.

Structure

The foundation is composed of the project members and leaders:

• Members: The Project members can be project contributor who gets an invitation from the previous Development Team.
• Project Leader: Dave Page is the project leader, primary and founder project developer. Also, the project leader is a member.

Responsibility Division

The major decisions should be voted on by the development team regarding the project. The votes will be made by email from the corresponding mailing list. Responses will be discounted through the vote if not received in 2 weeks.

Support of pgAdmin

pgAdmin is a community project which is composed of several volunteers worldwide. They don't offer 'paid for' support, although they do aim to resolve bugs and help out whenever possible.

Self Help

If we have any issue that we need help with, make sure we check out the pgAdmin documentation for the solution. If we can't find the solution, try finding it on Google. Make sure we include any corresponding error message in our query.

Community Help

If we cannot find the solution we need when using Google or in the documentation, we can post a query to the Support Mailing List.

Bugs and Issues

If we believe we have found an error in pgAdmin, we can log the corresponding details on the issue tracker.

pgAdmin Issues

Bugs and other problems in pgAdmin are generally fixed in a very short time when they have been sent to them. There are a few problems that might be out of their control or might take many efforts to resolve, in which case they might not be resolved.

Existing issues

If we believe we have found an issue or bug, we need to check the issue list before logging other issues. If we find an issue that is the same as the one we are seeing, we can feel free to add any helpful extra details we are able to give.

Logging a new issue

If we believe we have found a new issue or bug in pgAdmin, we can log it into the issue tracker. Try to add as much of the below information as possible:

• pgAdmin version we are running and from which we got it.
• Whether we are running in desktop or server mode.
• Steps needed to reproduce an issue. We need to add a simple schema using optional data if needed. It might be privately sent upon request from a developer if we are concerned about posting sample data or code to a public mailing list.
• The version and operating system details, e.g., macOS 10.11.5 or Windows 10.
• Any logfile result we may have.

Downloading pgAdmin

We need to visit the https://pgadmin.org website and the Download section.

We can find different options lists to download pgAdmin to our machine in the Download section.

There are several ways to get pgAdmin. A short understanding of every different option available in the download section is explained as follows:

• Container: pgAdmin can be downloaded on any docker container and linked to the container. A docker container will disclose the endpoints to use the pgAdmin server over any browser. We should utilize this option if we want our pgAdmin to independently run of the OS.
• macOS: If we are managing and developing a Postgres database on Mac, we should select this option to download pgAdmin on our machine. It will establish a standalone tool that will execute pgAdmin on any browser.
• Python: Also, pgAdmin is available to be downloaded as a Python package using the PyPi library. We can see more information about the Python package by going through the official URL.
• APT: It can be used to download the PostgreSQL management tool on a Debian 9 or Ubuntu instance. We can get more details about getting pgAdmin on a Debian or Ubuntu flavor through the official website.
• RPM: The RPM releases target the Linux flavors for Fedora and RHEL like the APT releases.
• Source Code: In case we are associated with a few open-source development or we wish to change a few existing codes using pgAdmin, we can install the raw source code through the mirrored links. Since downloading pgAdmin using a raw source code needs sufficient technical knowledge; hence, it is advised to get it by downloading any one of the binary packages rather than the source code.
• Windows: It permits pgAdmin to be downloaded for the windows user base. Downloading pgAdmin on a Windows device is much easier and more intuitive as we can simply pursue the steps and get it downloaded on our device.