Установка 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 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
Все команды запускаются под пользователем postgres (postgresql-суперпользователь $ su - postgres) sudo su - postgres - входим пользователем под postgres psql - Можно передавать аргументы или использовать консоль. Передача аргументов psql: psql --help - помощь 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-файл Режим консоли psql: psql - входим в консоль \? - помощь по сокращённым командам \help - помощь по командам sql \h - помощь по SQL-командам \h create database - просмотр синтаксиса создания базы данных \conninfo - проверка соединения \c dbname - подсоединение к БД dbname \l - список баз данных \l+ - просмотр существующих баз данных c более детальным выводом(размер, описание баз данных) \d table - структура таблицы table \du - список всех пользователей и их привилегий \dt - список всех таблиц \dt+ - список всех таблиц с описанием \dt *s* - список всех таблиц, содержащих s в имени \df+ - просмотр списка доступных функций \i FILE - выполнить команды из файла FILE \o FILE - сохранить результат запроса в файл FILE \a - переключение между режимами вывода: с/без выравнивания \timing - включить отображение времени выполнения запроса |
Посмотреть размер таблицы:
|
1 2 3 4 5 6 7 8 9 |
sudo su - postgres - входим пользователем под postgres psql - входим в psql psql -l - список баз данных psql -d dbname - подключение к БД dbname SELECT pg_size_pretty (pg_indexes_size('name_table')); - смотрим размер таблицы name_table |
Просмотр списка и путей к конфигурационным файлам:
|
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; |
Как получить последние 10 записей:
|
1 2 3 4 5 6 7 |
Если id индексируется, это будет очень эффективно. Естественно, это также может быть timestamp. select * from NAMETABLE order by id desc limit 10; SELECT * FROM your_table ORDER BY your_timestamp DESC LIMIT 10; SELECT * FROM "User" LIMIT 10; SELECT * FROM "Permission" LIMIT 10; |
Создание индексов:
|
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 14 15 |
В PostgreSQL есть две утилиты для бекапа pg_dump и pg_dumpall. pg_dump - используется для бекапа одной базы pg_dump mydatabase > mydatabase.sql - бекап базы данных с именем mydatabase 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 |
Создание пользователя myuser с паролем ‘123’:
|
1 |
CREATE USER myuser WITH PASSWORD '123'; |
Создание базы данных mytestdb с владельцем myuser:
|
1 |
CREATE DATABASE mytestdb OWNER = myuser; |
Предоставление пользователю myuser всех прав доступа к базе mytestdb:
|
1 |
GRANT ALL PRIVILEGES ON database mytestdb TO myuser; |
Удаление базы данных mytestdb:
|
1 |
DROP DATABASE mytestdb; |
Удаление пользователя myuser:
|
1 |
DROP USER myuser; |
Установка пароля для пользователя postgres:
|
1 2 3 4 5 6 |
passwd postgres - задаем пароль su postgres - подключаемся пользователем postgres psql - входим клиент postgres для управления db Для смены пароля уже в самом клиенте набираем alter user postgres with password 'ПАРОЛЬ'; |
Просмотр активных/текущих процессов:
|
1 |
SELECT * from pg_stat_activity; |
Обнуление/усечение таблицы:
|
1 |
truncate tablename; |
Просмотр размера базы данных:
|
1 |
SELECT pg_size_pretty( pg_database_size( 'databasename' ) ); |
Просмотр размера таблицы:
|
1 |
SELECT pg_size_pretty( pg_total_relation_size( 'tablename' ) ); |
Просмотр размера таблицы без индексов:
|
1 |
SELECT pg_size_pretty( pg_relation_size( 'tablename' ) ); |
Просмотр запущенной версии PostgreSQL:
|
1 |
SELECT version(); |
Репликация \ replication
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Входим: su - postgres psql Состояние репликации: master SELECT * FROM pg_stat_replication; slave select * from pg_stat_wal_receiver; Слоты репликации: master -- создание слота для репликации SELECT pg_create_physical_replication_slot('<SLOT_NAME>'); -- удаление слота для репликации SELECT pg_drop_replication_slot('<SLOT_NAME>'); -- слоты репликации SELECT * FROM pg_replication_slots; |