Работа с PostgreSQL

Небольшая шпаргалка по работе с #PostgreSQL.

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

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

1

sudo -u 'postgres' psql -c '\du'

• Создать пользователя DB_USER с паролем:

1

sudo -u 'postgres' createuser --pwprompt 'DB_USER'

• Удалить пользователя DB_USER:

1

sudo -u 'postgres' dropuser 'DB_USER'

Базы данных

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

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' 'DB_NAME'

• Создать базу данных DB_NAME из шаблона template0 с владельцем DB_USER:

1

sudo -u 'postgres' createdb --owner='DB_USER' --template='template0' 'DB_NAME'

• Удалить базу данных DB_NAME:

1

sudo -u 'postgres' dropdb 'DB_NAME'

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

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

1

f='backup.sql'; pg_dump --host='127.0.0.1' --port='5432' --username='DB_USER' --password --dbname='DB_NAME' --file="${f}" && xz "${f}" && rm -f "${f}"

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

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

1

sudo -u 'postgres' dropdb 'DB_NAME'

• Создать новую базу данных DB_NAME с владельцем DB_USER:

1

sudo -u 'postgres' createdb --owner='DB_USER' 'DB_NAME'

• Восстановить данные в новую базу данных DB_NAME из файла backup.sql.xz:

1

f='backup.sql'; xz -d "${f}.xz" && sudo -u 'postgres' psql --dbname='DB_NAME' --file="${f}"

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

• Выполнить очистку и собрать статистику базы данных 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

1

apt install timescaledb-2-postgresql-17 timescaledb-2-loader-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'