pg_basebackup is used to take a base backup of a running PostgreSQL database cluster
# Запуск кластера 15 версии
pg_ctlcluster start 15 mainport. Изначально он такой:#port = 5342 # (change requires restart)psql -p 5432 # 15 версия
psql -p 5433 # 13 версияC:\Program Files\PostgreSQL\%version%\bin:
with window_table as (
select array_agg(indx) over prev_rows as indexes,
array_agg(b04) over prev_rows as vals
from tb
window prev_rows as (order by indx rows between 2 preceding and current row)
)
select unnest(indexes)
from window_table
where vals = ARRAY[60, 119, 58];references на столбец другой таблицы. В таком случае, на последнее должно стоять ограничение уникальности - иначе как понять на какую строку ссылаться.
pg_dumpall --clean > db.out
pg_stat_activity в помощь.select application_name
, client_addr
, state
, query
from pg_stat_activity
where wait_event_type = 'Lock' and wait_event = 'transactionid';select a.application_name
, a.client_addr
, a.state
, s.query
from pg_stat_activity a
join pg_stat_statements s on a.query_id = s.queryid
where wait_event_type = 'Lock' and wait_event = 'transactionid';pg_locks, но он больше про блокировки объектов БД
CREATE INDEX name_idx ON users (lower(name));
TEXT вместо CHAR(N) и VARCHAR(N), т.к. производительность особо не страдает.There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.
hostnamedb:
ports:
- 5432:5432
hostname: 'db'
restart: always
build:
context: docker
dockerfile: db.Dockerfile
env_file:
- configuration/db.env
volumes:
- .:/docker-entrypoint-initdb.d
- ./dump:/var/www/dump
- dbdata:/var/lib/postgresql/data