Databases

PostgreSQL

PostgreSQL configs postgresql.conf and pg_hba.conf are stored where the PostgreSQL database cluster was initialized with initdb. This directory can be initialized anywhere, but the default in Red Hat systems is /var/lib/pgsql/data.

Tasks

Setup PostgresQL

Arch Red Hat Ubuntu

pacman -S postgresql
su - postgres -c 'initdb --pgdata /var/lib/postgres/data' # (1)
systemctl enable postgresql --now

On Arch, this step appears to be necessary before the postgresql service can be enabled. initdb requires a directory to be explicitly specified using --pgdata or alternatively the PGDATA environment variable.

dnf install libpq-devel mariadb-devel postgresql postgresql-server
postgresql-setup --initdb # (1)
systemctl enable postgresql --now

This command facilitates initialization of the database cluster, which defaults to /var/lib/pgsql/data, similar to using initdb.

apt install libpq-dev

systemctl start postgresql
sudo -u postgres psql

Role setup

CREATE ROLE username LOGIN INHERIT -- (1)
                     CREATEDB -- (5)
                     PASSWORD 'password'; -- (3)
GRANT postgres TO username; -- (2)
CREATE DATABASE username; -- (4)
exit

Create a new user or "role". Like SSH, psql by default will use the currently logged-in username, which does not exist on a fresh installation.
Grant group membership to the newly created user.
Manually set a password for the newly created user. Single quotes are necessary here, as double quotes will cause an error.
After role creation
```
ALTER USER username WITH PASSWORD 'password' ;
```
The default database which is logged into is also named after the currently logged-in user. Also the built-in command createdb can be used from the command-line.
Database creation is a restricted operation granted by an attribute.
After role creation
```
ALTER USER username WITH CREATEDB;
```

SQL

Starships

CREATE TABLE starships (
    name text, registry text, crew integer
);

INSERT INTO starships (name, registry, crew) 
    VALUES ('USS Enterprise', 'NCC-1701', 400); -- (1)

For some reason, a double-quote " produces an error, and only single-quotes are accepted.

Cosmos DB

...

Commands

psql

Enter an interactive shell to control a PostgreSQL server.

Install

dnf install postgresql

psql -d database

The interactive shell allows SQL queries to be run as well as meta-commands prefixed with a backslash \.

\dt -- (1)

Display tables

sqlite3

sqlite3 is an interactive frontend to the SQLite library.

Meta-commands, prefixed by ., can be used to examine database files or perform administrative operations.

.databases  -- (1)
.tables     -- (2)
.show       -- (3)
.exit

List names and files of attached databases.
List names of tables matching a given pattern.
Show the current values for various settings.

Files can be provided on invocation from the command-line or they can be provided after the .open meta-command.

.open database.db

Without providing an argument on invocation, sqlite3 will open an in-memory database by default, which can also be explicitly specified with a meta-command.

.open :memory: