Thank you for reading this post, don't forget to subscribe!

Получение информации о базе данных

Размер базы данных

Что­бы полу­чить физи­че­ский раз­мер фай­лов (хра­ни­ли­ща) базы дан­ных, исполь­зу­ем сле­ду­ю­щий запрос:

Резуль­тат будет пред­став­лен как чис­ло вида 41809016.

current_database() — функ­ция, кото­рая воз­вра­ща­ет имя теку­щей базы дан­ных. Вме­сто неё мож­но вве­сти имя текстом:

Для того, что­бы полу­чить инфор­ма­цию в чело­ве­ко­чи­та­е­мом виде, исполь­зу­ем функ­цию pg_size_pretty:

В резуль­та­те полу­чим инфор­ма­цию вида 40 Mb.

Перечень таблиц

Ино­гда тре­бу­ет­ся полу­чить пере­чень таб­лиц базы дан­ных. Для это­го исполь­зу­ем сле­ду­ю­щий запрос:

information_schema — стан­дарт­ная схе­ма базы дан­ных, кото­рая содер­жит кол­лек­ции пред­став­ле­ний (views), таких как таб­ли­цы, поля и т.д. Пред­став­ле­ния таб­лиц содер­жат инфор­ма­цию обо всех таб­ли­цах баз данных.

Запрос, опи­сан­ный ниже, выбе­рет все таб­ли­цы из ука­зан­ной схе­мы теку­щей базы данных:

В послед­нем усло­вии IN мож­но ука­зать имя опре­де­лен­ной схемы.

Размер таблицы

По ана­ло­гии с полу­че­ни­ем раз­ме­ра базы дан­ных раз­мер дан­ных таб­ли­цы мож­но вычис­лить с помо­щью соот­вет­ству­ю­щей функции:

Функ­ция pg_relation_size воз­вра­ща­ет объ­ём, кото­рый зани­ма­ет на дис­ке ука­зан­ный слой задан­ной таб­ли­цы или индекса.

Имя самой большой таблицы

Для того, что­бы выве­сти спи­сок таб­лиц теку­щей базы дан­ных, отсор­ти­ро­ван­ный по раз­ме­ру таб­ли­цы, выпол­ним сле­ду­ю­щий запрос:

Для того, что­бы выве­сти инфор­ма­цию о самой боль­шой таб­ли­це, огра­ни­чим запрос с помо­щью LIMIT:

relname — имя таб­ли­цы, индек­са, пред­став­ле­ния и т.п.
relpages — раз­мер пред­став­ле­ния этой таб­ли­цы на дис­ке в коли­че­ствах стра­ниц (по умол­ча­нию одна стра­ни­цы рав­на 8 Кб).
pg_class — систем­ная таб­ли­ца, кото­рая содер­жит инфор­ма­цию о свя­зях таб­лиц базы данных.

Перечень подключенных пользователей

Что­бы узнать имя, IP и исполь­зу­е­мый порт под­клю­чен­ных поль­зо­ва­те­лей, выпол­ним сле­ду­ю­щий запрос:

Активность пользователя

Что­бы узнать актив­ность соеди­не­ния кон­крет­но­го поль­зо­ва­те­ля, исполь­зу­ем сле­ду­ю­щий запрос:

Работа с данными и полями таблиц

Удаление одинаковых строк

Если так полу­чи­лось, что в таб­ли­це нет пер­вич­но­го клю­ча (primary key), то навер­ня­ка сре­ди запи­сей най­дут­ся дуб­ли­ка­ты. Если для такой таб­ли­цы, осо­бен­но боль­шо­го раз­ме­ра, необ­хо­ди­мо поста­вить огра­ни­че­ния (constraint) для про­вер­ки целост­но­сти, то уда­лим сле­ду­ю­щие элементы:

• дуб­ли­ру­ю­щи­е­ся строки,
• ситу­а­ции, когда одна или более коло­нок дуб­ли­ру­ют­ся (если эти колон­ки пред­по­ла­га­ет­ся исполь­зо­вать в каче­стве пер­вич­но­го ключа).

Рас­смот­рим таб­ли­цу с дан­ны­ми поку­па­те­лей, где задуб­ли­ро­ва­на целая стро­ка (вто­рая по счёту).

Уда­лить все дуб­ли­ка­ты помо­жет сле­ду­ю­щий запрос:

Уни­каль­ное для каж­дой запи­си поле ctid по умол­ча­нию скры­то, но оно есть в каж­дой таблице.

Послед­ний запрос тре­бо­ва­те­лен к ресур­сам, поэто­му будь­те акку­рат­ны при его выпол­не­нии на рабо­чем проекте.

Теперь рас­смот­рим слу­чай, когда повто­ря­ют­ся зна­че­ния полей.

Если допу­сти­мо уда­ле­ние дуб­ли­ка­тов без сохра­не­ния всех дан­ных, выпол­ним такой запрос:

Если дан­ные важ­ны, то сна­ча­ла нуж­но най­ти запи­си с дубликатами:

Перед уда­ле­ни­ем такие запи­си мож­но пере­не­сти во вре­мен­ную таб­ли­цу или заме­нить в них зна­че­ние customer_id на другое.

Общая фор­ма запро­са на уда­ле­ние опи­сан­ных выше запи­сей выгля­дит сле­ду­ю­щим образом:

Безопасное изменение типа поля

Может воз­ник­нуть вопрос о вклю­че­нии в этот спи­сок такой зада­чи. Ведь в PostgreSQL изме­нить тип поля очень про­сто с помо­щью коман­ды ALTER. Давай­те для при­ме­ра сно­ва рас­смот­рим таб­ли­цу с покупателями.

Для поля customer_id исполь­зу­ет­ся стро­ко­вый тип дан­ных varchar. Это ошиб­ка, так как в этом поле пред­по­ла­га­ет­ся хра­нить иден­ти­фи­ка­то­ры поку­па­те­лей, кото­рые име­ют цело­чис­лен­ный фор­мат integer. Исполь­зо­ва­ние varchar неоправ­дан­но. Попро­бу­ем испра­вить это недо­ра­зу­ме­ние с помо­щью коман­ды ALTER:

Но в резуль­та­те выпол­не­ния полу­чим ошибку:

ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.

Это зна­чит, что нель­зя про­сто так взять и изме­нить тип поля при нали­чии дан­ных в таб­ли­це. Так как исполь­зо­вал­ся тип varchar, СУБД не может опре­де­лить при­над­леж­ность зна­че­ния к integer. Хотя дан­ные соот­вет­ству­ют имен­но это­му типу. Для того, что­бы уточ­нить этот момент, в сооб­ще­нии об ошиб­ке пред­ла­га­ет­ся исполь­зо­вать выра­же­ние USING, что­бы кор­рект­но пре­об­ра­зо­вать наши дан­ные в integer:

В резуль­та­те всё про­шло без ошибок:

Обра­ти­те вни­ма­ние, что при исполь­зо­ва­нии USING кро­ме кон­крет­но­го выра­же­ния воз­мож­но исполь­зо­ва­ние функ­ций, дру­гих полей и операторов.

Напри­мер, пре­об­ра­зу­ем поле customer_id обрат­но в varchar, но с пре­об­ра­зо­ва­ни­ем фор­ма­та данных:

В резуль­та­те таб­ли­ца при­мет сле­ду­ю­щий вид:

Поиск «потерянных» значений

Будь­те вни­ма­тель­ны при исполь­зо­ва­нии после­до­ва­тель­но­стей (sequence) в каче­стве пер­вич­но­го клю­ча (primary key): при назна­че­нии неко­то­рые эле­мен­ты после­до­ва­тель­но­сти слу­чай­но про­пус­ка­ют­ся, в резуль­та­те рабо­ты с таб­ли­цей неко­то­рые запи­си уда­ля­ют­ся. Такие зна­че­ния мож­но исполь­зо­вать сно­ва, но най­ти их в боль­ших таб­ли­цах сложно.

Рас­смот­рим два вари­ан­та поиска.

Пер­вый способ
Выпол­ним сле­ду­ю­щий запрос, что­бы най­ти нача­ло интер­ва­ла с «поте­рян­ным» значением:

В резуль­та­те полу­чим зна­че­ния: 5, 9 и 11.

Если нуж­но най­ти не толь­ко пер­вое вхож­де­ние, а все про­пу­щен­ные зна­че­ния, исполь­зу­ем сле­ду­ю­щий (ресур­со­ём­кий!) запрос:

В резуль­та­те видим сле­ду­ю­щий резуль­тат: 5, 9 и 6.

Вто­рой способ
Полу­ча­ем имя после­до­ва­тель­но­сти, свя­зан­ной с customer_id:

И нахо­дим все про­пу­щен­ные идентификаторы:

Подсчёт количества строк в таблице

Коли­че­ство строк вычис­ля­ет­ся стан­дарт­ной функ­ци­ей count, но её мож­но исполь­зо­вать с допол­ни­тель­ны­ми условиями.

Общее коли­че­ство строк в таблице:

Коли­че­ство строк при усло­вии, что ука­зан­ное поле не содер­жит NULL:

Коли­че­ство уни­каль­ных строк по ука­зан­но­му полю:

Использование транзакций

Тран­зак­ция объ­еди­ня­ет после­до­ва­тель­ность дей­ствий в одну опе­ра­цию. Её осо­бен­ность в том, что при ошиб­ке в выпол­не­нии тран­зак­ции ни один из резуль­та­тов дей­ствий не сохра­нит­ся в базе данных.

Нач­нём тран­зак­цию с помо­щью коман­ды BEGIN.

Для того, что­бы отка­тить все опе­ра­ции, рас­по­ло­жен­ные после BEGIN, исполь­зу­ем коман­ду ROLLBACK.

А что­бы при­ме­нить — коман­ду COMMIT.

Просмотр и завершение исполняемых запросов

Для того, что­бы полу­чить инфор­ма­цию о запро­сах, выпол­ним сле­ду­ю­щую команду:

Для того, что­бы оста­но­вить кон­крет­ный запрос, выпол­ним сле­ду­ю­щую коман­ду, с ука­за­ни­ем id про­цес­са (pid):

Для того, что­бы пре­кра­тить рабо­ту запро­са, выполним:

Работа с конфигурацией

Поиск и изменение расположения экземпляра кластера

Воз­мож­на ситу­а­ция, когда на одной опе­ра­ци­он­ной систе­ме настро­е­но несколь­ко экзем­пля­ров PostgreSQL, кото­рые «сидят» на раз­лич­ных пор­тах. В этом слу­чае поиск пути к физи­че­ско­му раз­ме­ще­нию каж­до­го экзем­пля­ра — доста­точ­но нерв­ная зада­ча. Для того, что­бы полу­чить эту инфор­ма­цию, выпол­ним сле­ду­ю­щий запрос для любой базы дан­ных инте­ре­су­ю­ще­го кластера:

Изме­ним рас­по­ло­же­ние на дру­гое с помо­щью команды:

Но для того, что­бы изме­не­ния всту­пи­ли в силу, тре­бу­ет­ся перезагрузка.

Получение перечня доступных типов данных

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

typname — имя типа данных.
typlen — раз­мер типа данных.

Изменение настроек СУБД без перезагрузки

Настрой­ки PostgreSQL нахо­дят­ся в спе­ци­аль­ных фай­лах вро­де postgresql.conf и pg_hba.conf. После изме­не­ния этих фай­лов нуж­но, что­бы СУБД сно­ва полу­чи­ла настрой­ки. Для это­го про­из­во­дит­ся пере­за­груз­ка сер­ве­ра баз дан­ных. Понят­но, что при­хо­дит­ся это делать, но на про­дакшн-вер­сии про­ек­та, кото­рым поль­зу­ют­ся тыся­чи поль­зо­ва­те­лей, это очень неже­ла­тель­но. Поэто­му в PostgreSQL есть функ­ция, с помо­щью кото­рой мож­но при­ме­нить изме­не­ния без пере­за­груз­ки сервера:

Но, к сожа­ле­нию, она при­ме­ни­ма не ко всем пара­мет­рам. В неко­то­рых слу­ча­ях для при­ме­не­ния настро­ек пере­за­груз­ка обязательна.