Linux postgres командная строка

Работаем с 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:

Для подключения к базе данных PostgreSQL можно использовать команду:

Если такая команда не просит ввести пароль пользователя, то можно еще добавить опцию -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.

sudo vim /etc/postgresql/12/main/pg_hba.conf

После этого сделать restart PostgreSQL:

sudo service postgresql restart

Полезные команды PostgreSQL

Выйти из клиента PostgreSQL:

Показать список баз данных PostgreSQL:

Показать список пользователей (ролей):

Показать структуру таблицы:

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

ALTER DATABASE db RENAME TO newdb;

Изменить текущую базу данных в PostgreSQL (вы не сможете переименовать или удалить текущую базу данных):

\connect db_name или более короткий alias: \c db_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;

Источник

📑 Шпаргалка по основным командам PostgreSQL

Вся работа с PostgreSQL осуществляется под пользователем 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 — выходной формат дампа, custom, tar, или plain text.
• -t, —table=TABLE — указываем определенную таблицу для дампа.
• -v, —verbose — вывод подробной информации.
• -D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду pg_dumpall.

Восстановление таблиц из резервных копий (бэкапов):

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 создает отдельного пользователя Linux с именем postgres . В моих примерах я буду запускать команды от имени этого пользователя. Хотя вы можете авторизоваться под ним и запускать команды непосредственно из сессии пользователя postgres.

Вывести список баз с расширенной информацией:

# sudo -u postgres psql -U postgres -l+

Создать текстовый дамп базы данных:

# sudo -u postgres pg_dump -U postgres dbtemp01 \
> ~/ dbtemp01.sql

Сжать дамп базы данных PostgreSQL на лету:

# sudo -u postgres pg_dump -U postgres dbtemp01 \
| pigz > ~/dbtemp01.sql.gz

Восстановить базу данных из дампа в новую БД:

# sudo -u postgres createdb -U postgres \
-T template0 dbtemp02

# sudo -u postgres psql -U postgres dbtemp02 \
< ~/ dbtemp01.sql

Создать нового пользователя PostgreSQL:

# sudo -u postgres createuser -U postgres usrsite1

Задать пароль пользователя

# sudo -u postgres psql -U postgres -c \
«ALTER USER usrsite1 PASSWORD ‘SecRet2pwd'»

Вывести список пользователей:

# sudo -u postgres psql -U postgres -c \
«select * from pg_user»

Дать полные права на базу:

# sudo -u postgres psql -U postgres -c \
«GRANT ALL PRIVILEGES ON DATABASE sitedb to usrsite1»

Назначить пользователя владельцем базы:

# sudo -u postgres psql -U postgres -c \
«ALTER DATABASE sitedb OWNER TO usrsite1»

Выполнить очистку ( -f ) и анализ ( -z ) базы данных (Postgres Pro):

# sudo -u postgres vacuumdb -U postgres -f -z -d sitedb

# sudo -u postgres reindexdb -U postgres -d sitedb

# sudo -u postgres psql -U postgres -c \
«DROP DATABASE sitedb»

Вывести права пользователя:

select * from INFORMATION_SCHEMA.table_privileges WHERE grantee = »;

Права на группы, членом которых является пользователь USER_NAME:

select * from INFORMATION_SCHEMA.role_table_grants WHERE grantee = »;

select relacl from pg_catalog.pg_class where relname=»;

Еще несколько популярных команд для интерактивного режима psql:

\connect db_name – подключиться к базе данных

\du – вывести список пользователей

\dp — вывести список таблиц, представлений, последовательностей, прав доступа к ним

Источник