PostgreSQL

Первоначальное создание файлов БД, пользователя-админа и пароля с помощью Docker-image

Нужно при первом запуске прокинуть в контейнер переменную POSTGRES_PASSWORD:

environment:
  POSTGRES_PASSWORD: example

При первом запуске скрипт initdb создаст папку для базы данных и инициализирует файлы в ней, пользователя POSTGRES_USER (по умолчанию - postgres) с паролем POSTGRES_PASSWORD.

При последующих запусках переменные POSTGRES_USER и POSTGRES_PASSWORD не учитываются (см. entrypoint образа)

Создание БД, пользователя и предоставление доступа

CREATE USER myuser WITH PASSWORD 'myPassword';
CREATE DATABASE mydb;
GRANT ALL PRIVILEGES ON DATABASE mydb to myuser;

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

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

Обновление пароля для пользователя

ALTER USER user_name WITH PASSWORD 'new_password';

Списки пользователей

• Список всех пользователей:

    SELECT u.usename AS "User name", u.usesysid AS "User ID",
        CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text)
            WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
            WHEN u.usecreatedb THEN CAST('create database' AS pg_catalog.text)
        ELSE CAST('' AS pg_catalog.text)
        END AS "Attributes"
    FROM pg_catalog.pg_user u
    ORDER BY 1;
  

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

    SELECT u.usename, (SELECT string_agg(d.datname, ',' ORDER BY d.datname)
       FROM pg_database d
       WHERE has_database_privilege(u.usename, d.datname, 'CONNECT')) AS allowed_databases
    FROM pg_user u
    ORDER BY u.usename
  

• Список пользователей, котором разрешено присоединяться к базе:

    SELECT d.datname, (SELECT string_agg(u.usename, ',' order by u.usename) 
        FROM pg_user u
        WHERE has_database_privilege(u.usename, d.datname, 'CONNECT')) AS allowed_users
    FROM pg_database d
    ORDER BY d.datname;
  

Статистика и прочее

• подключиться пользователем postgres к контейнеру с PostgreSQL: sudo docker exec -ti <CONTAINER_ID> psql -U postgres

• посмотреть текущие процессы (соединения?): SELECT * FROM pg_stat_activity ;

• удалить соединения к базе данных db: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='db';

• посмотреть сколько соединений используется, сколько зарезервировано для суперпользователя и каков лимит:

    select  * from
    (select count(*) used from pg_stat_activity) q1,
    (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) q2,
    (select setting::int max_conn from pg_settings where name=$$max_connections$$) q3;
  

Обновление мажорной версии PostgreSQL в Docker-контейнере

docker exec <postgres-old> pg_dumpall -U postgres | docker exec -i <postgres-new> psql -U postgres

Эта команда сделает дамп из старого PostgreSQL и сразу же импортирует в новый. Не забудьте подменить <postgres-old> и <postgres-new> на ваши актуальные имена (или id) контейнеров.

Также, лучше сначала сделать sudo su т.к. пайп | нельзя направить через sudo команде после sudo.

Обновление мажорной версии PostgreSQL в Docker-контейнере: 2-й способ

Способ заключается в использовании этого образа: https://github.com/tianon/docker-postgres-upgrade, который использует штатное средство pg_update для обновления мажорных версий PostgreSQL.

Пример запуска для обновления с версии 11 на 12:

sudo docker run --rm -v /data/postgres:/var/lib/postgresql tianon/postgres-upgrade:11-to-12

Перед использованием, нужно подготовить директорию /data/postgres так, чтобы ее содержимое оказалось в подпапке /data/postgres/11/data. После завершения работы, обновленный вариант будет в папке /data/postgres/12/data, содержимое которой нужно обратно положить в /data/postgres. Также, возможно, перед копированием папки с нужно будет положить файл прошлой версии /data/postgres/11/data/pg_hba.conf заместо нового: /data/postgres/12/data/pg_hba.conf