Show Database in PostgreSQL
In PostgreSQL, you can “show” databases using the interactive terminal client psql or SQL commands to query the system catalogue. SQL queries on the PostgreSQL system catalogue, notably the pg_database table, can also extract database information. For instance, SELECT datname FROM pg_database will list all databases. One of the most popular system catalogue tables for cluster database meta-information is pg_database.
Database clusters are managed by PostgreSQL servers. Initdb creates postgres, template1, and template0 when a database cluster is established. List all databases to see these template databases. Template0 is a fallback, while template1 models new databases. If you prefer a graphical interface, pgAdmin, a popular free GUI tool for PostgreSQL administration, lets you connect to and manage numerous servers and inspect tables, views, and schemas.
Listing Databases
Most database listings in PostgreSQL interactive terminal, psql, use the \l meta-command. This command displays all database cluster databases and offers important information about them. Output usually includes:
Name: Database name. A site can create any number of PostgreSQL databases with alphabetic initial names and 63-byte name limits.
Owner: Database owner. Each database has an owner and permissions. On database creation, the owner is automatically updated to the current role.
Encoding: The database’s chosen character set encoding, such as SQL_ASCII or UTF8. One character set is usually the only one supported by PostgreSQL databases.
Collate: Collation determines how PostgreSQL compares, orders, and processes strings by sort order and character categorisation. Case-sensitive or accent-sensitive comparisons and character categorisation (letters or punctuation) are examples.
Ctype: The locale in which the database uses character categorisation (e.g., upper, lower, digit).
Access privileges: Information about which roles have particular database permissions (such as create, temporary, and connect). The privileges are implied to apply to the special PUBLIC role if the user portion is absent.
Querying System Catalogs
Using a SQL SELECT statement to directly query the PostgreSQL system catalogues provides an additional way to list databases. A vital system catalogue that lists every database in the PostgreSQL cluster is the pg_database table. SELECT datname FROM pg_database is an example query.
This query returns only database names. Combine it with other system catalogue tables or select pg_database fields for more details. Database sizes are human-readable from pg_database_size and pg_size_pretty. Name, size, pg_size_pretty(datname) from pg_database;
Table, view, function, index, and other database object metadata is in the system catalogue. The information_schema schema simplifies meta-data ANSI SQL views in PostgreSQL.
Graphical User Interfaces (GUIs)
A graphical interface for managing and accessing databases is provided by programs such as DBeaver. The Database Navigator serves as the main location within DBeaver where users may examine and interact with database metadata objects. By selecting an arrow adjacent to the database name, you can expand a list of available metadata objects and investigate these things. Users may see and work with databases, schemas, tables, columns, and more using DBeaver’s support for multiple PostgreSQL metadata types.
DBeaver offers choices such as
- Selecting “Show all databases” will cause the Database Navigator to show every database.
- Click “Show databases statistics” to see database statistics.
- The Database Navigator has three view modes (Simple, Advanced, and Custom) to customise how database objects are shown.
Underlying Concepts
Knowing the fundamentals of PostgreSQL will help you better understand database listings:
Database Cluster: A database cluster is a disc storage space that is initialised during a PostgreSQL installation. A single database server instance is in charge of overseeing a group of databases in this cluster. On a single server, you can operate several PostgreSQL instances (clusters), possibly even with distinct PostgreSQL versions.
Template Databases: Postgres, template1, and template0 are the three databases that are automatically created when a PostgreSQL cluster is initialised using initdb.
Postgres: PostgreSQL, or Postgres, is a sophisticated. It evolved from Professor Michael Stonebraker’s 1986 POSTGRES system at the University of California, Berkeley. The term “Postgres” comes from Stonebraker’s pioneering relational database system Ingres.
Template1:Every time a new database is generated, this one is cloned. Extensions that ought to be compatible with all new databases can be installed onto it.
Template0:In the event that template1 is corrupted or a new locale is needed, template0 offers a backup plan. It is an unaltered copy that must not be altered. To guarantee a clean start without any special objects or settings from template1, it is frequently advised to create a new database from template0 (createdb -T template0 dbname, for example).
Roles and Privileges: Roles are used to control database access rights. A role has the ability to assign privileges, own database objects, and be a database user or group of users. To list existing roles, use the psql \du meta-command.
Related Database Commands
Other commands manage the database lifetime without SHOW DATABASE:
CREATE DATABASE: Create a database in a PostgreSQL cluster with the CREATE DATABASE command. The simplest form is CREATE DATABASE name;, which must fulfil SQL identifier standards, usually having an alphabetic first character and 63 bytes. The role executing the action automatically owns the new database and can remove it later.
DROP DATABASE: A database is deleted with the SQL command DROP DATABASE. Use this action cautiously because it permanently deletes all linked files and objects. The dropdb shell script wraps it.
Conclusion
PostgreSQL offers various methods for listing and inspecting databases, from basic meta-commands like \l to extensive SQL queries on the pg_database system catalogue. These methods display database names, owners, encodings, locales, privileges, and default template databases (postgres, template1, and template0) produced during cluster initialisation. Administration is easier with knowledge of database clusters, template roles, and rights. Graphical utilities like pgAdmin and DBeaver provide navigation, statistics, and metadata views. Both command-line and GUI users can see and control their PostgreSQL setups with these options.