psql

Database management

This post is on database management with psql.

Postgres comes with a built-in Terminal access that allows you to carry out administrative functions without needing to know their actual SQL commands, psql. Start psql by typing at the Terminal prompt:

$ psql yourdbcluster

$ psql geoimagine

When psql is active, the last line in the Terminal window changes and now indicates the database (‘postgres’=#), and the psql command line prompt (#):

geoimagine=#

Use the psql command line to query PostgreSQL for which users are installed:

# \du

The Terminal window should return at least one role - in PostgreSQL terminology ‘user’ is called ‘role’. By default Homebrew adds the system logged in system (machine) user (‘yourUser’) that installed PostgreSQL as a ‘SuperUser’. You should also see the attributes connected to ‘yourUser’, and which groups you are a member of (none at start). If you added other users they will also be seen.

List of roles
Role name       |                         Attributes                         |    Member of
-----------------+------------------------------------------------------------+-----------
karttur         | Superuser, Create role, Create DB                          | {}
thomasgumbricht | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

List the installed databases by typing the command:

# \l

Name    |      Owner      | Encoding | Collate | Ctype |          Access privileges          
------------+-----------------+----------+---------+-------+-------------------------------------
geoimagine | karttur         | UTF8     | C       | C     |
postgres   | thomasgumbricht | UTF8     | C       | C     |
template0  | thomasgumbricht | UTF8     | C       | C     | =c/thomasgumbricht                 +
				|                 |          |         |       | thomasgumbricht=CTc/thomasgumbricht
template1  | thomasgumbricht | UTF8     | C       | C     | =c/thomasgumbricht                 +
				|                 |          |         |       | thomasgumbricht=CTc/thomasgumbricht

List all the schemas in the present database cluster:

# \dn

List of schemas
Name     |      Owner      
--------------+-----------------
ancillary    | karttur
export       | karttur
landsat      | karttur
layout       | karttur
modis        | karttur
process      | karttur
public       | thomasgumbricht
regions      | karttur
sentinel     | karttur
smap         | karttur
soilmoisture | karttur
specimen     | karttur
system       | karttur
userlocale   | karttur

To list all table in all schemas:

# \dt .

or list tables for a particular schema:

# \dt process.*

List of relations
Schema  |         Name          | Type  |  Owner  
---------+-----------------------+-------+---------
process | procdiv               | table | karttur
process | processparams         | table | karttur
process | processparamsetminmax | table | karttur
process | processparamsetvalues | table | karttur
process | procsys               | table | karttur
process | rootprocesses         | table | karttur
process | subprocesses          | table | karttur
(7 rows)