Syntax highlighting of 08418a9 ~( db/postgresql)
= PostgreSQL = <<TableOfContents(2)>> == Полезные ссылки == [[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; }}}