За да излезете от обвивката, въведете командата \q.
Сега нека се опитаме да работим със създадената база данни от името на test_user:
psql -h localhost test_database test_user
Нека създадем нова таблица:
Test_database=> CREATE SEQUENCE user_ids; CREATE SEQUENCE test_database=> СЪЗДАВАНЕ НА ТАБЛИЦА потребители (id INTEGER PRIMARY KEY DEFAULT NEXTVAL("user_ids"), вход CHAR(64), парола CHAR(64)); ЗАБЕЛЕЖКА: СЪЗДАВАНЕ НА ТАБЛИЦА / ПЪРВИЧЕН КЛЮЧ ще СЪЗДАВА неявен ИНДЕКС "users_pkey" ЗА ТАБЛИЦА "users" СЪЗДАВАНЕ НА ТАБЛИЦА
Изтриване на потребител
За да изтриете потребител, трябва да прехвърлите правата му на друг и след това само да изтриете
ПРЕПРЕДНАЗВАНЕ НА СОБСТВЕНОСТ ОТ doomed_role НА наследник_роля; ОТПУСКАНЕ НА doomed_role;- повторете предишните команди за всяка база в клъстера
ИЗПУСКАНЕ РОЛЯ doomed_role;
Скрипт за архивиране на таблица Postgres.
#!/bin/bash DBNAMES="web"; USER="postgres"; DB_NAME="web"; NEW_OWNER="user_remote"; DATE_Y=`/bin/date "+%y"` DATE_M=`/bin/date "+%m"` DATE_D=`/bin/date "+%d"` SERVICE="pgdump" BACKUP_DIR="/var/ backup_db/20$(DATE_Y)/$(DATE_M)/$(DATE_D)" mkdir -p $BACKUP_DIR; за tbl в `psql -qAt -c "изберете име на таблица от pg_tables, където schemaname = "public";" $(DB_NAME)` \ `psql -qAt -c "изберете име_на последователност от information_schema.sequences, където sequence_schema = "public";" $(DB_NAME)` \ `psql -qAt -c "изберете table_name от information_schema.views, където table_schema = "public";" $(DB_NAME)`; do echo "Експортиране на таблица $tbl от db $(DB_NAME) към file tables3/$tbl.backup" #pg_dump --format p --verbose --table public.$tbl $(DB_NAME) > $BACKUP_DIR/$tbl pg_dump - -format p --verbose --table public.$tbl $(DB_NAME) | gzip > $BACKUP_DIR/$tbl #pg_dump -a -d -t public.$tbl $(DB_NAME) > tables3/$tbl.sql готово ##################BACKUP POSTGRES ФУНКЦИИ # Изхвърляйте базата данни без дата, за да ги извлечете от нея по-нататъшни функции pg_dump -Fc -s -f $BACKUP_DIR/db_dump $(DB_NAME) /bin/sleep 4; # Създаване на функция за списък pg_restore -l $BACKUP_DIR/db_dump | grep FUNCTION > $BACKUP_DIR/function_list ##Как да възстановим функции ######################### #pg_restore -h localhost -U потребителско име -d базово име - L function_list db_dump ##########################Скрипт за архивиране на таблица Postgres. написан на perl
Изпълнява се от user'a - postgres. Ако в crons, тогава и от потребителя на postgresql.
#!/usr/bin/env perl use strict; използвайте предупреждения; my $database_name = "book_library"; моята $заявка =<<"EOT"; SELECT n.nspname as table_schema, c.relname as table_name FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ("r","") AND n.nspname NOT IN ("pg_catalog", "information_schema") AND n.nspname NOT LIKE "^pg_%" ; EOT $query =~ s/\n\s*/ /g; my @results = `echo "$query" | psql -At $database_name`; foreach (@results) { chomp; my ($schema, $table) = split /\|/, $_; next unless ($schema && $table); my $cmd = "pg_dump -U postgres -Fp -t $schema.$table -f $schema.$table.dump $database_name"; system($cmd); } #If you wanted to restore only a single function: ##pg_restore -U $username --dbname=$dbname --function=$functionname(args) #If you wanted to restore only a single table: ##pg_restore -U $username --dbname=$dbname --table=$tablenameПълнотекстово търсене в речници в postgresql
Архивиране на база данни от отдалечена машина
PGPASSWORD="PASSWORD" pg_dump -h $HOSTNAME -U потребител на базата данни -Fc --verbose "database.itc-life.ru" | gzip > име на база данни.gzАрхивиране и възстановяване на таблици
AT PostgreSQLИма две помощни програми за архивиране pg_dump и pg_dumpall. pg_dump се използва за архивиране на една база данни, pg_dumpall се използва за архивиране на всички бази данни и сървъра като цяло (трябва да се изпълнява като postgresql суперпотребител).
Създаване на резервно копие на база данни mydb, компресиран
Pg_dump -h localhost -p 5432 -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
Изхвърляне на данни само на една конкретна таблица. Ако трябва да архивирате няколко таблици, тогава имената на тези таблици се изброяват с помощта на ключа -тза всяка маса.
pg_dump -a -t име на таблица -f име на файл име на база данни
Създайте компресиран архив gz
pg_dump -h локален хост -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) — изходен формат на дъмп, обичай, катран,или обикновен текст.
T, --table=TABLE - посочете конкретна таблица за дъмпа.
V, --verbose - извежда подробна информация.
D, --attribute-inserts - изхвърляне с помощта на командата INSERTсъс списък с имена на свойства.
Архивирайте всички бази данни с помощта на командата pg_dumpall.
pg_dumpall > all.sql # проверка на архивиране grep "^[\]connect" all.sql \connect db1 \connect db2
PostgreSQL има две помощни програми за възстановяванерезервни бази.
- psql- възстановяване на резервни копия, които се съхраняват в обикновен текстов файл (обикновен текст);
- pg_restore— възстановяване на компресирани архиви (tar);
Възстановяване на база данни и
#pg_restore -v -e -d dbname dbname.dump
Възстановяване на целия архив с игнориране на грешки
psql -h локален хост -U някой потребител -d dbname -f mydb.sql
Възстановяване на целия архив, спиране при първата грешка
psql -h localhost -U someuser --set ON_ERROR_STOP=on -f mydb.sqlЗа възстановяване от катран-archive трябва първо да създадем база данни, използвайки CREATE DATABASE mydb; (ако опцията -C не е посочена при създаването на архива) и възстановяване
pg_restore --dbname=mydb --jobs=4 --подробно mydb.backup
Възстановяване на компресиран архив на база данни gz
Gunzip mydb.gz psql -U postgres -d mydb -f mydb
Започвайки от версия 9.2, само структурата на таблиците може да бъде възстановена чрез опцията --section
# създаване на база данни CREATE DATABASE mydb2; # Възстанови pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backupПоддръжка на масата
маса за ВАКУУМЕН АНАЛИЗ; REINDEX DATABASE dbName; REINDEX TABLE tabName;Прехвърляне на директорията с данни
Разберете текущия път
# начин 1 $ su - postgres $ psql psql > SHOW data_directory; # начин 2 $ ps брадва | grep "postgres -D"Създайте нова директория, задайте потребител и инициализирайте
mkdir -p /pathto/postgresql/data chown -R postgres:postgres /pathto/postgresql su - postgres initdb -D /pathto/postgresql/dataСега трябва да коригираме файла със стартиращата услуга postgresql
# под arch linux sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service Environment =PGROOT=/pathto/postgresql/ PIDFile =/pathto/postgresql/data/postmaster.pidПочистване на масата
Почистване на масата име на таблицаи нулиране на брояча с ID.
TRUNCATE TABLE име на таблица RESTART IDENTITY CASCADE ;КАСКАДАнеобходими в случай име на таблицасвързан с друга таблица.
Премахване на NULL от поле
ALTER TABLE филми ALTER COLUMN year DROP NOT NULL;Пускане на pgbouncer
su -s /bin/sh - postgres -c "/usr/sbin/pgbouncer -d --verbose /etc/pgbouncer/pgbouncer.ini"Отделете потребителите от базата данни
ИЗБЕРЕТЕ pg_terminate_backend(pid) ОТ pg_stat_activity WHERE datname = "mydb";`Здравейте на всички, днес искам да направя малко напомняне за основните команди на PostgreSQL. Можете да работите с PostgreSQL както интерактивно, така и от командния ред. Програмата е psql. Сигурен съм, че този списък ще ви бъде много полезен и ще ви спести време за търсене в различни ресурси. Нека ви напомня, че това е проект с отворен код, базиран на СУБД Postgres, издаден през 1986 г., разработва се от глобалната група за разработка на PGDG, по същество е 5-8 души, но въпреки това се развива много интензивно, въвеждайки всички нови функции и коригирайки стари бъгове и грешки.
Основни команди на PostgreSQL в интерактивен режим:
- \connect db_name - свързване към база данни с име db_name
- \du - списък с потребители
- \dp (или \z) - списък с таблици, изгледи, последователности, права за достъп до тях
- \di - индекси
- \ds - последователности
- \dt - списък с таблици
- \dt+ - списък на всички таблици с описание
- \dt *s* - списък на всички таблици, съдържащи s в името
- \dv - изгледи
- \dS - системни таблици
- \d+ - описание на таблицата
- \o - изпращане на резултатите от заявката във файл
- \l - списък с бази данни
- \i - четене на входящи данни от файл
- \e - отваря текущото съдържание на буфера за заявки в редактора (освен ако не е посочено друго в средата на променливата EDITOR, то ще се използва от vi по подразбиране)
- \d “table_name” – описание на таблицата
- \i стартирам команда от външен файл, напр. \i /my/directory/my.sql
- \pset - команда за настройка на опции за форматиране
- \echo - показва съобщение
- \set - Задава стойността на променлива на средата. Без параметри, показва списък с текущи променливи (\unset - изтрива).
- \? - psql справка
- \help - 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 файл.
PostgreSQL помощни програми (програми):
- 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 локален хост -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 локален хост -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 Dump с помощта на командата 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
За да възстановим от tarball, първо трябва да създадем база данни с CREATE DATABASE mydb; (ако опцията -C не е посочена при създаването на архива) и възстановяване
pg_restore -dbname=mydb -jobs=4 -verbose mydb.backup
Възстановяване на архив на база данни, компресиран с gz
psql -U postgres -d mydb -f mydb
Мисля, че базата данни на postgresql вече ще бъде по-разбираема за вас. Надявам се този списък с команди на PostgreSQL да ви е бил полезен.
В тази статия ще покажа 15 най-полезни команди за управление postgreSQL.
1. Как да променя root паролата в PostgreSQL?
$ /usr/local/pgsql/bin/psql postgres postgresПарола: (стара парола) # ПРОМЕНИ ПОТРЕБИТЕЛския postgres С ПАРОЛА ‘tmppassword’; $ /usr/local/pgsql/bin/psql postgres postgresПарола: (tmppassword)Промяната на паролата за обикновен потребител работи по същия начин. Потребителят root може да промени паролата за всеки потребител.
# ПРОМЕНИ потребителско име на ПОТРЕБИТЕЛ С ПАРОЛА ‘tmppassword’;
2. Как да настроя PostgreSQL на автоматично стартиране?
$ su - root # tar xvfz postgresql-8.3.7.tar.gz # cd postgresql-8.3.7 # cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql # chmod a+x / etc/rc.d/init.d/postgresql3. Проверете състоянието на сървъра
$ /etc/init.d/postgresql състояниеПарола: pg_ctl: сървърът работи (PID: 6171) /usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data" [ Коментирайте: Това съобщение показва, че сървърът работи и работи правилно] $ /etc/init.d/postgresql състояниеПарола: pg_ctl: няма работещ сървър [ Коментирайте: Това съобщение показва, че сървърът не работи]4. Как да стартирате, спрете, рестартирате PostgreSQL?
# услуга postgresql стопСпиране на PostgreSQL: сървърът спря добре # стартиране на услугата postgresqlСтартиране на PostgreSQL: добре # рестартиране на услугата postgresqlРестартиране на PostgreSQL: сървърът спря добре5. Как да видите коя версия на PostgreSQL работи?
$ /usr/local/pgsql/bin/psql тестДобре дошли в psql 8.3.7, интерактивния терминал PostgreSQL. Въведете: \copyright за условия за разпространение \h за помощ при SQL команди \? за помощ с psql команди \g или прекратете с точка и запетая, за да изпълните заявка \q, за да излезете от test=# selectversion();версия ———————————————————————————————— PostgreSQL 8.3.7 на i686-pc-linux-gnu, компилиран от GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 ред) тест=#5. Как да създадете потребител в PostgreSQL?
Има два метода за това..
Метод 1:Създаваме потребител в PSQL обвивката с помощта на командата CREATE USER.
# СЪЗДАЙТЕ ПОТРЕБИТЕЛ ramesh С парола 'tmppassword';СЪЗДАВАЙТЕ РОЛЯ
Метод2: Създаваме потребител чрез командата createuser shell.
$ /usr/local/pgsql/bin/createuser sathiyaНовата роля ще бъде ли суперпотребител? (y/n) Ще бъде ли разрешено на новата роля да създава бази данни? (y/n) Ще бъде ли разрешено новата роля да създава още нови роли? (y/n) n СЪЗДАЙТЕ РОЛЯ
6. Как да създадете база данни в PostgreSQL?
Има 2 метода за това.
Метод1: Създайте база данни с помощта на обвивката PSQL, като използвате командата CREATE DATABASE.
# СЪЗДАВАНЕ НА БАЗА ДАННИ mydb СЪС СОБСТВЕНИКА ramesh;СЪЗДАЙТЕ БАЗА ДАННИ
Метод2: Използваме командата createdb.
$ /usr/local/pgsql/bin/createdb mydb -O rameshСЪЗДАЙТЕ БАЗА ДАННИ
7. Получаване на списък с всички бази данни в Postgresql?
# \лСписък на базите данни Име | Собственик | Кодиране ———-+————+———- архивиране | postgres | UTF8 mydb | рамеш | UTF8 postgres | postgres | UTF8 шаблон0 | postgres | UTF8 шаблон1 | postgres | UTF88. Как да изтрия база данни в PostgreSQL?
# \лСписък на базите данни Име | Собственик | Кодиране ———-+————+———- архивиране | postgres | UTF8 mydb | рамеш | UTF8 postgres | postgres | UTF8 шаблон0 | postgres | UTF8 шаблон1 | postgres | UTF8# ИЗПУСКАНЕ БАЗА ДАННИ mydb;ИЗПУСКАНЕ НА БАЗА ДАННИ9. Използване на вградената помощ за команди
Екип \? ще покаже помощен ред за командата psql. \h CREATE ще покаже помощ за всички команди, които започват с CREATE.
# \? # \h СЪЗДАВАЙТЕ # \h СЪЗДАВАНЕ НА ИНДЕКС
10. Как да получите списък с всички таблици в база данни в Postgresql?
# \дЗа празна база данни ще получите съобщението „Не са намерени отношения“.
11. Как мога да разбера времето за изпълнение на заявка?
# \timing - след изпълнение на тази команда, всяка следваща заявка ще показва времето за изпълнение.
# \времеВремето е включено. # SELECT * от pg_catalog.pg_attribute;Време: 9,583 мс
12. Как да архивирам и възстановявам бази данни и таблици в PostgreSQL?
Този въпрос е доста голям и ще го публикувам по-късно в отделна статия.
13. Как да изброя наличните функции в PostgreSQL?
За списък с налични функции кажете \df+
# \df # \df+
14. Как да редактирам PostgreSQL заявка в редактора?
# \д\e ще отвори редактор, където можете да редактирате заявката и да я запишете.
15. Къде мога да намеря файла с история на postgreSQL?
Подобно на файла ~/.bash_history, postgreSQL съхранява всички sql команди във файла ~/.psql_history.
$ котка ~/.psql_historyпромяна на потребителския postgres с парола 'tmppassword'; \h промяна на версията за избор на потребител (); създаване на потребител ramesh с парола 'tmppassword'; \избор на време * от pg_catalog.pg_attribute;
Последна актуализация: 17.03.2018 г
Таблиците се създават с помощта на командата CREATE TABLE, последвана от името на таблицата. Можете също да използвате редица оператори с тази команда, които дефинират колоните на таблицата и техните атрибути. Общият синтаксис за създаване на таблица е както следва:
CREATE TABLE име_на_таблица (име_на_колона1_тип_на_данни_атрибути_1,_на_на_на_2_тип_на_данни атрибути_колона2, ................................ .... .......... column_nameN data_type column_attributesN, table_attributes);
След името на таблицата спецификацията за всички колони е изброена в скоби. Освен това за всяка колона трябва да посочите името и типа данни, които ще представлява. Типът данни определя какви данни (числа, низове и т.н.) може да съдържа колоната.
Например, нека създадем таблица в базата данни с помощта на pgAdmin. За да направите това, първо изберете целевата база данни в pgAdmin, щракнете с десния бутон върху нея и изберете елемента Query Tool... в контекстното меню:
След това ще се отвори поле за въвеждане на кода в SQL. Освен това таблицата ще бъде създадена специално за базата данни, за която ще отворим това поле за въвеждане на SQL.
CREATE TABLE клиенти (ID SERIAL ПЪРВИЧЕН КЛЮЧ, Име на ХАРАКТЕР VARYING(30), Фамилия CHARACTER VARYING(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, ...];
Например изтриване на таблицата с клиенти.
15 полезни PostgreSQL команди
В мрежата има много ръководства за PostgreSQL, които описват основните команди. Но когато се гмуркате по-дълбоко в работата, има такива практически въпроси, които изискват разширени команди.
Такива команди или фрагменти рядко се документират. Нека разгледаме няколко примера, които са полезни както за разработчиците, така и за администраторите на бази данни.
Получаване на информация за базата данни
Размер на базата данни
За да получите физическия размер на файловете (хранилище) на базата данни, използвайте следната заявка:
ИЗБЕРЕТЕ pg_database_size(current_database());
Резултатът ще бъде представен като число като 41809016.
current_database() е функция, която връща името на текущата база данни. Вместо това можете да въведете име в текст:
SELECT pg_database_size("my_database");
За да получим информация в разбираема от човека форма, ние използваме функцията pg_size_pretty:
ИЗБЕРЕТЕ pg_size_pretty(pg_database_size(current_database()));
В резултат на това получаваме информация като 40 Mb.
Списък с таблици
Понякога трябва да получите списък с таблици на базата данни. За да направим това, използваме следната заявка:
ИЗБЕРЕТЕ име_на_таблица ОТ information_schema.tables КЪДЕТО table_schema НЕ В ("information_schema", "pg_catalog");
information_schema - стандартна схема на база данни, която съдържа колекции от изгледи (изгледи), като таблици, полета и т.н. Изгледите на таблица съдържат информация за всички таблици на базата данни.
Заявката, описана по-долу, ще избере всички таблици от посочената схема на текущата база данни:
ИЗБЕРЕТЕ име_на_таблица ОТ information_schema.tables КЪДЕТО table_schema НЕ В ("information_schema", "pg_catalog") И table_schema IN("public", "myschema");
В последната IN клауза можете да посочите името на конкретна схема.
Размер на масата
По аналогия с получаването на размера на база данни, размерът на данните от таблицата може да бъде изчислен с помощта на съответната функция:
SELECT pg_relation_size("сметки");
Функцията pg_relation_size връща размера, който посоченият слой от дадена таблица или индекс заема на диска.
Името на най-голямата маса
За да покажете списък с таблици в текущата база данни, сортирани по размер на таблицата, изпълнете следната заявка:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
За да покажем информация за най-голямата таблица, ние ограничаваме заявката с LIMIT:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;
relname е името на таблица, индекс, изглед и т.н.
relpages - размерът на представянето на тази таблица на диска в брой страници (по подразбиране една страница е 8 KB).
pg_class е системна таблица, която съдържа информация за връзките на таблицата на базата данни.
Списък на свързани потребители
За да разберете името, IP и използвания порт на свързаните потребители, изпълнете следната заявка:
SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;
Потребителска активност
За да разберете активността на връзката на конкретен потребител, използвайте следната заявка:
SELECT datname FROM pg_stat_activity WHERE usename = "devuser";
Работа с полета с данни и таблица
Премахване на дублиращи се редове
Ако се случи така, че таблицата няма първичен ключ (първичен ключ), тогава със сигурност ще има дубликати сред записите. Ако за такава таблица, особено голяма, е необходимо да зададете ограничения (ограничение), за да проверите целостта, след това премахнете следните елементи:
- дублирани линии
- ситуации, при които една или повече колони се дублират (ако се предполага, че тези колони се използват като първичен ключ).
Помислете за таблица с данни за клиентите, където целият ред е дублиран (вторият по ред).
Следната заявка ще ви помогне да премахнете всички дубликати:
ИЗТРИВАНЕ ОТ клиенти, КЪДЕТО ctid НЕ В (ИЗБЕРЕТЕ max(ctid) ОТ клиенти ГРУПА ПО клиенти.*);
Полето ctid, което е уникално за всеки запис, е скрито по подразбиране, но присъства във всяка таблица.
Последната заявка е ресурсоемка, така че бъдете внимателни, когато я изпълнявате върху работещ проект.
Сега разгледайте случая, когато стойностите на полето се повтарят.
Ако е приемливо да изтриете дубликати, без да запазвате всички данни, изпълнете следната заявка:
ИЗТРИВАНЕ ОТ клиенти, КЪДЕТО ctid НЕ В (ИЗБЕРЕТЕ max(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) ОТ 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 (ИЗБЕРЕТЕ макс.
В резултат на това виждаме следния резултат: 5 , 9 и 6 .
Втори начин
Вземете името на последователността, свързана с customer_id:
SELECT pg_get_serial_sequence("customers", "customer_id");
И намерете всички липсващи идентификатори:
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 clients;
Преброяване на броя на редовете в таблица
Броят на редовете се изчислява от стандартната функция за броене, но може да се използва с допълнителни условия.
Общ брой редове в таблицата:
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 query != "
За да спрете конкретна заявка, изпълнете следната команда, като посочите идентификатора на процеса (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. Но това не са всички възможни методи. Ако попаднете на интересни задачи, пишете за тях в коментарите. Нека споделим нашия полезен опит!