Команды psql: PostgreSQL: зачем и как | статьи о программировании mkdev

Создание базы данных PostgreSQL с управляемым удостоверением

Twitter LinkedIn Facebook Адрес электронной почты

• Статья
• 10/24/2022
• Чтение занимает 13 мин

Эта статья является частью руководства по развертыванию приложения Python для Служба приложений Azure. Веб-приложение использует управляемое удостоверение для проверки подлинности в других ресурсах Azure. В этой статье вы создадите службу База данных Azure для PostgreSQL.

1. Создание сервера Azure PostgreSQL

Сервер База данных Azure для PostgreSQL можно создать с помощью портал Azure, Visual Studio Code или Azure CLI.

Примечание

Управляемое удостоверение в настоящее время поддерживается только на отдельном сервере PostgreSQL.

• Портал Azure
• Код VS
• Azure CLI

Войдите на портал Azure и выполните следующие действия, чтобы создать ресурс базы данных Azure для PostgreSQL.

Инструкции	Снимок экрана
На портале: Введите postgres в строку поиска в верхней части портала Azure. Выберите элемент, помеченный База данных Azure для PostgreSQL серверами под заголовком «Службы» в меню, которое отображается под строкой поиска.
На странице База данных Azure для PostgreSQL серверов нажмите кнопку «+ Создать»
На следующей странице: Выберите «Создать» в разделе «Отдельный сервер«. В последующих диалоговых окнах выберите параметры, чтобы продолжить создание отдельного сервера, необходимого в этом руководстве.
На странице с одним сервером заполните форму следующим образом: Группа ресурсов → Выбрать и использовать имя msdocs-web-app-rg . Имя сервера → Введите имя, например msdocs-web-app-postgres-database-unique-id<>. Имя должно быть уникальным во всех областях Azure с URL-адресом https://<server-name>.postgres.database.azure.com сервера базы данных). Допустимые символы: A—Z, 0—9 и -. Источник данных → Нет Регион → тот же регион Azure, который используется для Служба приложений. Версия → используйте значение по умолчанию (последняя версия). Вычисление + хранилище → выберите Настроить сервер, чтобы выбрать другой план вычислений и хранения, который рассматривается ниже. Администратор имя пользователя → введите имя администратора после предложений портала об именовании. Пароль → введите пароль администратора. Подтвердите пароль → повторно введите пароль администратора.
Чтобы изменить параметры вычислений и хранилища , выберите ссылку «Настроить сервер» , чтобы перейти на страницу «Настройка «. После завершения работы на странице «Настройка » нажмите кнопку «ОК «, чтобы вернуться на страницу с одним сервером .
Нажмите кнопку «Просмотр и создание «, чтобы перейти на страницу проверки . На странице проверки выберите «Создать«, чтобы создать службу сервера База данных Azure для PostgreSQL.
После создания базы данных можно перейти к ресурсу, щелкнув ссылку «Перейти к ресурсу «.

2. Добавление правил брандмауэра базы данных

На этом шаге вы добавите правила брандмауэра, которые позволяют:

• Веб-приложение для доступа к серверу базы данных. Этот доступ включен с помощью правила брандмауэра базы данных, которое принимает подключения из всех ресурсов Azure. В рабочей системе необходимо отключить это правило и использовать azure виртуальная сеть (виртуальная сеть). Это правило брандмауэра также может быть полезно во время настройки базы данных, если вы можете использовать azure Cloud Shell (ресурс Azure) с psql для доступа к базе данных.

• Локальная среда для доступа к серверу базы данных. Этот доступ полезен для последующих шагов конфигурации, особенно но его следует отключить после завершения настройки и развертывания.

• Портал Azure
• Код VS
• Azure CLI

Инструкции

Снимок экрана

Добавьте правило, разрешающее веб-приложению доступ к гибкому серверу PostgreSQL. В меню ресурсов слева для сервера выберите «Сеть«. Нажмите кнопку «Да» , чтобы разрешить общий доступ к службам Azure. Выберите «+ Добавить текущий IP-адрес клиента , если вы еще не сделали этого» и подключитесь к базе данных из локальной среды. Щелкните Сохранить для сохранения изменений. Для защиты обмена данными между рабочими веб-приложениями и серверами баз данных используйте azure виртуальная сеть (виртуальная сеть).

3. Создание базы данных

• psql
• Код VS

В локальной среде или в любом месте можно использовать psql интерактивного терминала PostgreSQL, например Azure Cloud Shell, подключиться к серверу базы данных PostgreSQL для создания restaurant базы данных.

Запустите psql:

psql --host=<server-name>.postgres.database.azure.com \
     --port=5432 \
     --username=<admin-user>@<server-name> \
     --dbname=postgres

Значения и <admin-user> являются значениями <server-name> из предыдущего шага, используемыми при создании службы базы данных PostgreSQL. Приведенная выше команда запросит пароль администратора. Если возникают проблемы с подключением, перезапустите базу данных и повторите попытку. При подключении из локальной среды IP-адрес необходимо добавить в список правил брандмауэра для службы базы данных.

В командной строке postgres=> создайте базу данных:

CREATE DATABASE restaurant;

Точка с запятой («;») в конце команды необходима. Чтобы убедиться, что restaurant база данных успешно создана, используйте команду, чтобы изменить строку \c restaurant с postgres=> (по умолчанию) на restaurant->. Введите \?, чтобы отобразить справку или \q, чтобы выйти.

Вы также можете создать базу данных с помощью Azure Data Studio или любой другой интегрированной среды разработки, а также Visual Studio Code с установленным пакетом расширений средств Azure.

4. Настройка управляемого удостоверения для PostgreSQL

При настройке управляемого удостоверения для PostgreSQL веб-приложение позволяет безопасно подключаться к базе данных без пароля. Вместо этого Служба приложений выполняет проверку подлинности в PostgreSQL с помощью управляемого удостоверения. Дополнительные сведения см.

в статье Аутентификация размещенных в Azure приложений в ресурсах Azure с помощью пакета AZURE SDK для Python.

Конфигурация управляемого удостоверения для PostgreSQL может быть разбита на два этапа:

• Задайте администратора Active Directory для базы данных PostgreSQL.
• Создайте роль для управляемого удостоверения в базе данных PostgreSQL.

Настройка администратора Active Directory для базы данных PostgreSQL

На этом шаге вы создадите пользователя Azure Active Directory в качестве администратора сервера База данных Azure для PostgreSQL. Дополнительные сведения см. в статье «Использование Azure Active Directory для проверки подлинности с помощью PostgreSQL».

• Портал Azure
• Azure CLI

Инструкции	Снимок экрана
В портал Azure для базы данных PostgreSQL: На панели ресурсов выберите администратора Active Directory. Затем выберите «Задать администратора» , чтобы добавить пользователя.
В администраторе Active Directory выполните следующие действия. Найдите учетную запись пользователя и выберите ее. Выберите «Выбрать «, чтобы добавить пользователя в качестве администратора Active Directory.
После выбора пользователя обязательно нажмите кнопку «Сохранить «, чтобы применить изменение.

Создание роли для управляемого удостоверения в базе данных PostgreSQL

Созданная роль — это роль, используемая веб-приложением (Служба приложений) для подключения к серверу PostgreSQL. Укажите имя пользователя роли webappuser и пароль, равный идентификатору приложения управляемого удостоверения для веб-приложения.

Прежде чем создать роль, необходимо получить идентификатор приложения , созданный при настройке управляемого удостоверения, назначаемого системой, на предыдущем шаге этого руководства. Идентификатор приложения отличается от идентификатора объекта (субъекта) при настройке управляемого удостоверения для Служба приложений.

• Портал Azure
• Azure CLI

Инструкции	Снимок экрана
На портале Azure выполните следующие действия: Перейдите к ресурсу Azure Active Directory. В поле поиска клиента найдите имя веб-приложения, для которого настроено управляемое удостоверение.
На странице «Корпоративное приложение»: Выберите обзор возвращаемого корпоративного приложения. В свойствах корпоративного приложения скопируйте идентификатор приложения для последующего использования. Сохраните идентификатор приложения для следующего шага.

Затем необходимо предоставить удостоверению разрешение на доступ к базе данных. Это предоставление выполняется путем создания новой роли, которая определяет управляемое удостоверение как одно из них, которое может получить доступ к базе данных. Если вы уже находитесь в портал Azure, для выполнения этой задачи можно использовать Cloud Shell Azure.

Совет

Кроме того, можно подключиться к базе данных с помощью локального экземпляра PostgreSQL или Azure Data Studio. Для интерактивного терминала PostgreSQL , используемого локально, необходимо создать токен с помощью az account get-access-token. Azure Data Studio интегрирована с Azure Active Directory таким образом, что маркер создается автоматически. Независимо от способа подключения убедитесь, что имя пользователя указано как <azure-ad-user-name>@<server-name>.

При входе в Cloud Shell с учетной записью, отличной от учетной записи, которая была задана в качестве администратора для PostgreSQL, измените учетные записи с az loginпомощью .

В Cloud Shell можно выбрать Bash и PowerShell.

• bash
• Терминал PowerShell

# Sign into Azure as the Azure AD user that was set as Active Directory admin
# az login
# Get an access token for PostgreSQL with the Azure AD user
token=$(az account get-access-token \
    --resource-type oss-rdbms \
    --output tsv \
    --query accessToken)
# View token to confirm
echo $token 
# Sign into the Postgres server
psql "host=<server-name>.postgres.database.azure.com \
    port=5432 \
    dbname=restaurant \
    user=<aad-user-name>@<server-name> \
    password=$token \
    sslmode=require"

В базе данных PostgreSQL выполните следующие команды, чтобы создать роль, которую веб-приложение будет использовать для доступа к базе данных.

SET aad_validate_oids_in_tenant = off;
CREATE ROLE webappuser 
    WITH LOGIN PASSWORD '<application-id-of-system-assigned-managed-identity>'
    IN ROLE azure_ad_user;

Вы будете использовать имя пользователя webappuser в качестве параметра конфигурации Служба приложений на следующем шаге.

Следующий этап

Развертывание в приложении Python в Azure

Импорт и экспорт данных в PostgreSQL, гайд для начинающих / Хабр

В процессе обучения аналитике данных у человека неизбежно возникает вопрос о миграции данных из одной среды в другую. Поскольку одним из необходимых навыков для аналитика данных является знание SQL, а одной из наиболее популярных СУБД является PostgreSQL, предлагаю рассмотреть импорт и экспорт данных на примере этой СУБД.

В своё время, столкнувшись с импортом и экспортом данных, обнаружилось, что какой-то более-менее структурированной инфы мало: этот момент обходят на всяких там курсах по аналитике, подразумевая, что это очень простые моменты, которым не следует уделять внимание.

В данной статье приведены примеры импорта в PostgreSQL непосредственно самой базы данных в формате sql, а также импорта и экспорта данных в наиболее простом и распространенном формате .csv, в котором в настоящее время хранятся множество существующих датасетов. Формат .json хоть и является также очень распространенным, рассмотрен не будет, поскольку, по моему скромному мнению, с ним все-таки лучше работать на Python, чем в SQL.

1.    Импорт базы данных в формате в PostgreSQL

Скачиваем (получаем из внутреннего корпоративного источника) файл с базой данных в выбранную папку. В данном случае путь: 

C:\Users\User-N\Desktop\БД  

Имя файла: demo-big-20170815

Далее понадобиться командная строка windows или SQL shell (psql). Для примера воспользуемся cmd. Переходим в каталог, где находится скачанная БД, командой  cd C:\Users\User-N\Desktop\БД :

Далее выполняем команду для загрузки БД из sql-файла:

 "C:\Program Files\PostgreSQL\10\bin\psql" -U postgres -f demo-big-20170815.sql

Где сначала указывается путь, по которому установлен PostgreSQL на компьютере, -U – имя пользователя, -f  — название файла БД.

Отметим, что в зависимости от размера базы данных загрузка может занимать до нескольких десятков минут. Конец загрузки будет отмечен следующим видом:

Заходим в pgAdmin и наблюдаем там импортированную БД:

С таблицами:

2. Импорт данных из csv-файла

Предполагается, что у вас уже есть необходимый .csv-файл, и первое, что нужно сделать, это перейти pgAdmin и создать там новую базу данных. Ну или воспользоваться уже существующей, в зависимости от текущих нужд. В данном случае была создана БД airtickets.

В выбранной БД создается таблица с полями, типы которых должны соответствовать «колонкам» в выбранном .csv-файле.

Далее воспользуемся SQL shell (psql) для подключения к нужной БД и для подачи команд на импорт данных. При открытии SQL shell (psql) она стандартно спросит про имя сервера, имя подключаемой БД, порт и пользователя. Ввести нужно только имя БД и пароль пользователя, всё остальное проходим нажатием ентра. Создается подключение к нужной БД – airtickets.

Ну и вводим команды на импорт данных из файла:

\COPY tickets FROM ‘C:\Users\User-N\Desktop\CSV\ticket_dataset_MOW. csv’ DELIMITER ‘,’ CSV HEADER;

Где tickets – название созданной в БД таблицы, из – путь, где хранится  .csv-файл, DELIMITER ‘,’ – разделитель, используемый в импортируемом .csv-файле, сам формат файла и HEADER, указывающий на заголовки «колонок».

Один интересный момент. Написание команды COPY строчными (маленькими) буквами привело к тому, что psql ругнулся, выдал ошибку и предложил написать команду прописными буквами.

Заходим в pgAdmin и удостоверяемся, что данные были загружены.

3. Экспорт данных в .csv-файл

Предположим, нам надо сохранить таблицу airports_data из уже упоминаемой выше БД demo.

Для этого подключимся к БД demo через SQL shell (psql) и наберем команду, указав уже знакомые параметры разделителя, типа файла и заголовка:

 \COPY airports_data TO ‘C:\Users\User-N\Desktop\CSV\airports.csv’ DELIMITER ‘,’ CSV HEADER;

Существует и другой способ экспорта через pgAdmin: правой кнопкой мыши по нужной таблице – экспорт – указание параметров экспорта в открывшемся окне.

4. Экспорт данных выборки в .csv-файл

Иногда возникает необходимость сохранить в .csv-файл не полностью всю таблицу, а лишь некоторые данные, соответствующие некоторому условию. Например, нам нужно из БД demo таблицы flights выбрать поля flight_id, flight_no, departure_airport, arrival_airport, где departure_airport = ‘SVO’. Данный запрос можно вставить сразу в команду psql:

\COPY (SELECT flight_id, flight_no, departure_airport, arrival_airport FROM flights WHERE departure_airport = 'SVO') TO ‘C:\Users\User-N\Desktop\CSV\flights_SVO.csv' CSV HEADER DELIMITER ',';

Вот такой небольшой гайд получился.

Как подключиться к PostgreSQL с помощью psql

Как подключиться к PostgreSQL с помощью psql

В этой статье описывается, как подключиться к базе данных PostgreSQL из командной строки с помощью программы psql . Вы можете использовать программу psql как быстрый и простой способ прямого доступа к вашим базам данных. psql — это интерактивный терминал PostgreSQL, который позволяет выполнять запросы PostgreSQL и просматривать результаты. psql также имеет ряд метакоманд и возможностей оболочки, упрощающих создание сценариев и автоматизацию ряда действий. Пожалуйста, обратитесь к этой ссылке, чтобы узнать больше об этих командах.

Содержание

• Подключение к PostgreSQL с помощью psql
• Дополнительная информация

Подключение к PostgreSQL с помощью psql

Чтобы подключиться к PostgreSQL из командной строки, выполните следующие действия:

1. Войдите в свою учетную запись A2 Hosting, используя SSH.
2. В командной строке введите следующую команду. Замените dbname на имя базы данных, а username на имя пользователя базы данных:

    psql имя базы данных имя пользователя 

3. В приглашении пароля введите пароль пользователя базы данных. Когда вы вводите правильный пароль, появляется приглашение psql.

4. После доступа к базе данных PostgreSQL вы можете выполнять SQL-запросы и многое другое. Вот некоторые распространенные команды psql :
   

   • Чтобы просмотреть справку по командам psql , введите \?.
   • Чтобы просмотреть справку по командам SQL, введите \h.
   • Чтобы просмотреть информацию о текущем соединении с базой данных, введите \conninfo.
   • Чтобы получить список таблиц базы данных и их соответствующих владельцев, введите \dt.
   • Чтобы вывести список всех таблиц, представлений и последовательностей в базе данных, введите \z.
   • Чтобы выйти из программы psql , введите \q.

Дополнительная информация

• Чтобы просмотреть онлайн-документацию по psql в PostgreSQL 8.4, посетите http://www.postgresql.org/docs/8.4/static/app-psql.html.
• Чтобы просмотреть онлайн-документацию по psql в PostgreSQL 9.1, посетите http://www.postgresql.org/docs/9..1/static/app-psql.html.
• Чтобы узнать больше о размещении базы данных PostgreSQL с помощью A2 Hosting, нажмите здесь.

Разместите базу данных PostgreSQL @ A2

Подробности статьи

• Уровень: средний

Другие статьи в этой категории

• Подключение к PostgreSQL с помощью psql
• Подключение к PostgreSQL с использованием PHP
• Подключение к PostgreSQL с помощью Python
• Подключение к PostgreSQL с помощью Perl
• Импорт и экспорт базы данных PostgreSQL
• Удаленные подключения PostgreSQL
• Клиентские приложения PostgreSQL
• Резервное копирование базы данных PostgreSQL с использованием заданий cron
• Управление базами данных и пользователями PostgreSQL из командной строки
• Определение версий PostgreSQL и PostGIS
• Определение размера баз данных и таблиц PostgreSQL
• Переименование базы данных PostgreSQL
• номеров ECCN и HTS для PostgreSQL

Показать больше

Статьи по теме

• Создание установки PostgreSQL для локального тестирования
• Импорт и экспорт базы данных PostgreSQL
• Управление базами данных и пользователями PostgreSQL
• Управление базами данных и пользователями PostgreSQL из командной строки

Показать больше

Развивайте свой веб-бизнес

Подпишитесь, чтобы получать еженедельные советы, стратегии и новости, необходимые для развития вашей сети бизнес.

Бесплатно. Отписаться в любое время.

Была ли эта статья полезной для вас? Тогда вам понравится наша поддержка. Испытайте преимущества хостинга A2 уже сегодня и получите предварительно защищенный и предварительно оптимизированный веб-сайт. Ознакомьтесь с нашими планами веб-хостинга сегодня.

Команды интерфейса командной строки PostgreSQL (psql)

Раймонд

Артикул Артикул 543

изображение Диаграммы 48

code Code 2

chat_bubble_outline Threads 8

comment Comments 240

loyalty Kontext Points 5960

account_circle Профиль

видимость 8 670 событие 2021-02-14 access_time 2 года назад язык английский

more_vert

В статье Установка PostgreSQL в WSL я показал шаги по установке PostgreSQL в WSL (подсистема Windows для Linux). На этой странице представлены некоторые наиболее часто используемые команды интерфейса командной строки psql .

Подключение к psql

Следующие команды подключаются к серверу PostgreSQL через команду psql:

 psql -U postgres -W -p 5432 -h localhost 
 

Вывод должен выглядеть следующим образом:

 $ psql -U postgres -W -p 5432 -h localhost
Пароль пользователя postgres:
psql (10.15 (Ubuntu 10.15-0ubuntu0.18.04.1))
SSL-соединение (протокол: TLSv1.3, шифр: TLS_AES_256_GCM_SHA384, биты: 256, сжатие: выключено)
Введите "помощь" для помощи.

postgres=# 
 

Показать базы данных

 \l 

Пример вывода:

 postgres=# \l
                              Список баз данных
   Имя | Владелец | Кодирование | Разобрать | Тип | Права доступа
-----------+----------+-----------+---------+------ ---------------
 постгрес | постгрес | UTF8 | C.UTF-8 | C.UTF-8 |
 шаблон0 | постгрес | UTF8 | C.UTF-8 | C.UTF-8 | =с/постгрес +
           | | | | | postgres=CTc/postgres
 шаблон1 | постгрес | UTF8 | C. UTF-8 | C.UTF-8 | =с/постгрес +
           | | | | | postgres=CTc/postgres
 тестовая база данных | постгрес | UTF8 | C.UTF-8 | C.UTF-8 |
(4 ряда) 
 

Использовать базу данных

Используйте следующую команду для изменения базы данных по умолчанию:

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

Пример вывода:

 postgres=# \c testdb
Пароль:
SSL-соединение (протокол: TLSv1.3, шифр: TLS_AES_256_GCM_SHA384, биты: 256, сжатие: выключено)
Теперь вы подключены к базе данных "testdb" как пользователь "postgres".
testdb=# 
 

Список таблиц

Чтобы просмотреть таблицы в базе данных, выполните следующие команды:

 \dt 

Пример вывода:

 testdb=# \dt
           Список отношений
 Схема | Имя | Тип | Владелец
--------+------------+-------+----------
 общественный | тестовая_таблица | стол | постгрес
(1 строка) 

Запустить оператор выбора

Чтобы запустить оператор SQL, просто введите его и закончите точкой с запятой (;).