Как подключиться к базе данных PostgreSQL?
Для доступа к PostgreSQL используйте один из способов:
- через сервис NAT Gateway , находящийся в той же подсети (subnet), что и база данных. Необходимо назначить шлюзу внешний IP-адрес и открыть порт базы данных TCP:5432. После этого можно будет подключиться к базе данных по этому IP-адресу и порту.
- через ECS с помощью консольного клиента psql или графического клиента pgAdmin.
- через сервис Data Admin Service .
- Создание EIP .
- Прикрепление EIP к экземпляру БД .
- Подключение к экземпляру БД PostgreSQL .
Была ли статья полезной?
© Авторские права 2024, Cloud.ru
Собрано при помощи Sphinx с использованием темы, предоставленной Read the Docs.
Подключение к базе данных PostgreSQL
В этом разделе описывается, как подключиться к базе данных PostgreSQL.
На этой странице
- Базовое подключение
- Подключение с помощью SSL-сертификата
Для подключения к экземпляру БД можно использовать клиентский psql для базового подключения или SSL-соединения. Рекомендуется использовать SSL-соединение.
Для подключения необходимы:
- Привязанный к экземпляру БД внешний IP-адрес.
- Установленный клиент PostgreSQL psql на ECS.
Базовое подключение
- Подключитесь к экземпляру виртуальной машины ECS , где развернут экземпляр RDS.
- Запустите следующую команду для подключения к базе данных RDS:
psql --no-readline -U user> -h host> -p port> -d datastore> -W
- -U — имя пользователя экземпляра базы данных RDS. По умолчанию — root .
- -h — IP-адрес экземпляра первичной БД. Получить этот IP-адрес можно на странице Instance Management , нажав на название экземпляра БД.
- через ECS, то IP-адрес можно найти на вкладке Basic Information разделе Connection Information → Floating IP Address .
- через EIP, то адрес IP будет располагаться на вкладке EIPs .
- -p — использующийся порт для базы данных. По умолчанию значение порта — 5432. Номер порта можно получить на странице Instance Management , нажав на нужный экземпляр RDS и перейдя в раздел Connection Information → Database Port .
- -d — название БД. По умолчанию название будет «postgres».
- -W — запрос пароля, который нужно будет указать при подключении. После запуска этой команды будет запрашиваться пароль.
psql --no-readline -U root -h 192.168.0.44 -p 5432 -d postgres -W
Подключение с помощью SSL-сертификата
- Войдите в консоль управления Advanced:
- через личный кабинет Cloud.ru ;
- как IAM-пользователь .
- Чтобы открыть список сервисов, нажмите Service List .
- Выберите Database → Relational Database Service .
- На странице Basic Information в разделе DB Information скачайте SSL сертификат.
- Загрузите сертификат в ВМ ECS или сохраните его на устройстве, которое будет подключено к экземпляру базы данных.
Запустите команду для подключения к экземпляру. В нашем примере используется команда для Linux:
psql --no-readline -h host> -p port> "dbname= user= sslmode=verify-ca sslrootcert="
-h — IP-адрес экземпляра первичной БД. Получить этот IP-адрес можно на странице Instance Management , нажав на название экземпляра БД.
Если подключение осуществляется:
- через ECS, то IP-адрес можно найти на вкладке Basic Information разделе Connection Information → Floating IP Address .
- через EIP, то адрес IP будет располагаться на вкладке EIPs .
Например, чтобы подключиться к экземпляру базы данных через SSL-соединение от имени пользователя root, выполните следующую команду:
psql --no-readline -h 192.168.0.44 -p 5432 "dbname=postgres user=root sslmode=verify-ca sslrootcert=/root/ca.pem" Password:
Если успешно установлено SSL-соединение, то отобразится сообщение:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Работаем с 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 через консоль
Как открыть базу данных postgresql
Консольный клиент psql представляет еще один способ взаимодействия с сервером PostgreSQL. Данная программа также, как и pgAdmin, позволяет выполнять команды языка SQL.
Запустим psql. Программа предложит ввести название сервера, базы данных, порта и пользователя. Эти пункты можно прощелкать, так как для них будут использоваться значения по умолчанию (для сервера — localhost, для базы данных — postgres, для порта — 5432, в качестве пользователя — суперпользователь postres). Далее надо будет ввести пароль для пользователя (по умолчанию пользователя postgres):
И после удачного подключения можно будет отправлять серверу команды через psql.
Теперь создадим базу данных с помощью следующей команды языка SQL:
create database test2;
Для создания базы данных применяется команда create database , после которой указывается название бд. То есть в данном случае название бд — «test2». Причем команда завершается точкой с запятой.
Далее подключимся к этой базе данных для осуществления с ней взаимодействия. Для этого применяется команда \c (сокращение от connect), после которой указывается имя базы данных:
\c test2
Затем создадим в этой базе данных таблицу с помощью команды:
create table users (Id serial primary key, Name character varying(30), Age integer);
Данная команда создает таблицу users, в которой будет три столбца — Id, Name и Age.
После этого мы можем добавлять и получать данные из выше созданной таблицы. Вначале добавим в таблицу одну строку с помощью следующей команды:
insert into users (Name, Age) values ('Tom', 33);
И в конце получим добавленные данные:
select * from users;
Стоит отметить, что по умолчанию консоль в Windows поддерживает кодировку CP866, тогда как базы данных могут работать совсем с другой кодировкой, например, 1251. И даже сам клиент psql выводит нам соответствующие сообщения. Кроме того, при получении данных, при выводе информации о базах данных, таблицы и т.д. некоторая информация может отображаться некорректно. В этом случае перед запуском psql надо установить нужную кодировку и затем из консоли запустить программу psql.