Skip to main content

Работа с PostgreSQL

Эта инструкция охватывает подключение к PostgreSQL, запущенному на сервере 192.168.0.103, и базовые операции.

текущие данные для входа:

  • ip 192.168.0.103
  • логин postgres(супер-пользователь), kollokpoi
  • пароль 12332155

Предварительные условия​

На сервере уже должен быть установлен и запущен PostgreSQL. Проверить это можно командой:

# Проверить состояние службы PostgreSQL
systemctl status postgresql
# или для конкретной версии (например, 14)
systemctl status postgresql@14-main

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

1. Подключение через терминал сервера (psql)​

Утилита psql — это интерактивный терминал PostgreSQL.

# Подключение от имени пользователя postgres (суперпользователь)
sudo -u postgres psql

# Подключение от имени конкретного пользователя
psql -U kollokpoi -d postgres

# Подключение к конкретной базе данных
psql -U kollokpoi -d mydatabase

2. Удаленное подключение из терминала​

Для подключения с локальной машины:

# Стандартное подключение
psql -h 192.168.0.103 -U kollokpoi -d postgres

# С указанием порта (по умолчанию 5432)
psql -h 192.168.0.103 -p 5432 -U kollokpoi -d postgres

3. Графические клиенты (рекомендуется)​

pgAdmin (Официальный графический интерфейс)​

DBeaver (Бесплатный, кроссплатформенный)​

  • Официальный сайт
  • Универсальный клиент с отличной поддержкой PostgreSQL
  • Автодополнение, форматирование SQL, ER-диаграммы

DataGrip (JetBrains, платный)​

  • Официальный сайт
  • Продвинутая IDE для работы с базами данных
  • Интеллектуальное автодополнение, рефакторинг, отладка

Postico (Только для macOS, платный)​

  • Официальный сайт
  • Элегантный и простой интерфейс
  • Отлично подходит для повседневной работы

Настройка удаленного доступа к PostgreSQL​

По умолчанию PostgreSQL разрешает подключения только с localhost. Для настройки удаленного доступа:

  1. Измени файл pg_hba.conf (Host-Based Authentication):

    sudo nano /etc/postgresql/14/main/pg_hba.conf

    Добавь строку для разрешения подключений с сети:

    # Было (только локальные подключения):
    # host    all             all             127.0.0.1/32            md5

    # Добавь (разрешить подключения с любой сети):
    host    all             all             0.0.0.0/0               md5

    # Или только с конкретной подсети:
    host    all             all             192.168.0.0/24          md5

  2. Измени файл postgresql.conf:

    sudo nano /etc/postgresql/14/main/postgresql.conf

    Найди и измени параметр:

    # Было:
    # listen_addresses = 'localhost'

    # Стало:
    listen_addresses = '*'  # или '192.168.0.103,localhost'

  3. Перезапусти PostgreSQL:

    sudo systemctl restart postgresql

  4. Настрой брандмауэр (если используется):

    sudo ufw allow 5432/tcp

  5. Проверь, что порт слушается:

    sudo netstat -tlnp | grep 5432

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

Мета-команды psql (начинаются с обратной косой черты)​

-- Показать все базы данных
\l

-- Подключиться к базе данных
\c mydatabase

-- Показать таблицы в текущей базе
\dt

-- Показать расширенные сведения о таблицах
\dt+

-- Показать структуру таблицы
\d users
\d+ users  -- расширенная информация

-- Показать список пользователей/ролей
\du

-- Выйти из psql
\q

-- Показать справку по командам
\?

Работа с базами данных​

-- Создать новую базу данных
CREATE DATABASE mydatabase;

-- Создать базу с указанием владельца
CREATE DATABASE projectdb OWNER kollokpoi;

-- Удалить базу данных
DROP DATABASE mydatabase;

-- Переименовать базу данных
ALTER DATABASE oldname RENAME TO newname;

Работа с таблицами​

-- Создать таблицу
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- Создать таблицу с индексами
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2),
    category_id INTEGER,
    CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_price ON products(price);

-- Удалить таблицу
DROP TABLE users;

-- Очистить таблицу (удалить все данные)
TRUNCATE TABLE users;

CRUD-операции​

-- Добавление данных
INSERT INTO users (username, email) 
VALUES ('kollokpoi', 'user@example.com');

-- Вставка нескольких строк
INSERT INTO users (username, email) 
VALUES 
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com')
RETURNING id;  -- вернет id вставленных строк

-- Чтение данных
SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;
SELECT * FROM users WHERE created_at > '2024-01-01';
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- Обновление данных
UPDATE users SET email = 'new@example.com' WHERE id = 1;
UPDATE users SET is_active = false WHERE created_at < '2023-01-01';

-- Удаление данных
DELETE FROM users WHERE id = 1;

Расширенные возможности PostgreSQL​

JSONB (работа с JSON документами)​

-- Создание таблицы с JSONB полем
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    data JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Вставка JSON
INSERT INTO documents (data) 
VALUES ('{"title": "PostgreSQL Guide", "tags": ["database", "sql"], "author": "kollokpoi"}');

-- Поиск по JSON
SELECT * FROM documents WHERE data @> '{"author": "kollokpoi"}';
SELECT data->>'title' AS title FROM documents;

-- Индексирование JSONB
CREATE INDEX idx_documents_data ON documents USING GIN (data);

Полнотекстовый поиск​

-- Создание таблицы с полнотекстовым поиском
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    search_vector TSVECTOR
);

-- Создание индекса
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Поиск
SELECT title FROM articles 
WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & руководство');

Экспорт и импорт данных​

Экспорт с помощью pg_dump​

# Экспорт всей базы
pg_dump -U kollokpoi -h 192.168.0.103 mydatabase > mydatabase_backup.sql

# Экспорт только схемы (без данных)
pg_dump -U kollokpoi -h 192.168.0.103 --schema-only mydatabase > mydatabase_schema.sql

# Экспорт только данных
pg_dump -U kollokpoi -h 192.168.0.103 --data-only mydatabase > mydatabase_data.sql

# Экспорт в сжатом формате
pg_dump -U kollokpoi -h 192.168.0.103 mydatabase | gzip > mydatabase_backup.sql.gz

# Экспорт конкретной таблицы
pg_dump -U kollokpoi -h 192.168.0.103 -t users mydatabase > users_backup.sql

Импорт с помощью psql​

# Импорт всей базы
psql -U kollokpoi -h 192.168.0.103 -d mydatabase < mydatabase_backup.sql

# Импорт в новую базу
createdb -U kollokpoi -h 192.168.0.103 newdatabase
psql -U kollokpoi -h 192.168.0.103 -d newdatabase < mydatabase_backup.sql

Копирование данных в/из CSV​

-- Экспорт в CSV
COPY users TO '/tmp/users.csv' DELIMITER ',' CSV HEADER;

-- Импорт из CSV
COPY users FROM '/tmp/users.csv' DELIMITER ',' CSV HEADER;

Из командной строки:

# Экспорт в CSV
psql -U kollokpoi -h 192.168.0.103 -d mydatabase -c "COPY users TO STDOUT CSV HEADER" > users.csv

# Импорт из CSV
psql -U kollokpoi -h 192.168.0.103 -d mydatabase -c "COPY users FROM STDIN CSV HEADER" < users.csv

Рекомендуемые официальные туториалы​

Для глубокого изучения PostgreSQL:

  1. PostgreSQL Official Documentation — полная официальная документация
  2. PostgreSQL Tutorial — неофициальный, но отличный учебник
  3. Interactive PostgreSQL Tutorial — интерактивный учебник от PostgreSQL
  4. PostgreSQL Exercises — практические упражнения
  5. PostgreSQL Wiki — сообщественная вики с советами и best practices

Решение типичных проблем​

Ошибка подключения "Connection refused"​

  1. Проверь, что PostgreSQL запущен:

    sudo systemctl status postgresql

  2. Проверь настройки listen_addresses в postgresql.conf:

    sudo grep listen_addresses /etc/postgresql/*/main/postgresql.conf

  3. Проверь правила pg_hba.conf:

    sudo cat /etc/postgresql/*/main/pg_hba.conf | grep -v '^#' | grep -v '^$'

Ошибка "Role does not exist"​

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

sudo -u postgres createuser --interactive
# или
sudo -u postgres psql -c "CREATE USER kollokpoi WITH PASSWORD 'strong_password';"

Проблемы с правами доступа​

-- Дать права пользователю на базу данных
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO kollokpoi;

-- Дать права на все таблицы в схеме public
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO kollokpoi;

-- Дать права на выполнение функций
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO kollokpoi;

Ошибка "Database is being accessed by other users"​

Принудительно заверши все подключения:

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'mydatabase' AND pid <> pg_backend_pid();

Полезные системные запросы​

-- Показать активные подключения
SELECT * FROM pg_stat_activity;

-- Показать размер баз данных
SELECT 
    datname AS database,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Показать размер таблиц
SELECT 
    schemaname AS schema,
    tablename AS table,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- Проверка производительности запросов
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Следующие шаги​

После освоения основ PostgreSQL рекомендуется:

  1. Настроить репликацию (streaming replication) для отказоустойчивости
  2. Изучить партиционирование таблиц для больших данных
  3. Настроить мониторинг с помощью pg_stat_statements и pgBadger
  4. Изучить расширения PostgreSQL:
    • PostGIS для геоданных
    • pg_cron для выполнения задач по расписанию
    • TimescaleDB для временных рядов
  5. Оптимизировать конфигурацию под нагрузку
  6. Настроить PgBouncer для пулинга соединений

Для сервера 192.168.0.103 рекомендуется настроить регулярное резервное копирование с помощью pg_dump и мониторинг свободного места.