= PostgreSQL =
<<TableOfContents()>>
== Полезные ссылки ==
[[https://www.youtube.com/watch?v=SNzOZKvFZ68|Postgres vs Mongo / Олег Бартунов (Postgres Professional)|class=" moin-https"]]
== Установка ==
[[https://linuxize.com/post/how-to-install-postgresql-on-ubuntu-20-04/||class=" moin-https"]]
Установка сервера и клиента
{{{#!highlight bash
sudo apt update
sudo apt install postgresql postgresql-contrib
}}}
Устанока только psql
{{{#!highlight bash
sudo apt install postgresql-client
}}}
{{{#!wiki note
Для установки ruby-гема `pq` достаточно установить пакет `libpq-dev`
}}}
Проверка версии
{{{#!highlight bash
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
{{{#!highlight 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 ===
{{{#!highlight bash
# --- создание резервной копии
pg_dump --user={user-name} {source_db} -f {dumpfilename.sql}
# --- восстановление из резервной копии
psql --user={user-name} -d {desintation_db} -f {dumpfilename.sql}
}}}
=== tar.gz ===
{{{#!highlight 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 ===
{{{#!highlight 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 ===
{{{#!highlight bash
Восстановление БД из дампа *dmp*
psql --host host --user username --password database < dump.dmp
}}}
== CLI ==
Подключение к БД
{{{#!highlight 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 ==
Размер БД
{{{
SELECT pg_size_pretty( pg_database_size( 'pcs_production' ) );
}}}
Определение размера массива поля ''jsonb''[[https://stackoverflow.com/questions/33041184/calculate-jsonb-array-length-using-postgresql-9-4|ссылка|class=" moin-https"]]
{{{
SELECT JSONB_ARRAY_LENGTH('["question","solved"]') AS length
}}}
== Postgres service ==
=== Отключение автостарта ===
[[https://dba.stackexchange.com/questions/31210/preventing-postgresql-from-starting-on-boot-in-ubuntu|источник|class=" moin-https"]]
Открываем файл конфигурации
{{{#!highlight bash
sudo vim /etc/postgresql/9.2/main/start.conf
}}}
Меняем ''auto'' на ''manual''
Управляем состоянием:
{{{#!highlight bash
sudo pg_ctlcluster 9.2 main start/stop/status
}}}
{{{#!wiki important
'''ВНИМАНИЕ'''
При остановке кластера командой pg_ctlcluster команда sudo service postgresql status отображает зеленый статус ''active (exited)''. При этом порт сама БД остановлена и порт свободен.
}}}
=== Remote Connection ===
https://www.bigbinary.com/blog/configure-postgresql-to-allow-remote-connection
== docker ==
[[https://hub.docker.com/r/_/postgres/|image|class=" moin-https"]]
[[https://ryaneschinger.com/blog/dockerized-postgresql-development-environment/|Dockerized Postgresql Development Environment|class=" moin-https"]]
{{{#!highlight 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/
{{{#!highlight 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 ===
При создании миграции получаем ошибку
{{{
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;
}}}
