ruk·si

🐘 PostgreSQL
Visual Clients

Updated at 2015-11-03 21:40

The most powerful interface to PostgreSQL databases is raw SQL, but it's frequently equally important to get a good overview how the database is structured, especially if you are not too familiar with all aspects of PostgreSQL. Database admin tools and clients like pgAdmin are great for that and here are some notes related to those tools.

Main user interface hierarchy:

  • Server Group: Client-specific grouping of servers for your own use.
  • Server: Server connection configuration like address, user, password etc. Note that you frequently have multiple server connection configurations for the same database e.g. for different users. You can also specify color to servers, I always use red on production servers.
    • Database: Database has it's own privilege management and has the a set of the following entities. One server may contain multiple databases.
      • Catalog: All schemas that are for the database management software. Everything from authentication to available data types are in here.
      • Event Triggers: Super triggers that can be bound to global or to specific database context.
      • Extensions: Non-standard PostgreSQL libraries like UUID generation or geo-location features.
      • Schemas: Each database contains one or more schemas. You can make queries between different schemas without closing the connection, but not between separate databases.
      • Slony Replication: Active master-slave replications on this database.
    • Tablespace: Where the database files are stored on the host file system. One server may contain multiple tablespaces.
    • Group Roles: Group credentials for group roles. Membership information about which login roles are in which groups.
    • Login Roles: Login credentials for users, applications and services.

Each schema has:

  • Collations: Schema specific collations.
  • Domains: Schema specific domains. Domains are data types with restrictions rules e.g. string type with max length of 10.
  • FTS Configurations: TODO
  • FTS Dictionaries: TODO
  • FTS Parsers: TODO
  • FTS Templates: TODO
  • Function: Schema specific functions.
  • Sequences: Schema specific sequences
  • Tables: Schema specific tables.
  • Trigger Functions: Schema specific triggers. Trigger is a definition that a certain functions to to be called before of a database event like UPDATE or INSERT.
  • Views: Schema specific views and materialized views. Views are SELECT queries that function as tables and materialized views are views that are cached into the file system.

"Dropping" the SERVER means removing the connection settings. So it doesn't remove anything except your local settings. Be very careful not to drop the DATABASE, that is usually something you don't want to do.