Установка postgres debian:
|
1 2 3 4 5 6 7 |
apt update apt install postgresql postgresql-contrib apt show postgresql https://postgrespro.ru/docs/postgresql/9.6/app-pgrestore |
psql:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Все команды запускаются под пользователем postgres (postgresql-суперпользователь $ su - postgres) sudo su - postgres - входим пользователем под postgres psql -l - список баз данных psql -d dbname - подключение к БД dbname psql -f file.sql - выполнение команд из файла file.sql psql -U postgres -d dbname -c "CREATE TABLE test(some_id serial PRIMARY KEY, some_text text);" - выполнение команды в базе dbname psql -d dbname -H -c "SELECT * FROM test" -o test.html - вывод результата запроса в html-файл |
Проверка соединения:
|
1 |
\conninfo |
Подключится к базе:
|
1 |
\с basename |
Команды psql:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
\c dbname - подсоединение к БД dbname \l - список баз данных \dt - список всех таблиц \d table - структура таблицы table \du - список всех пользователей и их привилегий \dt+ - список всех таблиц с описанием \dt *s* - список всех таблиц, содержащих s в имени \i FILE - выполнить команды из файла FILE \o FILE - сохранить результат запроса в файл FILE \a - переключение между режимами вывода: с/без выравнивания |
Просмотр списка и путей к конфигурационным файлам:
|
1 2 3 |
psql > SELECT name, setting FROM pg_settings WHERE category = 'File Locations'; # или список всех конфигурационных параметров psql > show all; |
Список активных соединений с информацией о:
|
1 2 3 |
pid процесса, выполняющегося запроса, пользователя, базы данных psql > SELECT * FROM pg_stat_activity; |
Создание индексов:
|
1 2 3 4 5 |
# primary key psql > ALTER TABLE tableName ADD PRIMARY KEY (id); # unique index psql > CREATE UNIQUE INDEX indexName ON tableName (columnNames); |
Настройка подключения к базе:
|
1 2 3 4 5 6 7 8 9 10 11 |
postgresql.conf --------------- #listen_addresses = 'localhost' listen_addresses = '*' # разрешить подключение для всех --------------- !!! после изменений перезапустить postgres pg_hba.conf ----------- host all all 0.0.0.0/0 password # разрешения подключения для всех ----------- |
pg_dump — список наиболее часто используемых опций:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-h host - хост, если не указан то используется localhost или значение из переменной окружения PGHOST. -p port - порт, если не указан то используется 5432 или значение из переменной окружения PGPORT. -u - пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER. -a, --data-only - дамп только данных, по-умолчанию сохраняются данные и схема. -b - включать в дамп большие объекты (blog'и). -s, --schema-only - дамп только схемы. -C, --create - добавляет команду для создания БД. -c - добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.). -O - не добавлять команды для установки владельца объекта (таблиц, видов и т.д.). -F, --format {c|t|p} - выходной формат дампа, custom, tar, или plain text. -t, --table=TABLE - указываем определенную таблицу для дампа. -v, --verbose - вывод подробной информации. -D, --attribute-inserts - дамп используя команду INSERT с списком имен свойств. |
Бекап \ архивирования таблиц:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
В PostgreSQL есть две утилиты для бекапа pg_dump и pg_dumpall. pg_dump - используется для бекапа одной базы pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb - создание бекапа базы mydb, в сжатом виде pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb - создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb - создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments pg_dump -a -t table_name -f file_name database_name - Дамп данных только одной таблицы. Если нужно создать резервную копию нескольких таблиц, то имена этих таблиц перечисляются с помощью ключа -t для каждой таблицы. pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz - создание резервной копии с сжатием в gz |
Бекап всех баз данных используя команду pg_dumpall:
|
1 2 |
pg_dumpall - для бекапа всех баз и сервера в целом (необходимо запускать под postgresql-суперпользователем) pg_dumpall > all.sql |
Проверка бекапа:
|
1 2 3 |
grep "^[\]connect" all.sql \connect db1 \connect db2 |
В PostgreSQL есть две утилиты для восстановления базы из бекапа:
|
1 2 3 4 5 6 7 8 9 10 |
psql - восстановление бекапов, которые хранятся в обычном текстовом файле (plain text); pg_restore - восстановление сжатых бекапов (tar); psql -h localhost -U someuser -d dbname -f mydb.sql - восстановление всего бекапа с игнорированием ошибок psql -h localhost -U someuser --set ON_ERROR_STOP=on -f mydb.sql - восстановление всего бекапа с остановкой на первой ошибке Для восстановления из tar-архива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup |
Восстановление резервной копии БД, сжатой gz:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
gunzip mydb.gz psql -U postgres -d mydb -f mydb !!! Начиная с версии 9.2 можно восстановить только структуру таблиц с помощью опции --section # создаем БД CREATE DATABASE mydb2; # восстанавливаем pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup Обслуживание таблицы VACUUM ANALYZE table; REINDEX DATABASE dbName; REINDEX TABLE tabName; |
Перенос директории с данным (data directory):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
!!! Сначала узнать текущий путь # способ 1 $ su - postgres $ psql psql > SHOW data_directory; # способ 2 $ ps ax | grep 'postgres -D' Создадим новую директорию, назначим пользователя и инициализируем mkdir -p /pathto/postgresql/data chown -R postgres:postgres /pathto/postgresql su - postgres initdb -D /pathto/postgresql/data Теперь надо подправить файл с сервисом, который стартует postgresql # под arch linux sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service Environment=PGROOT=/pathto/postgresql/ PIDFile=/pathto/postgresql/data/postmaster.pid |
Очищение таблицы
|
1 2 3 4 |
!!! Очищение таблицы tablename и обнуление счетчика с ID !!! CASCADE нужен на случай если tablename связана с другой таблицей TRUNCATE TABLE tablename RESTART IDENTITY CASCADE; |
Удаление NULL у поля:
|
1 |
ALTER TABLE movies ALTER COLUMN year DROP NOT NULL; |
Утилита pgcli
|
1 2 3 4 5 6 7 |
pgcli - утилита командной строки с авто-дополнениям и подсветкой синтаксиса Установка pip install pgcli Запуск pgcli -U postgres -W dbname |
Установка пароля для пользователя postgres:
|
1 2 3 4 5 6 |
passwd postgres - задаем пароль su postgres - подключаемся пользователем postgres psql - входим клиент postgres для управления db Для смены пароля уже в самом клиенте набираем alter user postgres with password 'ПАРОЛЬ'; |