Индексы в postgresql

instalar e configurar postgres 9.6

При администрировании серверов баз данных PostgreSQL одной из наиболее распространенных задач, которую вы, вероятно, будете выполнять, является перечисление баз данных и их таблиц.

PostgreSQL поставляется с интерактивным инструментом который позволяет вам подключаться к серверу и выполнять запросы к нему. При использовании вы также можете воспользоваться его метакомандами. Эти команды полезны для сценариев и администрирования командной строки. Все мета-команды начинаются с обратного слэша без кавычек и также известны как команды обратного слэша.

В этом руководстве объясняется, как показывать базы данных и таблицы на сервере PostgreSQL с помощью .

Список баз данных

Вы можете подключиться к серверу PostgreSQL, используя команду как любой системный пользователь. В зависимости от конфигурации сервера пользователю может потребоваться ввести свой пароль для подключения к терминалу psql. Чтобы получить доступ к терминалу psql от имени пользователя, к которому вы сейчас подключены, просто введите .

Когда пакет PostgreSQL установлен, создается административный пользователь с именем «postgres». По умолчанию этот пользователь может подключиться к локальному серверу PostgreSQL без пароля.

Чтобы получить доступ к терминалу от имени пользователя «postgres», выполните:

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

В терминале psql выполните мета-команду или вывести список всех баз данных:

Вывод будет включать количество баз данных, имя каждой базы данных, ее владельца, кодирование и привилегии доступа:

Сервер PostgreSQL имеет три базы данных, созданные по умолчанию: template0, template1 и postgres. Первые два — это шаблоны, которые используются при создании новых баз данных.

Чтобы получить список всех баз данных без доступа к оболочке psql, используйте ключ как показано ниже:

Другой способ составить список баз данных — использовать следующий оператор SQL:

В отличие от мета-команды приведенный выше запрос покажет только имена баз данных:

Списки таблиц

Чтобы вывести список всех таблиц конкретной базы данных, сначала вам необходимо подключиться к ней с помощью мета-команды или . Пользователь, вошедший в систему как терминал psql, должен иметь возможность подключаться к базе данных.

Например, чтобы подключиться к базе данных с именем «odoo», вы должны набрать:

После переключения базы данных используйте мета-команду вывести список всех таблиц базы данных:

Вывод будет включать количество таблиц, имя каждой таблицы и ее схему, тип и владельца:

Если база данных пуста, вывод будет выглядеть так:

Чтобы получить информацию о размерах таблиц и описаниях, используйте .

Вывод

Вы узнали, как составлять список баз данных и таблиц PostgreSQL с помощью команды .

Не стесняйтесь оставлять комментарии, если у вас есть какие-либо вопросы.

PostgreSQL

В Linux группа — это совокупность пользователей. В этом руководстве объясняется, как показать все группы, в которые входит пользователь. Мы также объясним, как составить список всех членов группы.

Вы когда-нибудь хотели перечислить всех пользователей в вашей системе Linux или подсчитать количество пользователей в системе? Существуют команды для создания пользователя, удаления пользователя, просмотра списка зарегистрированных пользователей, но какова команда для вывода списка всех пользователей в Linux?

При администрировании серверов баз данных MySQL одной из наиболее распространенных задач, которые вам необходимо выполнить, является знакомство со средой. В этом руководстве объясняется, как показать все базы данных на сервере MySQL или MariaDB через командную строку.

Уровни блокировок таблиц

Команда LOCK TABLE предназначена для блокировки таблиц на время транзакции. Блокировкой называется временное ограничение доступа к таблице (в зависимости от выбранного режима). Сеанс, заблокировавший таблицу, пользуется нормальным доступом; последствия блокировки распространяются только на других пользователей, пытающихся получить доступ к заблокированной таблице.

Блокировка не означает отказа в доступе. С точки зрения пользователя, подключенного к базе данных и пытающегося обратиться к заблокированному ресурсу, блокировка приводит к задержке, но не к отказу в предоставлении доступа. Пользователю приходится ожидать либо завершения заблокированной команды пользователем, либо снятия блокировки с таблицы.

Некоторые команды SQL автоматически устанавливают блокировку для выполнения своих функций; в таких случаях PostgreSQL всегда выбирает минимально необходимый уровень блокировки. После завершения транзакции блокировка немедленно снимается.

Команда LOCK TABLE без параметра устанавливает максимально жесткий режим блокировки (ACCESS EXCLUSIVE). Чтобы ограничения были менее жесткими, следует явно задать нужный режим.

Блокировка таблиц возможна только в транзакциях. Выполнение команды LOCK TABLE вне транзакционного блока не приводит к ошибке, но установленная блокировка немедленно снимается. Транзакция создается командой BEGIN; команда COMMIT фиксирует изменения в базе данных и снимает блокировку.

Ситуация взаимной блокировки (deadlock) возникает в там случае, когда каждая из двух транзакций ожидает снятия блокировки другой транзакцией. Хотя PostgreSQL распознает взаимные блокировки и завершает их командой ROLLBACK, это все равно причиняет определенные неудобства. Приложения не должны сталкиваться с проблемой взаимных блокировок, поэтому проектируйте их так, чтобы объекты всегда блокировались в одинаковом порядке.

ACCESS SHARE MODE. Устанавливается автоматически командой SELECT для таблиц, из которых производится выборка данных. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровня ACCESS EXCLUSIVE MODE.
ROW SHARE MODE. Устанавливается автоматически командами SELECT, содержащими секцию FOR UPDATE или FOR SHARE. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
ROW EXCLUSIVE MODE. Устанавливается автоматически командами UPDATE, INSERT и DELETE. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и CREATE INDEX. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней SHARE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
SHARE UPDATE EXCLUSIVE MODE . Устанавливается автоматически командами VACUUM (без FULL), ANALYZE и CREATE INDEX CONCURRENTLY.
SHARE MODE. Устанавливается автоматически командами CREATE INDEX (без CONCURRENTLY). В заблокированных таблицах запрещается выполнение команд INSERT, UPDATE, DELETE, ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней ROW EXCLUSIVE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
SHARE ROW EXCLUSIVE MOOE. Специальный режим блокировки, практически идентичный режиму EXCLUSIVE MODE, но допускающий установку параллельных блокировок уровня ROW SHARE MODE.
EXCLUSIVE MODE. Запрещает выполнение команд INSERT, UPDATE, DELETE, CREATE INDEX, ALTER TABLE, DROP TABLE и VACUUM, а также команд SELECT с секцией FOR UPDATE. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней ROW SHARE MODE, ROW EXCLUSIVE MODE, SHARE MODE, SHARE ROW EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
ACCESS EXCLUSIVE MODE. Устанавливается автоматически командами ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц запрещаются любые команды или параллельные блокировки любого уровня.

Изменение таблиц

Последнее обновление: 19.03.2018

Нередко возникает изменить уже имеющуюся таблицу, в частности, добавить или удалить столбцы, изменить тип столбцов и т.д..
То есть потребуется изменить определение таблицы. Для этого применяется выражение ALTER TABLE,
которое имеет следующий формальный синтаксис:

ALTER TABLE название_таблицы
{ ADD название_столбца тип_данных_столбца  | 
  DROP COLUMN название_столбца |
  ALTER COLUMN название_столбца параметры_столбца |
  ADD  определение_ограничения |
  DROP  имя_ограничения}

Рассмотрим некоторые возможности по изменению таблицы.

Добавление нового столбца

Добавим в таблицу Customers новый столбец Phone:

ALTER TABLE Customers
ADD Phone CHARACTER VARYING(20) NULL;

Здесь столбец Phone имеет тип , и для него определен атрибут , то есть
столбец допускает отсутствие значения. Но что если нам надо добавить столбец, который не должен принимать значения NULL? Если в таблице есть
данные, то следующая команда не будет выполнена:

ALTER TABLE Customers
ADD Address CHARACTER VARYING(30) NOT NULL;

Поэтому в данном случае решение состоит в установке значения по умолчанию через атрибут :

ALTER TABLE Customers
ADD Address CHARACTER VARYING(30) NOT NULL DEFAULT 'Неизвестно';

Удалим столбец Address из таблицы Customers:

ALTER TABLE Customers
DROP COLUMN Address;

Изменение типа столбца

Для изменения типа применяется ключевое слово TYPE. Изменим в таблице Customers тип данных у столбца FirstName на (он же ):

ALTER TABLE Customers
ALTER COLUMN FirstName TYPE VARCHAR(50);

Изменение ограничений столбца

Для добавления ограничения применяется оператор SET, после которого указывается ограничение.
Например, установим для столбца FirstName ограничение :

ALTER TABLE Customers 
ALTER COLUMN FirstName 
SET NOT NULL;

Для удаления ограничения применяется оператор DROP, после которого указывается ограничение.
Например, удалим выше установленное ограничение:

ALTER TABLE Customers 
ALTER COLUMN FirstName 
DROP NOT NULL;

Изменение ограничений таблицы

Добавление ограничения CHECK:

ALTER TABLE Customers
ADD CHECK (Age > 0);

Добавление первичного ключа :

ALTER TABLE Customers 
ADD PRIMARY KEY (Id);

В данном случае предполагается, что в таблице уже есть столбец Id, который не имеет ограничения PRIMARY KEY. А с помощью вышеуказанного скрипта устанавливается ограничение PRIMARY KEY.

ALTER TABLE Customers
ADD UNIQUE (Email);

При добавлении ограничения каждому из них дается определенное имя. Например, выше добавленное ограничение для CHECK будет называться
. Имена ограничений можно посмотреть в таблице через pgAdmin.

Также мы можем явным образом назначить ограничению при добавлении имя с помощью оператора CONSTRAINT.

ALTER TABLE Customers
ADD CONSTRAINT phone_unique UNIQUE (Phone);

В данном случае ограничение будет называться «phone_unique».

Чтобы удалить ограничение, надо знать его имя, которое указывается после выражения . Например, удалим выше добавленное ограничение:

ALTER TABLE Customers
DROP CONSTRAINT phone_unique;

Переименование столбца и таблицы

Переименуем столбец Address в City:

ALTER TABLE Customers
RENAME COLUMN Address TO City;

Переименуем таблицу Customers в Users:

ALTER TABLE Customers
RENAME TO Users;

НазадВперед

Информация о текущих настройках сервера

В PostgreSQL есть 2 представления через которые можно посмотреть текущие настройки сервера:

pg_file_settings – какие параметры записаны в файлах postgresql.conf и postgresql.auto.conf;
pg_settings – текущие параметры, с которыми работает сервер.

Например посмотрим значение параметра config_file из представления pg_settings, который покажет конфигурационный файл текущего кластера:

postgres@postgres=# SELECT setting FROM pg_settings WHERE name = 'config_file';
                setting
---------------------------------------
 /usr/local/pgsql/data/postgresql.conf
(1 row)

Time: 1,844 ms

Внесём изменения в параметр work_mem в postgresql.conf и postgresql.auto.conf. Затем посмотрим на все не закомментированные параметры в этих файлах:

postgres@postgres=# \! echo 'work_mem = 8MB' >> $PGDATA/postgresql.conf

postgres@postgres=# ALTER SYSTEM SET work_mem TO '10MB';
ALTER SYSTEM
Time: 0,728 ms

postgres@postgres=# SELECT sourceline, name, setting, applied FROM pg_file_settings;
 sourceline |            name            |      setting       | applied
------------+----------------------------+--------------------+---------
         63 | port                       | 5433               | f
         64 | max_connections            | 100                | t
        121 | shared_buffers             | 128MB              | t
        142 | dynamic_shared_memory_type | posix              | t
        228 | max_wal_size               | 1GB                | t
        229 | min_wal_size               | 80MB               | t
        563 | log_timezone               | Europe/Moscow      | t
        678 | datestyle                  | iso, dmy           | t
        680 | timezone                   | Europe/Moscow      | t
        694 | lc_messages                | ru_RU.UTF-8        | t
        696 | lc_monetary                | ru_RU.UTF-8        | t
        697 | lc_numeric                 | ru_RU.UTF-8        | t
        698 | lc_time                    | ru_RU.UTF-8        | t
        701 | default_text_search_config | pg_catalog.russian | t
        780 | work_mem                   | 8MB                | f
          3 | work_mem                   | 10MB               | t
(16 rows)

Time: 0,650 ms

Как можно заметить в примере выше, у меня 2 одинаковых параметра work_mem. Колонка applied показывает, может ли быть применён параметр. Первый work_mem не может быть применен, так как второй его перезапишет. При этом реальное значение с которым работает сервер отличается, так как сервер не перечитал конфигурацию.

Теперь посмотрим на реальное, текущее значение этого параметра:

postgres@postgres=# SELECT name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context FROM pg_settings WHERE name = 'work_mem'\gx
----+---------
name            | work_mem
setting         | 4096
unit            | kB
boot_val        | 4096
reset_val       | 4096
source          | default
sourcefile      |
sourceline      |
pending_restart | f
context         | user

Time: 0,854 ms

В примере выше мы использовали расширенный режим (в конце запроса \gx), поэтому табличка перевёрнута. Разберём колонки:

name – имя параметра;
setting – текущее значение;
unit – единица измерения;
boot_val – значение по умолчанию (жёстко задано в коде postgresql);
reset_val – если перечитаем конфигурацию, то применится это значение;
source – источник, это значение по умолчанию;
sourcefile – если бы источником был конфигурационный файл, то тут был бы указан этот файл;
sourceline – номер строки в этом файле;
pending_restart – параметр изменили в конфигурационном файле и требуется перезапуск сервера. У нас требуется всего лишь перечитать конфигурацию;
context – действия, необходимые для применения параметра, может быть таким:
- internal – изменить нельзя, задано при установке;
- postmaster – требуется перезапуск сервера;
- sighup – требуется перечитать файлы конфигурации;
- superuser – суперпользователь может изменить для своего сеанса;
- user – любой пользователь может изменить для своего сеанса на лету.

Перечитаем конфигурацию сервера:

postgres@postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Time: 3,178 ms

postgres@postgres=# SELECT name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context FROM pg_settings WHERE name = 'work_mem'\gx
----+-------------------------------------------
name            | work_mem
setting         | 10240
unit            | kB
boot_val        | 4096
reset_val       | 10240
source          | configuration file
sourcefile      | /usr/local/pgsql/data/postgresql.auto.conf
sourceline      | 3
pending_restart | f
context         | user

Time: 1,210 ms

Как видим, параметр изменился. Он был взят из postgresql.auto.conf и теперь равняется 10 MB.

Подключение к базе данных в кластере PostgreSQL

К хостам кластера Managed Service for PostgreSQL можно подключиться:

Через интернет, если вы настроили публичный доступ для нужного хоста. К таким хостам подключиться можно только используя SSL-соединение.
С виртуальных машин Яндекс.Облака, расположенных в той же виртуальной сети. Если к хосту нет публичного доступа, для подключения с таких ВМ SSL-соединение использовать необязательно.

Примечание

Если публичный доступ в вашем кластере настроен только для некоторых хостов, автоматическая смена мастера может привести к тому, что вы не сможете подключиться к мастеру из интернета.

Описание БД

psql имеет достаточное количество специальных команд, которые помогают проще ориентироваться в базе данных. Для списка таблиц наберите , для списка индексов — , представлений — и т.д. Приятно, что все эти команды параметром принимают шаблон, по которому будет производиться фильтрация. Т.е. если вас интересуют только таблицы, начинающиеся на user, то выполните .
Там, где я работаю, частенько используются схемы. Грубо говоря это пространство имён для таблиц. По умолчанию есть только одна схема public. Когда вы создаёте там таблицу foo, обратиться к ней можно будет через public.foo. В нашей же компании для каждого клиента используется своя схема.
Иногда мне нужно проверить есть ли в схеме клиента определённая таблица (например, users). Для этого мне достаточно набрать , и я получу список всех таблиц с указанием схем.

Для получения информации о конкретном объекте таблицы или представления служит команда . Она показывает следующие вещи,

список столбцов вместе с их типами и значениями по умолчанию
индексы
ограничения
внешние ключи
триггеры

Для функций есть подобная команда \df. При вызове без аргументов, она покажет список всех функций. У меня их слишком много, так что с вашего позволения воспользуюсь фильтром:

Когда работаете со своими собственными функциями, то простого вывода сигнатуры оказывается мало. Тут на помощь приходит команда для редактирования. Передайте ей имя редактируемой функции в качестве первого параметра, и она откроется в $EDITOR. Если определение функции было задано с помощью , то после сохранения и закрытия редактора она будет обновлена.
Если же вам нужно только посмотреть описание функции, то закройте редактор с помощью ненулевого кода возврата (для vim это ). Таким образом она не будет обновлена и выполнена. psql в таком случае понимает, что что-то пошло не так, и не будет ничего делать.

Псевдо роль public

Псевдо роль public не видна, но про неё следует знать. Это групповая роль, в которую включены все остальные роли. Это означает, что все роли по умолчанию будут иметь привилегии наследуемые от public. Поэтому иногда у public отбирают некоторые привилегии, чтобы отнять их у всех пользователей.

Роль public по умолчанию имеет следующие привилегии:

для всех баз данных:
- CONNECT – это означает что любая созданная роль сможет подключаться к базам данных, но не путайте с привилегией LOGIN;
- TEMPORARY – любая созданная роль сможет создавать временные объекты во всех база данных и объекты эти могут быть любого размера;
для схемы public:
- CREATE (создание объектов) – любая роль может создавать объекты в этой схеме;
- USAGE (доступ к объектам) – любая роль может использовать объекты в этой схеме;
для схемы pg_catalog и information_schema

USAGE (доступ к объектам) – любая роль может обращаться к таблицам системного каталога;

:
для всех функций

EXECUTE (выполнение) – любая роль может выполнять любую функцию. Ещё нужны ещё права USAGE на ту схему, в которой функция находится, и права к объектам к которым обращается функция.

:

Это сделано для удобства, но снижает безопасность сервера баз данных.

Создание нового пользователя

Для того, чтобы была возможность подключения к СУБД PostgreSQL от нового пользователя, необходимо создать данного пользователя, назначить ему права, выполнить настройку файла pg_hba.conf.

1. Создание пользователя

а) Добавление новой роли (пользователя) из оболочки SQL:

=# CREATE USER dmosk WITH PASSWORD ‘myPassword’;

* в примере создана роль dmosk с паролем myPassword.

б) Добавление новой роли (пользователя) из командной строки Linux:

createuser -P dmosk

2. Назначение прав на использование базы данных

Даем права на базу командой:

=# GRANT ALL PRIVILEGES ON DATABASE «database1» to dmosk;

Теперь подключаемся к базе, к которой хотим дать доступ:

=# \c database1

* в примере подсоединимся к базе с названием database1.

а) Так мы добавим все права на использование всех таблиц в базе database1 учетной записи dmosk:

database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO «dmosk»;

* в большинстве случаев, используется схема по умолчанию public. Но администратор может создать новую схему. Это нужно учитывать при назначении прав.

б) Также можно дать доступ к базе для определенных таблиц:

database1=# GRANT ALL PRIVILEGES ON TABLE table1 IN SCHEMA public TO «dmosk»;

* в данном примере мы даем права на таблицу table1.

Выходим из SQL-оболочки:

database1=# \q

3. Настройка файла pg_hba.conf

Для возможности подключиться к СУБД от созданного пользователя, необходимо проверить настройки прав в конфигурационном файле pg_hba.conf.

Для начала смотрим путь расположения данных для PostgreSQL:

=# SHOW config_file;

В ответ мы получим, что-то на подобие:

——————————————
/var/lib/pgsql/9.6/data/postgresql.conf
(1 row)

* в данном примере /var/lib/pgsql/9.6/data/ — путь расположения конфигурационных файлов.

Открываем pg_hba.conf:

vi /var/lib/pgsql/9.6/data/pg_hba.conf

Добавляем права на подключение нашему созданному пользователю:

…
# IPv4 local connections:
host all dmosk 127.0.0.1/32 md5
…

* в данном примере мы разрешили подключаться пользователю dmosk ко всем базам на сервере (all) от узла 127.0.0.1 (localhost) с требованием пароля (md5).
* необходимо, чтобы данная строка была выше строки, которая прописана по умолчаниюhost all all 127.0.0.1/32 ident.

После перезапускаем службу:

systemctl restart postgresql-9.6

* в данном примере установлен postgresql версии 9.6, для разных версий на разных операционных системах команды для перезапуска сервиса могут быть разные.

Переменные окружения

Утилита psql может использовать переменные окружения. Для установки переменной используется команда “\set”, которой нужно передать имя и значение переменной. Если команде “\set” не передать никаких параметров, то она выведет все переменные окружения.

Вывести значение переменной можно с помощью команды “\echo”, которой нужно передать имя переменной, но перед именем нужно обязательно поставить двоеточие:

postgres=# \set TEST Hi!

postgres=# \echo :TEST
Hi!

postgres=# \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'postgres'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
ERROR = 'false'
FETCH_COUNT = '0'
HIDE_TABLEAM = 'off'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = '/tmp'
IGNOREEOF = '0'
LAST_ERROR_MESSAGE = ''
LAST_ERROR_SQLSTATE = '00000'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%x%# '
PROMPT2 = '%/%R%x%# '
PROMPT3 = '>> '
QUIET = 'off'
ROW_COUNT = '1'
SERVER_VERSION_NAME = '13.2'
SERVER_VERSION_NUM = '130002'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
SQLSTATE = '00000'
TEST = 'Hi!'
USER = 'postgres'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit'
VERSION_NAME = '13.2'
VERSION_NUM = '130002'

Чтобы удалить переменную окружения используйте команду “\unset”, например:

postgres=# \unset TEST

postgres=# \echo :TEST
:TEST

Как видно выше, если переменной нет, то “\echo” просто выводит переданный текст.

Но переменную можно создавать не только с помощью “\set”, также можно в переменную записать результат запроса. Чтобы это сделать, нужно использовать в конце запроса вместо точки с запятой (;) команду “\gset”:

postgres=# SELECT now() AS curr_time \gset

postgres=# \echo :curr_time
2021-04-09 10:31:49.521543+00

В примере выше мы результат функции now() записали в переменную curr_time!

Посмотреть и удалить активные запросы

Если запрос запущен из интерфейса pgsql, то завершение работы сервера не поможет — запрос все равно продолжит свое выполнение, необходимо вызывать функцию pg_cancel_backend.

select * from pg_stat_activity; # посмотреть все запросы
select * from pg_stat_activity WHERE current_query like 'SELECT%'; # посмотреть все SELECT запросы
select * from pg_stat_activity WHERE current_query like 'INSERT%';

# снять все активные select запросы
SELECT pg_cancel_backend(procpid) as x FROM pg_stat_activity WHERE current_query like 'SELECT%';

# снять запрос VACUUM
SELECT pg_cancel_backend(procpid) as x FROM  pg_stat_activity WHERE current_query like 'VACUUM%';

SELECT запросы можно снимать из ОС командой kill

# ps auxww | grep ^postgres
...
postgres 15724 97.7 11.3 2332996 1871476 ?     Rs   07:50   1:53 postgres: postgres mybd 127.0.0.1(53624) SELECT
...
# kill 15724

procpid содержит PID процесса, которому можно сделать kill при необходимости. Например PID можно узнать запросом(отсортируем по длительности выполнения)

select datname,procpid,now()-query_start as duration,current_query from pg_stat_activity order by duration DESC;

Транзакции в PostgreSQL

В PostgreSQL Транзакция — это список команд SQL, которые находятся внутри блока, начинающегося командой BEGIN и заканчивающегося командой COMMIT.

PostgreSQL фактически считает каждый оператор SQL запущенным в транзакции. Если вы не указываете команду BEGIN, то каждый отдельный оператор имеет неявную команду BEGIN перед оператором и (при успешной отработке оператора) команду COMMIT после оператора. Группа операторов заключаемая в блок между BEGIN и COMMIT иногда называется транзакционным блоком.

Пример запуска транзакции из файла delprices.sql, которая удаляет в БД test777 из таблиц prices и ratesheets строки с id=2

# nano delprices.sql
BEGIN;
DELETE FROM prices WHERE ratesheet_id=2;
DELETE FROM ratesheets WHERE id=2;
COMMIT;

Выполним транзакцию для test777:

# sudo -u postgres psql -l
# sudo -u postgres psql test777 < delprices.sql

После установки

После установки проверьте версию установленного PostgreSQL

postgres -V

postgres (PostgreSQL) 9.2.24

Расположение файлов с настройками, например,

postgresql.conf

можно получить выполнив

-bash-4.2$ su — postgres -c «psql -c ‘SHOW config_file;'»

Password:
config_file
————————————-
/var/lib/pgsql/data/postgresql.conf
(1 row)

В этом примере директория, которая содержит настройки это

Полезно изучить её содержание

ll /var/lib/pgsql/data/

total 48
drwx——. 7 postgres postgres 67 Jun 9 22:54 base
drwx——. 2 postgres postgres 4096 Jun 9 23:19 global
drwx——. 2 postgres postgres 18 Jun 9 13:54 pg_clog
-rw——-. 1 postgres postgres 4371 Jun 10 01:23 pg_hba.conf
-rw——-. 1 postgres postgres 1636 Jun 9 13:54 pg_ident.conf
drwx——. 2 postgres postgres 58 Jun 10 00:00 pg_log
drwx——. 4 postgres postgres 36 Jun 9 13:54 pg_multixact
drwx——. 2 postgres postgres 18 Jun 9 14:14 pg_notify
drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_serial
drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_snapshots
drwx——. 2 postgres postgres 25 Jun 10 02:06 pg_stat_tmp
drwx——. 2 postgres postgres 18 Jun 9 13:54 pg_subtrans
drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_tblspc
drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_twophase
-rw——-. 1 postgres postgres 4 Jun 9 13:54 PG_VERSION
drwx——. 3 postgres postgres 60 Jun 9 13:54 pg_xlog
-rw——-. 1 postgres postgres 19889 Jun 10 01:43 postgresql.conf
-rw——-. 1 postgres postgres 45 Jun 9 14:14 postmaster.opts
-rw——-. 1 postgres postgres 92 Jun 9 14:14 postmaster.pid