Установка postgres debian:
apt update apt install postgresql postgresql-contrib apt show postgresql https://postgrespro.ru/docs/postgresql/9.6/app-pgrestore
psql:
Все команды запускаются под пользователем 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 - включить отображение времени выполнения запроса
Посмотреть размер таблицы:
sudo su - postgres - входим пользователем под postgres
psql - входим в psql
psql -l - список баз данных
psql -d dbname - подключение к БД dbname
SELECT pg_size_pretty (pg_indexes_size('name_table')); - смотрим размер таблицы name_table
Просмотр списка и путей к конфигурационным файлам:
psql > SELECT name, setting FROM pg_settings WHERE category = 'File Locations'; # или список всех конфигурационных параметров psql > show all;
Список активных соединений с информацией о:
pid процесса, выполняющегося запроса, пользователя, базы данных psql > SELECT * FROM pg_stat_activity;
Как получить последние 10 записей:
Если 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;
Создание индексов:
# primary key psql > ALTER TABLE tableName ADD PRIMARY KEY (id); # unique index psql > CREATE UNIQUE INDEX indexName ON tableName (columnNames);
Настройка подключения к базе:
postgresql.conf --------------- #listen_addresses = 'localhost' listen_addresses = '*' # разрешить подключение для всех --------------- !!! после изменений перезапустить postgres pg_hba.conf ----------- host all all 0.0.0.0/0 password # разрешения подключения для всех -----------
pg_dump — список наиболее часто используемых опций:
-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 с списком имен свойств.
Бекап \ архивирования таблиц:
В 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:
pg_dumpall - для бекапа всех баз и сервера в целом (необходимо запускать под postgresql-суперпользователем) pg_dumpall > all.sql
Проверка бекапа:
grep "^[\]connect" all.sql \connect db1 \connect db2
В PostgreSQL есть две утилиты для восстановления базы из бекапа:
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:
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 $ 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
Очищение таблицы
!!! Очищение таблицы tablename и обнуление счетчика с ID !!! CASCADE нужен на случай если tablename связана с другой таблицей TRUNCATE TABLE tablename RESTART IDENTITY CASCADE;
Удаление NULL у поля:
ALTER TABLE movies ALTER COLUMN year DROP NOT NULL;
Утилита pgcli
pgcli - утилита командной строки с авто-дополнениям и подсветкой синтаксиса Установка pip install pgcli Запуск pgcli -U postgres -W dbname
Создание пользователя myuser с паролем ‘123’:
CREATE USER myuser WITH PASSWORD '123';
Создание базы данных mytestdb с владельцем myuser:
CREATE DATABASE mytestdb OWNER = myuser;
Предоставление пользователю myuser всех прав доступа к базе mytestdb:
GRANT ALL PRIVILEGES ON database mytestdb TO myuser;
Удаление базы данных mytestdb:
DROP DATABASE mytestdb;
Удаление пользователя myuser:
DROP USER myuser;
Установка пароля для пользователя postgres:
passwd postgres - задаем пароль su postgres - подключаемся пользователем postgres psql - входим клиент postgres для управления db Для смены пароля уже в самом клиенте набираем alter user postgres with password 'ПАРОЛЬ';
Просмотр активных/текущих процессов:
SELECT * from pg_stat_activity;
Обнуление/усечение таблицы:
truncate tablename;
Просмотр размера базы данных:
SELECT pg_size_pretty( pg_database_size( 'databasename' ) );
Просмотр размера таблицы:
SELECT pg_size_pretty( pg_total_relation_size( 'tablename' ) );
Просмотр размера таблицы без индексов:
SELECT pg_size_pretty( pg_relation_size( 'tablename' ) );
Просмотр запущенной версии PostgreSQL:
SELECT version();
Репликация \ replication
Входим:
su - postgres
psql
Состояние репликации:
master
SELECT * FROM pg_stat_replication;
slave
select * from pg_stat_wal_receiver;
Слоты репликации:
master
-- создание слота для репликации
SELECT pg_create_physical_replication_slot('');
-- удаление слота для репликации
SELECT pg_drop_replication_slot('');
-- слоты репликации
SELECT * FROM pg_replication_slots;