PostgreSQL

Содержание
Введение
Установка
\l: Список существующих БД
Создание БД
Удалить базу данных
Проверка кодировки
\c: Перейти в БД
Подключение к БД
Запуск скрипта из файла
\dt: Список таблиц
\d имя таблицы: Состав таблицы
Пользователи
Изменение таблицы
Импорт из .csv файла
Выйти из консоли PostgreSQL
Узнать текущую схему
PostgreSQL скрипт из Bash
Системные переменные

Введение

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

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

Установка

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

После установки проверьте версию установленного 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 файла

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

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

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

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

Похожие статьи
PostgreSQL
Установка в CentOS
Установка в Ubuntu
postgresql.conf: Конфигурационный файл
SELECT
WHERE
Репликация master slave
Write Ahead Log
recovery.conf
Help
Ошибки
Контакты и сотрудничество:
Рекомендую наш хостинг beget.ru
Пишите на info@urn.su если Вы:
1. Хотите написать статью для нашего сайта или перевести статью на свой родной язык.
2. Хотите разместить на сайте рекламу, подходящуюю по тематике.
3. Реклама на моём сайте имеет максимальный уровень цензуры. Если Вы увидели рекламный блок недопустимый для просмотра детьми школьного возраста, вызывающий шок или вводящий в заблуждение - пожалуйста свяжитесь с нами по электронной почте
4. Нашли на сайте ошибку, неточности, баг и т.д. ... .......
5. Статьи можно расшарить в соцсетях, нажав на иконку сети: