PostgreSQL

Postgre, PostgrePro

Команды в pgsql:

\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

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

https://www.postgresql.org/docs/current/libpq-envars.html - для клиентских приложений

Postgresql Pro 10

Исполняемые файлы: /opt/pgpro/std-10/bin

Переменная PATH для всех пользователей:

sudo sed -i '/PATH/s/"$/:\/opt\/pgpro\/std-10\/bin"/' /etc/environment

Переменная PATH для пользователей после выполнения команды sudo:

sudo sed -i '/secure_path/s/"$/:\/opt\/pgpro\/std-10\/bin"/' /etc/sudoers

Примеры

Роль для БД

Пример для создания групповой роли 'db_Owner' с полным доступом к схеме 'public' базы и пользователя 'user' в этой роли.Выполнять в базе db

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть

CREATE ROLE db_Owner;
 
GRANT CONNECT ON DATABASE db TO db_Owner;
 
GRANT ALL ON SCHEMA public TO db_Owner;
 
GRANT ALL ON ALL TABLES IN SCHEMA public TO db_Owner;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO db_Owner;
 
 
CREATE USER USER WITH PASSWORD '<SUPERPASSWORD>';
GRANT db_Owner TO USER;

Пример для удаления (обратная операция):

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть

REVOKE db_Owner FROM USER;
DROP USER USER;
 
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM db_Owner;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM db_Owner;
 
REVOKE ALL ON SCHEMA public FROM db_Owner;
 
REVOKE CONNECT ON DATABASE db FROM db_Owner;
 
DROP ROLE db_Owner;

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

Вход по умолчанию от системного пользователя postgres:

sudo -u postgres psql

Создать суперпользователя (pguser) с паролем:

sudo -u postgres createuser -P -s -e pguser

Создать пользователя с паролем:

CREATE USER username WITH PASSWORD 'password';

Изменить пароль пользователю:

ALTER USER username WITH ENCRYPTED PASSWORD 'password';

https://postgrespro.ru/docs/postgresql/12/app-createuser - Клиентские приложения: createuser
https://postgrespro.ru/docs/postgresql/12/sql-createrole - Команды SQL: CREATE ROLE

Роли

Показать существующие роли:

\du

Отозвать роль PUBLIC на базу данных:

REVOKE ALL ON DATABASE dbname FROM PUBLIC;

Привилегии

GRANT - https://www.postgresql.org/docs/current/sql-grant.html
REVOKE - https://www.postgresql.org/docs/current/sql-revoke.html

Работа с базой

Список баз:

sudo -u postgres psql -l

Создание пустой базы:

sudo -u postgres createdb -T template0 dbname
#или
sudo -u postgres psql -c 'CREATE DATABASE dbname'

Удаление базы:

sudo -u postgres dropdb dbname

Поменять владельца базы:

sudo -u postgres psql -c 'ALTER DATABASE dbname OWNER TO username'

Переименовать базу:

sudo -u postgres psql -c 'ALTER DATABASE dbname RENAME TO dbrename'

Работа со схемой

По умолчанию все объекты автоматически помещаются в схему «public». Она содержится во всех создаваемых базах данных.
Схемы (даже с одинаковыми именами) в разных базах данных не связаны. Предоставление привилегий для схемы применяется только к этой конкретной схеме в текущей БД (текущей БД на момент предоставления).

Показать текущий тип поиска схем:

SHOW search_path;

Создать схему:

CREATE SCHEMA имя_схемы;

Cоздать схему, владельцем которой будет другой пользователь:

CREATE SCHEMA имя_схемы AUTHORIZATION имя_пользователя;

Задать схему по умолчанию в сеансе:

SET search_path TO имя_схемы;
-- или
SET SCHEMA имя_схемы;

Задать схему по умолчанию пользователю:

ALTER USER имя_пользователя SET search_path TO имя_схемы;

Отозвать публичные права на схему public:

REVOKE ALL ON SCHEMA public FROM PUBLIC;

Предоставить явные права на схему public:

GRANT USAGE ON SCHEMA public TO имя_пользователя;

Создать права на создание объектов в схеме public:

GRANT CREATE ON SCHEMA public TO имя_пользователя;

https://postgrespro.ru/docs/postgresql/12/ddl-schemas
https://ru.stackoverflow.com/questions/1065574/Создание-пользователя-с-доступом-только-к-одной-схеме-postgres

Параметры

Некоторые указаны в postgresql.conf (/etc/postgresql/9.6/main/postgresql.conf) и начинают действовать только после перезагрузки postgres.

Показать текущий параметр temp_buffers, два способа:

sudo -u postgres psql -tc "SHOW temp_buffers;"
sudo -u postgres psql -tc "SELECT current_setting('temp_buffers');"

Установить переменную temp_buffers = 256MB:

sudo -u postgres psql -qc "ALTER SYSTEM SET temp_buffers='256MB';"

Пречитать конфиг:

sudo -u postgres psql -tc "SELECT pg_reload_conf();"

Тюнинг potgres:
http://pgtune.leopard.in.ua/- PgTune. Configuration calculator for PostgreSQL
https://its.1c.ru/db/metod8dev#content:5866:hdoc - Настройки PostgreSQL для работы с 1С:Предприятием

Сессии

Список сессий:

sudo -u postgres psql -tc "SELECT * FROM pg_stat_activity;"

Завершить все сессии к базе «db_bname»:

sudo -u postgres psql -tc "SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'db_name';

Запросы

Время запроса

psql -h ${HOST} -U ${USER} -d ${DB} --o /dev/null -c '\timing' -c '${QUERY}'

\timing on
\o /dev/null
${QUERY}

SSL

Создать сертификат

openssl req -nodes -new -x509 -keyout server.key -out server.crt -subj '/C=RU/L=MSK/O=Org/CN=postgres'

Скопировать server.key и server.crt в PGDATA
Назначить владельца для server.key,server.crt под кем работает postgres
Перегрузить конфигурацию
```
SELECT pg_reload_conf();
```

Проверить подключение SSL:

docker run --rm -e PGSSLMODE=require -e PGPASSWORD=${PGPASSWORD} -e PGHOST=${PGHOST} -e PGUSER=${PGUSER} -e PGDATABASE=${PGDATABASE} postgres:alpine psql -c "select 1"

Проверить текущие соединения:

SELECT pg_ssl.pid, pg_ssl.ssl, pg_ssl.version, pg_sa.backend_type, pg_sa.usename, pg_sa.client_addr
  FROM pg_stat_ssl pg_ssl
  JOIN pg_stat_activity pg_sa
  ON pg_ssl.pid = pg_sa.pid;

Проблемы:

UTC [1] LOG:  could not load server certificate file "server.crt": No such file or directory

Отсутствует сертификат в PGDATA, решение (п.2)

UTC [1] LOG:  private key file "server.key" must be owned by the database user or root
UTC [1] LOG:  could not load private key file "server.key": Permission denied

Неправильно назначенный владелец, решение (п.3)

https://www.postgresql.org/docs/8.4/libpq-connect.html
https://www.percona.com/blog/enabling-and-enforcing-ssl-tls-for-postgresql-connections/

Автозапуск

sudo systemctl enable postgresql

pg_hba.conf

Клиентская аутентификация

RU ENG - Файл pg_hba.conf
https://postgrespro.ru/docs/postgresql/9.6/auth-methods - Методы аутентификации

Команды SQL

EN, RU

GRANT — определить права доступа
SHOW — показать значение параметра времени выполнения
ALTER DATABASE — изменить атрибуты базы данных

Extension

Показать расширения

Доступные:

SELECT * FROM pg_available_extensions

Установленные:

SELECT * FROM pg_extension

\dx

Удалить расширения

DROP EXTENSION pg_stat_statements

Docker

Примеры запуска PostgreSQL

Первый запуск:

docker run --name postgres --rm --mount type=bind,src=/data/docker/postgres/var_lib_postgresql_data,dst=/var/lib/postgresql/data/ -e POSTGRES_PASSWORD=root -d postgres:10-alpine

Последующие запуски:

docker run --name postgres -p5432:5432 --mount type=bind,src=/data/docker/postgres/var_lib_postgresql_data,dst=/var/lib/postgresql/data/ -d postgres:10-alpine

Бэкап БД:

docker run --rm -v /data/backups:/backups -e PGPASSWORD=${PGPASSWORD} -e PGHOST=${PGHOST} -e PGUSER=${PGUSER} -e PGDATABASE=${PGDATABASE} postgres pg_dump -Fd -j 2 ${PGDATABASE} -f /backups/${PGDATABASE}_dump

https://hub.docker.com/_/postgres/

Резервное копирование и восстановление

Пример копирования базы с восстановлением на другом сервере:

pg_dump -h forsite.domain.com -U PatientAccountUser -d PatientAccount | psql -U PatientAccountUser -d PatientAccount

Ссылки

https://postgrespro.ru/
https://github.com/dalibo/pgbadger - pgBadger - a fast PostgreSQL log analysis report
http://pgconfigurator.cybertec.at/ - Cybertec PostgreSQL Configurator
https://translated.turbopages.org/proxy_u/en-ru.ru.478bd6ae-66151d99-0bfd81e8-74722d776562/https/stackoverflow.com/questions/24918367/grant-privileges-for-a-particular-database-in-postgresql?__ya_mt_enable_static_translations=1 - Предоставление привилегий для конкретной базы данных в PostgreSQL

wiki

Содержание