Рубрика: postgresql

Иногда при попытке запуска Datalens могут возникать странные проблемы отсутствия тех или иных объектов в БД (хотя они там есть).

Ошибка может быть связана в особенности PostgreSQL, когда по умолчанию схема public настроена, как «схема по умолчанию для вызова объектов».

Читать далее «Особенность работы Datalens в PostgreSQL. часть 2»

Данный способ позволяет создать скрипт INSERT для наполнения таблицы PostgreSQL.

pg_dump -a -U postgres -t table_name -f output_name.sql --inserts --column-inserts --on-conflict-do-nothing database_name

Описание параметров:

• a — Выгружаются только данные, а не схема (определения данных). Выгружаются табличные данные, большие объекты и значения последовательности. Этот параметр аналогичен, но по историческим причинам не идентичен параметру, определяющему --section=data.
• U — Имя пользователя для подключения как.
• t — Дамп только таблиц с совпадающими именами
• f — Отправляйте выходные данные в указанный файл. Этот параметр можно опустить для файловых форматов вывода, в этом случае используется стандартный вывод. Однако он должен быть указан для формата вывода каталога, где указывается целевой каталог, а не файл. В этом случае каталог создан pg_dump и не должен существовать ранее.
• inserts — Дамп данных в виде INSERT команд (а не COPY). Это сильно замедлит восстановление; в основном это полезно для создания дампов, которые можно загружать в базы данных, отличные от PostgreSQL. Любая ошибка во время восстановления приведет к потере только строк, которые являются частью проблемной таблицы, INSERT а не всего содержимого таблицы. Обратите внимание, что восстановление может завершиться полным сбоем, если вы изменили порядок столбцов. Опция --column-inserts защищает от изменений порядка столбцов, хотя и работает еще медленнее.
• column-inserts — Дамп данных в виде INSERT команд с явными именами столбцов (INSERT INTO table (column, ...) VALUES ...). Это сильно замедлит восстановление; в основном это полезно для создания дампов, которые можно загружать в базы данных, отличные от PostgreSQL. Любая ошибка во время восстановления приведет к потере только строк, которые являются частью проблемной таблицы INSERT, а не всего содержимого таблицы.
• on-conflict-do-nothing — Добавить команды ON CONFLICT DO NOTHING в INSERT. Этот параметр недопустим, если также не указано --inserts, --column-inserts или --rows-per-insert.

Пример:

pg_dump -a -U postgres -t dbo.cs_point_types -f cs_point_types.sql --inserts --column-inserts --on-conflict-do-nothing skr-dev-db

https://www.postgresql.org/docs/current/app-pgdump.html

from sqlalchemy import create_engine, text
import pandas as pd

engine = create_engine('postgresql://user:password@host:5432/dbname')
conn = engine.connect()

query = text("""SELECT * FROM core.sd_settings""")
df = pd.read_sql_query(query, conn)

print(df.head())

conn.close()

Примечание: требуется установка следующих компонентов

# requirements.txt
SQLAlchemy
psycopg2
pandas
# pip install -r requirements.txt

create or replace function update_array_elements(arr jsonb, key text, value jsonb)
returns jsonb language sql as $$
    select jsonb_agg(jsonb_build_object(k, case when k <> key then v else value end))
    from jsonb_array_elements(arr) e(e), 
    lateral jsonb_each(e) p(k, v)
$$;

select update_array_elements('[{"bar":true},{"bar":true}]'::jsonb, 'bar', 'false');

      update_array_elements
----------------------------------
 [{"bar": false}, {"bar": false}]
(1 row)

Примечание: данный способ не идеален, так как может быть создавать «дыру» в безопасности

Для чтения данных из файловой системы в postgresql требуется сохранить изображения в каталоге:

# psql
SHOW data_directory;

# /var/lib/postgresql/12/main

Читать далее «Чтение файлов из PostgreSQL»

По умолчанию поддержка в 14 версии отсутствует (на момент написания статьи).

Чтобы установить расширение требуется выполнить следующие шаги:

Шаг 1. Ставим зависимости

sudo apt install make git g++ clang python2 pkg-config libc++-dev libc++abi-dev libglib2.0-dev libtinfo5 ninja-build postgresql-server-dev-14

Шаг 2. Указываем, что команда python вызывает python версии 2

sudo ln -s /usr/bin/python2 /usr/bin/python

Читать далее «plv8 и PostgreSQL 14»

Резервное копирование производиться при помощи утилиты pg_probackup

sudo sh -c 'echo "deb [arch=amd64] https://repo.postgrespro.ru/pg_probackup/deb/ $(lsb_release -cs) main-$(lsb_release -cs)" > /etc/apt/sources.list.d/pg_probackup.list'
sudo wget -O - https://repo.postgrespro.ru/pg_probackup/keys/GPG-KEY-PG_PROBACKUP | sudo apt-key add - && sudo apt-get update
sudo apt-get install pg-probackup-12
sudo apt-get install pg-probackup-12-dbg

Примечание: на примере выше устанавливается 12 версия

Далее делаем символьную ссылку:

sudo ln -s /usr/bin/pg_probackup-12 /usr/bin/pg_probackup

Читать далее «Скрипт резервного копирования СУБД PostgreSQL»

sudo apt-get install postgresql-client
pg_isready -h localhost -p 5432

Дополнительные параметры тут https://www.postgresql.org/docs/12/app-pg-isready.html

PostgreSQL гарантирует, что столбец id будет уникальным, но при этом в индексе будет храниться дополнительное поле, так что при запросе обоих столбцов будет произведен просмотр индекса.

CREATE UNIQUE INDEX some_name ON person USING btree (id) INCLUDE (name);

Если вы хотите получить «настоящее время», то пользуйтесь функцией clock_timestamp(), а не now().

Читать далее «PostgreSQL 11: Мастерство разработки»

Оригинал инструкции размещен на сайте https://gist.github.com/peterneave/83cefce2a081add244ad7dc1c53bc0c3

• Устанавливаем приложение

sudo apt update
sudo apt install pgagent

• Далее создаем файл .pgpass в рабочей директории postgresql (/var/lib/postgresql)

sudo su - postgres
echo localhost:5432:*:pgagent:securepassword >> ~/.pgpass
chmod 600 ~/.pgpass
chown postgres:postgres /var/lib/postgresql/.pgpass

Читать далее «Установка настройка pgAgent»