Основни команди на PostgreSQL. Основни команди на PostgreSQL Намиране и промяна на местоположението на екземпляр на клъстер

Здравейте, днес искам да направя малка бележка за основните команди на PostgreSQL. Можете да работите с PosgreSQL както интерактивно, така и от командния ред. Програмата е psql. Сигурен съм, че този списък ще ви бъде много полезен и ще ви спести време за търсене в различни ресурси. Нека ви напомня, че това е проект с отворен код, базиран на СУБД Postgres, пуснат през 1986 г., разработва се от световна група разработчици на PGDG, по същество това е 5-8 души, но въпреки това, той се развива много интензивно, като въвежда всички нови функции и поправя стари бъгове и грешки.

Основни команди на PostgreSQL в интерактивен режим:

• \ свържете db_name - свържете се с база данни с име db_name
• \ du - списък с потребители
• \ dp (или \ z) - списък с таблици, изгледи, поредици, права за достъп до тях
• \ di - индекси
• \ ds - последователности
• \ dt - списък с таблици
• \ dt + - списък на всички таблици с описание
• \ dt * s * - списък на всички таблици, съдържащи s в името
• \ dv - изгледи
• \ dS - системни таблици
• \ d + - описание на таблицата
• \ o - изпращане на резултатите от заявката във файл
• \ l - списък с бази данни
• \ i - четене на входящи данни от файл
• \ e - отваря текущото съдържание на буфера на заявката в редактора (освен ако не е посочено друго в средата на променливата EDITOR, vi ще се използва по подразбиране)
• \ d „име_на таблица“ - описание на таблицата
• \ изпълнявам команда от външен файл като \ i /my/directory/my.sql
• \ pset - команда за настройка на параметри за форматиране
• \ ехо - показва съобщение
• \ set - задава стойността на променлива на средата. Без параметри, показва списък с текущи променливи (\ unset - премахва).
• \? - psql справка
• \ помощ - SQL справка
• \ q (или Ctrl + D) - излезте от програмата

Работа с PostgreSQL от командния ред:

• -c (или -command) - Изпълнете SQL командата, без да влизате в интерактивен режим
• -f file.sql - изпълнява команди от файла file.sql
• -l (или -list) - изброява наличните бази данни
• -U (или -username) - посочете потребителското име (например postgres)
• -W (или -password) - подкана за парола
• -d dbname - свързване към база данни dbname
• -h - име на хост (сървър)
• -s - режим стъпка по стъпка, тоест ще трябва да потвърдите всички команди
• –S - едноредов режим, тоест нов ред ще изпълни заявката (отърви се от; в края на SQL израза)
• -V - Версия на PostgreSQL без влизане в интерактивен режим

Примери:

psql -U postgres -d dbname -c „СЪЗДАВАНЕ НА ТАБЛИЦА my (some_id сериен ПРАВИЛЕН КЛЮЧ, some_text text);“ - изпълнение на командата в базата данни dbname.

psql -d dbname -H -c "SELECT * FROM my" -o my.html - извежда резултата от заявката в html файл.

PosgreSQL помощни програми (програми):

• createdb и dropdb - създайте и пуснете база данни (съответно)
• createuser и dropuser - създаване и потребител (съответно)
• pg_ctl - програма, предназначена за решаване на общи задачи за управление (стартиране, спиране, конфигуриране на параметри и др.)
• postmaster - многопотребителски сървърен модул на PostgreSQL (задаване на нива за отстраняване на грешки, портове, директории с данни)
• initdb - създаване на нови PostgreSQL клъстери
• initlocation - програма за създаване на директории за вторично съхранение на бази данни
• vacuumdb - физическа и аналитична поддръжка на база данни
• pg_dump - архивиране и възстановяване на данни
• pg_dumpall - архивиране на целия PostgreSQL клъстер
• pg_restore - възстановяване на база данни от архиви (.tar, .tar.gz)

Примери за създаване на резервни копия:

Създаване на резервно копие на базата данни mydb, в компресиран вид

Pg_dump -h localhost -p 5440 -U някой потребител -F c -b -v -f mydb.backup mydb

Създаване на резервно копие на базата данни mydb, под формата на обикновен текстов файл, включително команда за създаване на база данни

Pg_dump -h localhost -p 5432 -U някой потребител -C -F p -b -v -f mydb.backup mydb

Създаване на резервно копие на базата данни mydb, в компресирана форма, с таблици, които съдържат плащания в името

Pg_dump -h localhost -p 5432 -U някой потребител -F c -b -v -t * плащания * -f payment_tables.backup mydb

Изхвърлете данни само от една конкретна таблица. Ако трябва да архивирате няколко таблици, тогава имената на тези таблици са изброени с помощта на превключвателя -t за всяка таблица.

Pg_dump -a -t име на таблица -f име на файл име на база данни

Правене на резервно копие с gz компресия

Pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c> mydb.gz

Списък с най-често използваните опции:

• -h хост - хост, ако не е посочен, тогава се използва локален хост или стойността от променливата на средата PGHOST.
• -p порт - порт, ако не е посочено, тогава се използва 5432 или стойността от променливата на средата PGPORT.
• -u - потребител, ако не е посочен, се използва текущият потребител, стойността може да бъде посочена и в променливата на средата PGUSER.
• -a, -data-only - изхвърлят само данни, данните и схемата се запазват по подразбиране.
• -b - включва големи обекти (блогове) в сметището.
• -s, -schema-only - само изхвърляне на схема.
• -C, -create - добавя команда за създаване на база данни.
• -c - добавя команди за пускане (пускане) на обекти (таблици, изгледи и т.н.).
• -O - ​​не добавяйте команди за задаване на собственика на обект (таблици, изгледи и т.н.).
• -F, -format (c | t | p) - формат на изходен дъмп, персонализиран, tar или обикновен текст.
• -t, -table = TABLE - посочете конкретна таблица за дъмпа.
• -v, -verbose - извежда подробна информация.
• -D, -attribute-inserts - изхвърляне с помощта на команда INSERT със списък с имена на свойства.

Архивирайте всички бази данни с помощта на командата pg_dumpall.

Pg_dumpall> all.sql

Възстановяване на таблици от резервни копия (резервни копия):

psql - възстановяване на архиви, съхранени в обикновен текстов файл;
pg_restore - възстановяване на компресирани архиви (tar);

Възстановяване на целия архив, игнориране на грешки

Psql -h локален хост -U някой потребител -d dbname -f mydb.sql

Възстановяване на целия архив, спиране при първата грешка

Psql -h localhost -U someuser -set ON_ERROR_STOP = on -f mydb.sql

За да възстановим от tar архив, трябва първо да създадем база данни, използвайки CREATE DATABASE mydb; (ако опцията -C не е посочена при създаване на резервно копие) и възстановяване

Pg_restore -dbname = mydb -jobs = 4 -verbose mydb.backup

Възстановяване на gz-компресиран архив на база данни

psql -U postgres -d mydb -f mydb

Мисля, че базата данни на postgresql вече ще ви бъде по-ясна. Надявам се този списък с команди на PostgreSQL да ви е бил полезен.

Последна актуализация: 17.03.2018г

За да създадете таблици, използвайте командата CREATE TABLE, последвана от името на таблицата. Има също така редица оператори, които могат да се използват с тази команда, които дефинират колоните на таблицата и техните атрибути. Общият синтаксис за създаване на таблица е както следва:

CREATE TABLE име_на_таблица (име_на_колона1 тип данни колона_атрибути1, име_на_колона2 тип данни колона_атрибути2, .................................... .. .......... column_nameN тип данни column_ attributesN, table_ attributes);

Спецификацията за всички колони е изброена в скоби след името на таблицата. Освен това за всяка колона трябва да се посочи името и вида на данните, които ще представлява. Типът данни определя какви данни (числа, низове и т.н.) може да съдържа една колона.

Например, нека създадем таблица в базата данни чрез pgAdmin. За да направите това, първо изберете целевата база данни в pgAdmin, щракнете с десния бутон върху нея и изберете елемента Query Tool ... в контекстното меню:

След това ще се отвори поле за въвеждане на SQL код. Освен това таблицата ще бъде създадена специално за базата данни, за която ще отворим това поле за въвеждане на SQL.

CREATE TABLE клиенти (ID SERIAL ПЪРВИЧЕН КЛЮЧ, Име, РАЗЛИЧЕН ХАРАКТЕР (30), Фамилно име, РАЗЛИЧЕН ХАРАКТЕР (30), Email CHARACTER VARYING (30), Age INTEGER);

В този случай таблицата „Клиенти“ дефинира пет колони: Id, First Name, LastName, Age, Email. Първата колона, Id, представлява идентификатора на клиента, той служи като първичен ключ и следователно е от тип SERIAL. Всъщност тази колона ще съхранява числовата стойност 1, 2, 3 и т.н., която автоматично ще се увеличава с едно за всеки нов ред.

Следващите три колони представляват собственото име, фамилията и имейл адреса на клиента и са от тип CHARACTER VARYING (30), тоест представляват низ от не повече от 30 знака.

Последната колона, Възраст, представлява възрастта на потребителя и е от тип INTEGER, тоест съхранява числа.

И след изпълнение на тази команда, таблицата с клиенти ще бъде добавена към избраната база данни.

Отпадане на маси

За да пуснете таблици, използвайте командата DROP TABLE, която има следния синтаксис:

ИЗПУСКАНЕ ТАБЛИЦА таблица1 [, таблица2, ...];

Например отпадане на таблицата с клиенти.

Алтернатива на работата с базата данни през конзолата е по-интуитивна среда за всеки клиент с графичен интерфейс. Например, pgAdmin... Инсталирането му е много лесно, стартираме го с права на суперпотребител:

sudo apt-get install pgadmin3

Сега можете да бягате pgAdminчрез графичен интерфейс. Изберете локалния сървър на база данни от списъка (има порт по подразбиране 5432) и добавете базата данни, която вече сме създали с параметрите, които посочихме.

pgAdmin

В този локален сървър освен възела Бази данниможете също да намерите възел, наречен Роли за влизане- всички налични роли.

Нека изберем от всички налични бази данни тази, с която сме създали. Базата данни съдържа много различни видове обекти. Сред специално внимание трябва да се обърне на таблиците и последователностите ( последователности).

Най-често обикновената таблица съдържа първичен ключ под формата на положителна числова стойност. За всеки ред тази стойност трябва да е уникална, така че за да не я задавате директно в заявката всеки път, можете да зададете последователността като стойност по подразбиране.

Първо, нека създадем последователност. Ние избираме Последователности - Нова последователност... В първия раздел въведете името и го посочете като Собственикролята, която сме създали. Тази последователност няма да е достъпна за други роли. На втория раздел в полетата Увеличениеи Започнетевъвеждаме един по един (освен ако не изисквате друго). В последния раздел на диалоговия прозорец можете да видите получената SQL заявка, която ще бъде изпълнена спрямо базата данни.

След като последователността е създадена, нека започнем да създаваме таблицата. Посочваме и нейното име и собственик (собственик). В четвъртия раздел Колонидобавяме първо първичния ключ. Бутон Добавете, в прозореца, който се показва, посочете името на колоната, например документ за самоличност... Избираме като тип данни bigint... На втория раздел Определениев полето Стойност по подразбиранение посочваме нашата последователност. Полето трябва да съдържа стойност на формуляра nextval ("message_id_seq" :: regclass)... Тоест всеки път, когато се добави нов ред, следващата стойност ще бъде взета от последователността. Добавени са други колони според нуждите. И накрая, в раздела Ограничениядобавете ограничение към първичния ключ ( Първичен ключ). В последния раздел можем да се възхищаваме на получения SQL код, който pgAdmin генерира. След като щракнете върху OK, таблицата ще бъде създадена.

Вместо bigint като първичен ключ, можете да го посочите като тип колона голям сериал... Този тип се увеличава автоматично с всеки добавян нов ред, така че няма нужда да създавате последователност за него. Тоест, в най-простия случай, ако нямате специални правила за генериране на идентификатора на записа, можете да препоръчате използването на bigserial.

Нека видим съдържанието на нашата таблица. За да направите това, щракнете с десния бутон върху него и изберете от контекстното меню Преглед на данни - Вижте 100 реда.

В същия прозорец можете бързо да редактирате стойността на всяка клетка в таблицата. Ако вашата таблица има повече от 100 записа, променете броя на записите, показани в падащия списък в горната част на прозореца. Можете да покажете 100, 500, 1000 или всички записи. Но това не е нищо повече от начин за бързо разглеждане на съдържанието на таблицата и не бива да свиквате с него. Ако имате десетки хиляди записи в таблицата си, не бих препоръчал да показвате всички записи наведнъж - в този случай е по-добре да напишете заявка с помощта на параметри лимити изместване.

15 полезни PostgreSQL команди

В мрежата има много уроци за PostgreSQL, които описват основните команди. Но когато се потопите по-дълбоко в работата, възникват практически проблеми, които изискват напреднали екипи.

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

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

Размер на базата данни

За да получим физическия размер на файловете (хранилище) на базата данни, използваме следната заявка:

ИЗБЕРЕТЕ pg_database_size (текуща_база от данни ());

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

current_database () е функция, която връща името на текущата база данни. Вместо това можете да въведете име в текст:

SELECT pg_database_size ("моята_база от данни");

За да получим информация в разбираема от човека форма, ние използваме функцията pg_size_pretty:

ИЗБЕРЕТЕ pg_size_pretty (pg_database_size (текуща_база от данни ()));

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

Списък с таблици

Понякога искате да получите списък с таблици на базата данни. За да направите това, използвайте следната заявка:

ИЗБЕРЕТЕ име_на_таблица ОТ information_schema.tables WHERE table_schema НЕ В ("информационна_схема", "pg_catalog");

information_schema е стандартна схема на база данни, която съдържа колекции от изгледи като таблици, полета и т.н. Изгледите на таблици съдържат информация за всички таблици в база данни.

Заявката, описана по-долу, ще избере всички таблици от посочената схема на текущата база данни:

ИЗБЕРЕТЕ име_на_таблица ОТ information_schema.tables КЪДЕТО table_schema НЕ В ("information_schema", "pg_catalog") И table_schema IN ("public", "myschema");

В последната IN клауза можете да посочите името на конкретна схема.

Размер на масата

По аналогия с получаването на размера на базата данни, размерът на данните от таблицата може да бъде изчислен с помощта на съответната функция:

ИЗБЕРЕТЕ pg_relation_size ("сметки");

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

Името на най-голямата маса

За да се покаже списък с таблици в текущата база данни, сортирани по размер на таблицата, изпълнете следната заявка:

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

За да покажем информация за най-голямата таблица, ние ограничаваме заявката с LIMIT:

ИЗБЕРЕТЕ relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;

relname е името на таблицата, индекса, изгледа и т.н.
relpages - размерът на изгледа на диска на тази таблица в брой страници (по подразбиране една страница е 8 KB).
pg_class е системна таблица, която съдържа информация за връзките между таблиците на базата данни.

Списък на свързани потребители

За да разберете името, IP и използвания порт на свързаните потребители, изпълнете следната заявка:

ИЗБЕРЕТЕ име на данни, потребителско име, клиент_адрес, клиент_порт ОТ pg_stat_activity;

Потребителска активност

За да разберем активността на връзката на конкретен потребител, използваме следната заявка:

SELECT datname FROM pg_stat_activity WHERE usename = "devuser";

Работа с полета с данни и таблица

Премахване на дублиращи се редове

Ако се случи така, че таблицата няма първичен ключ, тогава има вероятност да има дубликати между записите. Ако за такава таблица, особено голяма, е необходимо да зададете ограничения за проверка на целостта, тогава ще премахнем следните елементи:

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

Помислете за таблица с данни за клиенти, където се дублира цял ред (вторият по ред).

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

ИЗТРИВАНЕ ОТ клиенти, КЪДЕТО ctid НЕ В (ИЗБЕРЕТЕ макс (ctid) ОТ клиенти ГРУПА ПО клиенти. *);

Полето ctid, което е уникално за всеки запис, е скрито по подразбиране, но присъства във всяка таблица.

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

Сега разгледайте случая, когато стойностите на полетата се повтарят.

Ако е възможно да изтриете дубликати, без да запазите всички данни, ще изпълним следната заявка:

ИЗТРИВАНЕ ОТ клиенти, КЪДЕТО ctid НЕ В (ИЗБЕРЕТЕ макс (ctid) ОТ клиенти ГРУПА BY customer_id);

Ако данните са важни, първо трябва да намерите записи с дубликати:

SELECT * FROM clients WHERE ctid NOT IN (ИЗБЕРЕТЕ макс (ctid) FROM clients GROUP BY customer_id);

Преди да изтриете такива записи, можете да преминете към временна таблица или да замените стойността customer_id в тях с друга.

Общата форма на искане за изтриване на описаните по-горе записи е както следва:

DELETE FROM table_name WHERE ctid NOT IN (ИЗБЕРЕТЕ макс (ctid) FROM table_name GROUP BY column1,);

Променете типа поле безопасно

Може да възникне въпросът относно включването на такава задача в този списък. Всъщност в PostgreSQL е много лесно да промените типа на полето с помощта на командата ALTER. Нека отново да разгледаме таблицата с клиенти като пример.

Полето customer_id използва типа данни на низа varchar. Това е грешка, тъй като това поле трябва да съхранява идентификатори на клиенти, които са в целочислен формат. Използването на varchar не е оправдано. Нека се опитаме да коригираме това недоразумение с помощта на командата ALTER:

ALTER TABLE клиенти ALTER COLUMN customer_id TYPE цяло число;

Но в резултат на изпълнението получаваме грешка:

ГРЕШКА: колоната „customer_id“ не може да бъде прехвърлена автоматично към тип цяло число
SQL състояние: 42804
Съвет: Посочете израз USING, за да извършите преобразуването.

Това означава, че не можете просто да вземете и промените типа на поле, ако има данни в таблицата. Тъй като е използван тип varchar, СУБД не може да определи дали стойността е цяло число. Въпреки че данните отговарят точно на този тип. За да се изясни тази точка, съобщението за грешка предлага използването на израза USING за правилно преобразуване на нашите данни в цяло число:

ALTER TABLE клиенти ALTER COLUMN customer_id TYPE integer USING (customer_id :: integer);

В резултат всичко мина без грешки:

Моля, имайте предвид, че при използване на USING, в допълнение към конкретен израз, е възможно да се използват функции, други полета и оператори.

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

ALTER TABLE клиенти ALTER COLUMN customer_id TYPE varchar ИЗПОЛЗВАНЕ (customer_id || "-" || first_name);

В резултат на това таблицата ще изглежда така:

Намиране на "загубени" ценности

Бъдете внимателни, когато използвате последователности като първичен ключ: при присвояване някои елементи от последователността случайно се пропускат и в резултат на работа с таблицата някои записи се изтриват. Тези стойности могат да се използват отново, но са трудни за намиране в големи таблици.

Нека разгледаме две опции за търсене.

Първият начин
Нека изпълним следната заявка, за да намерим началото на интервала с "загубената" стойност:

SELECT customer_id + 1 FROM clients mo WHERE NOT EXISTS (SELECT NULL FROM client mi WHERE mi.customer_id = mo.customer_id + 1) ORDER BY customer_id;

В резултат получаваме стойностите: 5, 9 и 11.

Ако трябва да намерите не само първото срещане, но и всички липсващи стойности, ние използваме следната (ресурно интензивна!) заявка:

С seq_max AS (SELECT max (customer_id) FROM clients), seq_min AS (SELECT min (customer_id) FROM clients) SELECT * FROM generate_series ((SELECT min FROM seq_min), (SELECT max FROM seq_max)) EXCEPT SELECT customer_id FROM клиенти;

В резултат на това виждаме следния резултат: 5, 9 и 6.

Втори начин
Получаваме името на последователността, свързана с customer_id:

SELECT pg_get_serial_sequence ("клиенти", "идентификатор на клиента");

И намираме всички липсващи идентификатори:

WITH_sequence_info AS (SELECT start_value, last_value FROM "SchemaName". "SequenceName") SELECT generate_series ((sequence_info.start_value), (sequence_info.last_value)) FROM sequence_info EXCEPT SELECT customer_id FROM клиенти;

Преброяване на броя на редовете в таблица

Броят на редовете се изчислява от стандартната функция за броене, но може да се използва с допълнителни условия.

Общият брой редове в таблицата:

SELECT count (*) FROM таблица;

Брой редове, при условие че посоченото поле не съдържа NULL:

SELECT count (col_name) FROM таблица;

Броят на уникалните редове за посоченото поле:

SELECT count (distinct col_name) FROM таблица;

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

Транзакцията комбинира последователност от действия в една операция. Неговата особеност е, че ако има грешка при изпълнението на транзакция, нито един от резултатите от действията няма да бъде запазен в базата данни.

Нека стартираме транзакция с помощта на командата BEGIN.

За да върнете обратно всички операции след BEGIN, използвайте командата ROLLBACK.

И за прилагане - командата COMMIT.

Преглед и попълване на изпълними заявки

За да получите информация за заявките, изпълнете следната команда:

SELECT pid, age (query_start, clock_timestamp ()), usename, query FROM pg_stat_activity WHERE заявка! = " "И заявка НЕ ​​ОБЯЗА"% pg_stat_activity% "ПОРЪЧАЙ ПО query_start desc;

За да спрете конкретна заявка, изпълнете следната команда, като посочите идентификатора на процеса (pid):

SELECT pg_cancel_backend (procpid);

За да прекратите заявката, изпълнете:

SELECT pg_terminate_backend (procpid);

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

Намиране и промяна на местоположението на екземпляр на клъстер

Възможна е ситуация, когато на една операционна система са конфигурирани няколко екземпляра на PostgreSQL, които "седят" на различни портове. В този случай намирането на път до физическото местоположение на всеки екземпляр е доста изнервяща задача. За да получим тази информация, ще изпълним следната заявка за всяка база данни от интересния клъстер:

ПОКАЖЕТЕ директория_данни;

Нека променим местоположението на нещо друго с помощта на командата:

SET data_directory на new_directory_path;

Но е необходимо рестартиране, за да влязат в сила промените.

Получаване на списък с налични типове данни

Нека получим списък с налични типове данни с помощта на командата:

SELECT typname, typlen от pg_type, където typtype = "b";

typname е името на типа данни.
typlen е размерът на типа данни.

Промяна на настройките на СУБД без рестартиране

Настройките на PostgreSQL се намират в специални файлове като postgresql.conf и pg_hba.conf. След промяна на тези файлове, СУБД трябва да получи настройките отново. За да направите това, сървърът на базата данни се рестартира. Ясно е, че трябва да направите това, но в производствената версия на проекта, която се използва от хиляди потребители, това е много нежелателно. Следователно PostgreSQL има функция, с която можете да прилагате промени, без да рестартирате сървъра:

ИЗБЕРЕТЕ pg_reload_conf ();

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

Обхванахме командите, за да улесним нещата за разработчиците и администраторите на база данни, използващи PostgreSQL. Но това не са всички възможни техники. Ако сте попаднали на интересни проблеми, пишете за тях в коментарите. Нека споделим полезен опит!