PostgreSQL, Базы данных

PostgreSQL: Результаты запроса в JSON

Мне бы хотелось рассказать очень кратенько про две часто мною используемые функции — json_agg и row_to_json.

Первая используется для получения результата выполнения SQL запроса как массив объектов JSON. Таким образом, одна запись складывается в один объект массива:

[pastacode lang=»sql» manual=»select%20json_agg(t)%20from%0A(select%20*%20from%20products)%20t» message=»json_agg» highlight=»» provider=»manual»/]

[pastacode lang=»sql» manual=»select%20json_agg(t)%20from%0A(select%20product_id%2C%20min_storage_temp%2C%20max_storage_temp%20%0A%20from%20products%20where%20coalesce(min_storage_temp%2C»)%20%3C%3E%20»)%20t» message=»Получить данные указанных полей» highlight=»json_agg» provider=»manual»/]

Результат такого запроса будет выглядеть похожим образом:

[
{
«product_id»:»aaa5f25c-5734-11ec-8cd2-00155d999336″,
«min_storage_temp»:»-4″,
«max_storage_temp»:»+6″
},
{
«product_id»:»1735acd6-fd3f-4146-8efc-718d7c55faaf»,
«min_storage_temp»:»0″,
«max_storage_temp»:»+6″
},
{
«product_id»:»c89cc4d8-c531-11eb-958d-772a230b0b21″,
«min_storage_temp»:»-4″,
«max_storage_temp»:»+6″
}
]

Для получения результата запроса, возвращающего одну строку, в качестве объекта JSON можно воспользоваться функцией row_to_json:

[pastacode lang=»sql» manual=»select%20row_to_json(t)%20FROM%20%0A(select%20*%20from%20products%20where%20product_id%20%3D%20’123′)%20t» message=»row_to_json» highlight=»» provider=»manual»/]

[
{
«product_id»:»c89cc4d8-c531-11eb-958d-772a230b0b21″,
«min_storage_temp»:»-4″,
«max_storage_temp»:»+6″
}
]

PostgreSQL

Принудительное удаление базы данных в PostgreSQL

Удалить базу данных, не обращая внимание на текущие сессии? Вопрос решается в три запроса!

1. Предотвращаем дальнейшие подключения к базе данных dbase_name:

update pg_database set datallowconn = 'false' 
where datname = 'dbase_name';

2. Закрываем все текущие сессии:

select pg_terminate_backend(pg_stat_activity.pid)
from pg_stat_activity
where pg_stat_activity.datname = 'dbase_name' and 
pid <> pg_backend_pid();

3. Собственно, удаляем базу данных:

drop database dbase_name;

Благодарность отправляется в Хабр Q&A.

PostgreSQL, Qt, Базы данных

Qt: Резервная копия pg_dump БД PostgreSQL в Windows

Пример того, как можно реализовать резервное копирование базы данных PostgreSQL в Qt. Создается batch-файл, где вызывается утилита pg_dump.

[pastacode lang=»cpp» manual=»%2F%2FQString%20name%20-%20%D0%B8%D0%BC%D1%8F%20%D0%B1%D0%B0%D0%B7%D1%8B%20%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85%20%D0%B4%D0%BB%D1%8F%20%D1%81%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%B8%D1%8F%20%D0%BA%D0%BE%D0%BF%D0%B8%D0%B8%0A%2F%2Ffile%20-%20%22%D0%BA%D1%83%D0%B4%D0%B0%20%D1%81%D0%BA%D0%BB%D0%B0%D0%B4%D1%8B%D0%B2%D0%B0%D1%82%D1%8C%22%20%D0%B1%D1%8D%D0%BA%D0%B0%D0%BF%0Abool%20myClass%3A%3AbackupDatabase(QString%20name%2C%20QString%20file)%0A%7B%0A%20%20%20%20QString%20binDir%20%3D%20postgresBinDirectory()%3B%0A%20%20%20%20if%20(binDir.isEmpty())%20%7B%0A%20%20%20%20%20%20%20%20error(tr(%22%D0%A1%D0%B8%D1%81%D1%82%D0%B5%D0%BC%D0%BD%D1%8B%D0%B9%20%D0%BA%D0%B0%D1%82%D0%B0%D0%BB%D0%BE%D0%B3%20bin%20%D1%81%D0%B5%D1%80%D0%B2%D0%B5%D1%80%D0%B0%20%D0%BD%D0%B5%20%D0%BD%D0%B0%D0%B9%D0%B4%D0%B5%D0%BD.%20%22%0A%09%09%09%09%20%22%D0%9E%D0%BF%D0%B5%D1%80%D0%B0%D1%86%D0%B8%D1%8F%20%D0%B2%D0%BE%D1%81%D1%81%D1%82%D0%B0%D0%BD%D0%BE%D0%B2%D0%BB%D0%B5%D0%BD%D0%B8%D1%8F%20%D0%B1%D0%B0%D0%B7%D1%8B%20%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85%20%22%0A%09%09%09%09%20%22%251%20%D0%B8%D0%B7%20%D1%80%D0%B5%D0%B7%D0%B5%D1%80%D0%B2%D0%BD%D0%BE%D0%B9%20%D0%BA%D0%BE%D0%BF%D0%B8%D0%B8%20%252%20%D0%BF%D1%80%D0%B5%D1%80%D0%B2%D0%B0%D0%BD%D0%B0%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20.arg(name).arg(file))%3B%0A%20%20%20%20%20%20%20%20return%20false%3B%0A%20%20%20%20%7D%0A%20%20%20%20if%20(file.isEmpty())%20%7B%0A%20%20%20%20%20%20%20%20QDir%20dir(QCoreApplication%3A%3AapplicationDirPath()%2B%0A%09%09%09%09%20%22%2Fdb_dumps%2F%22)%3B%0A%20%20%20%20%20%20%20%20if%20(!dir.exists())%0A%20%20%20%20%20%20%20%20%20%20%20%20dir.mkpath(QCoreApplication%3A%3AapplicationDirPath()%2B%0A%09%09%09%09%09%20%20%20%22%2Fdb_dumps%2F%22)%3B%0A%20%20%20%20%20%20%20%20file%20%3D%20QString(%22%251%2F%252-%253.dump%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.arg(QCoreApplication%3A%3AapplicationDirPath()%2B%0A%09%09%09%09%09%20%22%2Fdb_dumps%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.arg(name)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.arg(QDateTime%3A%3AcurrentDateTime()%0A%09%09%09%09%09%20.toString(%22yyyyMMddhhmm%22))%3B%0A%20%20%20%20%7D%0A%20%20%20%20QString%20text%20%3D%20QString(%0A%09%09%22%40ECHO%20OFF%20%5Cn%22%0A%20%20%20%20%20%20%20%20%22cd%20%2Fd%20%251%20%5Cn%22%0A%20%20%20%20%20%20%20%20%22pg_dump%20-Fc%20-U%20%254%20-Z%209%20-v%20%252%20%3E%20%5C%22%253%5C%22%20%5Cn%22%0A%20%20%20%20%20%20%20%20%22set%20%2Fp%20id%3D%5C%22Press%20Enter%20to%20exit…%5C%22%5Cn%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20.arg(binDir)%0A%20%20%20%20%20%20%20%20%20%20%20%20.arg(name)%0A%20%20%20%20%20%20%20%20%20%20%20%20.arg(file)%0A%20%20%20%20%20%20%20%20%20%20%20%20.arg(postgresUser)%3B%0A%20%20%20%20QString%20fileName%20%3D%20QString(%22%251-dump.bat%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20.arg(name)%3B%0A%09%2F%2F%D0%B7%D0%B0%D0%BF%D0%B8%D1%81%D1%8C%20%D0%97%D0%BD%D0%B0%D1%87%D0%B5%D0%BD%D0%B8%D1%8F%20%D1%81%D1%82%D1%80%D0%BE%D0%BA%D0%B8%20text%20%D0%B2%20%D1%84%D0%B0%D0%B9%D0%BB%20*.bat%0A%20%20%20%20if%20(!writeBatFile(text%2C%20fileName%2C%20%0A%09%09%09%09%20QCoreApplication%3A%3AapplicationDirPath()%2B%22%2Ftemp%22))%0A%09%09return%3B%0A%09%2F%2F%D0%BE%D0%B1%D0%BD%D0%BE%D0%B2%D0%BB%D0%B5%D0%BD%D0%B8%D0%B5%20%D1%84%D0%B0%D0%B9%D0%BB%D0%B0%20%D0%BF%D0%B0%D1%80%D0%BE%D0%BB%D0%B5%D0%B9%20pgpass%0A%20%20%20%20updatePgPassFile(QString(%22%251%3A%252%3A%253%3A%254%3A%255%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.arg(host%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ui-%3Eport-%3Etext()%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20name%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20postgresUser%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ui-%3Epassword-%3Etext()))%3B%0A%09%2F%2F%D0%B2%D1%8B%D0%B7%D0%BE%D0%B2%20%D1%81%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%BD%D0%BE%D0%B3%D0%BE%20batch%20%D1%84%D0%B0%D0%B9%D0%BB%D0%B0%0A%20%20%20%20system(qPrintable(%22cmd.exe%20%2Fc%20%5C%22%22%2B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20QDir%3A%3AtoNativeSeparators(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20QString(%22%251%2F%252%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.arg(QCoreApplication%3A%3AapplicationDirPath()%2B%0A%09%09%09%09%09%09%09%20%20%20%22%2Ftemp%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.arg(fileName)%2B%22%5C%22%22)))%3B%0A%09%2F%2F%D0%B5%D1%81%D0%BB%D0%B8%20%D1%84%D0%B0%D0%B9%D0%BB%20%D0%B1%D1%8D%D0%BA%D0%B0%D0%BF%D0%B0%20%D0%B8%D0%BC%D0%B5%D0%B5%D1%82%20%D0%BD%D0%B5%D0%BD%D1%83%D0%BB%D0%B5%D0%B2%D0%BE%D0%B9%20%D1%80%D0%B0%D0%B7%D0%BC%D0%B5%D1%80%0A%20%20%20%20return%20QFileInfo(file).size()%20%3E%200%3B%0A%7D» message=»» highlight=»» provider=»manual»/]

Как узнать путь к каталогу bin сервера PostgreSQL, подробно написано здесь: Путь к каталогу bin PostgreSQL Windows. При условии известности имени пользователя и его пароля можно запустить утилиту pg_dump без необходимости авторизации пользователем при наличии соответствующей строки подключения в файле паролей pgpass, про что подробно писала в этой статье: Файл паролей PostgreSQL pgpass в Windows. В вышеприведенном коде используется функция updatePgPassFile(QString), код которой можно найти по обозначенной ссылке.

Более ранняя статья на схожую тему: Резервное копирование базы данных по расписанию: MS SQL Server и PostgreSQL

PostgreSQL, Qt, Базы данных

Путь к каталогу bin PostgreSQL Windows

Задача: получить значение пути к каталогу бинарников сервера — к каталогу bin. Путь к каталогу data, как и прочие значения параметров времени выполнения, найти совершенно несложно: достаточно выполнить запрос show, воспользоваться функцией current_setting() или обратиться к представлению pg_settings:

[pastacode lang=»sql» manual=»show%20data_directory%3B%0Aselect%20current_setting(‘data_directory’)%3B%0Aselect%20setting%20from%20pg_settings%20%0A%20%20%20where%20name%20%3D%20’data_directory’%3B» message=»Три равнозначных примера получения значения пути к каталогу data» highlight=»» provider=»manual»/]

Еще парочку примеров показывала вот в этой записи: Количество подключений к БД PostgreSQL.

С значением пути к каталогу bin не все так просто — запросом его не получишь. Но можно узнать из реестра. В реестре не хранится чистый ключ со значением пути к каталогу, к сожалению, но можно узнать путь к исполняемому файлу службы сервера.

Итак, интересующий нас ключ: Продолжить чтение «Путь к каталогу bin PostgreSQL Windows»