PostgreSQL \ psql \ pg_dump \ pg_restore \ install

Установка postgres debian:

apt update
apt install postgresql postgresql-contrib

apt show postgresql


https://postgrespro.ru/docs/postgresql/9.6/app-pgrestore

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 - включить отображение времени выполнения запроса

Все команды запускаются под пользователем 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

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;

psql > SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

# или список всех конфигурационных параметров

psql > show all;

Список активных соединений с информацией о:

pid процесса, выполняющегося запроса, пользователя, базы данных

psql > SELECT * FROM pg_stat_activity;

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;

Если id индексируется, это будет очень эффективно. Естественно, это также может быть timestamp.

select * from NAMETABLE order by id desc limit 10;

SELECT * FROM your_table ORDER BY your_timestamp DESC LIMIT 10;

Создание индексов:

# primary key
psql > ALTER TABLE tableName ADD PRIMARY KEY (id);

# unique index
psql > CREATE UNIQUE INDEX indexName ON tableName (columnNames);

# 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 # разрешения подключения для всех
-----------

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 с списком имен свойств.

-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

В 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

1 2	pg_dumpall - для бекапа всех баз и сервера в целом (необходимо запускать под postgresql-суперпользователем) pg_dumpall > all.sql

Проверка бекапа:

grep "^[\]connect" all.sql
\connect db1
\connect db2

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

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;

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

!!! Сначала узнать текущий путь

# способ 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;

!!! Очищение таблицы tablename и обнуление счетчика с ID

!!! CASCADE нужен на случай если tablename связана с другой таблицей

TRUNCATE TABLE tablename RESTART IDENTITY CASCADE;

Удаление NULL у поля:

ALTER TABLE movies ALTER COLUMN year DROP NOT NULL;

1	ALTER TABLE movies ALTER COLUMN year DROP NOT NULL;

Утилита pgcli

pgcli - утилита командной строки с авто-дополнениям и подсветкой синтаксиса

Установка
pip install pgcli

Запуск
pgcli -U postgres -W dbname

pgcli - утилита командной строки с авто-дополнениям и подсветкой синтаксиса

Установка

pip install pgcli

Запуск

pgcli -U postgres -W dbname

Создание пользователя myuser с паролем ‘123’:

CREATE USER myuser WITH PASSWORD '123';

1	CREATE USER myuser WITH PASSWORD '123';

Создание базы данных mytestdb с владельцем myuser:

CREATE DATABASE mytestdb OWNER = myuser;

1	CREATE DATABASE mytestdb OWNER = myuser;

Предоставление пользователю myuser всех прав доступа к базе mytestdb:

GRANT ALL PRIVILEGES ON database mytestdb TO myuser;

1	GRANT ALL PRIVILEGES ON database mytestdb TO myuser;

Удаление базы данных mytestdb:

DROP DATABASE mytestdb;

1	DROP DATABASE mytestdb;

Удаление пользователя myuser:

DROP USER myuser;

1	DROP USER myuser;

Установка пароля для пользователя postgres:

passwd postgres - задаем пароль
su postgres - подключаемся пользователем postgres
psql - входим клиент postgres для управления db

Для смены пароля уже в самом клиенте набираем
alter user postgres with password 'ПАРОЛЬ';

passwd postgres - задаем пароль

su postgres - подключаемся пользователем postgres

psql - входим клиент postgres для управления db

Для смены пароля уже в самом клиенте набираем

alter user postgres with password 'ПАРОЛЬ';

Просмотр активных/текущих процессов:

SELECT * from pg_stat_activity;

1	SELECT * from pg_stat_activity;

Обнуление/усечение таблицы:

truncate tablename;

1	truncate tablename;

Просмотр размера базы данных:

SELECT pg_size_pretty( pg_database_size( 'databasename' ) );

1	SELECT pg_size_pretty( pg_database_size( 'databasename' ) );

Просмотр размера таблицы:

SELECT pg_size_pretty( pg_total_relation_size( 'tablename' ) );

1	SELECT pg_size_pretty( pg_total_relation_size( 'tablename' ) );

Просмотр размера таблицы без индексов:

SELECT pg_size_pretty( pg_relation_size( 'tablename' ) );

1	SELECT pg_size_pretty( pg_relation_size( 'tablename' ) );

Просмотр запущенной версии PostgreSQL:

SELECT version();

1	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('<SLOT_NAME>');
-- удаление слота для репликации
SELECT pg_drop_replication_slot('<SLOT_NAME>');
-- слоты репликации
SELECT * FROM pg_replication_slots;

Входим:

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;