Installation | Maintenance | Beyond Lino

Use a PostgreSQL database

If you decided to use PostgreSQL as database engine, then here is a cheat sheet for quickly doing so.

Installation

Install PostgreSQL on your site:

$ sudo apt install postgresql

Install the PostgreSQL client into your project’s virtualenv:

$ pip install psycopg2-binary

Show all users:

$ sudo -u postgres psql -c \\du postgres

                             List of roles
Role name |                   Attributes                   | Member of
----------+------------------------------------------------+-----------
django    |                                                | {}
postgres  | Superuser, Create role, Create DB, Replication | {}

Show all databases:

$ sudo -u postgres psql -c \\l postgres
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 cfoo      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 dfoo      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 bfoo      | django   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(6 rows)

Create a new database:

$ sudo -u postgres createdb <DB>

$ sudo -u postgres psql -c GRANT ALL PRIVILEGES ON DATABASE <DB> TO <USER>

Remove a database:

$ sudo -u postgres dropdb <DB>

Dump a database:

$ pg_dump <DB> > backup.psql

Restore a database:

$ sudo -u postgres psql <DB> < backup.psql

Postgresql commands cheat sheet

? list all the commands

l list databases

conninfo display information about current connection

c [DBNAME] connect to new database, e.g., c template1

dt list tables of the public schema

dt <schema-name>.* list tables of certain schema, e.g., dt public.*

dt . list tables of all schemas

Any SQL statement, e.g., SELECT * FROM my_table;

q quit psql