Работаем с PostgreSQL через командную строку в Linux

Работаем с PostgreSQL через командную строку в Linux

Установка PostgreSQL на Linux (Mint)

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

sudo apt install postgresql-client-<VERSION>

Например:

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<DB_NAME>

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

Посмотреть список всех схем:

\dn

Подключиться к конкретной схеме:

SET search_path TO schema_name

Sequences

Получить имена всех созданных sequences:

select relname from pg_class where relkind='S';

Получить последнее значение sequence, которые будет присвоено новой вставляемой в таблицу записи:

SELECT last_value FROM order_id_seq;

https://jeka.by/post/1104/postgresql-connect-via-terminal/