Postgresql

Входим в контейнер с постгресом:

docker run -t --rm --net=host -v /tmp/build:/tmp/build postgres:9.6.23-alpine3.14 sh

Создание дампа только двух таблиц:

pg_dump -h 111.16.1.102 -U lebnik --dbname="svc-seo" --schema='public' --table=public."links_box" --table=public."links_box_link" --file="/tmp/build/dump.sql"

кстати дамп создается в собственном своеобразном формате постгреса и поменять его на SQL нельзя, можно только указать output file format (custom, directory, tar, plain text (default)): --format=c|d|t|p

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

psql -h 111.16.52.100 -U lebnik --dbname="svc-seo" -f /tmp/build/dump.sql

Делать дамп и восстанавливаться можно быстрее, если делать это параллельно в несколько потоков:

pg_dump --dbname="svc-seo" --file="./svc_seo_db" --format=d --blobs --jobs=8

pg_restore --clean --create --format=directory --jobs=8 --host=localhost --port=5432 --username="postgres" --dbname="svc-seo" "./svc_seo_db"

Ошибки при восстановлении

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

...
psql:/tmp/build/db.sql:533161: ERROR:  insert or update on table "links_box" violates foreign key constraint "fk_2ca2df9ec33f7837"
...

Подключиться к базе database_name

psql -h 127.0.0.1 -U postgres \c database_name

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

SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age
FROM pg_stat_activity
WHERE state <> 'idle'
  AND query NOT LIKE '% FROM pg_stat_activity %'
ORDER BY age;

Посмотреть все запросы к базе svc-seo:

select *
from pg_stat_activity
where datname = 'svc-seo';

Настройка доступа без пароля с помощью pg_hba.conf

 # Database administrative login by Unix domain socket
 local   all             all                                     trust

 # TYPE  DATABASE        USER            ADDRESS                 METHOD

 # "local" is for Unix domain socket connections only
 local   all             all                                     trust
 # IPv4 local connections:
 host    all             all             0.0.0.0/0               trust
 # IPv6 local connections:
 host    all             all             ::0/0                   trust

Логирование и просмотр логов

Add to postgres/data/postgresql.conf

log_directory = 'pg_log'
log_filename = 'postgresql.log'
log_statement = 'all'
logging_collector = on

How to use client (psql)

docker exec -it postgres-ct psql -h localhost -U postgres

How to watching sql-queries

docker exec -it postgres-ct tail -f /var/www/ansible-ct/postgres/data/pg_log/postgres.log

Как использовать клиент для postgresql

На тентакле:

docker exec -it testdeploy_pg_1 psql -h localhost -U postgres

подключаюсь к базе hr:

\c hr

смотрю схему таблицы:

\d+ public.employee

Проблемы с миграциями

Если возникает ошибка (postgreSql):

[InvalidArgumentException]
  There was a problem creating the schema table: SQLSTATE[42P07]: Duplicate table: 7 ОШИБКА:  отношение "phinxlog" уже существует

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

GRANT ALL PRIVILEGES ON TABLE public.phinxlog TO "user";

или

GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;

Если возникает ошибка:

SQLSTATE[23505]: Unique violation ...

значит сбился сиквенс:

SELECT (max(department_id) + 1) FROM department;-- например возвратился результат: 1679
ALTER SEQUENCE department_department_id_seq RESTART WITH 1679;

Создаем роль только на чтение и затем пользователям даем эту роль:

-- создаем роль только на чтение: CREATE ROLE user_readonly LOGIN PASSWORD 'readtruelove';

--смена пароля: ALTER ROLE user_readonly WITH PASSWORD 'readtruelove';

-- даем права на подключение к бд: GRANT CONNECT ON DATABASE your_db TO user_readonly;

-- теперь нужно подключится к your_db и дать права на чтение схемы public: GRANT USAGE ON SCHEMA public TO user_readonly;

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

SELECT 'GRANT SELECT ON '||schemaname||'."'||tablename||'" TO user_readonly;'
FROM pg_tables WHERE schemaname IN ('public') ORDER BY schemaname, tablename;

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

-- создадим пользователя CREATE USER "new.user" WITH ENCRYPTED PASSWORD 'y21sve'; --DROP USER "dmitry.5";

-- дадим пользователю роль чтения: GRANT user_readonly TO "new.user";

-- подробнее: https://www.8host.com/blog/ispolzovanie-rolej-i-upravlenie-dostupom-v-postgresql/

Добавление пользователя и доступа 

С помощью env-переменной и скрипта add.sh

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
DO
\$body\$
BEGIN
   IF NOT EXISTS (
      SELECT *
      FROM   pg_catalog.pg_user
      WHERE  usename = '$PROJECT_USER') THEN

      CREATE ROLE "$PROJECT_USER" LOGIN PASSWORD '$PROJECT_PASSWORD';
   END IF;
END
\$body\$;

CREATE DATABASE "catalog" WITH OWNER="$PROJECT_USER" TEMPLATE=template1;
GRANT ALL PRIVILEGES ON DATABASE "catalog" TO "$PROJECT_USER";
REVOKE ALL PRIVILEGES ON DATABASE "catalog" FROM PUBLIC;

EOSQL

Собственный Dockerfile

FROM postgres:9.5

RUN localedef -i ru_RU -c -f UTF-8 -A /usr/share/locale/locale.alias ru_RU.UTF-8
ENV LANG ru_RU.utf8

ENV POSTGRES_PASSWORD Oa32134233u2z1OHrzT
ENV PROJECT_USER user
ENV PROJECT_PASSWORD true343232

COPY config/* /docker-entrypoint-initdb.d/
RUN mkdir /log

в директорию config можно положить любое кол-во .sh файлов, например указанный выше add.sh

Отключаем подписки

psql -h 127.0.0.1 -U postgres \c clickhouse UPDATE pg_subscription SET subenabled = false;

History

Можно изменять значения полей таблицы pg_stat_activity, например:

SET application_name = 'test_application_name';

Теперь можно посмотреть его:

select * from pg_stat_activity WHERE pid = pg_backend_pid();

Таким образом в данное поле можно положить идентификатор реального пользователя при его авторизации.

Источники: 1