34 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Подключение к БД PostgreSQL из консоли

Содержание

Подключение к серверу базы данных PostgreSQL

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

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

Подключение к серверу баз данных PostgreSQL через psql

psql — это интерактивная терминальная программа, предоставляемая PostgreSQL. Она позволяет вам взаимодействовать с сервером баз данных PostgreSQL, например, выполнять операторы SQL и управлять объектами баз данных.

Следующие шаги показывают, как подключиться к серверу баз данных PostrgreSQL через программу 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

Вся работа с PostgreSQL осуществляется под пользователем postgres.

Работать с PosgreSQL можно как в интерактивном режиме, так и из командной строки. Программа — psql.

Основные команды PostgreSQL в интерактивном режиме:

  • connect db_name – подключиться к базе с именем db_name
  • du – список пользователей
  • dp (или z) – список таблиц, представлений, последовательностей, прав доступа к ним
  • di – индексы
  • ds – последовательности
  • dt – список таблиц
  • dt+ — список всех таблиц с описанием
  • dt *s* — список всех таблиц, содержащих s в имени
  • dv – представления
  • dS – системные таблицы
  • d+ – описание таблицы
  • o – пересылка результатов запроса в файл
  • l – список баз данных
  • i – читать входящие данные из файла
  • e – открывает текущее содержимое буфера запроса в редакторе (если иное не указано в окружении переменной EDITOR, то будет использоваться по умолчанию vi)
  • d “table_name” – описание таблицы
  • i запуск команды из внешнего файла, например i /my/directory/my.sql
  • pset – команда настройки параметров форматирования
  • echo – выводит сообщение
  • set – устанавливает значение переменной среды. Без параметров выводит список текущих переменных (unset – удаляет).
  • ? – справочник psql
  • help – справочник SQL
  • q (или Ctrl+D) – выход с программы
Читать еще:  УЦН Ростелеком — Устранение Цифрового Неравенства

Работа с PostgreSQL из командной строки:

  • -c (или –command) – запуск команды SQL без выхода в интерактивный режим
  • -f file.sql — выполнение команд из файла file.sql
  • -l (или –list) – выводит список доступных баз данных
  • -U (или –username) – указываем имя пользователя (например postgres)
  • -W (или –password) – приглашение на ввод пароля
  • -d dbname — подключение к БД dbname
  • -h – имя хоста (сервера)
  • -s – пошаговый режим, то есть, нужно будет подтверждать все команды
  • –S – однострочный режим, то есть, переход на новую строку будет выполнять запрос (избавляет от ; в конце конструкции SQL)
  • -V – версия PostgreSQL без входа в интерактивный режим

Примеры:

psql -U postgres -d dbname -c «CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);» — выполнение команды в базе dbname.

psql -d dbname -H -c «SELECT * FROM my» -o my.html — вывод результата запроса в html-файл.

Утилиты (программы) PosgreSQL:

  • createdb и dropdb – создание и удаление базы данных (соответственно)
  • createuser и dropuser – создание и пользователя (соответственно)
  • pg_ctl – программа предназначенная для решения общих задач управления (запуск, останов, настройка параметров и т.д.)
  • postmaster – многопользовательский серверный модуль PostgreSQL (настройка уровней отладки, портов, каталогов данных)
  • initdb – создание новых кластеров PostgreSQL
  • initlocation – программа для создания каталогов для вторичного хранения баз данных
  • vacuumdb – физическое и аналитическое сопровождение БД
  • pg_dump – архивация и восстановление данных
  • pg_dumpall – резервное копирование всего кластера PostgreSQL
  • pg_restore – восстановление БД из архивов (.tar, .tar.gz)

Примеры создания резервных копий:

Создание бекапа базы mydb, в сжатом виде

Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

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

Создание резервной копии с сжатием в gz

Список наиболее часто используемых опций:

  • -h host — хост, если не указан то используется localhost или значение из переменной окружения PGHOST.
  • -p port — порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.
  • -u — пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.
  • -a, —data-only — дамп только данных, по-умолчанию сохраняются данные и схема.
  • -b — включать в дамп большие объекты (blog’и).
  • -s, —schema-only — дамп только схемы.
  • -C, —create — добавляет команду для создания БД.
  • -c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).
  • -O — не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).
  • -F, —format — выходной формат дампа, custom, tar, или plain text.
  • -t, —table=TABLE — указываем определенную таблицу для дампа.
  • -v, —verbose — вывод подробной информации.
  • -D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду pg_dumpall.

Восстановление таблиц из резервных копий (бэкапов):

psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
pg_restore — восстановление сжатых бекапов (tar);

Восстановление всего бекапа с игнорированием ошибок

Восстановление всего бекапа с остановкой на первой ошибке

Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

Восстановление резервной копии БД, сжатой gz

Подключение к базе данных в кластере PostgreSQL

К хостам кластера Managed Service for PostgreSQL можно подключиться:

  • Через интернет, если вы настроили публичный доступ для нужного хоста. К таким хостам подключиться можно только используя SSL-соединение.
  • С виртуальных машин Яндекс.Облака, расположенных в той же виртуальной сети. Если к хосту нет публичного доступа, для подключения с таких ВМ SSL-соединение использовать необязательно.

Примечание

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

Настройка SSL-сертификата

PostgreSQL-хосты с публичным доступом поддерживают только соединения с SSL-сертификатом. Подготовить сертификат можно так:

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

Подключиться к БД с помощью команды psql .

Пример команды с заполненным FQDN хоста вы можете посмотреть в консоли управления, нажав на кнопку Подключиться на странице кластера.

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

Если вам не нужно шифровать трафик внутри виртуальной сети при подключении к БД, то можно подключаться к базе без SSL-соединения. Передайте параметр sslmode со значением disable :

Автоматический выбор хоста-мастера

С библиотекой libpq

Чтобы гарантированно подключиться к хосту-мастеру, укажите FQDN всех хостов кластера в аргументе host и передайте параметр target_session_attrs=read-write . Этот параметр поддерживается библиотекой libpq начиная с версии 10:

Чтобы обновить версию библиотеки, которую использует утилита psql :

Для дистрибутивов Linux на основе Debian — установите пакет postgresql-client-10 (например, через apt-репозиторий).

Для ОС, использующих RPM-пакеты, дистрибутив PostgreSQL доступен в yum-репозитории.

Адреса всех хостов в кластере БД можно найти на странице нужного кластера в консоли управления.

С драйвером, поддерживающим только один хост

Если ваш драйвер для подключения к базе данных не позволяет передавать несколько хостов в строке подключения (например,
pgx в Go), вы можете подключаться на специальный хост вида c- .rw.mdb.yandexcloud.net .

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

Как настроить?

Всё о Интернете, сетях, компьютерах, Windows, iOS и Android

Подключение к БД PostgreSQL из консоли


Есть такие инструкции, которые я делаю для себя в качестве шпаргалки. Это одна из таких. Если она поможет кому-то ещё — буду только рад. Я покажу как сделать подключение к базе данных PostgreSQL из консоли сервера. Конечно, эта БД не так распространена, как всем известная MySQL. Но благодаря некоторым существенным преимуществам, она тоже используется достаточно часто и многим системным администраторам больше нравится именно Постгрес. Мне часто приходится работать и с тем и с другим, но чаще, конечно же с Майскул.

Как подключиться к БД PostgreSQL:

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

Таким образом Вы сможете запустить консоль PostgreSQL с правами суперпользователя — postgres.

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

Все внутренние команды, не являющиеся операторами БД, начинаются с косой черты — «».

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

Выбрать базу данных можно так:

Здесь dbname — это имя нужно базы данных.

Получаем список таблиц в текущей базе данных PostgreSQL:

Если добавить в конце значок «+», чтобы команда выглядела так: dt+, то будет выведен расширенный список таблиц с описанием.

Структура таблицы table:

Переключение между режимами вывода:

Чтение входящих данных из файла:

Вывод результатов запроса в файл:

Настройка параметров форматирования:

Установка переменных среды:

Справка команд SQL:

Выход из консоли:

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

# psql -U postgresql-user -d postgresql-db -f /tmp/droptables

Снятие дампа БД PostgreSQL:

# pg_dump -c -h localhost -U myuser mydatabase > ./mydump.sql

Вот тот же вариант с IP вместо имени хоста:

# pg_dump -c -h 192.168.0.1 -U myuser mydatabase > ./dump.sql

Создание резервной копии с сжатием в gz:

# pg_dump -h localhost -O -F p -c -U myuser mydatabase | gzip -c > mydb.gz

Дамп данных только одной, конкретной таблицы:

# pg_dump -h localhost -U myuser -F c -b -v -t *pay* -f pay_tables.backup mydb

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

# pg_dump -a -t table_name -f file_name database_name

Восстановление из резервной копии:

# cat dump.sql | psql -h localhost mydatabase myuser

Импорт в PostgreSQL делается следующим образом:

Подключение к БД PostgreSQL из консоли

Все команды запускаются под пользователем postgres (postgresql-суперпользователь)

psql -l — список баз данных.

psql -d dbname — подключение к БД dbname.

psql -f file.sql — выполнение команд из файла file.sql.

psql -U postgres -d dbname -c «CREATE TABLE test(some_id serial PRIMARY KEY, some_text text);» — выполнение команды в базе dbname.

psql -d dbname -H -c «SELECT * FROM test» -o test.html — вывод результата запроса в html-файл.

Просмотр списка и путей к конфигурационным файлам

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

c dbname — подсоединение к БД dbname.

l — список баз данных.

dt — список всех таблиц.

d table — структура таблицы table.

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

dt+ — список всех таблиц с описанием.

dt *s* — список всех таблиц, содержащих s в имени.

i FILE — выполнить команды из файла FILE.

o FILE — сохранить результат запроса в файл FILE.

a — переключение между режимами вывода: с/без выравнивания.

Бекап и восстановление таблиц

В PostgreSQL есть две утилиты для бекапа pg_dump и pg_dumpall . pg_dump используется для бекапа одной базы, pg_dumpall для бекапа всех баз и сервера в целом (необходимо запускать под postgresql-суперпользователем).

Создание бекапа базы mydb, в сжатом виде

Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

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

Создание резервной копии с сжатием в gz

Список наиболее часто используемых опций:

-h host — хост, если не указан то используется localhost или значение из переменной окружения PGHOST.

-p port — порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.

-u — пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.

-a, —data-only — дамп только данных, по-умолчанию сохраняются данные и схема.

-b — включать в дамп большие объекты (blog’и).

-s, —schema-only — дамп только схемы.

-C, —create — добавляет команду для создания БД.

-c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).

-O — не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).

-F, —format — выходной формат дампа, custom, tar, или plain text.

-t, —table=TABLE — указываем определенную таблицу для дампа.

-v, —verbose — вывод подробной информации.

-D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду pg_dumpall .

В PostgreSQL есть две утилиты для восстановления базы из бекапа.

  • psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
  • pg_restore — восстановление сжатых бекапов (tar);

Восстановление всего бекапа с игнорированием ошибок

Восстановление всего бекапа с остановкой на первой ошибке

Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C ) и восстановить

Восстановление резервной копии БД, сжатой gz

Начиная с версии 9.2 можно восстановить только структуру таблиц с помощью опции —section

Перенос директории с данным (data directory)

Узнать текущий путь

Создадим новую директорию, назначим пользователя и инициализируем

Теперь надо подправить файл с сервисом, который стартует postgresql

Очищение таблицы tablename и обнуление счетчика с ID.

CASCADE нужен на случай если tablename связана с другой таблицей.

Удаление NULL у поля

pgcli утилита командной строки с авто-дополнениям и подсветкой синтаксиса.

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

Главное меню » Базы данных » База данных PostgreSQL » Как вывести список баз данных и таблиц PostgreSQL с помощью psql

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

В этой статье объясняется, как использовать базы данных и таблицы на сервере PostgreSQL psql.

Листинг баз данных

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

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

Чтобы получить доступ к psqlтерминалу как пользователь «postgres», запустите:

Из терминала psql выполните мета-команду l или list, чтобы вывести список всех баз данных:

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

Сервер PostgreSQL имеет три базы данных, созданные по умолчанию: template0, template1 и postgres. Первые два – это шаблоны, которые используются при создании новых баз данных.

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

Чтобы получить список всех баз данных без доступа к оболочке psql, используйте переключатель -c, как показано ниже:

Другой способ составить список баз данных – использовать следующий оператор SQL:

В отличие от мета-команды l, приведенный выше запрос покажет только имена баз данных:

Таблицы списков

Чтобы получить список всех таблиц конкретной базы данных сначала необходимо подключиться к нему с помощью мета-команды c или connect. Пользователь, вошедший в систему как терминал psql, должен иметь возможность подключаться к базе данных.

Например, чтобы подключиться к базе данных с именем «odoo», вы должны набрать:

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

Вывод будет включать количество таблиц, имя каждой таблицы и ее схему, тип и владельца:

Если база данных пуста, вывод будет выглядеть так:

Для получения информации о размерах таблиц и описаний используйте dt+.

Заключение

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

Не стесняйтесь оставлять комментарии, если у вас есть какие-либо вопросы.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Ссылка на основную публикацию
Статьи c упоминанием слов:
Adblock
detector
×
×