Как создать таблицу в postgresql linux

Обновлено: 07.07.2024

PostgreSQL (произносится «Пост-Грэс-Кью-Эл») — свободная объектно-реляционная система управления базами данных (СУБД).

Установка

После установки

После установки проверьте версию установленного PostgreSQL

postgres (PostgreSQL) 9.2.24

Расположение файлов с настройками, например, postgresql.conf можно получить выполнив

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.

Если Вы хотите подключиться из bash в Windows не забудьте добавить местоположение psql.exe (у меня это C:\Program Files\PostgreSQL\12\bin) в PATH

Как это делается описано в статье PATH

psql.exe -h localhost -p 5433 -U postgres

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".

Список существующих БД

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

Обратите внимание на столбцы Encoding, Collate и Ctype. Знание кодировок может Вам пригодиться в будущем.

Создать базу данных

Создадим базу данных heihei_ru_db с кодировкой utf8

Создадим базу данных heihei с кодировкой utf8 и укажем значения для Collate, Ctype и Template

Проверим, что получилось

Если я в bash сделаю

То получу сразу четыре доступные кодировки

C
C.UTF-8
en_US.utf8
POSIX

Но создать БД, например, с en_US.utf8 у меня не получается

Приводит к ошибке

ERROR: invalid locale name: "en_US.UTF-8"

Если Вы знаете как решить эту проблему - отпишитесь в комментариях к статье.

UPD: Выполнил такую же команду в Ubuntu и база успешно создана

Удалить базу данных

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

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

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

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

SELECT * FROM person;

Запуск скрипта из файла

Сперва проверим, что с переменными окружения всё впорядке.

Для этого введём в консоль psql.exe нажмём Enter и проверим что bash не жалуется на неизвестную команду.

Если жалуется - прочитайте мои советы в статье Системная переменная PATH

Пишем скрипт script.sql

CREATE TABLE person ( id int, first_name VARCHAR(50), last_name VARCHAR(50), gender VARCHAR(5), date_of_birth DATE )

Применим этот скрипт к базе данных HeiHei_ru_DB

У меня postgres запущен локально на порту 5433. У Вас может быть на 5432 - проверьте.

cat script.sql | psql.exe -h localhost -p5433 -U postgres HeiHei_ru_DB

Password for user postgres:
CREATE TABLE

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

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

CREATE TABLE booking_sites ( id BIGSERIAL NOT NULL PRIMARY KEY, company_name VARCHAR(50) NOT NULL, origin_country VARCHAR(50) NOT NULL, age VARCHAR(3) NOT NULL, date_of_birth DATE NOT NULL, website_url VARCHAR(50) );

Теперь запустим этот скрпит уже не в тестовую а в рабочую базу данных heihei (которая совпадает с названием сайта HeiHei.ru, но если написать .ru будет синтаксическая ошибка ERROR: syntax error at or near ".")

cat booking_sites.sql | psql.exe -h localhost -p5433 -U postgres heihei

Password for user postgres:
CREATE TABLE

Содержание базы данных

В предыдущем параграфе мы создали в базе данных heihei таблицу booking_sites

Убедимся в том, что скрипт отработал удачно

Перейдём в базу данных heihei и проверим создалась ли таблица

You are now connected to database "heihei" as user "postgres".

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

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

Пользователи

Получить список пользователей

List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication | <>

Изменение таблицы

Когда нужно обновить название столбца таблицы используем команду ALTER

Предположим полю website_url не хватает 50 символов. Увеличим длину до 60.

ALTER TABLE booking_sites ALTER column website_url TYPE VARCHAR(60);

Для подключения к базе данных PostgreSQL понадобится установленный PostgreSQL клиент:

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

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

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

PostgreSQL Подключение, Пользователи (Роли) и Базы Данных

Логин в только что установленный postgreSQL сервер нужно производить под именем пользователя postgres:

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

Если такая команда не просит ввести пароль пользователя, то можно еще добавить опцию -W.

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

PostgreSQL создание новой роли и базы данных

Создать новую роль c именем admin (указывайте нужное имя):

Создание новой базы данных:

Дать права роли на базу данных:

Включить удаленный PostgreSQL доступ для пользователей

Нам нужно отредактировать файл /etc/postgresql/<VERSION>/main/pg_hba.conf, задав опцию md5 вместо peer.

<VERSION> может быть 10, 11, 12 и т.д.

После этого сделать restart PostgreSQL:

Полезные команды PostgreSQL

Выйти из клиента PostgreSQL:

Показать список баз данных PostgreSQL:

Показать список таблиц:

\dt

Показать список пользователей (ролей):

\du

Показать структуру таблицы:

Переименовать базу данных:

Удалить базу данных:

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

\connect db_name или более короткий alias: \c db_name

Удалить роль (пользователя):

Роль не будет удалена, если у нее есть привелегии - возникнет ошибка ERROR: role cannot be dropped because some objects depend on it .

Нужно удалить привелегии у роли, например если нужно удалить роль admin2, нужно выполнить последовательность комманд с Drop Owned:

Дать права пользователю/роли на логин ( role is not permitted to log in ):

Выбор shema psql в консоли:

Посмотреть список всех схем:

Подключиться к конкретной схеме:

Sequences

Получить имена всех созданных sequences:

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

20 Фев 2018 21:02:09 | 1 comment

Начало работы с PostgreSQL 10

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

Исходные данные: Debian 9.3 (Stretch), PostgreSQL 10.2
Задача: Научиться базовым навыкам работы с PostgreSQL

Давайте зайдем под пользователем postgres, запустим оболочку psql и попробуем поработать:

Перед нами открылась оболочка с приглашением ввести команды:

Первым делом давайте посмотрим информацию о версии PostgreSQL:

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

1. Список баз данных.

Список БД можно посмотреть 3-мя способами:
Ключ -l командной строки приложения psql, метакоманда \l или select запрос к системному каталогу pg_database, например

2. Создание баз данных.

Базу данных можно создать 2-мя способами: Через утилиту createdb или с помощью команды CREATE DATABASE имя;
Утилита createdb не делает ничего волшебного, она просто подключается к базе данных postgres и выполняет SQL-команду CREATE DATABASE.

Пример этих команд и вывод списка БД:

или из командной оболочки под пользователем postgres:

Просмотр флагов datistemplate и datallowconn:

3. Удаление баз данных.

Тут так же как и при создании, есть 2 подхода: Через утилиту dropdb или с помощью команды DROP DATABASE имя;

Пример:
Пример этих команд и вывод списка БД:

4. Работа с табличными пространствами.

Табличные пространства в PostgreSQL позволяют администраторам организовать логику размещения файлов объектов базы данных в файловой системе. К однажды созданному табличному пространству можно обращаться по имени на этапе создания объектов.
Табличные пространства позволяют администратору управлять дисковым пространством для инсталляции PostgreSQL. Это полезно минимум по двум причинам. Во-первых, это нехватка места в разделе, на котором был инициализирован экземпляр Pg или кластера Pg и невозможность его расширения. Табличное пространство можно создать в другом разделе и использовать его до тех пор, пока не появится возможность переконфигурирования системы. Во-вторых, табличные пространства позволяют администраторам оптимизировать производительность согласно бизнес-процессам, связанным с объектами базы данных. Например, часто используемый индекс можно разместить на очень быстром и надёжном, но дорогом SSD-диске. В то же время таблица с архивными данными, которые редко используются и скорость к доступа к ним не важна, может быть размещена в более дешёвом и медленном хранилище.

Для создания табличного пространства используется команда CREATE TABLESPACE.
Каталог нового табличного пространства должен существовать, быть пустым и принадлежать пользователю ОС, под которым запущен PostgreSQL.

Создаем каталог и выставляем права:

Подключаемся к Pg и создаем новое табличное пространство:

Посмотреть список табличных пространств можно 2-мя способами: метакоманда \db или \db+ или select запрос к системному каталогу pg_tablespace;

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

Например, далее создаётся база данных в табличном пространстве fast:

В колонке Tablespace мы видим что база test2 находится в новом табличном пространстве fast.

Табличное пространство, связанное с базой данных, также используется для хранения её системных каталогов. Более того, это табличное пространство используется по умолчанию для таблиц, индексов и временных файлов, создаваемых в базе данных, если не указано иное в выражении
TABLESPACE, или переменной default_tablespace, или temp_tablespaces (соответственно). Если база данных создана без указания конкретного табличного пространства, то используется пространство, к которому принадлежит копируемый шаблон.

При инициализации экземпляра Pg или кластера Pg автоматически создаются два табличных пространства. Табличное пространство pg_global используется для общих системных каталогов. Табличное пространство pg_default используется по умолчанию для баз данных template1 и template0 (в свою очередь, также является пространством по умолчанию для других баз данных, пока не будет явно указано иное в выражении TABLESPACE команды CREATE DATABASE).

5. Работа с ролями.

В PostgreSQL используется концепция ролей (roles) для управления разрешениями на доступ к базе данных. Роль можно рассматривать как пользователя базы данных или как группу пользователей, в зависимости от того, как роль настроена. Роли могут владеть объектами базы данных (например, таблицами и функциями) и выдавать другим ролям разрешения на доступ к этим объектам, управляя тем, кто имеет доступ и к каким объектам. Кроме того, можно предоставить одной роли членство в другой роли, таким образом одна роль может использовать привилегии других ролей.

Концепция ролей включает в себя концепцию пользователей (user) и групп (groups). До версии 8.1 в PostgreSQL пользователи и группы были отдельными сущностями, но теперь есть только роли. Любая роль может использоваться в качестве пользователя, группы, и того и другого.

Роли базы данных концептуально полностью отличаются от пользователей операционной системы.На практике поддержание соответствия между ними может быть удобным, но не является обязательным. Роли базы данных являются глобальными для всего инстана Pg или кластера Pg базы данных (не для отдельной базы данных). Для создания роли используется SQL-команда CREATE ROLE, а для удаления DROP ROLE.

5.1 Создание ролей.

Роль можно создать/удалить 2-мя путями: через консольную утилиту createuser или dropuser и через psql с помощью CREATE ROLE или DROP ROLE.

Давайте попробуем обя способа создания роли:

Для просмотра списка ролей можно использовать метакоманду \du или сделать select запрос к системному каталогу pg_roles.

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

Доступны следующие атрибуты (в скобках указаны столбцы из каталогу pg_roles):

1) Право подключения (rolcanlogin):
Только роли с атрибутом LOGIN могут использоваться для начального подключения к базе данных. Роль с атрибутом LOGIN можно рассматривать как пользователя базы данных. Для создания роли такой роли можно использовать любой из вариантов:
CREATE ROLE имя LOGIN;
или
CREATE USER имя;
или использование консольной утилиты createuser.
Как мы уже сказали выше, команда CREATE USER эквивалентна CREATE ROLE за исключением того, что CREATE USER по умолчанию предполагает атрибут LOGIN, в то время как CREATE ROLE нет.)

2) Статус суперпользователя (rolsuper):
Суперпользователь базы данных обходит все проверки прав доступа, за исключением права на вход в систему. Это опасная привилегия и она не должна использоваться небрежно. Лучше всего выполнять большую часть работы не как суперпользователь. Для создания нового супер пользователя используется CREATE ROLE имя SUPERUSER. Это нужно выполнить из под роли, которая также является суперпользователем.

3) Создание базы данных (rolcreatedb):
Роль должна явно иметь разрешение на создание базы данных (за исключением суперпользователей, которые пропускают все проверки). Для создания такой роли используется CREATE ROLE имя CREATEDB.

4) Создание роли (rolcreaterole):
Роль должна явно иметь разрешение на создание других ролей (за исключением суперпользователей, которые пропускают все проверки). Для создания такой роли используется CREATE ROLE имя CREATEROLE. Роль с привилегией CREATEROLE может также изменять и удалять другие
роли, а также выдавать и отзывать членство в ролях. Однако, для создания, изменения, удаления суперпользовательских ролей, а также изменения в них членства, требуется иметь статус суперпользователя, привилегии CREATEROLE в таких случаях недостаточно.

5) Запуск репликации (rolreplication):
Роль должна иметь явное разрешение на запуск потоковой репликации (за исключением суперпользователей, которые пропускают все проверки). Роль, используемая для потоковой репликации, также должна иметь атрибут LOGIN. Для создания такой роли используется CREATE ROLE имя REPLICATION LOGIN.

7) Игнорировать систему защиты строк (rolbypassrls):
В дополнение к стандартной системе прав SQL, управляемой командой GRANT, на уровне таблиц можно определить политики защиты строк, ограничивающие для пользователей наборы строк, которые могут быть возвращены обычными запросами или добавлены, изменены и удалены командами, изменяющими данные. Это называется также защитой на уровне строк (RLS, Row-Level Security). Суперпользователи и роли с атрибутом BYPASSRLS всегда обращаются к таблице, минуя систему защиты строк. Более детально о RLS читайте в официальной документации или в переводе на русский.

Атрибуты ролей могут быть изменены после создания командой ALTER ROLE. Для получения более детальной информации Вам следует обратиться к справке по командам CREATE ROLE и ALTER ROLE.

Давайте добавим право LOGIN и CREATEDB для нашей роли utest2:

А теперь отберем у роли utest2 право CREATEDB и добавим CREATEROLE и REPLICATION:

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

Сделаем групповую роль, добавим членов и создадим новую БД в табличном пространстве fast и укажем эту групповую роль владельцем БД:

Теперь мы можем попробовать подключиться к базе group_db под пользователем utest2 из консоли:

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

Так же хочу обратить внимание на такую вещь как владелец табличного пространства, чуть выше когда мы создавали табличное пространство fast, мы его создали под пользователем postgres и его владельцем стал он, см. столбец Owner при выводе информации метакомандой \db
Поменять владельца табличного пространства можно командой ALTER TABLESPACE OWNER TO ;
Например:

5.2 Удаление ролей.

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

То есть наша роль является владельцем базы group_db. Чтобы решить эти проблемы нужно поменять владельца БД и только после этого удалить роль:

Как мы видим и вывода \l+ у нашей БД group_db поменялся владелец и роль group_role1 была удалена (вывод \du).

6. Работа с таблицами.

Посмотрим список таблиц в БД с помощью метакоманды \dt

Теперь удалим табличку my_first_table с помощью DROP TABLE
Попытка удаления несуществующей таблицы считается ошибкой, поэтому рекомендуется использовать конструкцию DROP TABLE IF EXISTS

После удаления вывод \dt скажет нам, что в БД нет таблиц.

7. Работа со схемами.

Для создания схемы используется команда CREATE SCHEMA ;

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

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

Есть ещё более общий синтаксис

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

Создадим схему и таблицу в ней:

Посмотрим список схем (метакоманда \dn):

Чтобы найти нашу таблицу мы должны будем обращаться к ней по полному пути, но вывод списка таблиц через метакоманду \dt не выведет нашу новую таблицу из схемы sc_test, чтобы он вывел таблицу мы должны добавить путь поиска схемы, это делается командой:

Чтобы не вводить SET search_path TO каждый раз мы можем задать search_path для конкретного пользователя на постоянной основе командой:

После этого мы сможем вывести список наших таблиц из схемы public и новой sc_test:

Указав путь поиска схемы мы можем выполнить DROP TABLE my_first_table; без указания схемы, но тут есть нюанс:
ВНИМАНИЕ! Т.к. мы можем создавать одинаковые по именам объекты в разных схемах, то порядок поиска схем в путь будет иметь значение при разных операциях (создание, модификацию, удаления и др.) над объектами, вывести список путей поиска схем можно командой:

Таким образом DROP TABLE my_first_table; первым делом удалит таблицу из схемы sc_test если она там есть, а повторный вызов DROP TABLE my_first_table; уже удалит таблицу из схемы public, при её наличии там.
Вот поэтому если Вы используете схемы, то и все операции над объектами должны выполнятся с указанием полного имени объекта.

Для удаления схемы нужно воспользоваться командой DROP SCHEMA ;
Попытка удаления несуществующей схемы считается ошибкой, поэтому рекомендуется использовать конструкцию DROP SCHEMA IF EXISTS ;

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

В таком случае нужно воспользоваться доп. опцией CASCADE, например:

Как мы видим, схема sc_test и таблица my_first_table были удалены.

На этом все, до скорых встреч.

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

Сегодня поговорим о том, как установить PostgreSQL на Kali Linux.

Чтобы установить PostgreSQL введите в терминале следующую команду:

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

Если вас интересует куда поставился PostgreSQL, выполните такую команду:

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

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

Получаем примерно такое.

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

Теперь проверим, создалась ли база, вводим снова команду:

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

Как мы видим, у нас нет никаких таблиц.

Попробуем создать таблицу используя данную команду.

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

Теперь попробуем изменить структуру таблицы и добавить еще одну колонку middlename.

Теперь обновим таблицу, добавив midllename к первой записи.

Теперь удалим запись такой командой.

Готово, теперь у нас 0 записей в таблице.

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

Если что, вы можете ввести команду \help, чтобы посмотреть список доступным команд.

Читайте также: