PostgreSQL

Введение

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

Существует в реализациях для множества UNIX-подобных платформ, включая AIX, различные BSD-системы, HP-UX, IRIX, Linux , macOS, Solaris/OpenSolaris, Tru64, QNX, а также для Microsoft Windows .

Установка

• Установка в Ubuntu
• Установка в Centos

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

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

postgres -V

postgres (PostgreSQL) 9.2.24

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

-bash-4.2$ su - postgres -c "psql -c 'SHOW config_file;'"

Password: config_file ------------------------------------- /var/lib/pgsql/data/postgresql.conf (1 row)

В этом примере директория, которая содержит настройки это

/var/lib/pgsql/data

Полезно изучить её содержание

ll /var/lib/pgsql/data/

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

Получить данные о подключении

\conninfo

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

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

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

\l

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';

CREATE DATABASE

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

CREATE DATABASE "heihei" WITH OWNER "postgres" ENCODING
'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0;

CREATE DATABASE

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

\l

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 сделаю

locale -a

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

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;

CREATE DATABASE

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

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

DROP DATABASE db_name;

DROP DATABASE

Проверить кодировки

Проверить кодировку сервера можно командой

SHOW SERVER_ENCODING;

server_encoding ----------------- UTF8 (1 row)

Проверить кодировку клиента

SHOW CLIENT_ENCODING;

client_encoding ----------------- WIN1252 (1 row)

Когда вы находитесь в интерактивном режиме PostgreSQL в консоле появляется префикс

db=>

Где db это название текущей базы данных

Показать адрес текущей директории

\!

[andrei@localhost ~]$

Вернуться в PostgreSQL можно выполнив

exit

db=>

Зайти в БД

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

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

\c HeiHei_ru_DB

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

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

psql -h 127.0.0.1 -d DATABASENAME -U DATABASEUSERNAME

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

\dt

List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | person | table | postgres
(1 row)

Чтобы посмотреть всю таблицу 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 и проверим создалась ли таблица

\c heihei

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

heihei=# \dt

List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | booking_sites | table | postgres (1 row)

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

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

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

\d booking_sites

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)

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

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

postgres=# \du

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);

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

\d booking_sites

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)

Импорт из .csv файла

Чтобы файл открылся желательно положить его в папку

• C:\Users\Public\ - для Windows
• /tmp - для Linux и BSD

Либо грамотно настроить разрешения - иначе будет ошибка

Пример скрипта для импорта

set datestyle to "US"; COPY public.people (id, date, name, address, comment) FROM '/tmp/file.csv' DELIMITER ',' CSV HEADER ENCODING 'UTF8' QUOTE '"' ESCAPE '''';

Выйти из консоли

\q

Узнать текущую схему

SELECT current_schema();

current_schema ---------------- public (1 row)

PostgreSQL скрипт из Bash

Чтобы выполнить Bash скрипт с командой PostgreSQL достаточно создать файл script.sh подобного содержания

#!/bin/bash PGPASSWORD=ПАРОЛЬ psql -h ХОСТ -U ИМЯ_ПОЛЬЗОВАТЕЛЯ -d ИМЯ_БД -с "ВАША КОМАНДА"

Например

#!/bin/bash PGPASSWORD=QWERTY psql -h 127.0.0.1 -U andrei -d urnsu -с "SELECT * FROM news"

И вызвать его

. script.sh

Если SQL скрипт большой и содержит сложный синтаксис, его не так просто подружить с синтаксисом bash.

В такой ситуации может быть полезно создать отдельный файл sql_script.sql и файл с bash скриптом bash_script.sh

Из bash_script.sh можно вызвать sql_script.sql следующим образом

#!/bin/bash cat sql_script.sql | PGPASSWORD=QWERTY psql -h 127.0.0.1 -U andrei -d urnsu

. bash_script.sh

Переменные окружения

$PGDATA - адрес директории data

echo $PGDATA

/var/lib/pgsql/data