PostgreSQL — ~ # Unix-Garage

Хорошее видео по знакомству с PostgreSQL (установка, добавление пользователей, pgAdmin) — https://youtu.be/kWUW3sMK0Mk

Установка версии 14 на Debian 12

Основы работы

При установке PostgreSQL автоматически создаётся пользователь postgres. Надо войти в его учётную запись:

sudo -i -u postgres

Потом можно открыть консоль posgres:

psql

sudo -u postgres psql -c «SELECT version();»

\du - посмотреть пользователей
\list или \l: вывести список всех баз данных
\l+ : вывести с размерами БД
\dt: вывести список всех таблиц в текущей базе
\с basename; выбрать БД для работы

Создание базы данных:

createdb <base_name>
или 
CREATE DATABASE flask1;

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

dropdb <base_name>

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

postgres=# ALTER USER postgres WITH PASSWORD 'new_password';

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

CREATE USER <username> WITH PASSWORD 'password';

Дать суперпользователя:

ALTER USER username WITH SUPERUSER;

Установка pgAdmin — https://www.pgadmin.org/download/pgadmin-4-apt/

Несортированное

psql -h localhost -p 5432 -U postgress testdb

Работа с postgres в Docker контейнере. При выполнении команды psql получил ответ:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "root" does not exist

Поскольку пользователь и база уже заведены через докер-файл, то сделал так:

psql --username=postgres_user --dbname=postgres_db

Имя пользователя и базы у каждого свои будут. Часть содержимого докер-файла:

environment:

      POSTGRES_DB: "postgres_db"

      POSTGRES_USER: "postgres_user"

      POSTGRES_PASSWORD: "secret"

Далее можно через \l или \dt посмотреть базы и таблицы.

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "xxxxxx" does not exist

Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"

В моём случае эти ошибки были вызваны тем, что уже существовал volume для postgres контейнера и в нём была какая-то информация. Я удалил volume и передеплоился. Всё заработало.

Ещё первоначально помогли смена логина и пароля при деплое на простые (POSTGRES_USER=admin
POSTGRES_PASSWORD=admin) (чем-то не нравились установленные сложные имена. Но потом не создавалась база. Короче, подчищать надо volume.

Показать каталог хранения базы:

marketing=# SHOW data_directory;
data_directory
—————————
/var/lib/postgresql/data
(1 row)

Восстановление базы после pg_dumpall

psql -U username -f backupfile.sql

https://hevodata.com/learn/postgresql-dump-import/#:~:text=You%20can%20use%20the%20psql,file%20with%20the%20psql%20tool.

ps aux | grep postgres | grep -- -D  - узнать где лежит конфиг

Разрешить подключение из вне и с определённого ip https://mhelp.pro/ru/kak-nastroit-udalennoye-podklyucheniye-k-bd-postgresql-iz-windows/

#Запросы

Поиск значения внутри временной метки (поле с timestamp)

SELECT *
FROM base_feed_feed
WHERE TO_CHAR(modified, 'YYYY-MM-DD HH24:MI:SS') LIKE '%2023-10%';

Поиск в текстовом поле

SELECT *
FROM base_feed_feed
WHERE modified ILIKE '%word%';