PostgreSQL

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

Введение

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

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

Установка

Проверка версии

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

postgres -V

postgres (PostgreSQL) 9.2.24

или

psql -V

psql (PostgreSQL) 13.7

Проверить статус

Вместо 11 укажите вашу версию

systemctl status postgresql-11

Возможны варианты inactive, failed, active

● postgresql-11.service - PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: inactive (dead) Docs: https://www.postgresql.org/docs/11/static/

postgresql-11.service - PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: failed (Result: exit-code) since Tue 2022-09-20 12:01:41 EEST; 6s ago Docs: https://www.postgresql.org/docs/11/static/ Process: 12952 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE) Oct 02 03:54:21 host.andrei.com systemd[1]: Starting PostgreSQL 11 database server... Oct 02 03:54:21 host.andrei.com systemd[1]: postgresql-11.service: control process exited, code=exited status=1 Oct 02 03:54:21 host.andrei.com systemd[1]: Failed to start PostgreSQL 11 database server. Oct 02 03:54:21 host.andrei.com systemd[1]: Unit postgresql-11.service entered failed state. Oct 02 03:54:21 host.andrei.com systemd[1]: postgresql-11.service failed.

postgresql-11.service - PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: active (running) since Tue 2022-09-20 12:19:45 EEST; 7s ago Docs: https://www.postgresql.org/docs/11/static/ Process: 15899 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 15904 (postmaster) Tasks: 8 CGroup: /system.slice/postgresql-11.service ├─15904 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/ ├─15906 postgres: logger ├─15908 postgres: checkpointer ├─15909 postgres: background writer ├─15910 postgres: walwriter ├─15911 postgres: autovacuum launcher ├─15912 postgres: stats collector └─15913 postgres: logical replication launcher Oct 02 03:54:21 host.andrei.com systemd[1]: Starting PostgreSQL 11 database server... Oct 02 03:54:21 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.397 EEST [15904] LOG: listening on IPv6 address "::1", port 5432 Oct 02 03:54:21 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.397 EEST [15904] LOG: listening on IPv4 address "127.0.0.1", port 5432 Oct 02 03:54:21 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.401 EEST [15904] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" Oct 02 03:54:21 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.405 EEST [15904] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" Oct 02 03:54:21 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.414 EEST [15904] LOG: redirecting log output to logging collector process Oct 02 03:54:21 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.414 EEST [15904] HINT: Future log output will appear in directory "log". Oct 02 03:54:21 host.andrei.com systemd[1]: Started PostgreSQL 11 database server.

Зайти в Postgres

Когда postgresql-11 находится имеет статус active можно зайти в postgres выполнив

sudo su - postgres

Last login: Sun Oct 02 03:54:21 EEST 2022 on pts/0 -bash-4.2$

psql

psql (11.17) Type "help" for help. postgres=#

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

Расположение файлов с настройками, например, 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;

Список активных подключений к БД

Изучить подключения к БД можно выполнив

SELECT * FROM pg_stat_activity WHERE datname = 'ИМЯ_БД';

datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_star t | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type -------+---------+-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+---------- --+-------------+--------------+-----------------+------------+-------+-------------+--------------+-------+-------------- (0 rows)

Переименовать БД

ALTER DATABASE старое_имя RENAME TO новое_имя;

Подробности:

Список существующих БД можно получить командой \l

\l

List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- db01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)

Если нужно переименовать БД db01 первым делом нужно выйти из неё.

Для этого можно зайти в другую БД, например, postgres.

Изучить подключения к БД можно выполнив

SELECT * FROM pg_stat_activity WHERE datname = 'db01';

datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_star t | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type -------+---------+-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+---------- --+-------------+--------------+-----------------+------------+-------+-------------+--------------+-------+-------------- (0 rows)

Непосредственно переименование выполняется командой

ALTER DATABASE db01 RENAME TO db02;

ALTER DATABASE

\l

List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- db02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | … (4 rows)

Если к базе данных кто-то подключен. Например, вы сами из другого терминала

\c db02

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

postgres=# SELECT datname, pid, backend_start, state FROM pg_stat_activity WHERE datname = 'db02';

datname | pid | backend_start | state ---------+-------+-------------------------------+------- db02 | 21238 | 2022-10-02 03:54:21.503073+03 | idle (1 row)

Все подключения можно закрыть выполнив

postgres=# SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = 'db02';

pg_terminate_backend ---------------------- t (1 row)

Достать значение из БД

Пример - запишем значение из базы данных в файл с помощью echo

echo $(psql -h localhost -d db_name -U db_user -c "SELECT host_certificate FROM hosts WHERE (addresses='192.168.56.200');") > raw_cert

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

В предыдущем параграфе мы создали в базе данных 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)

UPDATE: Изменение значения

Изменим дату годности сертификата в таблице certificates

Изучим текущую ситуацию

aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070');

issuer | not_after --------------------------------------------------------------------------------------------+------------------------ CN=192.168.56.174,OU=Andrei CA 3bbce96c-b117-46l0-65a7-f3c04c9f591b | 2027-07-19 16:39:59+03 (1 row)

Обновим значение

aodb=> UPDATE certificates SET not_after='2022-07-22 16:39:59+03' WHERE (serial='2402020070');

UPDATE 1

Проверим результат

aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070263697801');

issuer | not_after --------------------------------------------------------------------------------------------+------------------------ CN=192.168.56.174,OU=Andrei CA 3bbce96c-b117-46l0-65a7-f3c04c9f591b | 2022-07-22 16:39:59+03 (1 row)

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

Когда нужно обновить название столбца таблицы используем команду 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)

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

\q

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

SELECT current_schema();

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

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

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

echo $PGDATA

/var/lib/pgsql/data

Похожие статьи
PostgreSQL
Установка в CentOS
Установка в Ubuntu
postgresql.conf: Конфигурационный файл
Таблицы
Пользователи
SELECT
WHERE
bash скрипты для Postgres
Репликация master slave
Write Ahead Log
recovery.conf
pg_hba.conf
Help
Ошибки

Поиск по сайту

Подпишитесь на Telegram канал @aofeed чтобы следить за выходом новых статей и обновлением старых

Перейти на канал

@aofeed

Задать вопрос в Телеграм-группе

@aofeedchat

IT

Образование

Актуально сейчас

Разное

Поиск по сайту

Подпишитесь на Telegram канал @aofeed чтобы следить за выходом новых статей и обновлением старых

Перейти на канал

@aofeed

Задать вопрос в Телеграм-группе

@aofeedchat

Контакты и сотрудничество:
Рекомендую наш хостинг beget.ru
Пишите на info@urn.su если Вы:
1. Хотите написать статью для нашего сайта или перевести статью на свой родной язык.
2. Хотите разместить на сайте рекламу, подходящуюю по тематике.
3. Реклама на моём сайте имеет максимальный уровень цензуры. Если Вы увидели рекламный блок недопустимый для просмотра детьми школьного возраста, вызывающий шок или вводящий в заблуждение - пожалуйста свяжитесь с нами по электронной почте
4. Нашли на сайте ошибку, неточности, баг и т.д. ... .......
5. Статьи можно расшарить в соцсетях, нажав на иконку сети: