Installation | Topics | 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
----------+------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication | {}
NB In case you keep getting error messages “could not change directory to “/home/joe”: Permission denied”, read this
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 <DBNAME>
$ sudo -u postgres psql -c GRANT ALL PRIVILEGES ON DATABASE <DBNAME> TO <USERNAME>
$ sudo -u postgres psql -c "CREATE USER <USERNAME> WITH PASSWORD '<PASSWORD>';"
Change password of an existing user:
$ sudo -u postgres psql -c "ALTER USER <USERNAME> WITH PASSWORD '<PASSWORD>';"
Check whether your Lino site collaborates with the database:
$ pm dbshell
This should output something like:
psql (14.13 (Ubuntu 14.13-0ubuntu0.22.04.1))
Type "help" for help.
django=>
If you get error message psql: error: connection to server on socket
"/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed
for user "django" then edit your pg_hba.conf and change “peer” into
“md5” (source):
sudo nano /etc/postgresql/14/main/pg_hba.conf
Remove a database:
$ sudo -u postgres dropdb <DBNAME>
Dump a database:
$ pg_dump <DBNAME> > backup.psql
Restore a database:
$ sudo -u postgres psql <DBNAME> < backup.psql