Работаем с PostgreSQL через командную строку в Linux
Для подключения к базе данных PostgreSQL понадобится установленный PostgreSQL клиент:
sudo apt install postgresql-client-
sudo apt install postgresql-client-12
Для установки PostgreSQL сервера:
sudo apt install postgresql
Проверим, можем ли мы подключиться к базе данных PostgreSQL:
sudo -u postgres psql -c "SELECT version();"
Вывод команды должен быть примерно таким:
$ sudo -u postgres psql -c "SELECT version();" version ---------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit (1 row)
PostgreSQL Подключение, Пользователи (Роли) и Базы Данных
Логин в только что установленный postgreSQL сервер нужно производить под именем пользователя postgres:
sudo -i -u postgres psql
Выйти из клиента PostgreSQL:
\q
Для подключения к базе данных PostgreSQL можно использовать команду:
psql -U -h -d
Если такая команда не просит ввести пароль пользователя, то можно еще добавить опцию -W.
$ psql -Usrv161924_dom -hpgsql-161924.srv.hoster.ru -dsrv161924_dom -W Password for user srv161924_dom:
После ввода пароля и успешного подключения к базе данных PostgreSQL, можно посылать SQL-запросы и psql-команды.
PostgreSQL создание новой роли и базы данных
Создать новую роль c именем admin (указывайте нужное имя):
sudo su - postgres -c "createuser admin"
Создание новой базы данных:
sudo su - postgres -c "createdb testDb"
Дать права роли на базу данных:
grant all privileges on database testDb to admin;
Включить удаленный PostgreSQL доступ для пользователей
Нам нужно отредактировать файл /etc/postgresql//main/pg_hba.conf, задав опцию md5 вместо peer.
может быть 10, 11, 12 и т.д.
sudo vim /etc/postgresql/12/main/pg_hba.conf
После этого сделать restart PostgreSQL:
sudo service postgresql restart
Полезные команды PostgreSQL
Выйти из клиента PostgreSQL:
\q
Показать список баз данных PostgreSQL:
\l
Показать список таблиц:
\dt
Показать список пользователей (ролей):
\du
Показать структуру таблицы:
\d table_name
Переименовать базу данных:
ALTER DATABASE db RENAME TO newdb;
Удалить базу данных:
drop database db_name;
Изменить текущую базу данных в PostgreSQL (вы не сможете переименовать или удалить текущую базу данных):
\connect db_name или более короткий alias: \c db_name
Удалить роль (пользователя):
DROP ROLE user_name;
Роль не будет удалена, если у нее есть привелегии — возникнет ошибка ERROR: role cannot be dropped because some objects depend on it .
Нужно удалить привелегии у роли, например если нужно удалить роль admin2, нужно выполнить последовательность комманд с Drop Owned:
db=# REASSIGN OWNED BY admin2 TO postgres; REASSIGN OWNED db=# DROP OWNED BY admin2; DROP OWNED db=# drop role admin2; DROP ROLE
Дать права пользователю/роли на логин ( role is not permitted to log in ):
ALTER ROLE admin2 WITH login;
Выбор shema psql в консоли:
Посмотреть список всех схем:
Подключиться к конкретной схеме:
SET search_path TO schema_name
Sequences
Получить имена всех созданных sequences:
select relname from pg_class where relkind='S';
Получить последнее значение sequence, которые будет присвоено новой вставляемой в таблицу записи:
SELECT last_value FROM order_id_seq;
- Работа с MySQL через консоль
Как выйти из psql
Создав базу данных, вы можете обратиться к ней:
Запустив терминальную программу Postgres Pro под названием psql , в которой можно интерактивно вводить, редактировать и выполнять команды SQL .
Используя существующие графические инструменты, например, pgAdmin или офисный пакет с поддержкой ODBC или JDBC , позволяющий создавать и управлять базой данных. Эти возможности здесь не рассматриваются.
Чтобы работать с примерами этого введения, начните с psql . Подключиться с его помощью к базе данных mydb можно, введя команду:
$psql mydb
Если имя базы данных не указать, она будет выбрана по имени пользователя. Об этом уже рассказывалось в предыдущем разделе, посвящённом команде createdb .
В psql вы увидите следующее сообщение:
psql (9.6.24.1) Type "help" for help. mydb=>
Последняя строка может выглядеть и так:
mydb=#
Что показывает, что вы являетесь суперпользователем, и так скорее всего будет, если вы устанавливали экземпляр Postgres Pro сами. В этом случае на вас не будут распространяться никакие ограничения доступа, но для целей данного введения это не важно.
Если вы столкнулись с проблемами при запуске psql , вернитесь к предыдущему разделу. Команды createdb и psql подключаются к серверу одинаково, так что если первая работает, должна работать и вторая.
Последняя строка в выводе psql — это приглашение, которое показывает, что psql ждёт ваших команд и вы можете вводить SQL -запросы в рабочей среде psql . Попробуйте эти команды:
mydb=>SELECT pgpro_version();version ------------------------------------------------------------------------------------------ PostgresPro 9.6.24.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 row)mydb=>SELECT current_date;date ------------ 2016-01-07 (1 row)mydb=>SELECT 2 + 2;?column? ---------- 4 (1 row)
В программе psql есть множество внутренних команд, которые не являются SQL-операторами. Они начинаются с обратной косой черты, « \ » . Например, вы можете получить справку по различным SQL -командам Postgres Pro , введя:
mydb=>\h
Чтобы выйти из psql , введите:
mydb=>\q
и psql завершит свою работу, а вы вернётесь в командную оболочку операционной системы. (Чтобы узнать о внутренних командах, введите \? в приглашении командной строки psql .) Все возможности psql документированы в справке psql . В этом руководстве мы не будем использовать эти возможности явно, но вы можете изучить их и применять при удобном случае.
| Пред. | Начало | След. |
| 1.3. Создание базы данных | Наверх | Глава 2. Язык SQL |
psql — консольная утилита для работы с PostgreSQL¶
Вывод peзультатов запроса не в строку, а столбцом¶
Ключ \x
Покажу на примере, как это выглядит (это очень хорошо работает на запросах со множеством столбцов и «узким» экраном)
denis=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | waiting | current_query --------+---------+---------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+------------------------------+------------------------------+---------+--------------------------------- 629830 | denis | 13205 | 629829 | denis | psql | | | -1 | 2012-11-10 11:57:05.634017+06 | 2012-11-10 11:59:11.27402+06 | 2012-11-10 11:59:11.27402+06 | f | select * from pg_stat_activity; (1 row) -- Включаю альтернативный режим отображения результатов запроса denis=# \x Expanded display is on. denis=# select * from pg_stat_activity; -[ RECORD 1 ]----+-------------------------------- datid | 629830 datname | denis procpid | 20187 usesysid | 629829 usename | denis application_name | psql client_addr | client_hostname | client_port | -1 backend_start | 2012-11-07 23:29:00.264029+06 xact_start | 2012-11-07 23:29:47.653051+06 query_start | 2012-11-07 23:29:47.653051+06 waiting | f current_query | select * from pg_stat_activity;
Выход в шел без прерывания сессии работы с базой данных¶
Ключ \!
denis=# \! denis@server:~$ ls -l total 126172 drwxr-xr-x 2 denis denis 4096 Oct 21 10:28 Desktop drwxr-xr-x 3 denis denis 4096 Nov 5 10:03 Documents drwxr-xr-x 14 denis denis 12288 Nov 7 16:45 Downloads drwx------ 11 denis denis 4096 Nov 7 08:19 Dropbox drwxr-xr-x 19 denis denis 4096 Sep 30 23:03 Music drwxr-xr-x 3 denis denis 4096 Oct 9 12:44 Pictures drwxrwxr-x 11 denis denis 4096 Nov 3 17:27 Projects drwxr-xr-x 2 denis denis 4096 Sep 26 20:03 Public drwxrwxr-x 2 denis denis 4096 Sep 27 10:17 Snapshots drwxr-xr-x 2 denis denis 4096 Sep 26 20:03 Templates denis@server:~$ exit exit denis=# \q denis@server: ~$
Редактирование запросов во внешнем редакторе¶
Многострочные запросы очень неудобно редактировать в стандартной строке psql. Но есть возможность редактировать запросы во внешнем редакторе.
Ключ \e
При вводе ключа \e запускается внешний редактор, в котором уже содержится последний запрос, который был введен в psql. Запрос можно отредактировать или ввести новый, сохранить файл и выйти из редактора. Если в конце запроса стоит точка с запятой, то запрос будет выполнен сразу после закрытия редактора. В противном случае, чтобы запрос выполнился, надо будет ввести точку с запятой уже в psql и нажать ENTER.
За то, какой редактор вызывается, отвечает переменная окружения PSQL_EDITOR, которую можно настроить на vim:
export PSQL_EDITOR="vim"
Прокрутка результата выполнения запроса вперед/назад¶
Довольно часто в дистрибутивах linux в качестве программы для постраничного вывода результатов выполнения запросов используется программа more, которую не всегда удобно использовать для интерактивной работы с результатом запроса (производить поиск, прокручивать результат запрос вверх/вниз, выводить результат запроса с использованием переноса строк).
Выход есть! Можно заменить more на less, которая позволяет интерактивно работать с результатом запроса. Для этого необходимо установить следующие переменные окружения:
export PAGER="less" export LESS="-iMSx4 -FX"
Помимо всего вышеперечисленного опция S отключает перенос длинных строк, что улучшает читабельность результата запроса.
Есть неприятная особенность работы с less, если приходится прокручивать результат запроса вправо/влево — в этом случае, генерируется много пустых экранов, которые занимают много места вверху терминала (опция X). Однако часто эта небольшая неприятность перекрывается остальными преимуществами less
Включить/отключить постраничный вывод результата запроса можно опцией
\pset pager [always|off]
Время выполнения запроса¶
Вывод времени выполнения запрос выключается опцией \timing
denis=# \timing Timing is on. denis=# select 1; ?column? ---------- 1 (1 row) Time: 0.280 ms
Редактирование хранимой процедуры¶
Иногда нужно быстро отредактировать текст хранимой процедуры, но нет возможности воспользоваться привычной программой для этого. psql же практически всегда доступен и отредактировать процедуру можно им.
Для этого служит опция \ef
При использовании этой опции запускается редактор, указанный в переменной окружения PSQL_EDITOR
Сохранения результатов запросов в файл¶
Ключ \copy позволяет импортировать целые таблицы или результаты запросов в файлы и экспортировать данных из файлов в таблицы. Это бывает полезно, если надо переместить данные какой-то таблицы между базами данных.
Есть таблица customers (id int4, first_name varchar(64), last_name varchar(64)). Её нужно перенести в другую базу данных в таблицу temp_customers
Сохраняю содержимое таблицы в файл customers.dump:
db1# \copy customers to 'customers.dump'
Загружаю содержимое файла в другую таблицу:
db2# \copy temp_customers from 'customers.dump'
Важно, чтобы таблицы, которые участвуют в переносе, имели одинаковую структуру
Поиск по прошлым запросам¶
Поиск по запросам под OS linux работает аналогично поиску в BASH’е. Находясь в консоле, надо нажать CTRL+R
Появляется приглашение ввести буквы, по которым будет производиться поиск
denis=# (reverse-i-search)`':
По мере ввода букв уточняется запрос, который вы ищите.
denis=# (reverse-i-search)`dr': drop sequence eobjects_objects_15_id_seq;
Дополнительные горячие клавиши для управления режимом поиска
- ENTER — выполнение найденного запрос
- ESC — переход в режим редактирования найденного запроса
- CTRL+R — циклический перебор sql-запросов, которые соответствуют введенной строке поиска
- CTRL+G — закрытие режима поиска без выполнения запроса
�� Шпаргалка по основным командам PostgreSQL
Вся работа с PostgreSQL осуществляется под пользователем postgres.
$sudo su postgres
Работать с PosgreSQL можно как в интерактивном режиме, так и из командной строки. Программа — psql.
Основные команды PostgreSQL в интерактивном режиме:
- \connect db_name – подключиться к базе с именем db_name
- \du – список пользователей
- \dp (или \z) – список таблиц, представлений, последовательностей, прав доступа к ним
- \di – индексы
- \ds – последовательности
- \dt – список таблиц
- \dt+ — список всех таблиц с описанием
- \dt *s* — список всех таблиц, содержащих s в имени
- \dv – представления
- \dS – системные таблицы
- \d+ – описание таблицы
- \o – пересылка результатов запроса в файл
- \l – список баз данных
- \i – читать входящие данные из файла
- \e – открывает текущее содержимое буфера запроса в редакторе (если иное не указано в окружении переменной EDITOR, то будет использоваться по умолчанию vi)
- \d “table_name” – описание таблицы
- \i запуск команды из внешнего файла, например \i /my/directory/my.sql
- \pset – команда настройки параметров форматирования
- \echo – выводит сообщение
- \set – устанавливает значение переменной среды. Без параметров выводит список текущих переменных (\unset – удаляет).
- \? – справочник psql
- \help – справочник SQL
- \q (или Ctrl+D) – выход с программы
Работа с PostgreSQL из командной строки:
- -c (или –command) – запуск команды SQL без выхода в интерактивный режим
- -f file.sql — выполнение команд из файла file.sql
- -l (или –list) – выводит список доступных баз данных
- -U (или –username) – указываем имя пользователя (например postgres)
- -W (или –password) – приглашение на ввод пароля
- -d dbname — подключение к БД dbname
- -h – имя хоста (сервера)
- -s – пошаговый режим, то есть, нужно будет подтверждать все команды
- –S – однострочный режим, то есть, переход на новую строку будет выполнять запрос (избавляет от ; в конце конструкции SQL)
- -V – версия PostgreSQL без входа в интерактивный режим
Примеры:
psql -U postgres -d dbname -c «CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);» — выполнение команды в базе dbname.
psql -d dbname -H -c «SELECT * FROM my» -o my.html — вывод результата запроса в html-файл.
Утилиты (программы) PosgreSQL:
- createdb и dropdb – создание и удаление базы данных (соответственно)
- createuser и dropuser – создание и пользователя (соответственно)
- pg_ctl – программа предназначенная для решения общих задач управления (запуск, останов, настройка параметров и т.д.)
- postmaster – многопользовательский серверный модуль PostgreSQL (настройка уровней отладки, портов, каталогов данных)
- initdb – создание новых кластеров PostgreSQL
- initlocation – программа для создания каталогов для вторичного хранения баз данных
- vacuumdb – физическое и аналитическое сопровождение БД
- pg_dump – архивация и восстановление данных
- pg_dumpall – резервное копирование всего кластера PostgreSQL
- pg_restore – восстановление БД из архивов (.tar, .tar.gz)
Примеры создания резервных копий:
Создание бекапа базы mydb, в сжатом виде
pg_dump -h localhost -p 5440 -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, в сжатом виде, с таблицами которые содержат в имени payments
pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb
Дамп данных только одной, конкретной таблицы. Если нужно создать резервную копию нескольких таблиц, то имена этих таблиц перечисляются с помощью ключа -t для каждой таблицы.
pg_dump -a -t table_name -f file_name database_name
Создание резервной копии с сжатием в gz
pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz
Список наиболее часто используемых опций:
- -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
- -t, —table=TABLE — указываем определенную таблицу для дампа.
- -v, —verbose — вывод подробной информации.
- -D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств.
Бекап всех баз данных используя команду pg_dumpall.
pg_dumpall > all.sql
Восстановление таблиц из резервных копий (бэкапов):
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
�� Похожие статьи на сайте
- Резервное копирование и восстановление баз данных PostgreSQL
- Создание кластера баз данных PostgreSQL
- Настройка аутентификации клиентов в PostgreSQL — файл pg_hba.conf
- Системные колонки таблиц PostgreSQL
- Оптимизация PostgreSQL. Autovacuum — сборка мусора
- Оптимизация PostgreSQL. Журнал транзакций и контрольные точки
- Оптимизация PostgreSQL. Настройка ресурсов
- Шпаргалка по основным командам PostgreSQL
- Установка 1C 8.3 под PostgreSQL 9.3 на Ubuntu Server 14.04 X64