Ctrl+K
Ctrl+K
Ctrl+K

Postgresql

Postgresql#

Предупреждение

Является сертифицированным средством защиты информации

PostgreSQL — это мощная, открытая и объектно-реляционная система управления базами данных (СУБД), которая широко используется для работы с большими объемами данных. Она поддерживает расширенные функции, такие как транзакции, триггеры, хранимые процедуры и многое другое. PostgreSQL идеально подходит для сложных приложений, где требуется высокая производительность, надежность и гибкость.

Основные пакеты

Пакет

Версия

Репозиторий

Описание

postgesql

15+246astra3+ci1

installation

объектно-реляционная база данных

Дополнительные пакеты

Пакет

Версия

Репозиторий

Описание

postgesql-contrib

15+246astra3+ci1

installation

дополнительные возможности для PostgreSQL

postgesql-plpython3-15

15.10-astra.se1

installation

процедурный язык PL/Python 3 lkz PostgreSQL 15

Примечание

В репозиториях большая часть пакетов, относящихся к PostgreSQL, имеют вид postgresql…

Установка PostgreSQL#

  • установите PostgreSQL:

sudo apt install postgresql postgresql-contrib

Пакет postgresql-contrib содержит дополнительные модули и утилиты.

  • проверьте статус службы:

sudo systemctl status postgresql

Убедитесь, что служба активна и работает.

  • инициализируйте базу данных:

sudo postgresql-setup --initdb

  • запустите и включите службу:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Подключение к PostgreSQL#

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

sudo -u postgres psql

Это откроет интерактивную оболочку psql.

Предупреждение

Если после выполнения команды отображается ошибка «could not change directory to «/home/»: Отказано в доступе» и не появляется приглашение командной строки postgres=#, необходимо вместо su postgres использовать конструкцию su - postgres. Если приглашение postgres=# появилось, то сообщение об ошибке можно проигнорировать.

Если приглашение postgres=# не появляется, то вероятнее всего ОС Astra Linux Special Edition используется в более защищенном режиме, например, «Смоленск». В таком случае нужно для пользователя postgres назначить высокий уровень целостности:

sudo pdpl-user -i 63 postgres

Создание нового пользователя и базы данных#

  • создайте нового пользователя:

CREATE USER myuser WITH PASSWORD 'mypassword';

  • создайте базу данных и назначьте её новому пользователю:

CREATE DATABASE mydatabase OWNER myuser;

  • подключитесь к новой базе данных:

psql -U myuser -d mydatabase

Основные команды PostgreSQL#

Создание таблицы#

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

Вставка данных#

INSERT INTO users (name, email) VALUES ('Иван Иванов', 'ivan@example.com');
INSERT INTO users (name, email) VALUES ('Мария Петрова', 'maria@example.com');

Выборка данных#

SELECT * FROM users;

или

SELECT name, email FROM users WHERE id = 1;

Обновление данных#

UPDATE users SET email = 'ivan.new@example.com' WHERE id = 1;

Удаление данных#

DELETE FROM users WHERE id = 2;

Удаление таблицы#

DROP TABLE users;

Работа с PostgreSQL из командной строки, основные команды psql#

Подключение к базе данных:#

psql -U username -d databasename

Показать список таблиц:#

\dt

Показать структуру таблицы:#

\d users

Экспорт данных в файл:#

\copy (SELECT * FROM users) TO '/path/to/output.csv' WITH CSV HEADER;

Импорт данных из файла:#

\copy users FROM '/path/to/input.csv' WITH CSV HEADER;

Выход из psql:#

\q

Управление PostgreSQL, запуск и остановка службы#

Запуск:#

sudo systemctl start postgresql

Остановка:#

sudo systemctl stop postgresql

Перезапуск:#

sudo systemctl restart postgresql

Проверка статуса службы:#

sudo systemctl status postgresql

Использование PostgreSQL в скриптах#

Пример на Python:#

import psycopg2

# Подключение к базе данных
conn = psycopg2.connect(
    dbname="mydatabase",
    user="myuser",
    password="mypassword",
    host="localhost"
)
cursor = conn.cursor()

# Создание таблицы
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
)
''')

# Вставка данных
cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ('Петр Петров', 'petr@example.com'))

# Выборка данных
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Закрытие соединения
conn.commit()
cursor.close()
conn.close()

Логирование#

Для просмотра логов PostgreSQL в реальном времени есть возможность использовать утилиту tail. Это полезно для мониторинга активности базы данных, поиска ошибок или анализа запросов.

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

tail -f /var/lib/postgresql/15/main/pg_log/postgresql-Thu.log

Что происходит при вызове команды:

  • /var/lib/postgresql/15/main/pg_log, директория где хранятся логи PostgreSQL.

  • использование команды tail -f /путь к логам/postgresql-Thu.log, чтобы отслеживать изменения в лог-файле за четверг (Thu).

Как интерпретировать логи PostgreSQL

Логи PostgreSQL содержат информацию о различных событиях, таких как:

  • Запросы к базе данных (SELECT, INSERT, UPDATE, DELETE и т.д.).

  • Ошибки (например, синтаксические ошибки в запросах или проблемы с подключением).

  • Предупреждения (например, долгие запросы).

  • Информация о подключениях (кто подключился, кто отключился).

Пример строки лога:

2023-10-05 12:34:56 UTC LOG:  connection received: host=127.0.0.1 port=5432
2023-10-05 12:34:57 UTC LOG:  connection authorized: user=postgres database=test_db
2023-10-05 12:35:00 UTC ERROR:  syntax error at or near "SELECT"
2023-10-05 12:35:01 UTC LOG:  disconnection: session time: 0:00:05 user=postgres database=test_db host=127.0.0.1 port=5432

Графические инструменты для работы с PostgreSQL#

Если вы предпочитаете графический интерфейс, вот несколько популярных инструментов pgAdmin4, DBeaver.

Содержание