Работаем с postgresql из psql

Мета-команды PostgreSQL

Теперь, когда ты все настроил и готов приступить к работе с базой данных, осталось разобрать несколько мета-команд.
Это не SQL запросы, а команды специфичные для PostgreSQL.

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

Всем мета-командам предшествует обратная косая черта , за которой следует фактическая команда.

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

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

Ввод этой мета-команды в оболочке Postgres выведет:

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

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

postgres — это просто пустая база данных.
«template0» и «template1» — это служебные базы данных, которые служат шаблоном для создания новых баз.

Тебе пока не стоит беспокоиться о них. Если хочешь изучить все детали, то проверь официальную документацию.

Подключаемся к базе данных PostgreSQL

Некоторые команды SQL требуют, чтобы ты сначала вошел в базу данных (например, для создания новой таблицы).
Ты можешь выбрать, в какую базу данных входить, при запуске SQL Shell.

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

Полностью в терминале у тебя получится что-то такое:

Обрати внимание, что приглашение оболочки изменилось с на. Это значит, что теперь ты
подключен к базе данных , а не

Получить список всех таблиц в базе данных

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

Перед выполнением этой команды вам необходимо войти в базу данных.

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

Ты можешь увидеть имя таблицы и некоторую другую информацию, такую как схема (мы обсудим схемы в более сложных
руководствах) и владельца.

Владелец (owner) — это пользователь, который создал таблицу.

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

Список пользователей и ролей

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

Обрати внимание, что первый столбец называется — роль (role name).
И весь вывод на экран называется “список ролей” (List of roles), а не список пользователей. В PostgreSQL пользователи и роли практически
одинаковы

В PostgreSQL пользователи и роли практически
одинаковы.

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

Любая роль с атрибутом LOGIN может рассматриваться, как пользователь.

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

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

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

Установка

Вы можете установить сервер баз данных PostgreSQL из официального репозитория и из репозитория CentOS. Единственное различие в этих двух способах в том, что в официальном репозитории PostgreSQL будет более новая версия.

Сама установка делится на 2 пакета:

postgresql-server — сервер базы данных;
postgresql-contrib — дополнительные инструменты для работы с базами данных. Например для анализа или администрирования. Не входят в основной пакет, так как считается, что он нужен не всем. Пакет поддерживается теми же разработчиками, что и сервер. Устанавливать не обязательно, но рекомендуется.

С помощью репозитория CentOS

Установка пакетов выполняется следующей командой:

Следующая команда создаст системные базы данных, директории и системные таблицы:

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

Если этот шаг у вас завершился с ошибками, значит что не корректно была выполнена команда initdb. Ошибки могут быть следующими:

Failed to start postgresql.service: Unit postgresql.service not found;
Failed to enable unit: Unit file postgresql.service does not exist.

О том что сервис включен говорят следующие значения:

Для проверки корректной установки так же можно запустить команду, которая подключится к серверу PostgreSQL и вернет его версию, для проверки корректной установки:

Вы можете получить следующую ошибку:

could not change directory to “/home/user”: Permission denied

Говорит о том, что пользователь ‘postgres’ (который был создан автоматически с сервером PostgreSQL) не имеет прав на указанный каталог для создания файла .psql_history. В рамках примера эта ошибка не критична и может не отображаться вовсе, если вы выполняете команду не из домашнего каталога. Если, в последующем, она у вас так же появится, нужно будет добавить права на исполнение (‘x’) этой директории.

Установка из репозитория PostgreSQL

Как можно увидеть в примере выше, то из репозитория CentOS была доступна только 10 версия PostgreSQL. Если добавить в вашу ОС ссылку на официальный репозиторий PostgreSQL, то, скорее всего, у нас станет доступна более новая версия. На момент написания статьи, самая последняя актуальная версия PostgreSQL — 12. Вы можете проверить последнюю версию на официальном сайте проекта.

Установим репозиторий:

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

Установим сервер базы данных и пакет приложений:

Создадим системные базы и таблицы:

Запустим и включим демон, что бы после перезагрузки он это делал автоматически:

О том что демон включен и работает говорят следующие значения:

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

Зайти в БД

Чтобы начать работу с базой данных нужно знать её имя, например
пусть Вам нужна база даныых, которую назвали просто HeiHei_ru_DB

Воспользуйтесь командой

\c HeiHei_ru_DB

You are now connected to database «HeiHei_ru_DB» as user «postgres».

Если вы работаете в

Linux

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

psql -h 127.0.0.1 -d DATABASENAME -U DATABASEUSERNAME

Чтобы посмотреть список таблиц введите

\dt

Чтобы посмотреть всю таблицу person уже можно использовать стандартный

SELECT * FROM person;

Using psql

You’ll use (aka the PostgreSQL interactive terminal) most of all because it’s used to create databases and tables, show information about tables, and even to enter information (records) into the database.

Quitting pqsql

Before we learn anything else, here’s how to quit and return to the operating system prompt.
You type backslash, the letter , and then you press the Enter or return key.

This takes you back out to the operating system prompt.

Opening a connection locally

A common case during development is opening a connection to a local database (one on your own machine).
Run with (for user name) followed by the name of the database, in this example:

Opening a connection remotely

To connect your remote PostgreSQL instance from your local machine, use at your operating system command line.
Here’s a typical connection.

Here you’d enter the password. In case someone is peering over your shoulder, the characters are hidden. After you’ve entered your information properly you’ll get this message (truncated for clarity):

Looking at the psql prompt

A few things appear, then the prompt is displayed.
The name of the current database appears before the prompt.

At this point you’re expected to type commands and parameters into the command line.

psql vs SQL commands

has two different kinds of commands. Those starting with a backslash
are for itself, as illustrated by the use of to quit.

Those starting with valid SQL are of course interactive SQL used to
create and modify PostgreSQL databases.

Warning: SQL commands end with a semicolon!

One gotcha is that almost all SQL commands you enter into must end in a semicolon.

For example,suppose you want to remove a table named sample_property_5. You’d enter this command:

It’s easy to forget. If you do forget the semicolon, you’ll see this perplexing prompt.
Note that a has been inserted before the username portion of the prompt, and another
prompt appears below it:

When you do, just remember to finish it off with that semicolon:

Use the up and down arrow keys to move backwards and forwards through the command history.

Клонирование базы данных

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

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

В данном случае проще будет воспользоваться клонированием базы данных, а если что-то пойдёт не так, то восстановить из бэкапа. Благо это делается в пару строчек. Для копирования достаточно указать существующую в качестве шаблона.

Для восстановления удалим существующую и склонируем обратно.

How to Restore a Database

You can restore a database from a backup file using either psql or the utility. Which one you choose depends on the type of file you are restoring the database from.

If you backed up the database to a plaintext format, such as , use psql.
If you backed up the database to an archive format, such as , use .

Option 1 – Restore a database using psql

To restore a database from a file, on the command line (so not in psql), use .

You can use the database and file you used earlier, or create a new backup file.

Create an empty database for the file to restore the data into. If you’re using to restore , the easiest thing might be to delete and recreate it.

In a separate terminal (not in psql), run the following command, passing in as the username, and the names of the database and backup file you are using.

The flag points psql to a specific database, while the flag tells psql to read from the specified file.

If you don’t specify a path for the backup file, psql will look for the file in the last directory that you were in before you connected to PostgreSQL.

You will be prompted for the superuser’s password and then will see a series of commands get printed to the command line while psql recreates the database.

Restoring a database using psql

This command ignores any errors that occur during the restore. If you want to stop restoring the database if an error occurs, pass in .

Option 2 – Restore a database using

To restore a database using , use .

Create an empty database for the file to restore the data into. If you’re restoring from a file, the easiest thing might be to delete and recreate it.

On the command line (not in psql), run the following command, passing in as the username, and the names of the database and backup file you are using.

Restoring a database using pg_restore

You can also pass in the or flag to see what is doing at each step.

Using pg_restore in verbose mode

Файлы, которые использует psql

.psqlrc

При входе в psql будут выполнятся команды записанные в ~/.psqlrc. Этого файла может и не быть, но если он будет создан в домашнем каталоге пользователя, то psql будет его читать при каждом подключении к серверу.

Вот некоторые примеры настроек, которые можно ввести в ~/.psqlrc:

— результат запроса, будет попадать в утилиту less, при этом листать можно будет как вправо (если не влезает по ширине) так и вниз (если не влезает по высоте);
— после запроса показывать время его выполнения;
— приглашение вода команды, когда psql ждет новую команду;
— приглашение вода команды, когда psql ждет дополнительный ввод;
— история команд будет хранить 2000 строк.

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

%n — имя пользователя;
%/ — имя базы;
%R — заменяет символ в зависимости от режима ввода;
- # — # если суперпользователь,
- > — если обычный пользователь.

В качестве практики давайте настроим свой .psqlrc:

postgres@s-pg13:~$ cat <<EOT >> .psqlrc
> \setenv PAGER 'less -XS'
> \timing on
> \set PROMPT1 '%n@%/%R%# '
> \set PROMPT2 '%n@%/%R%# '
> \set HISTSIZE 2000
> EOT

postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.

postgres@postgres=# CREATE DATABASE test_database;
CREATE DATABASE
Time: 62,978 ms

postgres@postgres=# \c test_database
You are now connected to database "test_database" as user "postgres".

postgres@test_database=# \c postgres
You are now connected to database "postgres" as user "postgres".

postgres@postgres=# DROP DATABASE test_database;
DROP DATABASE
Time: 9,911 ms

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

.psql_history

Другой полезный файл это ~/.psql_history. В нем хранится история команд введенных в терминале psql. Перемещаться по истории команд в терминале psql можно клавишами вверх и вниз. Количество хранимых команд изменяется установкой переменной HISTSIZE.

Управление базой данных

Создать одинdbuser, используйте этого пользователя для управления базой данных, установленной в Ubuntu:

Войдите в базу данных postgres и создайтеdbuser, создайте базу данных пользователей exampledb и укажите владельца как dbuser:

Удалить базу данныхУдалить пользователя базы данных。 Метод входа: Если текущий пользователь системы Linux также является пользователем PostgreSQL, вы можете войти в базу данных, используя условное обозначение, пропустив имя пользователя, и впоследствии не нужно вводить пароль:

Инструмент визуального управления pgAdmin

PostgreSQL предоставляет инструменты визуального управленияpgAdmin, Может использоваться в различных системных средах, также может использоваться в контейнерах. Установите с помощью колеса Python под Ubuntu,

Он будет установлен под home / <username> /. Local / python <version> / site-packages /. Чтобы запустить pgAdmin4, установите колбу:：

Первый логин попросит адрес электронной почты и пароль:

Вы можете видеть, что это приложение колбы:

Следуйте инструкциям для входа в браузерВы также можете использовать китайский интерфейс: Откройте bashrc и установите псевдоним инструкции

После запуска перезагрузите bashrc:

Шаг 6 — Создание и удаление таблиц

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

Во-первых, создайте таблицу для хранения данных. Например, таблицу, где описано оборудование для детских площадок.

Базовый синтаксис этой команды выглядит следующим образом:

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

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

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

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

Для двух из столбцов ( и ) команды не указали длину поля. Это объясняется тем, что некоторые типы столбцов не требуют указания длины, поскольку длина подразумевается в зависимости от типа значения.

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

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

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

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

Проверка удаленного подключения к PostgreSQL

Протестируем возможность подключения к PostgreSQL под Linux. Сначала проверим доступность 5432 порта. Сделать это удобнее всего утилитой telnet. Если telnet не установлен — скачать его можно следующей командой:

Возможно, вам понадобятся root-права для установки утилиты. Получить их можно командой: sudo su. Конечно, вы должны знать пароль от root-пользователя.

Проверим доступность 5432 порта:

Синтаксис команды следующий: telnet IP-адрес сервера Порт. В нашем случае IP-адрес сервера — 185.233.2.45, а порт — 5432.

Если порт доступен, telnet вернет следующую информацию:

Чтобы прервать подключение нажмите 2 раза Enter или сочетание клавиш Ctrl + Z. Теперь, когда мы убедились в доступности 5432 порта, подключимся к PostgreSQL с помощью специального PostgreSQL клиента — psql.

Скачаем psql из репозитория:

Теперь подключимся удаленно к PostgreSQL. Синтаксис команды следующий:

В нашем случае команда выглядит так:

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

Команда вернула следующую информацию:

Все, мы подключились к PostgreSQL удаленно из Linux с помощью psql.

Описание таблицы

Если таблица создана какое-то время назад. Вы могли уже забыть, какие конкретно столбцы она содержит.

Для описания таблицы используется команда \d

\d booking_sites

Редактирование конфигурации PostgreSQL

Перейдите в директорию, в которой располагаются БД PostgreSQL. Для PostgreSQL 13 это следующие директории:

x86: C:\Program Files (x86)\PostgreSQL\13\data
x64: C:\Program Files\PostgreSQL\13\data

В директории data найдите файл pg_hba.conf и откройте его любым текстовым редактором. В конец файла добавьте следующую запись:

Расшифровка записи:

Вид подключения	База данных	Пользователь	IP-адрес удаленного подключения	Метод аутентификации
host	postgres	postgres	all	md5

Мы разрешили подключения к СУБД с любого IP под стандартным пользователем postgres к стандартной базе postgres. Конечно, так делать нельзя. Это небезопасно. Администратор должен создать пользователя для удаленного подключения с ограниченными правами.

Далее необходимо убедиться в наличии строки listen_addresses = ‘*’ в файле postgresql.conf. Файл находится там же, в директории data. Откройте файл в текстовом редакторе и найдите раздел CONNECTIONS AND AUTHENTICATION.

В одной из первых строчек раздела будет указан параметр listen_addresses = ‘*’

Важно убедиться в том, что значение параметра = ‘*’. Если параметр отсутствует или имеет другое значение

Скопируйте правильное значение параметра из нашей инструкции и вставьте его в файл.

Редактирование конфигурационных файлов PostgreSQL закончено, теперь осталось настроить брандмауэр Windows.

How to Access PostgreSQL With pgAdmin

The pgAdmin is a graphical tool for managing PostgreSQL databases. After installing and configuring the latest browser version of pgAdmin 4, you need to create an initial pgAdmin user account.

Or:

The General and Connection tabs allow you to enter values for your server name and database user credentials.

The Hostname/address is the location of the machine where the PostgreSQL server is running. A connection with your user account is established once you save the information entered. The interface presents an overview of the databases that your user account has access to.

To enter and execute queries, click Tools > Query Tool or press ALT+Shift+Q within the current database.

Conclusion

This article provided two (2) simple solutions on how to connect to a PostgreSQL database.

If you are looking for a terminal-based solution, psql is an excellent choice for speed and effectiveness.

The GUI based pgAdmin provides a secure, user-friendly way to log in, administer, and shape databases to fit your requirements. A graphical interface can be indispensable when working on a host of databases simultaneously.

Alternatively, you can also connect to PostgreSQL with SQL Workbench.

Загрузка и установка PostgreSQL

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

Для Windows и Mac ты можешь загрузить установщик
с
веб-сайта EDB.

EDB больше не предоставляет пакеты для систем GNU/Linux. Вместо этого они рекомендуют вам использовать диспетчер
пакетов твоего дистрибутива.

Установщики включают в себя разные компоненты.

Вот самые важные из них:

Сервер PostgreSQL (очевидно)
pgAdmin, графический инструмент для управления базами данных
Менеджер пакетов для загрузки и установки дополнительных инструментов и драйверов

Windows

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

Диалоговое окно «Выбрать компоненты» позволяет выборочно устанавливать компоненты.
Если у тебя нет веской причины что-то менять — оставляй все как есть.

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

Во время установки тебе нужно будет указать пароль для суперпользователя (root).

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

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

Порт по умолчанию — 5432. Если ты устанавливаешь PostgreSQL впервые, то он скорее всего свободен.
Если окажется, что этот порт уже занят другим экземпляром PostgreSQL, ты можешь указать другое значение, например 5433.

После завершения установки ты сможешь запустить SQL Shell, поставляемый с Postgres.

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

Используй данные, которые ты вводил на предыдущих шагах.

Поздравляю! Настройка для Windows завершена, и скоро мы начнем писать первые SQL запросы.

Ниже список вариантов установки для других операционных систем.

macOS

Для macOS у тебя есть разные варианты. Можно скачать установщик с сайта EDB и запустить его.

Кроме того, можно использовать , простое приложение для macOS.

После запуска у тебя появится сервер PostgreSQL, готовый к использованию.
Завершить работу сервера можно просто закрыв приложение.

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

GNU/Linux

Ты можешь найти PostgreSQL в репозиториях большинства дистрибутивов Linux. Установить его можно одним щелчком мыши
из выбранного графического диспетчера пакетов.

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

Arch

Запуск оболочки PostgreSQL

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

Открой терминал и введи:

— это оболочка Postgres, аргумент используется для указания пользователя.

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

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

Как только пароль установлен, база данных PostgreSQL готова к работе!

Если сервер PostgreSQL по какой-то причине не запускается, можешь попробовать запустить его вручную.

Управление таблицами

Создание новой или временной таблицы

CREATE  TABLE  table_name(
   pk SERIAL PRIMARY KEY,
   c1 type(size) NOT NULL,
   c2 type(size) NULL,
   ...
);

Добавление нового столбца в таблицу:

ALTER TABLE table_name ADD COLUMN new_column_name TYPE;

Удаление столбца в таблице:

ALTER TABLE table_name DROP COLUMN column_name;

Переименовать столбец:

ALTER TABLE table_name RENAME column_name TO new_column_name;

Установите или удалите значение по умолчанию для столбца:

ALTER TABLE table_name ALTER COLUMN

Добавление первичного ключа к таблице.

ALTER TABLE table_name ADD PRIMARY KEY (column,...);

Удаление первичного ключа из таблицы.

ALTER TABLE table_name 
DROP CONSTRAINT primary_key_constraint_name;

Переименовать таблицу.

ALTER TABLE table_name RENAME TO new_table_name;

Удаление таблицы и зависимых от нее объектов:

DROP TABLE  table_name CASCADE;

Установка PostgreSQL

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

Обязательно проверяем, что после установки служба postgresql активна и включена на запуск при загрузке:

Во время установки создаётся пользователь Ubuntu с именем , связанный с ролью postgres в PostgreSQL, что даёт права администратора PostgreSQL

Обратите внимание, что домашним каталогом этого пользователя является директория. Чтобы нам залогиниться в консоль под пользователем postgres необходимо выполнить:

Или в два шага, сначала подключившись к консоли под пользователем postgres и потом войти в psql:

Чтобы посмотреть информацию о подключении, выполним в psql команду:

И получим ответ:

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