Sql

Команды postgresql: Шпаргалка по основным командам PostgreSQL

Содержание

Б3. Использование СУБД PostgreSQL

Общее описание

PostgreSQL — объектно-реляционная СУБД. Является свободной альтернативой коммерческой СУБД (таким как Oracle Database, Microsoft SQL Server и др.). В больших антивирусных сетях СУБД PostgreSQL может быть использована в качестве внешней БД для Dr.Web Enterprise Security Suite.

Чтобы использовать PostgreSQL в качестве внешней БД

1.Установить сервер PostgreSQL или Postgres Pro.

2.Настроить Сервер Dr.Web на использование соответствующей внешней базы. Это можно сделать в конфигурационном файле или при помощи Центра управления: в меню Конфигурация Сервера Dr.Web, на вкладке База данных.

При подключении к БД PostgreSQL может быть использована только авторизация trust, password и MD5.

Установка и поддерживаемые версии

1.Загрузите самую последнюю версию бесплатного продукта PostgreSQL (сервер PostgreSQL и, если необходимо, соответствующий ODBC-драйвер) или, по крайней мере, не используйте версию ранее чем 8.

4 или 11.4.1 для Postgres Pro.

2.Создайте базу данных PostgreSQL одним из следующих способов:

a)При помощи графического интерфейса pgAdmin.

b)При помощи SQL-команды CREATE DATABASE.

База данных должна быть создана в кодировке UTF8.

Переход на внешнюю БД описан в п. Смена типа СУБД Dr.Web Enterprise Security Suite.

Также обратите внимание на системные требования к Серверу Dr.Web при работе с внешней базой данных PostgreSQL (см. Руководство по установке, п. Системные требования).

Параметры

При настройке обращения к БД PostgreSQL используются параметры, описываемые в таблице ниже.

PostgreSQL

Имя

Значение по умолчанию

Описание

host

<Локальный UNIX-сокет>

Хост сервера PostgreSQL

port

 

Порт сервера PostgreSQL или расширение имени файла сокета

dbname

drwcs

Название базы данных

user

drwcs

Имя пользователя

password

drwcs

Пароль

options

 

Опции отладки/трассировки для отправки серверу

requiressl

 

•1 для запроса установки SSL соединения

•0 для отсутствия запроса

temp_tablespaces

 

Пространство имен для временных таблиц

default_transaction_isolation

 

Режим изоляции транзакции (см. документацию к PostgreSQL)

Техническую информацию можно также найти по адресу https://www.postgresql.org/docs/.

Взаимодействие Сервера Dr.Web с БД PostgreSQL через UDS

При установке Сервера Dr.Web и БД PostgreSQL на одной машине возможна настройка их взаимодействия через UDS (доменный сокет UNIX).

Чтобы настроить работу через UDS

1.В конфигурационном файле БД PostgreSQL postgresql.conf прописать следующую директорию для UDS:

unix_socket_directory = ‘/var/run/postgresql’

2.Перезапустить PostgreSQL.

Настройка базы данных PostgreSQL

Для увеличения производительности при работе с базой данных PostgreSQL рекомендуется провести настройку на основе информации из официальных руководств по базе данных.

В случае использования базы данных больших размеров и при наличии соответствующих вычислительных ресурсов, рекомендуется настроить следующие параметры в конфигурационном файле postgresql. conf:

Минимальная настройка:

shared_buffers = 256MB

temp_buffers = 64MB

work_mem = 16MB

Расширенная настройка:

shared_buffers = 1GB

temp_buffers = 128MB

work_mem = 32MB

fsync = off

synchronous_commit = off

wal_sync_method = fdatasync

commit_delay = 1000

max_locks_per_transaction = 256

max_pred_locks_per_transaction = 256

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

 

Настройка параметра max_locks_per_transaction может быть полезна для обеспечения бесперебойной работы при массовом обращении к таблицам базы данных, в частности, при обновлении базы данных до новой версии.

PostgreSQL

В статье описана установка PostgreSQL для ОС Ubuntu Linux 20.04. Вы также можете ознакомиться с кратким руководством в документации.

начало внимание 

Для примера используется имя базы elma365, пользователь elma365 и пароль SecretPassword.

При настройке задайте эти данные согласно политике безопасности, принятой в вашей организации.

конец внимание

 

Установка PostgreSQL

Для корректной работы системы требуется PostgreSQL версии 10 — 13.

Для установки PostgreSQL добавьте официальный репозиторий:

sudo sh -c ‘echo «deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main» > /etc/apt/sources.list.d/pgdg.list’
wget —quiet -O — https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add —
sudo apt update

 

Далее установите PostgreSQL:

sudo apt install postgresql-13

 

Настройка PostgreSQL

Начало внимание

Для пароля разрешается применять следующие символы:

  • Заглавные латинские буквы: от A до Z;
  • Строчные латинские буквы: от a до z;
  • Цифры от 0 до 9;
  • Символы: !()-_+=$

Конец внимание

Для настройки PostgreSQL используйте следующие команды:

 

  1. Создайте отдельного пользователя `elma365` с паролем `SecretPassword` для работы с БД. Имя пользователя и пароль приведены для примера:

sudo -u postgres psql -c «CREATE ROLE elma365 WITH login password ‘SecretPassword’;»

 

  1. Создайте отдельную базу данных elma365 и назначьте выделенного пользователя elma365 её владельцем. Нельзя использовать существующую базу, т. к. система создаёт таблицы и схемы:

sudo -u postgres psql -c «CREATE DATABASE elma365 WITH OWNER elma365;»

 

  1. Добавьте необходимые расширения для базы данных elma365:

sudo -u postgres psql -d elma365 -c «CREATE EXTENSION \»uuid-ossp\»; CREATE EXTENSION pg_trgm;»

Разрешения подключения к PostgreSQL

  1. Добавьте IP-адрес сервера, на котором запущен PostgreSQL, в конфигурационный файл /etc/postgresql/13/main/postgresql.conf в параметр listen_addresses (например, 192.168.10.10):

sudo nano /etc/postgresql/13/main/postgresql.conf
listen_addresses = ‘localhost, 10. 0.1.1, 192.168.10.10′

 

10.0.1.1 является IP адресом по умолчанию при разрешении доступа к хосту из microk8s командой `sudo microk8s enable host-access`.

  1. Добавьте в конец конфигурационного файла авторизации /etc/postgresql/13/main/pg_hba.conf адрес сети, в которой работают ноды elma365:

sudo nano /etc/postgresql/13/main/pg_hba.conf
host    all             all             10.0.0.0/8              md5
host    all             all             192.168.0.0/16              md5

 

  1. Перезагрузите сервис PostgreSQL:

sudo systemctl restart postgresql

Настройка ограничений и лимитов к PostgreSQL

  1. Откройте для редактирования конфигурационный файл /etc/postgresql/13/main/postgresql.conf

sudo nano /etc/postgresql/13/main/postgresql.conf

  1. Увеличьте максимальное количество подключений, изменив значение параметра max_connections на 2000:

max_connections = 2000

  1. Увеличьте максимальное количество блокировок на транзакцию, изменив значение параметра max_locks_per_transaction на 512, также необходимо раскомментировать эту строку (убрать знак решетки #):

max_locks_per_transaction = 512

  1. Перезагрузите сервис PostgreSQL:

sudo systemctl restart postgresql

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

Строка для подключения к PostgreSQL:

postgresql://elma365:SecretPassword@<postgresql-server-address>:5432/elma365?sslmode=disable

Резервное копирование данных PostgreSQL с использованием elma365ctl dump

начало внимание

Во время создания резервной копии сервисы elma365 останавливаются, приложение становится недоступным.

конец внимание

  1. Для создания резервной копии данных postgresql, используйте следующую команду:

elma365ctl dump —parts=postgres —consistent

  1. Дамп сохранится в отдельную папку с указанием в названии версии поставки и временного штампа, например, /backup/ELMA365_2022.11.5-rc_2023.01.09-05.45.01
    .

Восстановление данных PostgreSQL с использованием elma365ctl restore

  1. Остановите сервисы приложения и подготовьте систему к восстановлению данных с помощью команды:

elma365ctl restore —prepare

После этого нужно пересоздать базу PostgreSQL.

  1. Удалите базу данных elma365 с помощью команды:

DROP DATABASE elma365;

  1. Создайте базу и назначьте выделенного пользователя её владельцем:

CREATE DATABASE elma365 WITH OWNER elma365;

  1. Включите в базе требуемые расширения:

CREATE EXTENSION «uuid-ossp»; CREATE EXTENSION pg_trgm;

  1. Восстановите данные с помощью команды:

elma365ctl restore —parts=postgres —path=/backup/<папка_с_дампом> 

Система будет доступна для работы в течение нескольких минут после завершения работы команды.

create_external_database.html mongodb.html

Была ли статья полезной?

ДаНет

Выберите вариантРекомендации не помоглиТекст трудно понятьНет ответа на мой вопросСодержание статьи не соответствует заголовкуДругая причина

Основные команды и флаги psql, которые вам необходимо знать

PostgreSQL или Postgres — это система управления объектно-реляционными базами данных, использующая язык SQL. Он бесплатный, с открытым исходным кодом, надежный, надежный и производительный. PostgreSQL также является одной из самых популярных и используемых реляционных баз данных.

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

Если вы хотите узнать о командах Postgres или освежить память, вы попали по адресу! В этой статье вы узнаете о лучших командах и флагах psql , которые вам необходимо знать при работе с PostgreSQL.

1. Подключиться к базе данных —

psql -d

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

Та же база данных хоста

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

 psql -d <имя базы данных> -U <имя пользователя> -W
// пример
psql -d tutorials_db -U администратор -W

Приведенная выше команда включает три флага:

  • -d — указывает имя базы данных для подключения к
  • -U — указывает имя пользователя для подключения как
  • -W — заставляет psql запрашивать пароль пользователя перед подключением к базе данных

В этом примере команда подключает вас к tutorials_db под пользователем admin .

Другая база данных хоста

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

 psql -h <адрес БД> -d <имя БД> -U <имя пользователя> -W
//пример
psql -h my-psql-db.cloud.neon.tech -d tutorials_db -U администратор -W

Флаг -h указывает адрес хоста базы данных.

Режим SSL

Могут быть случаи, когда вы хотите использовать SSL для соединения.

 psql "sslmode=require host= dbname= user="
//пример
psql "sslmode=require host=my-psql-db.cloud.neon.tech dbname=tutorials_db user=admin"

Приведенная выше команда открывает соединение SSL с указанной базой данных.

2. Список всех баз данных —

\l

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

 л

На приведенном выше изображении показано, что происходит при выполнении команды. Вы получаете таблицу со всеми базами данных и их именами, владельцами, правами доступа и другой информацией.

3. Переключиться на другую базу данных —

\c

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

 \c <имя БД>
// пример
\c tutorials_db

На изображении ниже показан результат после выполнения команды.

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

4. Список таблиц базы данных —

\dt

Допустим, вы хотите просмотреть все таблицы из базы данных. Вы можете перечислить все таблицы базы данных следующим образом:

 \дт

Команда psql \dt возвращает таблицы рядом с:

  • схема, которой они принадлежат
  • их типа
  • их владелец

5. Описать таблицу —

\d

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

 \d <имя-таблицы>
// пример
\d учебники

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

Если вам нужна дополнительная информация о таблице, вы можете использовать команду:

 \d+ <имя-таблицы>

Теперь вы получаете дополнительную информацию, такую ​​как хранилище, сжатие, цель статистики и описание.

6. Список всех схем —

\дн

Команда \dn psql выводит список всех схем базы данных.

Возвращает имена схем и их владельцев.

7. Список пользователей и их ролей —

\du

Иногда может потребоваться изменить пользователя. В Postgres есть команда, которая выводит список всех пользователей и их ролей.

 \ду

Как видно из изображения, команда возвращает всех пользователей.

8.

Получить конкретного пользователя — \du

Вы также можете получить информацию о конкретном пользователе с помощью следующей команды:

 \du <имя пользователя>
//пример
/ du postgres

Теперь вы можете видеть роли указанного пользователя и то, является ли он членом группы или нет.

9. Список всех функций —

\df

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

Команда возвращает все функции и:

  • схема, которой они принадлежат
  • имен
  • тип данных результата
  • типы данных аргументов
  • тип

10. Список всех представлений —

\dv

Интерфейс psql позволяет вывести список всех представлений базы данных с помощью команды \dv .

11. Сохранить результаты запроса в файл —

\o

Могут быть случаи, когда вы захотите проанализировать результат запроса позже. Или два сравнивают два результата запроса. Интерфейс psql позволяет это сделать.

Вы можете сохранить результаты запроса в файл следующим образом:

 \o <имя-файла>
// пример
\o запрос_результаты
...запустите команды psql...
\o - остановить процесс и снова вывести результаты в терминал

Сохраним в файл следующие результаты запроса:

  • доступные таблицы базы данных
  • результат описания таблицы базы данных
  • список всех пользователей
  • данные пользователя «postgres»

Примечание : Чтобы остановить сохранение результатов в файл, необходимо снова запустить команду \o без имени файла.

На изображении выше показан файл, содержащий все результаты запроса.

12. Запуск команд из файла —

\i

Также возможен запуск команд из файла. Для простых команд это может быть не лучшим решением. Но когда вы хотите запустить несколько команд и сложных операторов SQL, это очень помогает.

Создайте файл txt со следующим содержимым:

 л
\дт
\ду

Когда вы запускаете файл, он должен вернуть список всех:

  • базы данных
  • таблиц базы данных
  • пользователей

Вы можете запускать команды из файла со следующими команда psql :

 \i <имя-файла>
// пример
\i psql_commands.txt

Команда вернула все базы данных, таблицы и пользователей, как и ожидалось.

Выйти из psql —

\q

Вы вышли из интерфейса psql с помощью команды \q .

Резюме

Интерфейс psql является мощным и позволяет выполнять многие действия, включая выполнение операторов SQL. Если вы хотите начать работу с PostgreSQL или углубить свои знания, ознакомьтесь с нашим руководством по PostgreSQL. Вы также можете ознакомиться со статьями о Postgre в нашем блоге.

Если вы ищете варианты размещения базы данных Postgres, проверьте эти варианты размещения PostgreSQL.

Дальнейшее чтение

  • Хасура и Постгрес
  • Основы PostgreSQL
  • Выпуск Postgres 15: что нужно знать
  • Изучайте Postgres бесплатно
  • Лучшие решения бесплатного уровня баз данных PostgreSQL
  • Ведущие бессерверные поставщики PostgreSQL

Основные команды postgres

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

createdb

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

createdb является оболочкой оператора SQL CREATE DATABASE . Принципиальной разницы между созданием баз данных с помощью этой утилиты и другими способами доступа к серверу нет.

dropdb

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

dropdb — это оболочка SQL-оператора DROP DATABASE. Принципиальной разницы между удалением баз данных с помощью этой утилиты и другими методами доступа к серверу нет.

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

 dropdb sql_book
pg_dump

pg_dump — это утилита для резервного копирования базы данных PostgreSQL. Он делает согласованные резервные копии, даже если база данных используется одновременно. pg_dump не блокирует доступ других пользователей к базе данных (читателей или записывающих).

Есть и другие применения. Если вы хотите увидеть, какие операторы SQL будут использоваться для создания схемы и данных в базе данных, команда pg_dump сделает то, что вы хотите (и многое другое). Следующая команда выведет эту информацию в виде операторов SQL:

 $ pg_dump --inserts --clean sql_book

Вывод этой команды можно сохранить в файл с помощью перенаправления вывода команды:

 $ pg_dump --inserts --clean sql_book > dump.sql

В дампе будет несколько операторов SQL для настройки параметров базы данных и установки права доступа к таблицам. Мы не будем рассматривать эти функции в этом курсе, так что не стесняйтесь просматривать их, но не беспокойтесь об их специфике.

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

Мы можем использовать текстовый файл, созданный pg_dump , для восстановления базы данных. Мы делаем это, читая файл в команду psql . Если бы мы использовали тот же файл, что и в предыдущем примере, тогда команда, которую мы будем использовать, будет выглядеть так:

 psql -d sql_book < dump.sql

Параметр -d указывает, к какой базе данных подключаться. Использование < читает содержимое файла dump.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *