Подключение к серверу базы данных PostgreSQL
Данная статья относится к циклу статей, посвященных PostgreSQL. В предыдущей статье мы узнали о том, как установить PostgreSQL для изучения и практики в вашей локальной системе. Теперь мы покажем вам, как подключиться к серверу баз данных PostgreSQL.
После того как вы установили сервер базы данных PostgreSQL, установщик также установил некоторые полезные инструменты для работы с сервером баз данных PostgreSQL. Вы можете подключиться к серверу с помощью терминальной программы psql или инструмента pgAdmin.
Подключение к серверу баз данных PostgreSQL через psql#
psql — это интерактивная терминальная программа, предоставляемая PostgreSQL. Она позволяет вам взаимодействовать с сервером баз данных PostgreSQL, например, выполнять операторы SQL и управлять объектами баз данных.
Следующие шаги показывают, как подключиться к серверу баз данных PostgreSQL через программу psql:
Во-первых, запустите программу psql и подключитесь к PostgreSQL Database Server с помощью пользователя postgres, нажав на иконку psql, как показано ниже:
Во-вторых, введите необходимую информацию, такую как сервер, база данных, порт, имя пользователя и пароль. Нажмите Enter, чтобы принять значения по-умолчанию. В любом случае вам нужно ввести пароль, который вы указали во время установки.
В-третьих, попробуйте повзаимодействовать с сервером баз данных PostgreSQL при помощи операторов SQL. Для этого вы можете воспользоваться следующим оператором:
Пожалуйста, не забудьте завершить команду точкой с запятой (;). После нажатия Enter, psql вернёт текущую версию PostgreSQL, установленную в вашей системе.
Подключение к серверу PostgreSQL через pgAdmin#
Вторым способом подключения к базе данных является использование приложения pgAdmin, благодаря которому вы можете взаимодействовать с сервером баз данных PostgreSQL через интуитивно понятный пользовательский интерфейс.
Далее мы покажем, как подключиться к базе данных, используя приложение с графическим интерфейсом pgAdmin.
Во-первых, запустите приложение pgAdmin.
Приложение pgAdmin 4 будет запущено в веб-браузере, как показано на картинке ниже:
Во-вторых, для создания сервера, щёлкните правой кнопкой мыши по узлу Servers и выберите меню Create > Server…
В-третьих, введите имя сервера, например, PostgreSQL и нажмите на вкладку Connection:
В-четвёртых, введите имя хоста и пароль для пользователя postgres, после чего щёлкните по кнопке Save:
В-пятых, щёлкните по узлу Servers, чтобы открыть иерархию сервера. Изначально, PostgreSQL содержит базу данных, которая называется postgres, как показано ниже:
В-шестых, откройте инструмент запросов, выбрав пункт меню Tool > Query Tool или нажав на иконку молнии.
В-седьмых, введите запрос в Query Editor, затем нажмите на кнопку Execute, после чего вы увидите результат запроса, отображающийся во вкладке DataOutput:
Подключение к серверу баз данных PostgreSQL через другие приложения#
Любое приложение, поддерживающее ODBC или JDBC, может подключиться к серверу баз данных PostgreSQL. К тому же, если вы разрабатываете приложение, которое использует соответствующий драйвер, оно также сможет подключиться к серверу баз данных PostgreSQL.
Замечательно! Вы научились подключаться к серверу баз данных PostgreSQL, используя различные клиентские инструменты, такие как psql и pgADMIN. Следующим шагом мы ознакомимся с примером базы данных PostgreSQL, которую вы сможете использовать для изучения и практики.
PostgreSQL beginner guide — connecting, remote access, psql CLI and troubleshooting connection
First time with PostgreSQL? Look how to move around running PostgreSQL database.
10 mins read Tested on: PostgreSQL 12
Configure remote access — listen address
By default after installation and creating database cluster PostgreSQL will listen only on localhost. No remote access will be allowed.
To change listen address we have to configure parameter in postgresql.conf
Check PGDATA — after -D parameter:
Locate the file:
Change in postgresql.conf parameter listen_addresses to your server IP or * to listen on all IP’s available on server:
Restart PostgreSQL to apply changes — you can do that with systemctl from root os user service or with pg_ctl -D PGDATA restart from postgres os user:
Check whre PostgreSQL is listening now:
Configure remote access — pg_hba.conf
PostgreSQL instance has got restricted access by pg_hba.conf file(host based authentication file).
We can provide in it information from which ADDRESS to which DATABASE on which USER by what METHOD we allow connecting. Additionaly we have to provide TYPE of connection.
This file resides in same place where postgresql.conf (we can alter this behavior by setting pg_hba parameter in postgresql.conf ):
- local — socket connection — needed to connect from shell on database server
- host — standard TCP/IP connection over the network — bnost SSL and no SSL
- hostssl — TCP/IP connection but only with SSL
- hostnossl — TCP/IP only without SSL
- hostgssenc — TCP/IP only GSSAPI
- hostnogssenc — TCP/IP only without GSSAPI
With DATABASE we can specify database name or use special value sameuser if database name should be same as name of user that is connecting.
With USER we can specify user or role — role name should be preceded by + sign.
ADDRESS field could be — hostname, IP range in CIDR format or special words:
- samehost — which correspond to all IP adresses of database server
- samenet — which correspond to all IP in database server subnet
With METHOD field we can set one of authentication methods — most important ones are:
- trust — allow connection without password — moslty set for local connections from database server itself
- reject — reject connections
- md5 — allow connections after getting from user password — encrypted
- password — allow connection after getting plain password — DO NOT USE in untrusted networks — better -> never use this option
- ldap — getting account authorization data from LDAP server
In DATABASE and USER fields you can specify special word all if you don’t want to create any restrictions here.
There can be situation when we must use additional field named auth-options for specyfying details for example for hostssl connection type. This topic will be covered in another post.
Sample pg_hba record — allow all users connect to any DB from all IP addresses — only with password
Add in pg_hba.conf :
Reload(online operation) PostgreSQL that it can use pg_hba.conf changes:
Connecting to PostgreSQL
Local from server
It will work without password because we have trust in pg_hba.conf for local connections:
Remote machine
Default URI syntax — you can connect like this:
psql postgresql://user:passwd@host:5432/dbame
or by more common method:
Connect to remote database from psql with connections details provided in parameters(it will ask for password because of md5 method in pg_hba.conf for connections from 0.0.0.0/0 ):
We can also use parameter -U to specify username different than OS username we currently are using.
Also all this parameters can be taken from shell variables which names are self descriptive — if we set all of them we can just use plain psql command to connect:
Настройка и начало работы с PostgreSQL
В прошлый раз я познакомил тебя с основами реляционных баз данных и SQL. Теперь пришло время настроить рабочую среду. Ты будешь использовать PostgreSQL в качестве своей СУБД, поэтому для начала ее нужно скачать и установить.
Загрузка и установка PostgreSQL
PostgreSQL поддерживает все основные операционные системы. Процесс установки прост, поэтому я постараюсь рассказать о нем как можно быстрее.
Для Windows и Mac ты можешь загрузить установщик с веб-сайта EDB.
EDB больше не предоставляет пакеты для систем GNU/Linux. Вместо этого они рекомендуют вам использовать диспетчер пакетов твоего дистрибутива.
Установщики включают в себя разные компоненты.
Вот самые важные из них:
- Сервер PostgreSQL (очевидно)
- pgAdmin, графический инструмент для управления базами данных
- Менеджер пакетов для загрузки и установки дополнительных инструментов и драйверов
Windows
Скачав установщик, запусти его как любой другой исполняемый файл. Процесс довольно прямолинеен, но некоторые вещи все же заслуживают внимания.
Диалоговое окно «Выбрать компоненты» позволяет выборочно устанавливать компоненты. Если у тебя нет веской причины что-то менять — оставляй все как есть.
По умолчанию PostgreSQL создает суперпользователя с именем postgres (воспринимай его как учетную запись администратора сервера базы данных).
Во время установки тебе нужно будет указать пароль для суперпользователя (root).
Позже ты сможешь создать других пользователей и назначать им отдельные доступы и роли. Мы вернемся к этому позже, а сейчас тебе понадобится учетная запись суперпользователя, чтобы начать использовать СУБД.
Чтобы запустить сервер разработки на твоем компьютере или localhost , необходимо назначить ему порт.
Порт по умолчанию — 5432. Если ты устанавливаешь PostgreSQL впервые, то он скорее всего свободен. Если окажется, что этот порт уже занят другим экземпляром PostgreSQL, ты можешь указать другое значение, например 5433.
После завершения установки ты сможешь запустить SQL Shell, поставляемый с Postgres.
Шаг за шагом ты выберешь сервер, какую базу данных использовать, порт, имя пользователя и пароль.
Используй данные, которые ты вводил на предыдущих шагах.
Поздравляю! Настройка для Windows завершена, и скоро мы начнем писать первые SQL запросы.
Ниже список вариантов установки для других операционных систем.
macOS
Для macOS у тебя есть разные варианты. Можно скачать установщик с сайта EDB и запустить его.
Кроме того, можно использовать Postgres.app , простое приложение для macOS.
После запуска у тебя появится сервер PostgreSQL, готовый к использованию. Завершить работу сервера можно просто закрыв приложение.
Кроме того, ты также можете использовать Homebrew , менеджер пакетов для macOS.
GNU/Linux
Ты можешь найти PostgreSQL в репозиториях большинства дистрибутивов Linux. Установить его можно одним щелчком мыши из выбранного графического диспетчера пакетов.
Альтернативно, можно использовать установку через терминал. Ты можешь обратиться к документации твоего дистрибутива для получения дополнительных сведений.
Ubuntu
Fedora
openSUSE
Запуск оболочки PostgreSQL
После установки PostgreSQL, нужно запустить оболочку(shell), с помощью которой ты получишь возможность управлять базой данных.
Открой терминал и введи:
psql — это оболочка Postgres, аргумент -U используется для указания пользователя.
Поскольку ты еще не создавал других пользователей, ты войдешь в систему как суперпользователь postgres .
После этого нужно будет ввести пароль суперпользователя, который ты выбрал во время установки.
Как только пароль установлен, база данных PostgreSQL готова к работе!
Если сервер PostgreSQL по какой-то причине не запускается, можешь попробовать запустить его вручную.
Понимание модели клиент-сервер
Я уже упоминал PostgreSQL Server как важный компонент базы данных. Но что такое сервер в этом контексте и зачем он нам нужен?
Для начала тебе необходимо понимать модель клиент-сервер.
Почти все СУБД (PostgreSQL, MySQL и другие) следуют клиент-серверной модели. В ней база данных находится на сервере, и клиент отправляет запросы на сервер, который их обрабатывает.
Под клиентом здесь подразумевается бекэнд нашего приложения, а запросы в — это SQL операции, такие как SELECT, INSERT, UPDATE и DELETE.
Для разработки любого бекэнда, тебе нужен локальный сервер для экспериментов и тестирования. Этот локальный сервер аналогичен удаленному, но работает прямо на твоем компьютере.
С точки зрения клиента удаленный и локальный сервер идентичны. После разработки и тестирования ты можешь заставить свой продукт взаимодействовать с удаленным сервером вместо локального, просто изменив пару параметров.
Некоторые базы данных не используют эту модель, например SQLite, которая хранит все в простом файле на диске. Это хорошо работает для небольших приложений, но для большинства реальных приложений тебе понадобится архитектура клиент-сервер.
Мета-команды PostgreSQL
Теперь, когда ты все настроил и готов приступить к работе с базой данных, осталось разобрать несколько мета-команд. Это не SQL запросы, а команды специфичные для PostgreSQL.
В других системах управления базами данных есть их аналоги, но их синтаксис немного отличается.
Всем мета-командам предшествует обратная косая черта \ , за которой следует фактическая команда.
Список всех баз данных
Чтобы получить список всех баз данных на сервере, ты можешь использовать команду \ l .
Ввод этой мета-команды в оболочке Postgres выведет:
Это список всех имеющихся баз данных и служебная информация, такая как владелец базы данных, кодировка и права доступа.
На данный момент мы пока ничего не создали, а базы данных которые ты видишь на экране — создаются по умолчанию при установке Postgres.
- postgres — это просто пустая база данных.
- «template0» и «template1» — это служебные базы данных, которые служат шаблоном для создания новых баз.
Тебе пока не стоит беспокоиться о них. Если хочешь изучить все детали, то проверь официальную документацию.
Подключаемся к базе данных PostgreSQL
Некоторые команды SQL требуют, чтобы ты сначала вошел в базу данных (например, для создания новой таблицы). Ты можешь выбрать, в какую базу данных входить, при запуске SQL Shell.
Когда ты находишься внутри оболочки (shell), то можешь использовать команду \c (или \connect ), за которой следует имя базы данных. Если бы у тебя была другая база данных под названием hello_world , то подключиться к ней можно было бы так:
Полностью в терминале у тебя получится что-то такое:
Обрати внимание, что приглашение оболочки изменилось с postgres на hello_world . Это значит, что теперь ты подключен к базе данных hello_world , а не postgres .
Получить список всех таблиц в базе данных
Как и в случае со списком существующих баз данных, ты можешь получить список таблиц внутри конкретной базы данных с помощью команды \dt .
Перед выполнением этой команды вам необходимо войти в базу данных.
Предположим, ты уже находишься внутри базы hello_world , и в ней есть таблица с именем my_table . Набрав \dt , ты получишь следующее:
Ты можешь увидеть имя таблицы и некоторую другую информацию, такую как схема (мы обсудим схемы в более сложных руководствах) и владельца.
Владелец (owner) — это пользователь, который создал таблицу.
Если ты создаешь других пользователей и используешь их для создания таблиц, то в последнем столбце будут именно они.
Список пользователей и ролей
Как ты уже знаешь, при установке Postgres создается суперпользователь с именем postgres . Список всех пользователей базы данных можно вывести на экран используя команду \dg .
Обрати внимание, что первый столбец называется — роль (role name). И весь вывод на экран называется “список ролей” (List of roles), а не список пользователей.
В PostgreSQL пользователи и роли практически одинаковы.
У ролей есть атрибуты, которые определяют их разрешения, такие как создание баз данных или даже создание других новых ролей.
Любая роль с атрибутом LOGIN может рассматриваться, как пользователь.
Здесь мы видим только одну роль, суперпользователя по умолчанию.
В реальном мире все будет иначе, потому что использовать только суперпользователя все время опасно. Вместо этого создают другие роли с меньшими привилегиями. Это гарантирует, что никто не совершит нежелательных действий по ошибке.
Если у одной из ролей есть доступ только на чтение данных, то с помощью этой роли будет невозможно удалить таблицу или поле.
Твой первый SQL оператор
Наконец, мы все настроили и готовы к работе и знаем основные мета-команды, специфичные для PostgreSQL.
Теперь приступим к изучению языка запросов SQL.
Я покажу тебе несколько базовых примеров, чтобы разобраться в структурированном языке запросов и получить представление о SQL. А более подробно мы рассмотрим операции CRUD это в следующей статье.
Создание новой базы данных
Первое, что тебе нужно узнать при изучении баз данных, — это как создать базу данных. Создать базу можно сделать с помощью команды CREATE DATABASE , за которой следует имя базы данных:
Команды и ключевые слова SQL обычно пишутся в верхнем регистре.
На самом деле это не является обязательным требованием, и обычно они нечувствительны к регистру.
То есть ты мог бы написать
И все сработало бы нормально.
Но при написании операторов SQL обычно предпочтительнее прописные буквы. Это хорошая практика, потому что она может помочь тебе визуально отличить ключевые слова SQL от других частей оператора, таких как имена таблиц и столбцов.
Заметь, что все стандартные команды в PostgreSQL должны заканчиваться точкой с запятой ; . Это часть стандарта.
Для мета-команд PostgreSQL точка с запятой не нужна.
Создание таблиц
После того как ты создал новую базу данных, можно приступать к созданию таблиц.
Но, для начала, подключимся к новой базе данных с помощью команды \c , за которой следует имя базы данных:
Теперь, когда ты подключился к базе данных (обратите внимание, что приглашение оболочки SQL теперь включает имя активной базы данных), ты готов создать свою первую таблицу.
Таблица создается с помощью команды CREATE TABLE, за которой следует список столбцов таблицы и их типы данных в круглых скобках:
Это создаст таблицу под названием products , которая содержит 3 столбца:
- id типа INT (целое число)
- name типа TEXT (строка)
- quantity также типа INT
После создания таблицы перейдем к добавлению данных.
Вставка данных в таблицы PostgreSQL
Чтобы добавить данные в таблицу, используют команду INSERT INTO следующим образом:
Посмотрим на команду INSERT INTO подробнее:
- Команда INSERT INTO означает, что вы собираетесь вставить новые данные
- products — это имя таблицы в базе данных, в которую ты хочешь вставить данные
- (id, name, quantity) — это список столбцов в нашей таблице, разделенных запятыми. Тебе не нужно указывать все столбцы (иначе какой в смысл?). В некоторых случаях вы хотите выборочно вставлять данные в некоторые столбцы. Остальные столбцы будут автоматически заполнены значениями по умолчанию.
- VALUES (1, ‘first product’, 20) — это фактические данные, которые будут вставлены в таблицу. «1» — это id , «first product» — это name , «20» — это quantity .
Выборка данных из SQL таблицы
Теперь, когда ты добавил в таблицу первую запись, ты можешь использовать SQL для получения содержимого таблицы.
Выборка данных осуществляется с помощью команды SELECT , и это выглядит следующим образом:
Мы используем команду SELECT . За ней следует список столбцов, которые мы хотим получить.
Затем мы используем команду FROM , чтобы указать, из какой таблицы брать данные. На этот раз это таблица products .
Для ситуаций когда ты хочешь выбрать все столбцы которые есть в таблице, ты можешь поставить звездочку вместо списка полей.
Звездочка означает: выбрать все столбцы. Результат останется прежним.
Ты должен обратить внимание на то, как команда SELECT выбирает столбцы и строки. Столбцы указываются в виде списка и разделяются запятыми. Затем команда переходит к выбору запрошенных строк.
Если условия не указаны (как в этом случае), будут выбраны все строки в таблице.
Позже мы увидим, как использовать условия с командой WHERE для создания эффективных запросов.
Обновление данных в PostgreSQL
Представь, что ты запустил свое потрясающее приложение для магазина и получили первый заказ на один из продуктов.
Первое, что нужно сделать — это обновить доступное количество в вашем инвентаре, чтобы в дальнейшем у вас не возникли проблемы с отсутствием товара на складе.
Для обновления данных ты можешь использовать команду UPDATE :
Давайте разберемся с тем как работает UPDATE .
Начинаем мы с ключевого слова UPDATE , за которым следует имя таблицы.
Затем мы используем SET , чтобы установить новые значения для наших столбцов.
После SET — пишем имена столбцов, которые хотим обновить.
За ними — знак равенства и новое обновленное значение.
Также ты можешь обновить сразу несколько столбцов, разделив их запятыми:
Но стоп, какие строки обновляются этой командой?
Ты уже должны были догадаться об этом. Чтобы указать, какие строки следует обновить новыми значениями, мы используем команду WHERE , за которым следует условие.
В этом случае мы сопоставляем строки, используя их столбец id , и обновляем строку с id 1.
Удаление данных из SQL таблицы
Теперь рассмотрим случай, когда ты прекратил продажу определенного продукта и захотел полностью удалить его из своей базы данных.
Для этого можно использовать команду DELETE :
Как и при обновлении данных, чтобы определить, какие именно строки мы хотим удалить, нам нужно условие WHERE .
Удаление таблиц в PostgreSQL
Если вдруг ты решил изменить структуру базы и для этого нужно удалить всю таблицу, то тебе подойдет команда DROP TABLE :
Это приведет к удалению всей таблицы products из базы данных.
Будь очень осторожен с командой DELETE !
Я бы не позавидовал тому, кто “случайно” удалит не ту таблицу из базы данных.
Удаление баз данных PostgreSQL
Точно так же ты можешь удалить из системы всю базу данных:
Заключение
Поздравляю, у тебя все получилось!
Ты установил и запустили PostgreSQL. Ты изучил основные команды SQL и проделали с ними несколько интересных вещей.
Эти несколько простых команд — основа, которую ты будешь использовать большую часть времени при взаимодействии с базами данных, поэтому тебе следует пойти и потренироваться и изучить самостоятельно. В следующий раз мы погрузимся глубже и обсудим Базы данных, роли и таблицы в PostgreSQL
PostgreSQL
PostgreSQL (произносится «Пост-Грэс-Кью-Эл») — свободная объектно-реляционная система управления базами данных (СУБД).
Существует в реализациях для множества UNIX -подобных платформ, включая AIX, различные BSD-системы, HP-UX, IRIX, Linux , macOS, Solaris/OpenSolaris, Tru64, QNX, а также для Microsoft Windows .
Установка
- Установка в Ubuntu
- Установка в Centos
Проверка версии
После установки проверьте версию установленного PostgreSQL
postgres (PostgreSQL) 9.2.24
psql (PostgreSQL) 13.7
Проверить статус
systemctl status -l postgresql
Вместо 11 укажите вашу версию
systemctl status -l postgresql- 11
Возможны варианты inactive, failed, active
● postgresql-11.service — PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: inactive (dead) Docs: https://www.postgresql.org/docs/11/static/
● postgresql-11.service — PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: failed (Result: exit-code) since Tue 2022-09-20 12:01:41 EEST; 6s ago Docs: https://www.postgresql.org/docs/11/static/ Process: 12952 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir $
● postgresql-11.service — PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: active (running) since Tue 2022-09-20 12:19:45 EEST; 7s ago Docs: https://www.postgresql.org/docs/11/static/ Process: 15899 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir $
Перезапуск PostgreSQL
systemctl restart postgresql
Для более современных версий, например 11
systemctl restart postgresql-11
Зайти в Postgres
Когда postgresql-11 находится имеет статус active можно зайти в postgres выполнив
sudo su — postgres
Last login: Fri Mar 03 23:34:13 EEST 2023 on pts/0 -bash-4.2$
psql (11.17) Type "help" for help. postgres=#
После установки
Расположение файлов с настройками, например, postgresql.conf можно получить выполнив
-bash-4.2$ su — postgres -c «psql -c 'SHOW config_file;'»
В PostgreSQL 9 результат будет таким:
Password: config_file ————————————- /var/lib/pgsql/data/postgresql.conf (1 row)
В более современных версиях результат будет содеражть номер версии
total 48 drwx——. 7 postgres postgres 67 Jun 9 22:54 base drwx——. 2 postgres postgres 4096 Jun 9 23:19 global drwx——. 2 postgres postgres 18 Jun 9 13:54 pg_clog -rw——-. 1 postgres postgres 4371 Jun 10 01:23 pg_hba.conf -rw——-. 1 postgres postgres 1636 Jun 9 13:54 pg_ident.conf drwx——. 2 postgres postgres 58 Jun 10 00:00 pg_log drwx——. 4 postgres postgres 36 Jun 9 13:54 pg_multixact drwx——. 2 postgres postgres 18 Jun 9 14:14 pg_notify drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_serial drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_snapshots drwx——. 2 postgres postgres 25 Jun 10 02:06 pg_stat_tmp drwx——. 2 postgres postgres 18 Jun 9 13:54 pg_subtrans drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_tblspc drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_twophase -rw——-. 1 postgres postgres 4 Jun 9 13:54 PG_VERSION drwx——. 3 postgres postgres 60 Jun 9 13:54 pg_xlog -rw——-. 1 postgres postgres 19889 Jun 10 01:43 postgresql.conf -rw——-. 1 postgres postgres 45 Jun 9 14:14 postmaster.opts -rw——-. 1 postgres postgres 92 Jun 9 14:14 postmaster.pid
Подключение к БД
Чтобы получить доступ к базам данных можно воспользоваться стандартной консолью psql.
Windows:
Если Вы хотите подключиться из bash в Windows не забудьте добавить местоположение psql.exe (у меня это C:\Program Files\PostgreSQL\12\bin) в PATH
Как это делается описано в статье PATH
psql.exe -h localhost -p 5433 -U postgres
Linux:
sudo su — postgres
psql
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1)) Type «help» for help.
Если БД на локальном хосте
psql -h 127.0.0.1 -d DATABASENAME -U DATABASEUSERNAME
Получить данные о подключении
You are connected to database «postgres» as user «postgres» via socket in «/var/run/postgresql» at port «5432».
Список существующих БД
Список уже существующих на сервере баз данных можно получить командой
Name | Owner | Encoding | Collate | Ctype | Access privileges —————+———-+———-+—————————-+—————————-+———————— urn.su | postgres | UTF8 | English_United States.1252 | English_United States.1252 | topbicyle.ru | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | English_United States.1252 | English_United States.1252 | (6 rows)
Обратите внимание на столбцы Encoding, Collate и Ctype. Знание кодировок может Вам пригодиться в будущем.
Создать базу данных
Создадим базу данных heihei_ru_db с кодировкой utf8
CREATE DATABASE «heihei_ru_db» WITH OWNER «postgres» ENCODING 'UTF8';
Создадим базу данных heihei с кодировкой utf8 и укажем значения для Collate, Ctype и Template
CREATE DATABASE «heihei» WITH OWNER «postgres» ENCODING
'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0;
Проверим, что получилось
Name | Owner | Encoding | Collate | Ctype | Access privileges —————+———-+———-+—————————-+—————————-+———————— heihei | postgres | UTF8 | C | C | urn.su | postgres | UTF8 | English_United States.1252 | English_United States.1252 | topbicyle.ru | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | English_United States.1252 | English_United States.1252 | (7 rows)
Если я в bash сделаю
То получу сразу четыре доступные кодировки
C
C.UTF-8
en_US.utf8
POSIX
Но создать БД, например, с en_US.utf8 у меня не получается
CREATE DATABASE «heihei» WITH OWNER «postgres» ENCODING 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8' TEMPLATE = template0;
Приводит к ошибке
ERROR: invalid locale name: «en_US.UTF-8»
Если Вы знаете как решить эту проблему — отпишитесь в комментариях к статье.
UPD: Выполнил такую же команду в Ubuntu и база успешно создана
CREATE DATABASE «new_db» WITH OWNER «postgres» ENCODING 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE = template0;
Удалить базу данных
Чтобы удалить базу данных воспользуйтесь командой
DROP DATABASE db_name;
Проверить кодировки
Проверить кодировку сервера можно командой
server_encoding —————— UTF8 (1 row)
Проверить кодировку клиента
client_encoding —————— WIN1252 (1 row)
Когда вы находитесь в интерактивном режиме PostgreSQL в консоле появляется префикс
Где db это название текущей базы данных
Показать адрес текущей директории
Вернуться в PostgreSQL можно выполнив
Зайти в БД
Чтобы начать работу с базой данных нужно знать её имя, например пусть Вам нужна база даныых, которую назвали просто 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
Чтобы посмотреть список таблиц введите
List of relations
Schema | Name | Type | Owner
———+———+——-+———-
public | person | table | postgres
(1 row)
Чтобы посмотреть всю таблицу person уже можно использовать стандартный
SELECT * FROM person;
Список активных подключений к БД
Изучить подключения к БД можно выполнив
SELECT * FROM pg_stat_activity WHERE datname = 'ИМЯ_БД';
Переименовать БД
ALTER DATABASE старое_имя RENAME TO новое_имя;
Список существующих БД можно получить командой \l
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ————+———-+———-+————-+————-+———————— db01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)
Если нужно переименовать БД db01 первым делом нужно выйти из неё.
Для этого можно зайти в другую БД, например, postgres.
Изучить подключения к БД можно выполнив
SELECT * FROM pg_stat_activity WHERE datname = 'db01';
Непосредственно переименование выполняется командой
ALTER DATABASE db01 RENAME TO db02;
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ————+———-+———-+————-+————-+———————— db02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | … (4 rows)
Если к базе данных кто-то подключен. Например, вы сами из другого терминала
You are now connected to database "db02" as user "postgres".
postgres=# SELECT datname, pid, backend_start, state FROM pg_stat_activity WHERE datname = 'db02';
datname | pid | backend_start | state ———+——-+——————————-+——- db02 | 21238 | 2023-03-03 23:34:13.503073+03 | idle (1 row)
Все подключения можно закрыть выполнив
postgres=# SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = 'db02';
pg_terminate_backend ———————- t (1 row)
Достать значение из БД
Пример — запишем значение из базы данных в файл с помощью echo
echo $ ( psql -h localhost -d db_name -U db_user -c " SELECT host_certificate FROM hosts WHERE ( addresses= '192.168.56.200' ) ; " ) > raw_cert
Содержание базы данных
В предыдущем параграфе мы создали в базе данных heihei таблицу booking_sites
Убедимся в том, что скрипт отработал удачно
Перейдём в базу данных heihei и проверим создалась ли таблица
You are now connected to database «heihei» as user «postgres».
List of relations Schema | Name | Type | Owner ———+—————+——-+———- public | booking_sites | table | postgres (1 row)
Описание таблицы
Если таблица создана какое-то время назад. Вы могли уже забыть, какие конкретно столбцы она содержит.
Для описания таблицы используется команда \d
Table «public.booking_sites» Column | Type | Collation | Nullable | Default —————-+————————+————+———-+——————————————- id | bigint | | not null | nextval('booking_sites_id_seq'::regclass) company_name | character varying(50) | | not null | origin_country | character varying(50) | | not null | age | character varying(3) | | not null | date_of_birth | date | | not null | website_url | character varying(50) | | | Indexes: «booking_sites_pkey» PRIMARY KEY, btree (id)
UPDATE: Изменение значения
Изменим дату годности сертификата в таблице certificates
Изучим текущую ситуацию
aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070');
aodb=> UPDATE certificates SET not_after='2022-07-22 16:39:59+03' WHERE (serial='2402020070');
aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070263697801');
Изменение таблицы
Когда нужно обновить название столбца таблицы используем команду ALTER
Предположим полю website_url не хватает 50 символов. Увеличим длину до 60.
ALTER TABLE booking_sites ALTER column website_url TYPE VARCHAR(60);
Проверим изменилась ли таблица
Table «public.booking_sites» Column | Type | Collation | Nullable | Default —————-+————————+————+———-+——————————————- id | bigint | | not null | nextval('booking_sites_id_seq'::regclass) company_name | character varying(50) | | not null | origin_country | character varying(50) | | not null | age | character varying(3) | | not null | date_of_birth | date | | not null | website_url | character varying(60) | | | Indexes: «booking_sites_pkey» PRIMARY KEY, btree (id)
Выйти из консоли
Узнать текущую схему
current_schema —————- public (1 row)
Переменные окружения
$PGDATA — адрес директории data
Разрешить внешние подключения
Для PostgreSQL 9
Для более современных версий PostgreSQL нужно указать директорию, например 11
# — Connection Settings — # listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) listen_addresses = '*' port = 5432 # (change requires restart)
host all all 0.0.0.0/0 md5
Включить SSL
openssl genrsa -des3 -out server.key 1024
openssl rsa -in server.key -out server.key
chmod 400 server.key
chown postgres.postgres server.key
openssl req -new -key server.key -days 3650 -out server.crt -x509
cp server.crt root.crt
# — SSL — ssl = on #ssl_ca_file = '' ssl_cert_file = '/var/lib/pgsql/11/data/server.crt' #ssl_crl_file = '' ssl_key_file = '/var/lib/pgsql/11/data/server.key'
Подпишитесь на Telegram канал @aofeed чтобы следить за выходом новых статей и обновлением старых