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

# Обновить список пакетов
apt update

# Установить PostgreSQL
apt install -y postgresql postgresql-contrib

# Проверить статус службы
systemctl status postgresql

# Включить автозапуск
systemctl enable postgresql

# Установить необходимые инструменты
apt install -y curl ca-certificates

# Добавить ключ подписи PostgreSQL
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# Добавить репозиторий
echo 'deb https://apt.postgresql.org/pub/repos/apt '$(lsb_release -cs)'-pgdg main' > /etc/apt/sources.list.d/pgdg.list

# Обновить и установить
apt update
apt install -y postgresql-16

# Добавить официальный репозиторий PostgreSQL
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Отключить встроенный модуль PostgreSQL
dnf -qy module disable postgresql

# Установить PostgreSQL 16
dnf install -y postgresql16-server postgresql16-contrib

# Инициализировать базу данных
/usr/pgsql-16/bin/postgresql-16-setup initdb

# Запустить и включить службу
systemctl enable --now postgresql-16

# Проверить статус службы
systemctl status postgresql-16

psql --version
# Вывод: psql (PostgreSQL) 16.x

# Информация о службе
psql -U postgres -c 'SELECT version();'

# Переключиться на системного пользователя postgres
su - postgres

# Подключиться к консоли PostgreSQL
psql

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

# Подключиться к конкретной базе данных
sudo -u postgres psql -d mydb

-- В консоли PostgreSQL
-- Создать нового пользователя
CREATE USER appuser WITH PASSWORD 'НадёжныйПароль123!';

-- Создать суперпользователя (использовать с осторожностью)
CREATE USER adminuser WITH PASSWORD 'АдминПароль!' SUPERUSER;

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

-- Изменить пароль пользователя
ALTER USER appuser WITH PASSWORD 'НовыйПароль456!';

-- Предоставить право создания баз данных
ALTER USER appuser CREATEDB;

-- Создать базу данных
CREATE DATABASE myapp OWNER appuser ENCODING 'UTF8' LC_COLLATE 'ru_RU.UTF-8' LC_CTYPE 'ru_RU.UTF-8' TEMPLATE template0;

-- Список баз данных
\l

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

-- Предоставить полные права пользователю
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;

-- Права на схему (PostgreSQL 15+)
GRANT ALL ON SCHEMA public TO appuser;

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

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

-- Вставить данные
INSERT INTO users (username, email) VALUES ('ivan', 'ivan@example.com');

-- Запросить данные
SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;

-- Обновить данные
UPDATE users SET email = 'novyi@example.com' WHERE id = 1;

-- Удалить данные
DELETE FROM users WHERE id = 5;

-- Список таблиц
\dt

-- Просмотр структуры таблицы
\d users

-- Создать индекс
CREATE INDEX idx_users_email ON users(email);

# Найти расположение файла конфигурации
sudo -u postgres psql -c 'SHOW config_file;'

# Редактировать
nano /etc/postgresql/16/main/postgresql.conf

# Изменить строку listen_addresses
listen_addresses = '*'

# Порт (по умолчанию 5432)
port = 5432

nano /etc/postgresql/16/main/pg_hba.conf

# Добавить строку для удалённых подключений (в конец файла)
# TYPE  DATABASE  USER      ADDRESS         METHOD
host    myapp     appuser   192.168.1.0/24  scram-sha-256
host    all       all       0.0.0.0/0       scram-sha-256

# Перезапустить службу
systemctl restart postgresql

# Открыть порт с помощью UFW
ufw allow 5432/tcp

# С помощью iptables
iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

# Резервная копия одной базы данных
pg_dump -U postgres myapp > myapp_backup.sql

# Сжатый формат (более быстрое восстановление)
pg_dump -U postgres -Fc myapp > myapp_backup.dump

# Резервная копия всех баз данных
pg_dumpall -U postgres > all_databases.sql

# Резервная копия с удалённого сервера
pg_dump -h 192.168.1.100 -U postgres myapp > myapp_remote.sql

# Восстановление из формата SQL
psql -U postgres myapp < myapp_backup.sql

# Восстановление из сжатого формата
pg_restore -U postgres -d myapp myapp_backup.dump

# Восстановление всех баз данных
psql -U postgres < all_databases.sql

# Редактировать crontab
crontab -e

# Создавать резервную копию каждую ночь в 02:00
0 2 * * * pg_dump -U postgres myapp | gzip > /backup/myapp_$(date +\%Y\%m\%d).sql.gz

# Удалять резервные копии старше 30 дней
0 3 * * * find /backup -name 'myapp_*.sql.gz' -mtime +30 -delete

# Запустить службу
systemctl start postgresql

# Остановить службу
systemctl stop postgresql

# Перезапустить службу
systemctl restart postgresql

# Перезагрузить конфигурацию (без перезапуска)
systemctl reload postgresql

# Просмотр журналов
journalctl -u postgresql -f
tail -f /var/log/postgresql/postgresql-16-main.log