Syntax highlighting of 08418a9 ~( db/postgresql)

# PostgreSQL
[TOC]

## Полезные ссылки

[Postgres vs Mongo / Олег Бартунов (Postgres Professional)](https://www.youtube.com/watch?v=SNzOZKvFZ68)

## Установка

[https://linuxize.com/post/how-to-install-postgresql-on-ubuntu-20-04/](https://linuxize.com/post/how-to-install-postgresql-on-ubuntu-20-04/)

Команда установки
```bash
sudo apt update
sudo apt install postgresql postgresql-contrib
```

> Для установки гема `pq` необходимо установить пакет `libpq-dev`
> Для установки только psql необходимо выполнить команду `sudo apt install postgresql-client`

Проверка версии
```bash
sudo su postgres
psql -c "SELECT version();"
```
Создание роли
```bash
sudo su - postgres -c "createuser dbuser"
```





## Создание БД
https://tableplus.com/blog/2018/04/postgresql-how-to-grant-access-to-users.html
```bash
sudo su - postgres
createuser dbuser --pwprompt
psql
postgres# create database database_name with owner = dbuser;
postgres# \q
# или
psql --user admin --host localhost --password
create user dbuser with password 'dbpassword'
create database database_name with owner = dbuser;
\q
# --- обновление пароля
postgres# ALTER ROLE dbuser WITH PASSWORD 'dbpassword';

# заходим под новым пользователем
psql --user dbuser --host localhost --password database_name
# получаем приглашение БД
database_name=#

# запускаем клиент
docker exec -it postgres psql --user=postgres --password
# создаем базу данных
postgres# create database zakupka_dev with owner = postgres;
CREATE DATABASE
postgres=# \q

# Копирование БД
CREATE  DATABASE  targetdb
WITH  TEMPLATE  sourcedb;

# --- add createdb privelege for user
ALTER USER dbuser CREATEDB;
```



## Backup / Restore

### sql

```bash
# --- создание резервной копии
pg_dump --user={user-name} {source_db} -f {dumpfilename.sql}
# --- восстановление из резервной копии
psql --user={user-name} -d {desintation_db} -f {dumpfilename.sql}
```

### tar.gz

```bash
# --- создание резервной копии
pg_dump -U {user-name} {source_db} -F tar  -f {dumpfilename.tar.gz}
# --- восстановление из резервной копии
psql -U {user-name} -d {desintation_db}-f {dumpfilename.tar.gz}
```

### bz2
```bash
# Создание резервной копии
pg_dump --host=localhost --user=misuz -C misuz | bzip2 > pcs_production_20170410.sql.bz2
# Восстановление из резервной копии
bzcat dump.sql.bz2 | psql --host=host --user=username --password database
```

### dmp

```bash
Восстановление БД из дампа *dmp*
psql --host host --user username --password database < dump.dmp
```

## CLI

Подключение к БД
```bash
psql --user username --password dbname
psql -U username -d databasename
psql -U username -c "SELECT * ... "
```

## Postgres CLI
Команда|Описание
---|---
`\l` | Список баз данных
`\c` | Подключиться к базе данных
`\dt` | Список таблиц
`\dn` | Список схем
`\dt schema_name.*` | Список таблицы в схеме
`\d` tablename | Структура таблицы
`\password USERNAME` | Задать пароль
`SHOW ALL;` | Отобразить текущие настройки

## Query

Размер БД
```sql
SELECT pg_size_pretty( pg_database_size( 'pcs_production' ) );
```

Определение размера массива поля *jsonb*
[ссылка](https://stackoverflow.com/questions/33041184/calculate-jsonb-array-length-using-postgresql-9-4)
```bash
SELECT JSONB_ARRAY_LENGTH('["question","solved"]') AS length
```

## Postgres service

### Отключение автостарта

[источник](https://dba.stackexchange.com/questions/31210/preventing-postgresql-from-starting-on-boot-in-ubuntu)
Открываем  файл конфигурации
```bash
sudo vim /etc/postgresql/9.2/main/start.conf
```
Меняем *auto* на *manual*

Управляем состоянием:
```bash
sudo pg_ctlcluster 9.2 main start/stop/status
```
> ВНИМАНИЕ: при остановке кластера командой pg_ctlcluster команда sudo service postgresql status отображает зеленый статус *active (exited)*. При этом порт сама БД остановлена и порт свободен.

### Remote Connection

https://www.bigbinary.com/blog/configure-postgresql-to-allow-remote-connection




## docker

[image](https://hub.docker.com/r/_/postgres/)

[Dockerized Postgresql Development Environment](https://ryaneschinger.com/blog/dockerized-postgresql-development-environment/)

```bash
# создаем том для хранения данных
docker volume create pgdata
# запускаем контейнер PostgreSQL с подключением тома my-vol
# пользователь БД по умолчанию postgres
# Вместо -e POSTGRES_PASSWORD=dbpassword можно использовать 
# --env-file ./postgre.env
# #postgre.env
# POSTGRES_PASSWORD=secret

docker run --name postgres -e POSTGRES_PASSWORD=dbpassword \
	-v pgdata:/var/lib/postgresql/data \
	-p 5432:5432 \
	--restart always \
	-d postgres:12

# запускаем клиент СУБД
docker exec -it postgres psql --user=postgres --host localhost --password
postgres# create database database_name with owner = postgres;
postgres# \l
postgres# \q
```

## pgAdmin

https://www.pgadmin.org/

```bash
docker volume create pgadmin4
# --- host network need for databases on localhost
#     custom port 5050
docker run -d \
  --name pgadmin4 \
  --restart unless-stopped \
  --network host \
  -v pgadmin4:/var/lib/pgadmin \
  -e "PGADMIN_DEFAULT_EMAIL=admin@example.com" \
  -e "PGADMIN_DEFAULT_PASSWORD=secret" \
  -e "PGADMIN_LISTEN_PORT=5050" \
  dpage/pgadmin4
```

## Troubleshooting

### PG::UndefinedObject:  gin

При создании миграции получаем ошибку
```bash
PG::UndefinedObject: ERROR:  data type character varying has no default operator class for access method "gin"
```
Необходимо активировать соответствующее расширение
```sql
create extension btree_gin;
```


### libpq-dev не устанавливается

```bash
sudo apt install libpq-dev
...
Следующие пакеты имеют неудовлетворённые зависимости:
 libpq-dev : Зависит: libpq5 (= 12.6-0ubuntu0.20.04.1) но 13.3-1.pgdg20.04+1 должен быть установлен
E: Невозможно исправить ошибки: у вас зафиксированы сломанные пакеты.
```
Решение
```bash
sudo apt-get install libpq5=12.6-0ubuntu0.20.04.1
sudo apt install libpq-dev
```

### gen_random_uuid() does not exist

Полный текст ошибки
```
PG::UndefinedFunction: ERROR:  function gen_random_uuid() does not exist
```

Решение
```bash
# подключаемся к БД
psql --user=USERNAME ...
# (пере)устанавливаем расширение
database=# drop extension pgcrypto;
database=# CREATE EXTENSION pgcrypto;
```