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

PostgreSQL: Установка пользовательских параметров базы данных и/или сессии

Через запрос show all можно посмотреть текущие значения параметров postgres времени выполнения. Но в добавок к имеющимся системным параметрам можно создать и свои, пользовательские.

Имена пользовательских параметров обязательно должны содержать точку, например, my.version, mydb.parameter и т.д. Установить значения этих параметров можно для сессии и/или для конкретной базы данных. Новое значение для базы данных будет считано при новом подключении к ней. В текущем подключении — будет считано предыдущее значение.

Для установки значения параметра для сессии — команда

set <parameter_name> to <value>;

Для конкретной базы данных —

alter database <db_name> set <parameter_name> = <value>;

Подробнее здесь: https://www.postgresql.org/docs/current/config-setting.html. Важно: установить значение пользовательского параметра в контексте базы данных возможно, если такой параметр уже был когда-то определен для сессии.

Получить значение параметра можно через функцию current_setting(<parameter_name>) или show <parameter_name>.

Пример функции получения версии (дата последнего изменения) базы данных:

[pastacode lang=»sql» manual=»CREATE%20OR%20REPLACE%20FUNCTION%20public.db_version(%0A%09)%0A%20%20%20%20RETURNS%20timestamp%20with%20time%20zone%0A%20%20%20%20LANGUAGE%20’plpgsql’%0A%20%20%20%20COST%20100%0A%20%20%20%20VOLATILE%20PARALLEL%20UNSAFE%0AAS%20%24BODY%24%0Abegin%0A%20%20%20%20return%20(select%20current_setting(‘my.version’)%3A%3Atimestamp%20with%20time%20zone)%3B%0A%20%20%20%20exception%0A%20%20%20%20when%20others%20then%0A%20%20%20%20%20%20%20%20set%20my.version%20to%20’1900-01-01%2000%3A00’%3B%0A%20%20%20%20%20%20%20%20alter%20database%20mydatabasename%20set%20my.version%20from%20current%3B%0A%20%20%20%20return%20’1900-01-01%2000%3A00’%3A%3Atimestamp%20with%20time%20zone%3B%0Aend%3B%0A%24BODY%24%3B» message=»» highlight=»» provider=»manual»/]

Если указанный параметр не известен сессии, то произойдет его инициализация. В противном случае — будет возвращено текущее значение.

[pastacode lang=»sql» manual=»alter%20database%20makro%20set%20my.version%20%3D%20’2022-09-30%2010%3A07’%3B» message=»Установка нового значения параметра my.version» highlight=»» provider=»manual»/]

C++, SQL, SQLite

Аналог UNNEST в SQLite

Такой удобной функции, как UNNEST, в SQLite нет (пссс, об UNNEST в PostgreSQL я писала вот здесь). А что использовать, если нам нужно элементы некоего массива/списка/контейнера впихнуть в один запрос вставки данных, например? Можно воспользоваться WITH и VALUES.

Предположим, у нас есть два списка — список кодов и список соответствующих им дат. Можно коды и даты скомпоновать сразу парами, можно — раздельно. В каких-то конкретных случаях будут разные ситуации.

[pastacode lang=»sql» manual=»with%20cte_code(id%2Ccode)%20as%20(%0Aselect%20rowid%2C%20a.*%20from%20%0A%09(values(‘111’)%2C(‘222’)%2C(‘333’))%20as%20a)%2C%0Acte_dates(id%2Cdate)%20as%20(%0Aselect%20rowid%2C%20b.*%20from%20%0A%09(values(‘2022-08-09’)%2C(‘2022-08-10’)%2C%0A%09%20(‘2022-08-11’))%20as%20b)%0A%0Ainsert%20into%20ones(task_id%2C%20code%2C%20date)%0Aselect%201%2C%20a.code%2C%20b.date%0Afrom%20cte_code%20a%20%0Ainner%20join%20cte_dates%20b%20on%20b.id%20%3D%20a.id» message=»Списки поотдельности» highlight=»1,4,9″ provider=»manual»/]

cte_code — список кодов, cte_dates — список дат. task_id — некая константа.

[pastacode lang=»sql» manual=»with%20cte_pairs(code%2C%20date)%20as%20(%0Aselect%20*%20from%20%0A%09(values(‘111’%2C%20’2022-08-09’)%2C%0A%09%20(‘222’%2C%20’2022-08-10’)%2C%0A%09%20(‘333’%2C%20’2022-08-11’)))%0A%0Ainsert%20into%20ones(task_id%2C%20code%2C%20date)%0Aselect%201%2C%20a.code%2C%20a.date%0Afrom%20cte_pairs%20a» message=»Спарованный список» highlight=»1″ provider=»manual»/]

cte_pairs — оба наших списка в одном.

Строка запроса в Qt C++, учитывая наличие QStringList codes и QStringList dates, может выглядеть так:

[pastacode lang=»cpp» manual=»QStringList%20codes%2C%20dates%3B%0AQString%20qry%20%3D%20QString(%22with%20cte_code(id%2Ccode)%20as%20(%20%22%0A%09%09%09%09%09%20%20%22select%20rowid%2C%20a.*%20from%20%22%0A%09%09%09%09%09%20%20%22(values(‘%252’))%20as%20a)%2C%20%22%0A%09%09%09%09%09%20%20%22cte_dates(id%2Cdate)%20as%20(%20%22%0A%09%09%09%09%09%20%20%22select%20rowid%2C%20b.*%20%22%0A%09%09%09%09%09%20%20%22from%20(values(‘%251′))%20as%20b)%20%22%0A%09%09%09%09%09%20%20%0A%09%09%09%09%09%20%20%22insert%20into%20ones(task_id%2C%20code%2C%20date)%20%22%0A%09%09%09%09%09%20%20%22select%201%2C%20a.code%2C%20b.date%20%22%0A%09%09%09%09%09%20%20%22from%20cte_code%20a%20%22%0A%09%09%09%09%09%20%20%22inner%20join%20cte_dates%20b%20on%20b.id%20%3D%20a.id%22)%0A%09.arg(dates.join(%22’)%2C%20(‘%22))%0A%09.arg(codes.join(%22’)%2C%20(‘%22))%3B» message=»» highlight=»» provider=»manual»/]

PostgreSQL

PostgreSQL: Запрос на получение списка комментариев к колонкам таблицы

В сем посте будет освещен один из способов хранить человекочитаемые наименования колонок таблицы.

Организовать это можно через комментарий к полю. Задать его можно через интерфейс pgAdmin, в свойствах поля:

Или прописать в скрипте:

[pastacode lang=»sql» manual=»COMMENT%20ON%20COLUMN%20public.products.product_id%0A%20%20%20%20IS%20’%D0%9A%D0%BE%D0%BC%D0%BC%D0%B5%D0%BD%D1%82%D0%B0%D1%80%D0%B8%D0%B9’%3B» message=»Добавление комментария к колонке» highlight=»» provider=»manual»/]

Допустим, комментарии ко всем колонкам прописаны. Как их теперь получить запросом, чтоб красивенько вывести таблицу на форме?

[pastacode lang=»sql» manual=»select%20subq.attname%3A%3Atext%2C%20d.description%0A%09FROM%20%0A%09%09(SELECT%20c.relname%2C%20obj_description(c.oid)%20obj_description%2C%20a.attname%2C%20c.oid%2C%20a.attnum%0A%09%09FROM%20pg_class%20c%2C%20pg_attribute%20a%0A%09%09WHERE%20c.oid%20%3D%20a.attrelid%0A%09%09AND%20c.relname%20in%20(‘products’)%0A%09%09AND%20a.attnum%20%3E%200)%20subq%20%0A%09LEFT%20OUTER%20JOIN%20pg_description%20d%20ON%20(d.objsubid%20%3D%20subq.attnum%20AND%20d.objoid%20%3D%20subq.oid)%0A%09where%20subq.attname%20not%20ilike%20’%25pg.dropped%25’%3B» message=»Имена колонок таблицы products и комментарии к ним» highlight=»6″ provider=»manual»/]

В скобках в выделенной строке можно перечислить несколько таблиц через запятую. Условие после WHERE нужно для того, чтобы в результате запроса не фигурировали строки, оставшиеся после удаленных колонок таблицы. Вот такие:

 

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, SQL

Прервать соединения IP с базой данных в PostgreSQL

В сообщении Принудительное удаление базы данных в PostgreSQL я писала о том, как принудительно закрыть все соединения с базой данных. Это удобно, когда есть «висящие» соединения, которые никак не хотят завершаться. Или нужно срочно удалить базу данных и нет времени выяснять, кто забыл от нее отключиться 🙂 Сегодня я хочу показать, как прервать соединения IP с базой данных.

Сначала выведем количество подключений с каждого IP к базе данных: Продолжить чтение «Прервать соединения IP с базой данных в PostgreSQL»

PostgreSQL, Qt, Портфолио

DBServerWizard — автоматизация работы с базами данных PostgreSQL

Сегодня хочу познакомить вас с утилитой DBServerWizard, написание которой закончила намедни. Если коротко, ее предназначение — самые базовые элементы управления базами данных PostgreSQL.

МенюУстановка сервераРезервное копирование/ восстановление из копииСоздание базы данных
Меню
Установка сервера
Бэкап/рестор
Создание БД

А если расписать немного подробнее, то перечень такой:

  • Установка сервера PostgreSQL (включает предварительную проверку наличия уже установленного сервера).
  • Создание и удаление баз данных. Есть некоторый обязательный набор баз данных; при подключении к серверу происходит проверка наличия этого минимального набора, если какие-либо БД из этого списка отсутствуют, то предлагается произвести создание БД и восстановление из бэкапа. Доступно принудительное удаление при наличии «застрявших» подключений, мешающих операции.
  • Резервное копирование и восстановление из копии. Перед восстановлением базы данных производится резервное копирование «на всякий случай».
  • Обновление баз данных из скриптов. Скрипт разделяется на отдельные инструкции, которые выполняются последовательно. Невыполненные инструкции записываются в отдельный файл.

Следующие сообщения были написаны в течение работы над утилитой:

Qt: Восстановление pg_restore БД PostgreSQL в Windows

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

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

Файл паролей PostgreSQL pgpass в Windows

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

Чтобы заказать проект или получить консультацию, перейдите, пожалуйста, на страницу Контакты.

Моё портфолио

Инфо обо мне

PostgreSQL, Qt

Qt: Восстановление pg_restore БД PostgreSQL в Windows

Здесь: Qt: Резервная копия pg_dump БД PostgreSQL в Windows я показала один из способов организации создания резервной копии базы данных.

Сейчас речь пойдет о восстановлении базы из бэкапа. Здесь тоже будем использовать пакетный файл для вызова pg_restore.

Про каталог bin, файл паролей pgpass написано в статье по ссылке выше.

[pastacode lang=»cpp» manual=»%2F%2Fname%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%0A%2F%2Ffile%20-%20%D1%84%D0%B0%D0%B9%D0%BB%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%0Avoid%20myClass%3A%3ArestoreDatabase(QString%20name%2C%20QString%20file)%0A%7B%0A%20%20%20%20QString%20errorMsg%3B%0A%20%20%20%20QString%20connectionName%20%3D%20connectToDB(name%2C%20errorMsg)%3B%0A%20%20%20%20if%20(connectionName.isEmpty())%7B%0A%20%20%20%20%20%20%20%20error(errorMsg)%3B%0A%20%20%20%20%20%20%20%20return%3B%0A%20%20%20%20%7D%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%D0%9E%D0%BF%D0%B5%D1%80%D0%B0%D1%86%D0%B8%D1%8F%20%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22%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%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%20QSqlDatabase%3A%3AremoveDatabase(connectionName)%3B%0A%20%20%20%20%20%20%20%20return%3B%0A%20%20%20%20%7D%0A%20%20%20%20QSqlQuery%20query(QSqlDatabase%3A%3Adatabase(connectionName))%3B%0A%09%2F%2F%D1%81%D0%BD%D0%B0%D1%87%D0%B0%D0%BB%D0%B0%20%D1%83%D0%B4%D0%B0%D0%BB%D1%8F%D0%B5%D0%BC%20%D1%81%D1%85%D0%B5%D0%BC%D1%83%20(%D1%83%20%D0%BC%D0%B5%D0%BD%D1%8F%20%D0%BF%D0%B0%D0%B1%D0%BB%D0%B8%D0%BA)%2C%20%0A%09%2F%2F%D0%B7%D0%B0%D1%82%D0%B5%D0%BC%20%D0%B5%D0%B5%20%D1%81%D0%BD%D0%BE%D0%B2%D0%B0%20%D1%81%D0%BE%D0%B7%D0%B4%D0%B0%D0%B5%D0%BC%20-%20%D1%87%D0%B8%D1%81%D1%82%D0%B5%D0%BD%D1%8C%D0%BA%D1%83%D1%8E%0A%20%20%20%20if%20(!query.exec(QString(%22DROP%20SCHEMA%20public%20cascade%3B%20%20%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%20%20%22CREATE%20SCHEMA%20public%20%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%20%20%22AUTHORIZATION%20postgres%3B%20%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%20%20%22COMMENT%20ON%20SCHEMA%20public%20%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%20%20%22IS%20’standard%20public%20schema’%3B%20%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%20%20%22GRANT%20ALL%20ON%20SCHEMA%20public%20TO%20PUBLIC%3B%20%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%20%20%22GRANT%20ALL%20ON%20SCHEMA%20public%20TO%20%251%3B%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.arg(postgresUser)))%20%7B%0A%20%20%20%20%20%20%20%20error(tr(%22%D0%9E%D1%88%D0%B8%D0%B1%D0%BA%D0%B0%20%D1%83%D0%B4%D0%B0%D0%BB%D0%B5%D0%BD%D0%B8%D1%8F%20%D1%81%D1%85%D0%B5%D0%BC%D1%8B%20%D0%B1%D0%B0%D0%B7%D1%8B%20%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85%20%251%3A%20%252%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20.arg(name).arg(query.lastError().text()))%3B%0A%20%20%20%20%20%20%20%20QSqlDatabase%3A%3AremoveDatabase(connectionName)%3B%0A%20%20%20%20%20%20%20%20return%3B%0A%20%20%20%20%7D%0A%20%20%20%20QSqlDatabase%3A%3AremoveDatabase(connectionName)%3B%0A%09%2F%2F%D1%82%D0%B5%D0%BA%D1%81%D1%82%20batch-%D1%84%D0%B0%D0%B9%D0%BB%D0%B0%0A%20%20%20%20QString%20batText%20%3D%20QString(%22%40ECHO%20OFF%20%5Cn%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%20%20%20%20%22SET%20dmpfile%3D%251%20%5Cn%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%20%20%20%20%22set%20text%20%3D%20Begin%20to%20restore…%20%20%5Cn%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%20%20%20%20%22echo%20%25text%25%20%5Cn%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%20%20%20%20%22cd%20%2Fd%20%252%20%5Cn%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%20%20%20%20%22pg_restore%20—host%3D%254%20—port%3D%255%20%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%20%20%20%20%22—username%3D%256%20—verbose%20-d%20%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%20%20%20%20%22%20%253%20%5C%22%25dmpfile%25%5C%22%5Cn%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%20%20%20%20%22set%20%2Fp%20id%3D%5C%22The%20process%20is%20finished%20successfully.%20%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%20%20%20%20%22Press%20Enter%20to%20exit…%5C%22%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20.arg(file%2C%20binDir%2C%20name%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20host%2C%20ui-%3Eport-%3Etext()%2C%20postgresUser)%3B%0A%20%20%20%20QString%20fileName%20%3D%20QString(%22%251-restore.bat%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20.arg(name)%3B%0A%20%20%20%20if%20(!writeBatFile(batText%2C%20fileName%2C%20%0A%09%09%09%09%09%20%20QCoreApplication%3A%3AapplicationDirPath()%2B%22%2Ftemp%22))%0A%20%20%20%20%20%20%20%20return%3B%0A%09%2F%2F%D0%BE%D0%B1%D0%BD%D0%BE%D0%B2%D0%BB%D1%8F%D0%B5%D0%BC%20%D1%84%D0%B0%D0%B9%D0%BB%20%D0%BF%D0%B0%D1%80%D0%BE%D0%BB%D0%B5%D0%B9%2C%20%D0%B5%D1%81%D0%BB%D0%B8%20%D0%BD%D1%83%D0%B6%D0%BD%D0%BE%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%D1%8B%D0%B2%D0%B0%D0%B5%D0%BC%20%D0%BF%D0%B0%D0%BA%D0%B5%D1%82%D0%BD%D1%8B%D0%B9%20%D1%84%D0%B0%D0%B9%D0%BB%20%D0%B4%D0%BB%D1%8F%20%D0%B2%D1%8B%D0%BF%D0%BE%D0%BB%D0%BD%D0%B5%D0%BD%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%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%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%7D» message=»» highlight=»» provider=»manual»/]

 

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, Базы данных

Файл паролей PostgreSQL pgpass в Windows

Для восстановления базы данных из резервной копии используется исполняемый файл сервера pg_restore. Если в настройках сервера выставлена авторизация по имени и паролю, то при использовании утилиты необходимо вводить пароль при каждой операции восстановления БД из бэкапа. Что не подходит в случае пакетной операции, например, или когда операция должна производится без взаимодействия с пользователем в этом ключе. Тогда сервер использует файл паролей pgpass. Он может и отсутствовать. Что тогда делать? Создать и обновлять. Продолжить чтение «Файл паролей PostgreSQL pgpass в Windows»