Pgadmin ��� linux ������

How to Install PostgreSQL and pgAdmin4 in Ubuntu 20.04

This guide will walk you through the instructions to install PostgreSQL 12 relational and object-oriented database management systems and pgAdmin4, a commonly-used web-based PostgreSQL database server administration tool. We will show how to install the latest version of pgAdmin4 that is v4.23.

Prerequisites:

Let’s get started…

Installing PostgreSQL in Ubuntu 20.04

Log into your Ubuntu system and update the system software packages using the following apt command.

Now install the latest version of PostgreSQL from the default Ubuntu repositories.

During the installation, the installer will create a new PostgreSQL cluster (a collection of databases that will be managed by a single server instance), thus initialize the database. The default data directory is /var/lib/postgresql/12/main and the configurations files are stored in the /etc/postgresql/12/main directory.

After PostgreSQL installed, you can confirm that the PostgreSQL service is active, running and is enabled under systemd using the following systemctl commands:

Check PostgreSQL Status

Also, confirm that the Postgresql server is ready to accept connections from clients as follows:

PostgreSQL Accepting Client Connections

Creating Database in PostgreSQL

To create a new database in PostgreSQL, you need to access the PostgreSQL database shell (psql) program. First, switch to the postgres system user account and run the psql command as follows:

Now create a new database and a user using the following commands.

Create a Database in PostgreSQL

Configuring PostgreSQL Client Authentication

PostgreSQL uses client authentication to decide which user accounts can connect to which databases from which hosts and this is controlled by settings in the client authentication configuration file, which on Ubuntu is located at /etc/postgresql/12/main/pg_hba.conf.

Open this file using your favorite text editor as shown.

PostgreSQL uses many types of client authentication methods including peer, ident, password, and md5 (read the PostgreSQL 12 documentation for a detailed explanation of each method).

md5 is the most secure and recommended because it requires the client to supply a double-MD5-hashed password for authentication. So, ensure that the entries below have md5 as the under method:

After making changes in the Client Authentication configuration file, you will need to restart the PostgreSQL service.

Installing pgAdmin4 in Ubuntu

pgAdmin4 is not available in the Ubuntu repositories. We need to install it from the pgAdmin4 APT repository. Start by setting up the repository. Add the public key for the repository and create the repository configuration file.

Then install pgAdmin4,

The above command will install numerous required packages including Apache2 webserver to serve the pgadmin4-web application in web mode.

Once the installation is complete, run the web setup script which ships with the pgdmin4 binary package, to configure the system to run in web mode. You will be prompted to create a pgAdmin4 login email and password as shown in the screenshot below.

This script will configure Apache2 to serve the pgAdmin4 web application which involves enabling the WSGI module and configuring the pgAdmin application to mount at pgadmin4 on the webserver so you can access it at:

It also restarts the Apache2 service to apply the recent changes.

Remember to replace [email protected] with your email address and set a strong secure password as well:

Set Up PgAdmin in Ubuntu

Accessing pgAdmin4 Web Interface

To access the pgAdmin4 web application interface, open a web browser, and use the following address to navigate:

Once the login page loads, enter the email address and password you created in the previous section while configuring the pgAdmin4 to run in web mode.

PgAdmin Login

After a successful login, you will be land in the pgAdmin4 web application dashboard. To connect to a server, click on Add New Server as highlighted in the following screenshot.

Add New Server in PgAdmin

Next, enter the connection in General settings (Name, Server group, and a comment). Then click Connections as highlighted in the following screenshot.

Add New Connection in PgAdmin

Next, enter the PostgreSQL database server hostname/address, Port number (leave 5432 to use default), select the Maintenance database (which should be postgres), enter the database username and password.

PostgreSQL Database Settings

If the database access credentials are OK and the server-client authentication configuration is too, pgAdmin4 should successfully connect to the database server.

PostgreSQL Databases

That’s all! For more information, see the PostgreSQL 12 documentation and pgAdmin 4 documentation. Remember to share your thoughts with us via the comment section below.

If You Appreciate What We Do Here On TecMint, You Should Consider:

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

We are thankful for your never ending support.

Источник

Getting Started¶

Pre-compiled and configured installation packages for pgAdmin 4 are available for a number of desktop environments; we recommend using an installer whenever possible.

In a Server Deployment, the pgAdmin application is deployed behind a webserver or with the WSGI interface. If you install pgAdmin in server mode, you will be prompted to provide a role name and pgAdmin password when you initially connect to pgAdmin. The first role registered with pgAdmin will be an administrative user; the administrative role can use the pgAdmin User Management dialog to create and manage additional pgAdmin user accounts. When a user authenticates with pgAdmin, the pgAdmin tree control displays the server definitions associated with that login role.

In a Desktop Deployment, the pgAdmin application is configured to use the desktop runtime environment to host the program on a supported platform. Typically, users will install a pre-built package to run pgAdmin in desktop mode, but a manual desktop deployment can be installed and though it is more difficult to setup, it may be useful for developers interested in understanding how pgAdmin works.

It is also possible to use a Container Deployment of pgAdmin, in which Server Mode is pre-configured for security.

Pre-compiled and configured installation packages are available for a number of platforms. These packages should be used by end-users whereever possible — the following information is useful for the maintainers of those packages and users interested in understanding how pgAdmin works.

The pgAdmin 4 client features a highly-customizable display that features drag-and-drop panels that you can arrange to make the best use of your desktop environment.

The tree control provides an elegant overview of the managed servers, and the objects that reside on each server. Right-click on a node within the tree control to access context-sensitive menus that provide quick access to management tasks for the selected object.

The tabbed browser provide quick access to statistical information about each object in the tree control, and pgAdmin tools and utilities (such as the Query tool and the debugger). pgAdmin opens additional feature tabs each time you access the extended functionality offered by pgAdmin tools; you can open, close, and re-arrange feature tabs as needed.

Use the Preferences dialog to customize the content and behaviour of the pgAdmin display. To open the Preferences dialog, select Preferences from the File menu.

Help buttons in the lower-left corner of each dialog will open the online help for the dialog. You can access additional Postgres help by navigating through the Help menu, and selecting the name of the resource that you wish to open.

You can search for objects in the database using the Search objects

Before using pgAdmin to manage objects that reside on a server, you must define a connection to the server; for more information please see Connecting to a Server in the next section.

Источник

pgAdmin

PostgreSQL Tools

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.

pgAdmin may be used on Linux, Unix, macOS and Windows to manage PostgreSQL and EDB Advanced Server 9.5 and above.

Quick Links

Latest Videos

Want to see your pgAdmin video here? Email webmaster@pgadmin.org.

2021-10-07 — pgAdmin 4 v6.0 Released

The pgAdmin Development Team are pleased to announce pgAdmin 4 version 6.0. This release of pgAdmin 4 includes 18 bug fixes and new features. For more details please see the release notes.

Notable changes in this release include:

Introduction to ReactJS framework in pgAdmin 4:

ReactJS has a strong developer community and popularity. It has grown to a level where its performance is comparable to that of desktop applications. Migrating pgAdmin to use ReactJS has proven to boost pgAdmin’s performance and user experience.

In this release we have ported the browser Tree, all the object dialogs, and the Grant Wizard.

Features:

Added support for OWNED BY Clause for sequences.

This feature adds the OWNED BY clause for the sequences. The OWNED BY option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well.

Bugs/Housekeeping:

• Port object nodes and properties dialogs to React.
• Port browser tree to React.
• Port Grant Wizard to react.
• Remove GPDB support completely.
• Added support to create the Partitioned table using COLLATE and opclass.
• Fixed keyerror issue in schema diff for ‘attnum’ and ‘edit_types’ parameter.
• Fixed an issue where the Execute button of the query tool gets disabled once we change anything in the data grid.
• Ensure that SQL help should work for EPAS servers.
• Fixed an issue where the grant wizard is unresponsive if the database size is huge.

Note: The publication of the python package to PyPi has been delayed and will be completed as soon as possible.

2021-09-09 — pgAdmin 4 v5.7 Released

The pgAdmin Development Team are pleased to announce pgAdmin 4 version 5.7. This release of pgAdmin 4 includes 26 bug fixes and new features. For more details please see the release notes.

Notable changes in this release include:

Features:

• Added support for the truncate table with restart identity.
• Added database and server information on the Maintenance process watcher dialog..
• Allow the referenced table to be the same as the local table in one to many relationship for ERD Tool..
• Make closing tabs to be smarter by focusing on the appropriate tab when the user closed a tab..
• Set PSQLRC and PSQL_HISTORY env vars to apt. user storage path in the server mode..

Bugs/Housekeeping:

• Fixed blank screen issue on windows and also made changes to use NWjs manifest for remembering window size.
• Ensure that trigger function SQL should have ‘create or replace function’ instead of ‘create function’ only.
• Fixed the export image issue where relation lines are over the nodes.
• Fixed width limitation issue in PSQL tool window.
• Fixed an issue where columns with sequences get altered unnecessarily with a schema diff tool.
• Ensure that the lock panel should not be blocked for larger records.
• Fixed an issue where whitespace in function bodies was not applied while generating the script using Schema Diff.
• Introduced the OAUTH2_SCOPE variable for the Oauth2 scope configuration.
• Enables pgAdmin to retrieve user permissions in case of nested roles, which helps to terminate the session for AWS RDS.
• Ensure that pgAdmin should not fail at login due to a special character in the hostname.

2021-08-12 — pgAdmin 4 v5.6 Released

The pgAdmin Development Team are pleased to announce pgAdmin 4 version 5.6. This release of pgAdmin 4 includes 14 bug fixes and new features. For more details please see the release notes.

Notable changes in this release include:

Features:

Added support to copy SQL from main window to query tool.

This feature is used to copy the SQL script for the selected browser tree node in the query tool. For this we have added new preferences setting ‘Copy SQL from main window to query tool?’

Added support for formatted JSON viewer/editor when interacting with data in a JSON column.

This feature allows the user to format, view and edit JSON data in a very nice way. JSON editors have so many formats to view data differently.

Bugs/Housekeeping:

• Rename the «Resize by data?» to «Columns sized by» and disabled the ‘Maximum column width’ button if ‘Columns sized by’ is set to ‘Column data’.
• Ensure that the login account should be locked after N number of attempts. N is configurable using the ‘MAX_LOGIN_ATTEMPTS’ parameter.
• Fixed CSRF errors for stale sessions by increasing the session expiration time for desktop mode.
• Fixed an issue in the search object when searching in ‘all types’ or ‘subscription’ if the user doesn’t have access to the subscription.
• Fixed an issue where paste was not working through the Right-Click option on PSQL.
• Fixed TypeError ‘NoneType’ object is not sub scriptable.
• Fixed an issue where the titles in query tabs are different.
• Fixed dashboard server activity issue when active_since parameter is None.

2021-07-15 — pgAdmin 4 v5.5 Released

The pgAdmin Development Team are pleased to announce pgAdmin 4 version 5.5. This release of pgAdmin 4 includes 18 bug fixes and new features. For more details please see the release notes.

Notable changes in this release include:

Features:

Highlighted long running queries on the dashboards

This feature is implemented to highlight the long running queries. It allows the user to provide the warning and alert threshold values in minutes. Depending on the threshold value pgAdmin4 highlights the long running queries on the Dashboard.

Added support for Reassign/Drop Owned for login roles.

This feature allows the user to change the ownership of database objects owned by a database role or remove database objects owned by a database role.

Added support for OAuth 2 authentication

This feature allows the user to connect to the pgAdmin 4 server using OAuth2 authentication. To enable OAUTH2 authentication for pgAdmin, you must configure the OAUTH2 settings in the config_local.py or config_system.py file.

Added option to provide maximum width of the column when ‘Resize by data?’ option in the preferences is set to True

Do not block the query editor window when running a query

Bugs/Housekeeping:

• Ensure that the Data Output panel can be snapped back after it is detached.
• Fixed replace keyboard shortcut issue in the query tool on the normal keyboard layout.
• Remove leading whitespace and replace it with ‘[. ] ‘ in the Query Tool data grid so cells don’t look empty.
• Fixed an issue in the search object when searching in ‘all types’ or ‘subscription’ if the user doesn’t have access to the subscription.
• Fixed an issue where the New Connection Drop Down has lost default maintenance database, auto-select, and tab-through functionality..
• Ensure that setting ‘Open in new browser tab’ should be visible, it should not be based on the value of ‘ENABLE_PSQL’.
• Disable email deliverability check that was introduced in flask-security-too by default to maintain backwards compatibility.
• Fixed an issue where incorrect column names were listed in the properties of Index.

2019-11-21 — Try pgAdmin online!

Our friends at EnterpriseDB have kindly made an online demo environment for pgAdmin available to allow new users to try it out in their browser without needing to download or install any software. The environment (based on the Katacoda learning platform) includes pgAdmin and PostgreSQL 12, preloaded with the pagila sample database, and is unique to every user allowing a far better experience than typical shared demo environments.

2018-07-12 — pgAgent v4.0.0 Released

The pgAdmin Development Team are pleased to announce the release of pgAgent v4.0.0.

pgAgent is a job scheduler for PostgreSQL; for more information please see the documentation included as part of the pgAdmin documentation at https://www.pgadmin.org/docs/pgadmin4/3.x/pgagent.html.

We expect DEB and RPM packages to be available from the PostgreSQL APT/YUM repositories in due course, as well as an updated installer from EnterpriseDB available through StackBuilder.

Notable changes in this release include:

• Remove the dependency on wxWidgets. pgAgent now uses Boost for thread management and synchronisation [Neel Patel]
• Refactor connection parsing logic to fix issues with and improve handling of connection strings [Thomas Krennwallner]
• Handle 2 digit server version numbers [Ashesh Vashi]
• Fix handling of the «succeeded» flag on job steps which could prevent failure of steps causing the following steps to fail [Sanket Mehta]

Источник