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

Dodatkowo dla PostgreSQL 15.x i nowszych:

ALTER DATABASE dbname OWNER TO username;

Źródło: https://stackoverflow.com/questions/74110708/postgres-15-permission-denied-for-schema-public

PostgreSQL 9.2 i nowsze:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
  AND pid <> pg_backend_pid();

PostgreSQL 9.1 i starsze:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
  AND procpid <> pg_backend_pid();

Dwie metody:

sudo -u postgres -i
pg_ctl reload

sudo -u postgres -i
psql
SELECT pg_reload_conf();

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

Optymalizacja datasetu pod ZFS powinna zawierać ustawienia:

• recordsize=8k
• compression=zstd
• atime=off
• xattr=sa
• logbias=throughput
• ashift=12

Wklejka:

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

Dodatkowo konfiguracja do postgresql.conf:

/var/db/postgres/data18/postgresql.conf

full_page_writes = off

Instalacja:

pkg install timescaledb

W postgresql.conf ustawiamy:

/var/db/postgres/data18/postgresql.conf

shared_preload_libraries = 'timescaledb'

Aktywacja rozszerzenia na bazie:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Sprawdzenie czy rozszerzenie aktywne:

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)

Polecenia diagnostyczne:

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';