Подключение к бд postgresql из консоли

Утилиты (программы) PosgreSQL:

• createdb и dropdb – создание и удаление базы данных (соответственно)
• createuser и dropuser – создание и пользователя (соответственно)
• pg_ctl – программа предназначенная для решения общих задач управления (запуск, останов, настройка параметров и т.д.)
• postmaster – многопользовательский серверный модуль PostgreSQL (настройка уровней отладки, портов, каталогов данных)
• initdb – создание новых кластеров PostgreSQL
• initlocation – программа для создания каталогов для вторичного хранения баз данных
• vacuumdb – физическое и аналитическое сопровождение БД
• pg_dump – архивация и восстановление данных
• pg_dumpall – резервное копирование всего кластера PostgreSQL
• pg_restore – восстановление БД из архивов (.tar, .tar.gz)

Примеры создания резервных копий:

Создание бекапа базы mydb, в сжатом виде

pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb

Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb

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

pg_dump -a -t table_name -f file_name database_name

Создание резервной копии с сжатием в gz

pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Список наиболее часто используемых опций:

• -h host — хост, если не указан то используется localhost или значение из переменной окружения PGHOST.
• -p port — порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.
• -u — пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.
• -a, —data-only — дамп только данных, по-умолчанию сохраняются данные и схема.
• -b — включать в дамп большие объекты (blog’и).
• -s, —schema-only — дамп только схемы.
• -C, —create — добавляет команду для создания БД.
• -c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).
• -O — не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).
• -F, —format {c|t|p} — выходной формат дампа, custom, tar, или plain text.
• -t, —table=TABLE — указываем определенную таблицу для дампа.
• -v, —verbose — вывод подробной информации.
• -D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду pg_dumpall.

pg_dumpall > all.sql

Восстановление таблиц из резервных копий (бэкапов):

psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
pg_restore — восстановление сжатых бекапов (tar);

Восстановление всего бекапа с игнорированием ошибок

psql -h localhost -U someuser -d dbname -f mydb.sql

Восстановление всего бекапа с остановкой на первой ошибке

psql -h localhost -U someuser —set ON_ERROR_STOP=on -f mydb.sql

Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

pg_restore —dbname=mydb —jobs=4 —verbose mydb.backup

Восстановление резервной копии БД, сжатой gz

gunzip mydb.gz
psql -U postgres -d mydb -f mydb

Проверка репликации

Посмотреть статус

Статус работы репликации можно посмотреть следующими командами.

На мастере:

=# select * from pg_stat_replication;

На слейве:

=# select * from pg_stat_wal_receiver;

Создать тестовую базу

На мастере заходим в командную оболочку Postgre:

su — postgres -c «psql»

Создаем новую базу данных:

=# CREATE DATABASE repltest ENCODING=’UTF8′;

Теперь на вторичном сервере смотрим список баз:

su — postgres -c «psql»

=# \l

Мы должны увидеть среди баз ту, которую создали на первичном сервере:

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
————+———-+———-+————-+————-+——————
 …
 repltest  | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | 
 …

Запуск скрипта из файла

Сперва проверим, что с переменными окружения всё впорядке.

Для этого введём
в консоль psql.exe нажмём Enter и проверим что bash не жалуется на неизвестную команду.

Если жалуется — прочитайте мои советы в статье

Пишем скрипт
script.sql

Применим этот скрипт к базе данных
HeiHei_ru_DB

У меня postgres запущен локально на порту 5433. У Вас может быть
на 5432 — проверьте.

cat script.sql | psql.exe -h localhost -p5433 -U postgres HeiHei_ru_DB

Password for user postgres:

CREATE TABLE

Саме время сделать что-то более близкое к реальному скрипту

На поля таблицы нужно ввести некоторые ограничения и добавить им свойств.

Теперь запустим этот скрпит уже не в тестовую а в рабочую базу данных
heihei (которая совпадает с названием сайта HeiHei.ru,
но если написать .ru будет синтаксическая ошибка ERROR: syntax error at or near «.»)

cat booking_sites.sql | psql.exe -h localhost -p5433 -U postgres heihei

Password for user postgres:

CREATE TABLE

Установка и настройка

В данном разделе представлена инструкция по установки и настройке PostgreSQL для разных ОС

Установка

Если установка происходит на macOS, то процесс установки можно запустить командой:

brew install postgresql

На Linux СУБД устанавливается так:

sudo apt-get install postgresql postgresql-contrib

После того, как все загружено и установлено, можно проверить, все ли в порядке, и какая стоит версия PostgreSQL. Для этого выполните следующую команду:

postgres --version

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

Настройка

Работа с PostgreSQL может быть произведена через командную строку (терминал) с использованием утилиты psql – инструмент командной строки PostgreSQL.

Необходимо ввести следующую команду:

psql postgres (для выхода из интерфейса используйте \q)

Этой командой запускается утилита psql. Хотя есть много сторонних инструментов для администрирования PostgreSQL, нет необходимости их устанавливать, т. к. psql удобен и отлично работает.

Если нужна помощь, введите (или ) в psql-терминале. Появится список всех доступных параметров справки. Вы можете ввести , если вам нужна помощь по конкретной команде. Например, если ввести в консоли psql, отобразится синтаксис команды .

 1 Description update rows of a table
 2  WITH  RECURSIVE  with_query [,  
 3 UPDATE  ONLY  table_name  *    AS  alias 
 4     SET { column_name = { expression | DEFAULT } |
 5           ( column_name [,  ) = ( { expression | DEFAULT } [,  ) |
 6           ( column_name [,  ) = ( sub-SELECT )
 7         } [, 
 8      FROM from_list 
 9      WHERE condition | WHERE CURRENT OF cursor_name 
10      RETURNING * | output_expression   AS  output_name  [,  

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

\list или \l

Рисунок 1 — Результат выполнения операции \l

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

Чтобы вывести список всех пользователей, выполните команду . Атрибуты пользователя postgres говорят нам, что он суперпользователь.

Рисунок 2 — Результат выполнения операции \du

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

Команда 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. В этом режиме для заблокированных таблиц запрещаются любые команды или параллельные блокировки любого уровня.

Основные настройки

Конфигурационный файл отвечающий за настройки аутентификации – pg_hba.conf. Он находится в каталоге PGDATA:

postgres@s-pg13:~$ ls -l $PGDATA/pg_hba.conf
-rw------- 1 postgres postgres 4760 июн 21 15:15 /usr/local/pgsql/data/pg_hba.conf

Его местоположение можно изменить задав параметр hba_file в конфигурационном файле postgresql.conf:

postgres@s-pg13:~$ cat $PGDATA/postgresql.conf | grep hba
#hba_file = 'ConfigDir/pg_hba.conf'     # host-based authentication file

При изменении этого файла конфигурацию сервера нужно перечитать, выполнив:

• – из операционной системы;
• – если вы подключены к СУБД.

Если вы подключены к СУБД, то узнать местоположение файла можно таким способом:

postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.

postgres@postgres=# SHOW hba_file;
             hba_file
-----------------------------------
 /usr/local/pgsql/data/pg_hba.conf
(1 row)
Time: 0,740 ms

Файл pg_hba.conf состоит из строк, а строки состоят из следующих полей:

• тип подключения;
• имя БД;
• имя пользователя;
• адрес узла;
• метод аутентификации;
• необязательные дополнительные параметры в виде имя=значение. Эти параметры нужны некоторым методам аутентификации.

Эти строки обрабатываются сверху вниз и применяется первая найденная строка. Таким образом если тип подключения, имя БД, имя пользователя и адрес сервера совпали, то применяется определённый метод аутентификации.

pg_hba – если-то

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

Вот пример файла pg_hba.conf, который создаётся при сборке из исходников:

# TYPE  DATABASE      USER    ADDRESS          METHOD
local   all           all                      trust
host    all           all     127.0.0.1/32     trust
host    all           all     ::1/128          trust
local   replication   all                      trust
host    replication   all     127.0.0.1/32     trust
host    replication   all     ::1/128          trust

Первая строчка это тип подключения local, в котором используется локальный unix сокет, и не задействована сеть. При таком подключении все пользователи (all) могут подключаться методом trust. О методах поговорим позже.

Третья и четвёртая строки относятся к tcp подключениям (host). При таком подключении все пользователи могут подключаться только из локального хоста (127.0.0.1/32 или ::1/128) используя метод trust.

Последние три строки относятся к репликации. Репликация возможна по сокету (local) и по сети (host) но только с локального хоста (127.0.0.1/32 или ::1/128). Здесь тоже используется метод trust.

Если вы подключены к СУБД, то сможете посмотреть содержимое файла pg_hba.conf с помощью представления pg_hba_file_rules:

postgres@postgres=# SELECT * FROM pg_hba_file_rules;
 line_number | type  |   database    | user_name |  address  |                 netmask                 | auth_method | options | error
-------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+-------
          88 | local | {all}         | {all}     |           |                                         | trust       |         |
          90 | host  | {all}         | {all}     | 127.0.0.1 | 255.255.255.255                         | trust       |         |
          92 | host  | {all}         | {all}     | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         |
          95 | local | {replication} | {all}     |           |                                         | trust       |         |
          96 | host  | {replication} | {all}     | 127.0.0.1 | 255.255.255.255                         | trust       |         |
          97 | host  | {replication} | {all}     | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         |
(6 rows)
Time: 2,006 ms

Если в строке допущена ошибка, то это представление в поле error покажет ошибку.

Работа с PostgreSQL из командной строки:

• -c (или –command) – запуск команды SQL без выхода в интерактивный режим
• -f file.sql — выполнение команд из файла file.sql
• -l (или –list) – выводит список доступных баз данных
• -U (или –username) – указываем имя пользователя (например postgres)
• -W (или –password) – приглашение на ввод пароля
• -d dbname — подключение к БД dbname
• -h – имя хоста (сервера)
• -s – пошаговый режим, то есть, нужно будет подтверждать все команды
• –S – однострочный режим, то есть, переход на новую строку будет выполнять запрос (избавляет от ; в конце конструкции SQL)
• -V – версия PostgreSQL без входа в интерактивный режим

Примеры:

psql -U postgres -d dbname -c «CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);» — выполнение команды в базе dbname.

psql -d dbname -H -c «SELECT * FROM my» -o my.html — вывод результата запроса в html-файл.

Резервные копии (экспорт и импорт дампа)

При установке PostgreSQL на сервер устанавливаются утилиты и , с помощью которых вы сможете из консоли Linux создавать резервные копии базы данных (pg_dump) и восстанавливать данные из них ().

Создание резервной копии

Чтобы создать резервную копию базы и сохранить ее на сервере, необходимо выполнить команду:

pg_dump -h хост -U имя_роли -F формат_дампа -f путь_к_дампу имя_базы

Параметры:

• — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
• — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
• — формат, в котором будет сохранен дамп; указывается буквами c, t или p: ‘с’ (custom — архив .tar.gz), ‘t’ (tar — архив .tar), ‘p’ (plain — текстовый файл без сжатия, как правило, .sql);
• — путь сохранения для файла дампа и имя файла;
• — имя базы данных, для которой создается резервная копия.

Например:

pg_dump -h localhost -U tmweb -F c -f /home/user/backups/dump.tar.gz tmweb

После выполнения команды будет запрошен пароль пользователя Postgres, указанного в команде (в примере — tmweb).

Восстановление из дампа

Импорт дампов, сохраненных в форматах .tar.gz и .tar, выполняется с помощью :

pg_restore -h хост -U имя_роли -F формат_дампа -d имя_базы путь_к_дампу

Параметры:

• — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
• — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
• — формат, в котором был сохранен дамп; необходимо указать ‘с’ для архива .tar.gz и ‘t’ для архива .tar;
• — имя базы данных, в которую импортируется дамп;
• — путь к файлу дампа и имя файла.

Например:

pg_restore -h localhost -U tmweb -F c -d new_db /home/user/backups/dump.tar.gz

Для импорта дампов в формате .sql используется cat:

cat путь_к_дампу | psql -h хост -U имя_роли имя_базы

Параметры:

• — путь к файлу дампа и имя файла;
• — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
• — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
• — имя базы данных, в которую импортируется дамп.

Например:

cat /home/user/backups/dump.sql | psql -h localhost -U tmweb new_db

Подключение к серверу PostgreSQL через pgAdmin

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

Далее мы покажем, как подключиться к базе данных, используя приложение с графическим интерфейсом pgAdmin.

Во-первых, запустите приложение pgAdmin.

Приложение pgAdmin 4 будет запущено в веб-браузере, как показано на картинке ниже:

Во-вторых, для создания сервера, щёлкните правой кнопкой мыши по узлу Servers и выберите меню Create > Server…

В-третьих, введите имя сервера, например, PostgreSQL и нажмите на вкладку Connection:

В-четвёртых, введите имя хоста и пароль для пользователя postgres, после чего щёлкните по кнопке Save:

В-пятых, щёлкните по узлу Servers, чтобы открыть иерархию сервера. Изначально, PostgreSQL содержит базу данных, которая называется postgres, как показано ниже:

В-шестых, откройте инструмент запросов, выбрав пункт меню Tool > Query Tool или нажав на иконку молнии.

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

Настройка ротации паролей

Данная настройка позволит автоматически по таймеру менять пароли в СУБД PostgreSQL для определенных учетных записей.

На стороне хранилища паролей необходимо:

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

Переходим к настройке.

Создаем роль для смены пароля

Настройка задается с помощью static-roles. В нашем Vault вводим команду:

vault write database/static-roles/postgresql-rotate \
 db_name=postgresql \
 rotation_statements=»ALTER USER \»`name`\» WITH PASSWORD ‘`password`’;» \
 username=»dmosk» \
 rotation_period=720h

* обратите внимание, что запрос rotation_statements важно написать именно так — первая кавычка двойная, вторая одинарная. ** в данном примере:

** в данном примере:

• Мы создадим роль с названием postgresql-rotate (с тем названием, которое мы использовали в allowed_roles, когда создавали конфигурацию).
• Опция db_name должна называться как настройка, которую мы создали на предыдущем шаге (database/config/postgresql).
• Наша роль будет выполнять команду rotation_statements (менять пароль для пользователя).
• Каждые 720 часов (30 дней) пароль будет меняться автоматически.

Мы должны увидеть что-то на подобие:

Success! Data written to: database/static-roles/postgresql-rotate

Готово.

Меняем пароль и проверяем доступ

Давайте посмотрим текущий пароль:

vault read database/static-creds/postgresql-rotate

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

Key                    Value
—                    ——
last_vault_rotation    2021-09-07T11:35:07.796668266+03:00
password               Cai-dzsJDtKTLHSl6Bvt
rotation_period        720h
ttl                    719h48m55s
username               dmosk

Где Cai-dzsJDtKTLHSl6Bvt — наш пароль от учетной записи dmosk. 

Перейдем на сервер с PostgreSQL и попробуем войти в СУБД с использованием этих данных:

psql -Udmosk -W template1

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

Команды copy echo

Обозначения stdin и stdout (стандартный ввод и вывод) интерпретируются по-разному: в psql они относятся ко входному и выходному потокам. В контексте серверного процесса stdin представляет источник данных команды COPY, a stdout представляет выходной поток запроса.

• \echo строка. Заданная строка передается в стандартный вывод. Команда часто используется при написании сценариев, поскольку с ее помощью можно включить в вывод сценария данные, не предоставляемые СУБД (например, комментарии).
• \д . Команда выполнения буфера. Как и символ точки с запятой (:) в конце команды SQL, передает текущее содержимое буфера запроса серверу для обработки. Аргумент позволяет сохранить итоговый набор в заданном файле или передать его следующей программе для дальнейшей обработки.
• \i файл. Чтение входных данных из файла с заданным именем. Содержимое файла интерпретируется так, словно оно было введено непосредственно в приглашении psql.
• \1o_export old файл. Экспортирование больших объектов с заданным идентификатором объекта (OID) в локальную файловую систему. Отличается от функции сервера 1o_export() так же, как команда \copy в psql отличается от команды COPY в SQL.
• \lo_import файл . Импортирование больших объектов из локальной файловой системы в базу данных. К объекту можно присоединить необязательный комментарий; это рекомендуется делать, поскольку в противном случае на него можно будет ссылаться только по значению OID, которое придется запоминать для дальнейшего использования. Если присоединить к объекту комментарий, то команда \lo_list выведет комментарий вместе со значением OID объекта, что упростит его поиск после импортирования.
• \о [ файл \ /команда ]. Перенаправление вывода (например, результатов выборки) в файл или его передача системной команде для дальнейшей обработки. Если аргумент не задан, перенаправление прекращается и происходит восстановление стандартного вывода. Особого внимания заслуживает возможность конвейерной (pipe) передачи вывода системным командам (например, дгер), что позволяет проводить поиск по шаблонам в выходных данных запросов и управляющих команд psql.
• \qecho строка. Заданная строка направляется в канал вывода, заданный командой \о (вместо stdout). Команда обычно используется для включения дополнительной информации в выходной поток psql.
• \w файл \ /команда. Текущий буфер запроса направляется в заданный файл или передается системной команде для дальнейшей обработки.

Параметры подключения

Теперь рассмотрим параметры подключений!

Типы подключений:

• local – использует unix сокет;
• host – использует TCP/IP, оно может быть шифрованным с помощью SSL или не шифрованным;
• hostssl – только зашифрованный TCP/IP;
• hostnossl – только не зашифрованный TCP/IP.

Имя базы данных:

• all – подключение к любой БД;
• sameuser – БД, совпадающая по имени с ролью;
• samerole – БД, совпадающая по имени с ролью или группой, в которую она входит;
• replication – специальное разрешение для протокола репликации;
• имя базы данных – имя конкретной базы данных, или через запятую можно перечислить список баз данных.

Адрес узла:

• all – любой IP-адрес;
• ip-адресс – конкретный ip адрес, подсеть, или диапазон (172.20.143.0/24 или 172.20.143.0 255.255.255.0)
• samehost – означает адрес сервера с которого ведётся подключение, аналог 127.0.0.1;
• samenet – означает любой ip адрес из подсети сервера;
• доменное имя (или часть доменного имени, начиная с точки) – при этом postgresql проверяет ip-адрес подключающегося клиента на принадлежность к этому домену.

Имя роли:

• all – любая роль;
• <имя роли> – роль с указанным именем, при этом можно перечислить роли через запятую;
• +<имя роли> – групповая роль, в которую включены другие роли.

Тип аутентификации:

• Аутентификация без проверок:
  • trust – разрешает подключение без аутентификации, то есть без проверки пароля или любых других проверок;
  • reject – запрещает подключение ничего не проверяя;
• Аутентификация по паролю:
  • md5 – пароль хранится в СУБД и шифруется MD5;
  • scram-sha-256 – пароль хранится в СУБД, используется протокол SCRAM (более надёжно);
  • ldap – пароль хранится на сервере LDAP;
  • radius – пароль хранится на сервере RADIUS;
  • pam – пароль хранится в подключаемом модуле PAM;
• Внешняя аутентификация:
  • peer – запрашивает имя пользователя у операционной системы (только для Linux и только для локальных подключений);
  • cert – аутентификация с использованием клиентского SSL-сертификата;
  • gss – аутентификация Kerberos по протоколу GSSAPI;
  • sspi – аутентификация Kerberos/NTLM для Windows.

Получение данных

Зачастую нам нужно поделиться данными из postgres с сотрудниками, руководством или клиентами, причём желательно в каком-нибудь удобочитаемом формате типа CSV или Excel. Вы уже подготовили запрос, свели всё в одну таблицу, осталось только куда-нибудь это выгрузить. Погуглив psql csv export, можно найти 2 способа. Первый более примитивный.

CSV фйалы представляют собой просто строки со значениями, которые разделены запятыми. Таким образом, указав psql как форматировать вывод, можно получить похожую структуру. Тут есть одна большая проблема — если ваши данные содержат запятую, то её нужно экранировать, а т.к. количество полей может быть огромным, то такой способ ведёт в тупик.

Правильным способом будет выгрузка в CSV с помощью мета-команды или SQL команды . выглядит это примерно так:

накладывает несколько ограничений. Во-первых, путь к файлу должен быть абсолютным. Во-вторых, вы можете писать только на локальную файловую систему. То есть подключиться к удалённой БД и выгрузить данные на локальный компьютер не получится. И тут на помощь приходит \copy, которая представляет из себя всего лишь более удобную оболочку для COPY. Запрос выше можно переписать так:

Обратите внимание, что используется относительный путь до файла. Также можно задать и другие параметры CSV

Единственное ограничение состоит в том, что команда должна быть одной строкой. Причина в том, что окончанием выражения для команды с \ является перевод строки, а не точка с запятой. Это не касается — там управление передаётся в редактор. Под капотом выполняет всё тот же COPY, перенаправляя вывод в STDOUT вместо файла. Далее psql забирает со STDOUT и записывает в локальный файл.
Если вам нужно выгрузить данные в Excel, то убедитесь, что задали правильную кодировку. Он не дружит с UTF-8, так что лучше откатиться до latin1 (2015 год на дворе, я солидарен с негодованиями автора):

Если sql-запрос у вас находится в файле, то использовать его для команды не получится. Вам придётся скопировать всё тело запроса в выражение в команде, удалив переносы строк.

Наверно, я вам уже надоел, так что закругляюсь. Напоследок хотелось бы ещё рассказать про утилиту psql2csv. Она позволяет запускать запрос из файла и получать отформатированный CSV в STDOUT (который можно потом перенаправить в файл):

В случае запроса из файла:

Параметры вызова утилиты совпадают с параметрами psql. Вы можете заменить вызов psql на psql2csv, передав запрос в качестве аргумента, а на выходе получить валидный CSV. Но это ещё не всё — почему бы не подать вывод на вход какой-нибудь другой утилите?!
psql2csv также принимает аргументы для совместимости с Excel.

Я уже очень привык работать с PostgreSQL через командную строку. Всё, что раньше делал в pgAdmin, можно делать и тут, причём быстрее. Я надеюсь, что эта статья убедила вас сделать psql основным инструментом для работы с PostgreSQL, показав удобство и гибкость.

Если у вас есть какие-то замечания или дополнения, пожалуйста, напишите!

Создание резервной копии и восстановление из бэкапа

Для создания резервной копии базы данных используется сложная команда:

pg_dump -h хост -U имя_роли -F формат_дампа -f путь_к_дампу имя_БД

Чтобы было проще разобраться, рассмотрим каждый параметр:

• хост – сервер, на котором располагается БД. Например, можно указать localhost, домен, IP-адрес.
• имя_роли – имя пользователя PostgreSQL, под которым мы работаем с базой данных.
• формат_дампа – формат, в котором дамп сохранится на сервере. Доступны следующие форматы: c (custom) – архив .tar.gz, t (tar) – архив .tar, p (plain) – текст без сжатия, обычно .sql.
• путь_к_дампу – путь, по которому будет сохранена резервная копия.
• имя_БД – название БД, для которой будет создана резервная копия.

Выглядит это примерно так:

pg_dump -h localhost -U mybase -F c -f /home/user/backups/dump.tar.gz mybase

Для выполнения этой команды нужно ввести пароль, который используется при входе в psql от имени указанной роли (mybase в приведенном примере).

Восстановление из резервной копии выполняется аналогичным образом: 

pg_restore -h хост -U имя_роли -F формат_дампа -d имя_базы путь_к_дампу

Параметры похожие, отличия минимальные

Важно знать хост, помнить формат и путь к бэкапу

Мы разобрались с основными действиями и настройками PostgreSQL. На этом все! 

Создание временных пользователей в PostgreSQL

Давайте теперь рассмотрим настройку, при которой Vault будет создавать временных пользователей. Для этого мы:

1. Создадим роль в Vault, которая будет создавать учетную запись в базе PostgreSQL.
2. Настроим файл pg_hba.conf для возможности подключаться к базе пользователям по паролю.
3. Проверим настройки, создав учетную запись и подключившись к СУБД.

Приступим.

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

Настройка задается с помощью roles. На сервере Vault вводим команду:

vault write database/roles/postgresql-create \
 db_name=postgresql \
 creation_statements=»CREATE ROLE \»`name`\» WITH LOGIN PASSWORD ‘`password`’ VALID UNTIL ‘`expiration`’; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO «`name`»;» \
 default_ttl=»1h» \
 max_ttl=»24h»

* обратите внимание, что запрос creation_statements важно написать именно так — первая кавычка двойная, остальные одинарные. ** в данном примере:

** в данном примере:

• Мы создадим роль с названием postgresql-create (с тем названием, которое мы использовали в allowed_roles, когда создавали конфигурацию).
• Опция db_name должна называться как настройка, которую мы создали на шаге создания конфига (database/config/postgresql).
• Наша роль будет выполнять команду creation_statements — создавать пользователя и задавать ему права. Нужно переписать запрос, чтобы права выдавались такими, какими нужны именно вам. Для тестовых целей можно поставить как в данном примере.
• default_ttl — время, в течение которого будет действовать пароль.
• max_ttl — время, в течение которого пользователю можно будет обновлять пароль.

Мы должны увидеть что-то на подобие:

Success! Data written to: database/roles/postgresql-create

Готово.

Настраиваем файл pg_hba.conf

Ранее в инструкции мы настраивали данный файл, чтобы обеспечить возможность подключения к базе пользователям dmosk и postgresql. Но при создании динамических учетных записей, логины будут разные — необходимо разрешить парольный вход для всех пользователей.

Открываем файл:

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

* напомню, что путь до нашего файла может быть другим — это зависит от операционной системы и версии postgresql. Посмотреть расположение конфигурационных файлов можно sql-командой SHOW config_file;

В открывшемся редакторе необходимо внести изменения:

…
# «local» is for Unix domain socket connections only
local   all             dmosk                                   md5
local   all             postgres                                peer
local   all             all                                     md5
##local   all             all                                     peer
…

* мы добавили строку, разрешающую пользователю postgres логин с локального хоста (local) без запроса пароля; также мы закомментировали строку, которая позволяет вход всем пользователям с локального хоста без пароля и добавили новую, почти, такую же, но требующую ввода логина и пароля.
** это, всего лишь, пример настройки. В вашем случае необходимо руководствоваться политикой безопасности, принятой в компании и требованиями приложения.

Перезапускаем сервис СУБД:

systemctl restart postgresql

* также напомню, что название сервиса может отличаться и это зависит от версии PostgreSQL.

Создаем нового пользователя и проверяем доступ

Создаем временного пользователя командой:

vault read database/creds/postgresql-create

Мы должны увидеть что-то на подобие:

Key                Value
—                ——
lease_id           database/creds/postgresql-create/HMkEz1q6zuaSeyDNBeC8nhDK
lease_duration     1h
lease_renewable    true
password           9oAhg1VIEhleeRA7EU-U
username           v-root-postgres-0VO0sWprjEuCPZkZpu62-1631008720

Наши логин и пароль соответственно — v-root-postgres-0VO0sWprjEuCPZkZpu62-1631008720 и 9oAhg1VIEhleeRA7EU-U.

Перейдем на сервер с PostgreSQL и попробуем войти в СУБД с использованием этих данных:

psql -U’v-root-postgres-0VO0sWprjEuCPZkZpu62-1631008720′ -W template1

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

О продукте

PostgreSQL поддерживается на всех современных Unix системах (34 платформы), включая наиболее распространенные, такие как Linux, FreeBSD, NetBSD, OpenBSD, SunOS, Solaris, DUX, а также под macOS. Начиная с версии 8.X PostgreSQL работает в «native» режиме под MS Windows NT, Win2000, WinXP, Win2003. Известно, что есть успешные попытки работать с PostgreSQL под Novell Netware 6 и OS2.

PostgreSQL неоднократно признавалась базой года, например, Linux New Media AWARD 2004, 2003 Editors’ Choice Awards, 2004 Editors’ Choice Awards.

PostgreSQL используется как полигон для исследований нового типа баз данных, ориентированных на работу с потоками данных — это проект TelegraphCQ, стартовавший в 2002 году в Беркли после успешного проекта Telegraph (название главной улицы в Беркли).