====== PostgreSQL ======
===== Tworzenie bazy i użytkownika =====

<code>
CREATE DATABASE dbname;
CREATE USER username WITH PASSWORD 'user_password';
GRANT ALL PRIVILEGES ON DATABASE "dbname" to username;
</code>

Dodatkowo dla PostgreSQL 15.x i nowszych:

<code>
ALTER DATABASE dbname OWNER TO username;
</code>

Źródło: https://stackoverflow.com/questions/74110708/postgres-15-permission-denied-for-schema-public
===== Killowanie połączeń do bazy =====
PostgreSQL 9.2 i nowsze:
<code>
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
  AND pid <> pg_backend_pid();
</code>

PostgreSQL 9.1 i starsze:
<code>
SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
  AND procpid <> pg_backend_pid();
</code>

===== Przeładowanie konfiguracji =====

Dwie metody:

<code bash>
sudo -u postgres -i
pg_ctl reload
</code>

<code bash>
sudo -u postgres -i
psql
SELECT pg_reload_conf();
</code>

//Źródło: https://www.heatware.net/databases/postgresql-reload-config-without-restarting/ //

====== ZFS ======

Optymalizacja datasetu pod ZFS powinna zawierać ustawienia:
  * ''recordsize=8k''
  * ''compression=zstd''
  * ''atime=off''
  * ''xattr=sa''
  * ''logbias=throughput''
  * ''ashift=12''

Wklejka:

<code bash>
zfs create -o recordsize=8k -o compression=zstd -o atime=off -o xattr=sa -o mountpoint=/var/db/postgres zroot/postgres
</code>

Dodatkowo konfiguracja do ''postgresql.conf'':

<file bash /var/db/postgres/data18/postgresql.conf>
full_page_writes = off
</file>

====== TimescaleDB ======

Instalacja:
<code bash>
pkg install timescaledb
</code>

W ''postgresql.conf'' ustawiamy:

<file bash /var/db/postgres/data18/postgresql.conf>
shared_preload_libraries = 'timescaledb'
</file>

Aktywacja rozszerzenia na bazie:

<code sql>
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
</code>

Sprawdzenie czy rozszerzenie aktywne:

<code bash>
db_name=# \dx
                                                         List of installed extensions
    Name     | Version | Default version |   Schema   |                                      Description                                     
-------------+---------+-----------------+------------+---------------------------------------------------------------------------------------
 plpgsql     | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.25.2  | 2.25.2          | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
(2 rows)

</code>


Polecenia diagnostyczne:

<code sql>
SELECT * FROM timescaledb_information.hypertables;
SELECT count(*) FROM timescaledb_information.chunks;
SELECT * FROM timescaledb_information.jobs;
SELECT * FROM timescaledb_information.jobs WHERE proc_name='policy_compression';
</code>