PostgreSQL

Начало работы

Начало работы

Переключение и подключение ``Сценарий оболочки $ sudo -u postgres psql

Вывести список всех баз данных
```Основной скрипт
postgres=# \l

Подключение к базе данных с именем postgres

postgres=# \c postgres

Отключить

postgres=# \q
postgres=# \!

Команды psql

Получение справки

Запуск в консоли PostgreSQL

Работа с PostgreSQL

Recon

Показать версию

SHOW SERVER_VERSION;

Показать состояние системы

\conninfo

Показать переменные окружения

SHOW ALL;

Показать список пользователей

SELECT rolname FROM pg_roles;

Показать текущего пользователя

SELECT current_user;

Показать права доступа текущего пользователя

\du

Показать текущую базу данных

SELECT current_database();

Показать все таблицы в базе данных

\dt

Перечислить функции

\df <schema>

Базы данных

Список баз данных

\l

Подключиться к базе данных

\c <имя_базы_данных>

Показать текущую базу данных

SELECT current_database();

Создать базу данных

CREATE DATABASE <имя_базы_данных> WITH OWNER <имя_пользователя>;

Drop database

DROP DATABASE IF EXISTS <database_name>;

Переименовать базу данных

ALTER DATABASE <old_name> RENAME TO <new_name>;

Таблицы

Список таблиц в текущей базе данных

\dt

SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

Перечислить таблицы в глобальном порядке

\dt *.*.

SELECT * FROM pg_catalog.pg_tables

Перечислить схему таблицы

\d <имя_таблицы>
\d+ <имя_таблицы>

SELECT имя_колонки, тип_данных, максимальная_длина_символа
ИЗ INFORMATION_SCHEMA.COLUMNS
WHERE имя_таблицы = '<имя_таблицы>';

Создать таблицу

CREATE TABLE <table_name>(
  <column_name> <column_type>,
  <имя_столбца> <тип_столбца>
);

Создание таблицы с автоинкрементным первичным ключом

CREATE TABLE <table_name> (
  <имя_столбца> SERIAL PRIMARY KEY
);

Удалить таблицу

DROP TABLE IF EXISTS <table_name> CASCADE;

Разрешения

Станьте пользователем postgres, если у вас есть ошибки в правах ``hell sudo su - postgres psql

[Grant](http://www.postgresql.org/docs/current/static/sql-grant.html) все разрешения на базу данных
```sql {.wrap}
GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;

Предоставление прав на подключение к базе данных

GRANT CONNECT ON DATABASE <db_name> TO <user_name>;

Предоставление прав на схему

GRANT USAGE ON SCHEMA public TO <user_name>;

Предоставление прав на функции

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;

Предоставление прав на выборку, обновление, вставку, удаление для всех таблиц

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;

Предоставление прав доступа к таблице

GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;

Предоставление прав на выборку в таблице

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;

Колонки

Добавить колонку

ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];

Обновление столбца

ALTER TABLE <table_name> IF EXISTS
ALTER <имя_колонки> TYPE <data_type> [<constraints>];

Удаление столбца

ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;

Обновить столбец, сделав его автоинкрементным первичным ключом

ALTER TABLE <table_name>
ADD COLUMN <column_name> SERIAL PRIMARY KEY;

Вставка в таблицу с автоинкрементным первичным ключом

INSERT INTO <имя_таблицы>
VALUES (DEFAULT, <value1>);


INSERT INTO <table_name> (<column1_name>,<column2_name>)
VALUES ( <value1>,<value2> );

Данные

Select all data

SELECT * FROM <имя_таблицы>;

Чтение одного ряда данных

SELECT * FROM <имя_таблицы> LIMIT 1;

Поиск данных

SELECT * FROM <table_name> WHERE <column_name> = <value>;

Вставка данных

INSERT INTO <table_name> VALUES( <value_1>, <value_2> );

Update данные

UPDATE <table_name>
SET <column_1> = <value_1>, <column_2> = <value_2>
WHERE <column_1> = <value>;

Удалить все данные

DELETE FROM <имя_таблицы>;

Удалить конкретные данные

DELETE FROM <имя_таблицы>
WHERE <column_name> = <value>;

Пользователи

Список ролей

SELECT rolname FROM pg_roles;

Создать пользователя

CREATE USER <имя_пользователя> WITH PASSWORD '<пароль>';

Drop user

DROP USER IF EXISTS <user_name>;

Alter пароль пользователя

ALTER ROLE <имя_пользователя> WITH PASSWORD '<пароль>';

Схема

Список схем

\dn

SELECT schema_name FROM information_schema.schemata;

SELECT nspname FROM pg_catalog.pg_namespace;

Create schema

CREATE SCHEMA IF NOT EXISTS <schema_name>;

Drop schema

DROP SCHEMA IF EXISTS <schema_name> CASCADE;

Даты

Показать текущую дату ГГГГ-ММ-ДД

SELECT current_date;

Вычислить возраст между двумя датами

SELECT age(timestamp, timestamp);

Показать текущее время с учетом часового пояса

SELECT current_time;

Make даты с использованием целых чисел

SELECT make_date(2021,03,25);

Команды PostgreSQL

Таблицы

Буфер запросов

Информационный

S: показать системные объекты, +: дополнительная детализация

Соединение

Форматирование

Ввод/вывод

Переменные

Misc

Большие объекты

• \lo_export LOBOID FILE
• \lo_import FILE [COMMENT]
• \lo_list
• \lo_unlink LOBOID

Разное

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

Используйте pg_dumpall для резервного копирования всех баз данных ‘‘‘shell-скрипт $ pg_dumpall -U postgres > all.sql

Использование pg_dump для резервного копирования базы данных
командный сценарий
$ pg_dump -d mydb -f mydb_backup.sql

•   -a   Выгружать только данные, но не схему
•   -s   Дамп только схемы, без данных
•   -c   Сбросить базу данных перед воссозданием
•   -C   Создать базу данных перед восстановлением
•   -t   Дамп только именованной таблицы (таблиц)
•   -F   Формат (c: custom, d: directory, t: tar) {.marker-none}

Для получения полного списка опций используйте команду pg_dump -?.

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

Восстановление базы данных с помощью psql командный сценарий $ psql -U user mydb < mydb_backup.sql

Восстановление базы данных с помощью pg_restore
``Основной сценарий
$ pg_restore -d mydb mydb_backup.sql -c

•   -U   Укажите пользователя базы данных
•   -c   Сбросить базу данных перед воссозданием
•   -C   Создать базу данных перед восстановлением
•   -e   Выход при возникновении ошибки
•   -F   Format (c: custom, d: directory, t: tar, p: plain text sql(default)) {.marker-none}

Используйте pg_restore -? для получения полного списка опций

Удаленный доступ

Получение местоположения файла postgresql.conf ‘‘‘shell-скрипт $ psql -U postgres -c ‘SHOW config_file’

Добавить в postgresql.conf
```Шелл-скрипт
listen_addresses = '*'

Добавьте в pg_hba.conf (в том же месте, что и postgresql.conf)

host all all 0.0.0.0/0 md5
host all all ::/0 md5

Перезапустите сервер PostgreSQL

$ sudo systemctl restart postgresql

Импорт/экспорт CSV

Экспорт таблицы в CSV-файл

\copy table TO '<path>' CSV
\copy table(col1,col1) TO '<path>' CSV
\copy (SELECT...) TO '<path>' CSV

Импорт CSV-файла в таблицу ``Основной сценарий \copy table FROM ‘’ CSV \copy table(col1,col1) FROM ‘’ CSV

См. также: [Copy](https://www.postgresql.org/docs/current/sql-copy.html)

Также см.
--------
- [Posgres-cheatsheet](https://gist.github.com/apolloclark/ea5466d5929e63043dcf#posgres-cheatsheet) _(gist.github.com)_