PostgreSQL

Команды

initdb

initdb -D /home/postgres/cluster --locale=ru_RU.UTF-8

psql

psql -U postgres -d postgres

>vacuum full verbose;
>reindex database $database_name;
>\c $database_name
>\db[+] //отображение списка табличных пространств
>\du //отображение аттрибутов ролей

pg_dump / pg_restore

pg_dump -U $user_name -d $base_name -F custom -f $backup_$(date +%d).backup

pg_restore -O -U $user_name -d $base_name --no-owner $backup_$(date +%d).backup

pg_resetwal

pg_resetwal -D /home/postgres/cluster

Миграция на версию выше

Утилита pg_upgrade принимает четыре обязательных параметра:

1) -b, --old-bindir= или переменная окружения PGBINOLD - каталог бинарных файлов текущей версии

2) -B, --new-bindir= или переменная окружения PGBINNEW - каталог бинарных файлов мигрируемой версии

3) -d, --old-datadir= или переменная окружения PGDATAOLD - каталог текущего кластера

4) -D, --new-datadir= или переменная окружения PGDATANEW - каталог кластера мигрируемой версии

Команда миграции с передачей параметров

sudo -u postgres /usr/lib/postgresql/14/bin/pg_upgrade \
--old-bindir=/usr/lib/postgresql/13/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-datadir=/home/postgres/cluster/13 \
--new-datadir=/home/postgres/cluster/14

Команда миграции с использованием переменных окружения

export PGBINOLD=/usr/lib/postgresql/13/bin

export PGBINNEW=/usr/lib/postgresql/14/bin

export PGDATAOLD=/home/postgres/cluster/13

export PGDATANEW=/home/postgres/cluster/14

sudo -u postgres /usr/lib/postgresql/14/bin/pg_upgrade

unset PGBINOLD; unset PGBINNEW; unset PGDATAOLD; unset PGDATANEW

Дополнительные параметры запуска миграции

-с или --check - только проверка кластеров

-k или --link - использовать жёсткие ссылки

SQL

Запрос select

Общее

select * from pg_database;

select * from pg_shadow;

//имя базы, роль, табличное пространство, id базы, 
select pg_database.datname as "name", pg_shadow.usename as "user", pg_tablespace.spcname as "tablespace", pg_database.oid as "id" from pg_database
  left join pg_shadow on (pg_database.datdba = pg_shadow.usesysid)
  left join pg_tablespace on (pg_database.dattablespace = pg_tablespace.oid)
  where pg_database.datname not in ('postgres', 'template0', 'template1')
  order by pg_tablespace.spcname, pg_shadow.usename, pg_database.datname;

//размер таблиц
select nspname || '.' || relname as "relation", pg_size_pretty(pg_relation_size(tbclass.oid)) as "size" from pg_class tbclass
  left join pg_namespace tbname on (tbname.oid = tbclass.relnamespace)
  where nspname not in ('pg_catalog', 'information_schema')
  order by pg_relation_size(tbclass.oid) desc;

//вывод таблиц по искомому владельцу
select schemaname || '.' || tablename as "name", tableowner as "owner" from pg_tables 
 where tableowner = '$user_name';

TOAST

//исходная таблица toast-таблицы
select t2.relname from pg_class t1
  left join pg_class t2 on (t1.oid = t2.reltoastrelid)
  where t1.relname = '$pg_toast_table';

Табличное пространство

//вывод таблиц вне табличного пространства по умолчанию
select tablename, tablespace from pg_tables
  where schemaname = 'public' and tablespace is not null;

//вывод списка табличных пространств
select oid, spcname from pg_tablespace;

//вывод списка баз, имеющих объекты в искомом табличном пространстве
select datname from pg_database
  where oid in (
    select pg_tablespace_databases($oid)
  );

//вывод списка таблиц в искомом табличном пространстве
select relnamespace::regnamespace, relname from pg_class
  where reltablespace = $oid;

Запрос create

create role dbuser0 superuser createdb nocreaterole login noreplication encrypted password 'pass';

create tablespace ts0 location '/home/postgres/tablespace/ts0' [with (compression={zlib,zstd})];

create database $base_name tablespace ts0;

Запрос alter

alter user dbuser0 with password 'pass';
alter user $old_user_name rename to $new_user_name;

alter database $database_name owner to $user_name;
alter database $database_name rename to $new_database_name;
alter database $database_name set tablespace $tablespace;

alter table $table_name set tablespace ts0;
alter table all in tablespace pg_default set tablespace ts0;

Процессы

//текущие процессы
select pid, datname, state from pg_stat_activity
order by datname;

//завершение процесса
select pg_cancel_backend($pid);

//принудительное завершение процесса
select pg_terminate_backend($pid);

Сценарии запросов

Смена владельца таблиц

psql -U postgres -qAt -d $database_name -c "select 'alter table ' || tablename || ' owner to $new_user_name;' from pg_tables where tableowner = '$old_user_name';" > query.txt

psql -q -U postgres -d $database_name < query.txt

Связанные разделы

Методичка по конфигурированию работы PostgreSQL