PostgreSQL

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

Postgres vs Mongo / Олег Бартунов (Postgres Professional)

Установка

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

Установка сервера и клиента

sudo apt update
sudo apt install postgresql postgresql-contrib

Устанока только psql

sudo apt install postgresql-client

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

sudo su postgres
psql -c "SELECT version();"

Создание роли

sudo su - postgres -c "createuser dbuser"

Создание БД

https://tableplus.com/blog/2018/04/postgresql-how-to-grant-access-to-users.html

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

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

tar.gz

# --- создание резервной копии
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

# Создание резервной копии
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

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

CLI

Подключение к БД

psql --user username --password dbname
psql -U username -d databasename
psql -U username -c "SELECT * ... "

Postgres CLI

Query

Размер БД

SELECT pg_size_pretty( pg_database_size( 'pcs_production' ) );

Определение размера массива поля jsonbссылка

SELECT JSONB_ARRAY_LENGTH('["question","solved"]') AS length

Postgres service

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

источник Открываем файл конфигурации

sudo vim /etc/postgresql/9.2/main/start.conf

Меняем auto на manual

Управляем состоянием:

sudo pg_ctlcluster 9.2 main start/stop/status

Remote Connection

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

docker

image

Dockerized Postgresql Development Environment

# создаем том для хранения данных
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/

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

UndefinedObject: gin ===

При создании миграции получаем ошибку

PG::UndefinedObject: ERROR:  data type character varying has no default operator class for access method "gin"

Необходимо активировать соответствующее расширение

create extension btree_gin;

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

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

Решение

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

Решение

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