Как подключиться к базе данных postgresql через консоль

Как подключиться к базе данных postgresql через консоль

Консольный клиент psql представляет еще один способ взаимодействия с сервером PostgreSQL. Данная программа также, как и pgAdmin, позволяет выполнять команды языка SQL.

Запустим psql. Программа предложит ввести название сервера, базы данных, порта и пользователя. Эти пункты можно прощелкать, так как для них будут использоваться значения по умолчанию (для сервера — localhost, для базы данных — postgres, для порта — 5432, в качестве пользователя — суперпользователь postres). Далее надо будет ввести пароль для пользователя (по умолчанию пользователя postgres):

И после удачного подключения можно будет отправлять серверу команды через psql.

Теперь создадим базу данных с помощью следующей команды языка SQL:

create database test2;

Для создания базы данных применяется команда create database , после которой указывается название бд. То есть в данном случае название бд — «test2». Причем команда завершается точкой с запятой.

Далее подключимся к этой базе данных для осуществления с ней взаимодействия. Для этого применяется команда \c (сокращение от connect), после которой указывается имя базы данных:

\c test2

Затем создадим в этой базе данных таблицу с помощью команды:

create table users (Id serial primary key, Name character varying(30), Age integer);

Данная команда создает таблицу users, в которой будет три столбца — Id, Name и Age.

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

insert into users (Name, Age) values ('Tom', 33);

И в конце получим добавленные данные:

select * from users;

Стоит отметить, что по умолчанию консоль в Windows поддерживает кодировку CP866, тогда как базы данных могут работать совсем с другой кодировкой, например, 1251. И даже сам клиент psql выводит нам соответствующие сообщения. Кроме того, при получении данных, при выводе информации о базах данных, таблицы и т.д. некоторая информация может отображаться некорректно. В этом случае перед запуском psql надо установить нужную кодировку и затем из консоли запустить программу psql.

Как подключиться к базе данных postgresql через консоль

Если вывод выглядит, как у меня на картинке, то postgresql установлен. Если нет «Repo : installed» , то придется установить. Стоит обратить внимание, на то что, если вы устанавливали posgresql вместе с осью сразу, то в некоторых дистрибутивах за вас уже все сделали и инициализировать базу не нужно, пропускаем эти манипуляции ! Остается только настроить под себя, создать пароль на локального пользователя postgres и создать пароль на администратора базы данных postgres, через утилиту «psql», описание процедуры находится ниже. Вернемся к установке, в консоли от root , вводим :

 #yum install postgresql 

 #yum install qt3-PostgreSQL

установив все зависимости , yum спросит про установить или нет , отвечаете «Y» , пакеты установятся. В самом низу будет виден результат. Вообще , если есть необходимость использовать весь пакет postgresql, то рекомендую установить его так :

 #yum install postgresql* 

 #yum install qt3-PostgreSQL 

В результате будет установлены все пакеты, начинающиеся на имя «postgresql» и пакет «qt3-PostgreSQL». Все делается от пользователя root. Убеждаемся всегда о положительном завершении процедуры установки. Теперь у вас проинсталлирован postgresql, но не стоит спешить его запускать, даже если он и запуститься по команде

 #service postgresql start 

То скорей всего, выдаст ошибку, так как мы еще не создали «template1» базы. От греха подальше перегружаем систему, смотрим, что у нас стартует, выдает ошибки или нет. Убедившись , что установка не внесла проблемных изменений, конфликтов и возвращаемся крутить postgresql дальше. Необходимо инициализировать сервер и базы данных для работы с ними от пользователя postgres.

 #su root вводим пароль пользователя root

Переходим в каталог var/lib/pgsql/data , для этого вводим:

 #cd /var/lib/pgsql/data/ 

 #ls (вывод имеющихся файлов )

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

 #mc /var/lib/pgsql/data/ Переходим в каталог, выделяем все файлы с помощью клавиши "Insert" и нажимаем "F8".

Если «MC» еще не установлен, то рекомендую его проинсталлировать , так как для начинающего пользователя это палка-выручалка номер один . Тем более что установить его не сложно:

 #yum install mc (он вам еще пригодится, особенно если не стоит ни каких Х-ов)

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

 #su postgres 

 bash-4.1$initdb -D /var/lib/pgsql/data 

Получится вывод как на скрине:

Базы данных инициализируются для пользователя «postgres». Не думаю, что вы часто будете сидеть под пользователем «root», это не совсем корректно! После рекомендую запустить «Postgresql» через команду от root:

 #service postgresql start 

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

 #setup 

Установим пароль для локального пользователя «postgres»:

 #passwd postgres 

Пароль рекомендую установить ! И чем сложней, тем лучше, это поможет вам избежать некоторых проблем с безопасностью! )))

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

 #su postgres 

 bash-4.1$psql 

 postgres=#\password postgres 

Не забываем подправить конфиг под себя ! Дело в том, что по умолчанию нет аутентификации локальных пользователей, а так же нет возможности использовать posgresql server для сетевого использования. Локальные пользователи подключаются свободно, что необходимо подправить. Открываем для редактирования конфигурационный файл /var/lib/pgsql/data/pg_hba.conf и приводим его в такой вид , как на картинке:

Теперь локальные пользователи проходят аутентификацию по паролю в md5 . Если необходимо , чтобы к серверу подключались из сети или определенного узла, то добавляем свои правила подобные локальным пользователям. Стоит лишний раз подумать про безопасность соединении к базе, так как если не шифровать трафик, то он спокойно может быть перехвачен. Ну и фаервол (на Федоре это iptables , мощный и могучий, главное держать талмут под рукой и понимать как он работает) стоит под настроить , под доступ к серверу баз данных только с тех хостов , которым он разрешен.

Для более глубокого изучения рекомендую хорошо погуглить данный вопрос, а лучше почитать полную документацию на http://postgresql.ru.net/. С помощью данной статьи вы получаете работоспособный postgresql, а статья используется мной как напоминалака в случаи, когда что то позабылось ). Для соответствующих программ нужно установить соответствующие модули, обычно описаны в readme к программе. Успешной вам настройки и использования pstgresql.

Создание нового пользователя и управление его правами.

Добавить нового пользователя можно командой :

 postgres=#CREATE USER имя_пользователя PASSWORD 'пароль'; (в ответ: «CREATE ROLE», что говорит о положительном результате).

Если есть надобность сделать обычного пользователя «суперпользователем» , то вводим

 postgres=#ALTER USER 'имя_пользователя' SUPERUSER; (в случае успеха получаем ответ ALTER ROLE).

Отменяем права супер пользователя:

 postgres=#ALTER USER 'имя_пользователя' NOSUPERUSER; (в случае успеха получаем ответ ALTER ROLE).

«ALTER USER» очень полезная команда, она позволяет выставить права пользователям, те , которые они заслужили. Далее чуть приведу информации по данному аператору с «postgresql.ru.net», которая понадобится почти каждому, да и вообщем очень рекомендую ознакомиться с содержанием данного сайта, особенно если решили серьезно заняться этим делом ) .

ALTER USER ALTER USER Имя_пользователя [ [ WITH ] option [ . ] ]

SUPERUSER | NOSUPERUSER - Как говорилось выше , дать или отнять прова суперпользователя. CREATEDB | NOCREATEDB - Дать разрешение создавать или запретить создавать базы данных пользователю. CREATEROLE | NOCREATEROLE - Дать пользователю возможность создавать роли доступа. CREATEUSER | NOCREATEUSER - Дать возможность пользователю создавать других пользователей или отнять данную привилегию.

 ALTER USER Имя_пользователя RENAME TO Новое _имя_пользователя - Переименовать одного пользователя в другого.

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

 postgres=#\du (в выводе будет список пользователей с их разрешениями).

Вход под желанным пользователем, по умолчанию вход производится от пользователя «postgresql»:

 bash-4.1$psql –U имя_пользователя 

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

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

1. Меняем пользователя на Postgres:

 #su postgres

bash-4.1$createdb Имя_создаваемой_базы

 bash-4.1$exit 

2. Надо сменить пользователя на Postgres и запустить утилиту psql.

 #su postrges

 bash-4.1$psql 

 postgres=#CREATE DATABASE имя_базы ; 

Вывод существующих баз данных производится по команде:

 postgres=#\l 

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

 postgres=#\q (выход из утилиты psql , если вы еще не выходили)

Следующая команда выполняется от пользователя «postgres», так что если что входим :

 #su postgres 

 bash-4.1$psql имя_базы (вход в утилиту с редактированием нужной базы данных) 

 здесь будет имя базы=# GRANT ALL PRIVILEGES ON DATABASE имя_базы TO имя_пользователя; 

Ответ : GRANT 

 postgres=#\q

 bash-4.1$psql

 postgres=#ALTER DATABASE имя_базы_данных owner to Имя_пользователя кому отдать базу данных ;

 postgres=#\l - Смотрим вывод и кому чего принадлежит.

После данных действий пользователь получает права на базу данных и получает ее в свое пользование. Обычно этого с головой достаточно. Не забываем ставить знак « ; », там, где он стоит у меня при вводе команд, если вдруг подзабыли или пропустили – не страшно, поставьте на следующей строке и нажмите ввод. Выйти из утилиты psql можно командой:

 postgres=#\q 

Удаление ненужной базы данных:

 #su postgres

 bash-4.1$ dropdb Имя_удоляемой_базы_данных

или

 bash-4.1$ psql

 postgres=# DROP DATABASE Имя_удаляемой базы данных; в ответ "DROP DATABASE"

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

 #su postgres

 bash-4.1$psql Имя_Базы_данных

 Сдесь_будет_имя_базы_данных =# \d - и мы получаем вывод таблиц из данной базы данных.

Резервное копирование и восстановление отдельных баз данных.

Резервная копия отдельной базы данных производится с помощью «pg_dump«.

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

 #mkdir /home/rezervdb

 #mkdir /home/rezervdb/Имя_базы_данных

 #chown -R postgres: /home/rezervdb

Так как мы не работаем под пользователем root с базами данных , то входим под пользователем postgres:

 #su postgres

 pg_dump Имя_базы_данных > /home/rezervdb/Имя_базы_данных/Имя_базы.pgdump

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

 bash-4.1$cd /home/rezervdb/Имя_базы_данных/

 bash-4.1$ls - В выводе видим нашу свеж созданную копию базы данных.

Восстановить, переименовать, переместить проще простого : bash-4.1$ cat /home/Имя_базы_данных/Имя_базы.pgdump | psql Имя_базы

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

 bash-4.1$pg_dump Имя_базы_данных | gzip > /home/rezervdb/Имя_базы_данных/Имя_базы.dump.gz

Восстанавление, переименование, перемещение:

 bash-4.1$gunzip -c /home/rezervdb/Имя_базы_данных/Имя_базы.dump.gz | psql Имя_базы_данных_в_которую_востанавливаем

Резервное копирование и восстановление всего в один клик ).

# mkdir /home/rezervdb/postgres

 #chown -R postgres: /home/rezervdb

#su postgres

 bash-4.1$pg_dumpall > /home/rezervdb/postgres/db.out

Для востановления:

#su postgres bash-4.1$psql -f /home/rezervdb/postgres/db.out postgres

И на последок , если вы запутались, ни кто не отменял помощь из консоли ))) :

 postgres-#\? 

Выведит помощь по командам и их синтаксису, а ввод допустим:

 postgres-#\h CREATE 

Подключение к базе данных PostgreSQL

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

На этой странице

• Базовое подключение
• Подключение с помощью SSL-сертификата

Для подключения к экземпляру БД можно использовать клиентский psql для базового подключения или SSL-соединения. Рекомендуется использовать SSL-соединение.

Для подключения необходимы:

• Привязанный к экземпляру БД внешний IP-адрес.
• Установленный клиент PostgreSQL psql на ECS.

Базовое подключение

1. Подключитесь к экземпляру виртуальной машины ECS , где развернут экземпляр RDS.
2. Запустите следующую команду для подключения к базе данных RDS:

psql --no-readline -U user> -h host> -p port> -d datastore> -W 

• -U — имя пользователя экземпляра базы данных RDS. По умолчанию — root .
• -h — IP-адрес экземпляра первичной БД. Получить этот IP-адрес можно на странице Instance Management , нажав на название экземпляра БД.

• через ECS, то IP-адрес можно найти на вкладке Basic Information разделе Connection Information → Floating IP Address .
• через EIP, то адрес IP будет располагаться на вкладке EIPs .

• -p — использующийся порт для базы данных. По умолчанию значение порта — 5432. Номер порта можно получить на странице Instance Management , нажав на нужный экземпляр RDS и перейдя в раздел Connection Information → Database Port .
• -d — название БД. По умолчанию название будет «postgres».
• -W — запрос пароля, который нужно будет указать при подключении. После запуска этой команды будет запрашиваться пароль.

psql --no-readline -U root -h 192.168.0.44 -p 5432 -d postgres -W 

Подключение с помощью SSL-сертификата

1. Войдите в консоль управления Advanced:
   • через личный кабинет Cloud.ru ;
   • как IAM-пользователь .
2. Чтобы открыть список сервисов, нажмите Service List .
3. Выберите Database → Relational Database Service .
4. На странице Basic Information в разделе DB Information скачайте SSL сертификат.
5. Загрузите сертификат в ВМ ECS или сохраните его на устройстве, которое будет подключено к экземпляру базы данных.

Запустите команду для подключения к экземпляру. В нашем примере используется команда для Linux:

psql --no-readline -h host> -p port> "dbname= user= sslmode=verify-ca sslrootcert=" 

-h — IP-адрес экземпляра первичной БД. Получить этот IP-адрес можно на странице Instance Management , нажав на название экземпляра БД.

Если подключение осуществляется:

• через ECS, то IP-адрес можно найти на вкладке Basic Information разделе Connection Information → Floating IP Address .
• через EIP, то адрес IP будет располагаться на вкладке EIPs .

Например, чтобы подключиться к экземпляру базы данных через SSL-соединение от имени пользователя root, выполните следующую команду:

psql --no-readline -h 192.168.0.44 -p 5432 "dbname=postgres user=root sslmode=verify-ca sslrootcert=/root/ca.pem" Password: 

Если успешно установлено SSL-соединение, то отобразится сообщение:

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) 

Как подключиться к базе данных postgresql через консоль

Создав базу данных, вы можете обратиться к ней:

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

Используя существующие графические инструменты, например, pgAdmin или офисный пакет с поддержкой ODBC или JDBC , позволяющий создавать и управлять базой данных. Эти возможности здесь не рассматриваются.

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

$ psql mydb 

Если имя базы данных не указать, она будет выбрана по имени пользователя. Об этом уже рассказывалось в предыдущем разделе, посвящённом команде createdb .

В psql вы увидите следующее сообщение:

psql (9.6.24) Type "help" for help. mydb=>

Последняя строка может выглядеть и так:

mydb=#

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

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

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

mydb=> SELECT version(); version ------------------------------------------------------------------------------------------ PostgreSQL 9.6.24 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 row) mydb=> SELECT current_date; date ------------ 2016-01-07 (1 row) mydb=> SELECT 2 + 2; ?column? ---------- 4 (1 row)

В программе psql есть множество внутренних команд, которые не являются SQL-операторами. Они начинаются с обратной косой черты, « \ » . Например, вы можете получить справку по различным SQL -командам PostgreSQL , введя:

mydb=> \h 

Чтобы выйти из psql , введите:

mydb=> \q 

и psql завершит свою работу, а вы вернётесь в командную оболочку операционной системы. (Чтобы узнать о внутренних командах, введите \? в приглашении командной строки psql .) Все возможности psql документированы в справке psql . В этом руководстве мы не будем использовать эти возможности явно, но вы можете изучить их и применять при удобном случае.

Пред.	Наверх	След.
1.3. Создание базы данных	Начало	Глава 2. Язык SQL