Работа с PostgreSQL

Работа с PostgreSQL

  • •

Шпаргалка по работе с #PostgreSQL.

Роли (пользователи)

  • Посмотреть список ролей:
1

sudo -u 'postgres' psql -c '\du'
  • Создать роль DB_USER с паролем:
1

sudo -u 'postgres' createuser --pwprompt 'DB_USER'
  • Переименовать роль DB_USER в DB_USER_NEW:
1

sudo -u 'postgres' psql -c 'alter role DB_USER rename to DB_USER_NEW;'
  • Сделать роль DB_USER супер-пользователем:
1

sudo -u 'postgres' psql -c 'alter role DB_USER superuser;'
  • Изменить пароль у роли DB_USER:
1

sudo -u 'postgres' psql -c "alter role DB_USER with password 'PASSWORD';"
  • Удалить пароль у роли DB_USER:
1

sudo -u 'postgres' psql -c 'alter role DB_USER with password null;'
  • Удалить роль DB_USER:
1

sudo -u 'postgres' dropuser 'DB_USER'
  • Изменить роль владельца у всех баз данных с DB_USER на DB_USER_NEW:
1

sudo -u 'postgres' psql -c 'reassign owned by DB_USER to DB_USER_NEW;'
  • Изменить роль у таблиц базы данных DB_NAME с DB_USER на DB_USER_NEW:
1

sudo -u 'postgres' psql -c '\c DB_NAME' -c 'reassign owned by DB_USER to DB_USER_NEW;'

Базы данных

  • Посмотреть список баз данных:
1

sudo -u 'postgres' psql -c '\l'
  • Посмотреть список таблиц схемы public в базе данных DB_NAME:
1

sudo -u 'postgres' psql -c '\c DB_NAME' -c '\dt'
  • Посмотреть список таблиц всех схем в базе данных DB_NAME:
1

sudo -u 'postgres' psql -c '\c DB_NAME' -c '\dt *.*'
  • Создать базу данных DB_NAME с владельцем DB_USER:
1

sudo -u 'postgres' createdb --owner='DB_USER' --locale='C' 'DB_NAME'
  • Создать базу данных DB_NAME из шаблона template0 с владельцем DB_USER:
1

sudo -u 'postgres' createdb --owner='DB_USER' --locale='C' --template='template0' 'DB_NAME'
  • Удалить базу данных DB_NAME:
1

sudo -u 'postgres' dropdb --if-exists 'DB_NAME'

Резервное копирование

  • Создать резервную копию базы данных DB_NAME при помощи пользователя DB_USER и записать в файл DB_NAME.DATE.sql.xz:
1

u='DB_USER'; d='DB_NAME'; f="${d}.$( date -u '+%Y-%m-%d.%H-%M-%S' ).sql"; sudo -u 'postgres' pg_dump --host='127.0.0.1' --port='5432' --username="${u}" --password --dbname="${d}" --file="${f}" && xz "${f}" && rm -f "${f}"

Восстановление

  • Удалить старую базу данных DB_NAME (при необходимости):
1

sudo -u 'postgres' dropdb --if-exists 'DB_NAME'
  • Создать новую базу данных DB_NAME с владельцем DB_USER:
1

sudo -u 'postgres' createdb --owner='DB_USER' 'DB_NAME'
  • Восстановить данные в новую базу данных DB_NAME под пользователем DB_USER из файла DB_NAME.sql.xz:
1

u='DB_USER'; d='DB_NAME'; f="${d}.sql"; xz -d "${f}.xz" && psql --host='127.0.0.1' --port='5432' --username="${u}" --dbname="${d}" --file="${f}" --no-psqlrc --single-transaction

Очистка и анализ

  • Выполнить очистку и собрать статистику базы данных DB_NAME:
1

sudo -u 'postgres' vacuumdb --dbname='DB_NAME' --analyze
  • Выполнить очистку и собрать статистику всех баз данных:
1

sudo -u 'postgres' vacuumdb --all --analyze

Обновление кластера

  • Посмотреть список экземпляров PostgreSQL в кластере:
1

pg_lsclusters
  • Установить новую версию PostgreSQL 17:
1

apt install postgresql-17
  • Установить новую версию TimescaleDB для PostgreSQL 17:
1

apt install timescaledb-2-postgresql-17 timescaledb-2-loader-postgresql-17
  • Импортировать настройки TimescaleDB в конфигурацию PostgreSQL 17:
1

timescaledb-tune --quiet --yes && systemctl restart postgresql@17-main.service
  • Остановить новую версию экземпляра PostgreSQL 17 в кластере:
1

pg_dropcluster --stop 17 main
  • Запустить обновление версии экземпляра PostgreSQL 16 до PostgreSQL 17 в кластере:
1

pg_upgradecluster 16 main
  • Удалить версию экземпляра PostgreSQL 16 из кластера:
1

pg_dropcluster 16 main
  • Посмотреть установленные пакеты для PostgreSQL 16 и удалить ненужные:
1

apt list --installed | grep 'postgresql.*-16'

Удалённое подключение

  • В файле postgresql.conf добавить listen_addresses = '*':
1
2

# listen_addresses = 'localhost'
listen_addresses = '*'
  • В файле pg_hba.conf добавить строки:
1
2

host    all             all             0.0.0.0/0               scram-sha-256
host    all             all             ::/0                    scram-sha-256
  • В брандмауэре открыть порт 5432/tcp.

Источники:

Категории
Теги
Авторы
Смотрите также
Мета
Лицензия
ID файла
UUID
Системный путь
Тип
Статистика
Количество слов
Время чтения
мин.