Documentation

10.0 psql : https://www.postgresql.org/docs/10/app-psql.html

Basic useful info

Server program : postgres
Client program : psql
Default listener port : 5432
Jdbc Url :
jdbc:postgresql:database
jdbc:postgresql://host/database
jdbc:postgresql://host:port/database
config location : /var/lib/postgresql/data/postgresql.conf


psql Commands

Connect to the interactive terminal by specifying the user :
psql -U fooUser

Execute a query specifying the user/database :
psql -U foo-user -c "select 1"

Execute a SQL file script with echo all by specifying the user :

Meta commands in psql

list all databases:
\list or \l

connect to a database :
\c or \connect [ dbname [ username ] [ host ] [ port ] | conninfo ]
Example : \c foodb

List tables : \dt
List sequences : \ds
List tables, views and sequences : \d

List schemas : \dn

Describe structure (table,..) : \d

Quit (important to free the connection) : \q

psql help : \?

Client connections

Manage client connections from the OS

On Linux, each client connection is managed by the PostgreSQL server as a distinct OS process.
We can count connections as :
ps aux | grep "postgres: client" | wc -l
A client connection looks like a process as :

 4228 postgres  0:00 postgres: client client 192.168.64.5(59022) idle

Killing the process kills the client connection.

Manage client connections from the DB

The current connections are stored in the pg_stat_activity table.
To count them :

client=# SELECT count(*) FROM pg_stat_activity a where a.backend_type='client backend';
 count 
-------
    99
(1 row)

To know the number of max connection for the current configuration and possible min and max values for that.
Changing the current configuration for max connection(boot_val field) requires a db restart to take effect

client=# select boot_val, min_val, max_val from pg_settings where name='max_connections';
 boot_val | min_val | max_val 
----------+---------+---------
 100      | 1       | 262143
(1 row)

Alternative way to show current max_connections config and update it to 1000 (for example) :
SHOW max_connections;
ALTER SYSTEM SET max_connections TO '1000';

SQL commands hints :

Syntax

-Keywords (user for example) may be used as identifier but have to be escaped with  » such as : "user".
-Double quotes are for names of tables or fields. You can omit them but in the previous case.
-Single quotes are for string values.
-in psql shell, SQL commands have to finish with ; to be executed.
-PostgreSQL unquoted identifiers are case-insensitive (For a foo table : SELECT * FROM foo)
-PostgreSQL quoted identifiers are case-sensitive (For a « foo » table : SELECT * FROM « foo »)

Query examples

Basic test query (equivalent to select * from dual in Oracle) :
select 1