Настройка и начало работы с postgresql

Таблицы списков

Чтобы получить список всех таблиц конкретной базы данных сначала необходимо подключиться к нему с помощью мета-команды \c или \connect. Пользователь, вошедший в систему как терминал psql, должен иметь возможность подключаться к базе данных.

Например, чтобы подключиться к базе данных с именем «odoo», вы должны набрать:

\c odoo

 
Как только база данных переключена, используйте мета-команду \dt для вывода списка всех таблиц базы данных:

Вывод будет включать количество таблиц, имя каждой таблицы и ее схему, тип и владельца:

                              List of relations
 Schema |                        Name                         | Type  | Owner 
--------+-----------------------------------------------------+-------+-------
 public | base_import_import                                  | table | odoo
 public | base_import_mapping                                 | table | odoo
 public | base_import_tests_models_char                       | table | odoo
 ...
 public | web_editor_converter_test_sub                       | table | odoo
 public | web_tour_tour                                       | table | odoo
 public | wizard_ir_model_menu_create                         | table | odoo
(107 rows)

 
Если база данных пуста, вывод будет выглядеть так:

No relations found.

 
Для получения информации о размерах таблиц и описаний используйте \dt+.
 

Views сборщик статистики

Представления (Views) сборщика статистики.

Если в PostgreSql postgresql.conf разрешён сбор статистики (logging_collector = on), то информация об активности базы данных собирается в специальных системных таблицах.

Информация собранная «статистическим сборником» может оказаться полезной для оценки эффективности базы данных и запросов. Из этих представлений можно узнать, например

Стандартные Statistics Views. Вывести все представления каталога

Вывести соотношение hit/read. При выполнении запроса PostgreSQL сначала смотрит, есть ли нужные в запросе данные в разделяемой памяти (shared buffers). Если они найдены, засчитывается hit, если нет — делается сравнительно медленный системный вызов fread для поднятия данных с диска или из дискового кеша операционной системы и засчитывается read. В среднем, верно правило: чем больше отношение hit/read, тем лучше настроен PostgreSQL, так как он очень мало читает с диска, в основном извлекая данные из разделяемой памяти. Для большинства не очень больших баз это отношение должно лежать в пределах 5000-10000. Не стремитесь, однако, искусственно завысить настройку shared_buffers, которая прямо определяет hit/read: слишком большие размеры разделяемой памяти ведут к потере производительности в базах с интенсивной записью. Также стоит помнить, что fread может быть довольно быстрым, если данные находятся в дисковом кеше ОС.

Количество модификаций, произошедших в таблице. Список по таблицам: какое количество записей в них было добавлено, изменено и удалено с момента последнего сброса статистики. Администратор БД должен представлять, какие таблицы являются самыми нагруженными в текущей базе данных, а также каково соотношение между различными типами модифицирующих запросов к ним.

Статистика по индексам. Список по индексам: сколько записей из индекса были использованы в запросах по этому индексу; сколько рядов при этом получилось достать из родительской таблицы; разность этих двух чисел. Суть данной статистики проста: если у вас большая разница read-ов и fetch-ей, значит индекс устарел и ссылается на уже несуществующие данные, т.е. не всякий просмотр индекса и чтение из него соответствующего указателя на данные из таблицы (read) вызывает чтение самих данных из таблицы (fetch). В этом случае необходимо перестроить данный индекс, чтобы он соответствовал реальным данным в таблице.

5.7.3. Путь поиска схемы

Полные имена довольно утомительно писать и часто в приложении лучше
бывает нигде не привязываться к отдельному имени схемы.
Таким образом на таблицы ссылаются через неполные имена,
которые содержат только имя таблицы. СУБД определяет какая таблица
имеется в виду с помощью пути поиска, который является
списком схем, в которых осуществляется поиск. Первая совпавшая
таблица в пути поиска считается той, которая запрашивалась.
Если совпадающих имён в пути поиска не найдено, будет выдана ошибка,
даже если совпадающее имя таблицы существует в других схемах текущей
базы данных.

Первая схема в пути поиска называется текущей схемой. Кроме того,
что текущая схема будет находится первой, она также является схемой,
где будут создаваться новые таблицы командой CREATE TABLE,
в которой не указано имя схемы.

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

SHOW search_path;

В случае настроек по умолчанию, она возвращает:

 search_path
--------------
 "$user",public

Первый элемент говорит, что поиск должен производится в схеме
с таким же именем как и у текущего пользователя. Если такой
схемы не существует, данный элемент игнорируется.
Второй элемент указывает на схему public, которую мы уже
рассматривали.

Первая существующая схема в пути поиска является местоположением
по умолчанию, для создания новых объектов. Именно по этой
причине, по умолчанию объекты создаются в схеме public.
Когда на объекты ссылаются из любых других контекстов без указания
схемы (модификация таблиц, данных или команды запросов) происходит
перебор схем в пути поиска пока не будет найден совпавший
объект. Следовательно, в конфигурации по умолчанию, все
операции без указания имени схемы снова указывают только на
схему public.

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

SET search_path TO myschema,public;

(Здесь мы опустили $user, потому что эта
схема в данный момент нам не нужна.) И после этого, мы получаем
доступ к таблице без указания схемы:

DROP TABLE mytable;

Также, поскольку myschema является первым
элементом в пути поиска, именно в ней по умолчанию будут
создаваться новые объекты.

Мы также можем написать:

SET search_path TO myschema;

Тогда мы не сможем больше обращаться к схеме public без явного
указания имени. В схеме public не существует ничего особенного,
кроме того, что она существует по умолчанию. Её можно даже
удалить.

Другие способы манипулирования путём поиска схем см. в
Section 9.23.

Путь поиска, точно также как и для таблиц, работает и для имён
типов данных, имён функций и операторов. Имена типов данных и
функций могут быть указаны и в полном виде, точно также как и
имена таблиц. Если вам необходимо написать полное имя оператора в
выражении, существует специальный синтаксис: вы должны написать

OPERATOR(схема.оператор)

Это необходимо для избежания неоднозначностей в синтаксе. Вот пример:

SELECT 3 OPERATOR(pg_catalog.+) 4;

Управление представлениями

Создаем представление:

CREATE OR REPLACE view_name AS
query;

Создаем рекурсивное представление:

CREATE RECURSIVE VIEW view_name(column_list) AS
SELECT column_list;

Создайте детализированное представление:

CREATE MATERIALIZED VIEW view_name
AS
query
WITH  DATA;

Обновление детализированного представления:

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

Удаление существующего представления.

DROP VIEW  view_name;

Удаление детализированного представления:

DROP MATERIALIZED VIEW view_name;

Переименование представления:

ALTER VIEW view_name RENAME TO new_name;

Получение данных. Команда Select

Последнее обновление: 20.03.2018

Для извлечения данных из БД применяется команда SELECT. В упрощенном виде она имеет следующий синтаксис:

SELECT список_столбцов FROM имя_таблицы

Например, пусть ранее была создана таблица Products, и в нее добавлены некоторые начальные данные:

CREATE TABLE Products
(
	Id SERIAL PRIMARY KEY,
	ProductName VARCHAR(30) NOT NULL,
	Manufacturer VARCHAR(20) NOT NULL,
	ProductCount INTEGER DEFAULT 0,
	Price NUMERIC
);

INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)
VALUES
('iPhone X', 'Apple', 3, 36000),
('iPhone 8', 'Apple', 2, 41000),
('Galaxy S9', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000),
('Desire 12', 'HTC', 5, 28000);

Получим все объекты из этой таблицы:

SELECT * FROM Products;

Символ звездочка * указывает, что нам надо получить все столбцы.

Однако использование символа звездочки * считается не очень хорошей практикой, так как, как правило, не все столбцы бывают нужны. И более
оптимальный подход заключается в указании всех необходимых столбцов после слова SELECT. Исключение составляет тот случай, когда надо получить данные по абсолютно всем столбцам таблицы.
Также использование символа * может быть предпочтительно в таких ситуациях, когда в точности не известны названия столбцов.

Если нам надо получить данные не по всем, а по каким-то конкретным столбцам, то тогда все эти спецификации столбцов перечисляются через запятую после SELECT:

SELECT ProductName, Price FROM Products;

Спецификация столбца необязательно должна представлять его название. Это может быть любое выражение, например, результат арифметической операции.
Так, выполним следующий запрос:

SELECT ProductCount, Manufacturer, Price * ProductCount
FROM Products;

Здесь при выборке будут создаваться три столбца. Причем третий столбец представляет значение столбца Price, умноженное на значение столбца
ProductCount, то есть совокупную стоимость товара.

С помощью оператора AS можно изменить название выходного столбца или определить его псевдоним:

SELECT ProductCount AS Title, 
Manufacturer, 
Price * ProductCount  AS TotalSum
FROM Products;

В данном случае результатом выборки являются данные по 3-м столбцам. Для первого столбца определяется псевдоним Title, хотя в реальности
он будет представлять столбец ProductName. Второй столбец сохраняет свое название — Manufacturer. Третий столбец TotalSum хранит произведение столбцов
ProductCount и Price.

НазадВперед

ЗАПРОСЫ

Удаление данных от удаленных плагинов и данные постов

После удаления ненужных плагинов в таблице  могут остаться записи от них. В этой же таблице находятся мета данные постов.

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

DELETE FROM wp_postmeta WHERE meta_key = ‘ваш-мета-ключ‘;

Замените ваш-мета-ключ на нужное значение.

Для мультисайта:

DELETE FROM wp_#_postmeta WHERE meta_key = ‘ваш-мета-ключ‘;

Измените # на ID сайта и ваш-мета-ключ на нужное значение.

Удаление спам комментариев

Удалить весь спам из бд можно этим запросом:

DELETE FROM wp_comments WHERE comment_approved = ‘spam‘;

Для мультисайта:

DELETE FROM wp_#_comments WHERE comment_approved = ‘spam‘;

Измените # на ID сайта.

Удаление комментариев, ожидающих проверки

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

DELETE FROM wp_comments WHERE comment_approved = ‘‘;

Для мультисайта:

DELETE FROM wp_#_comments WHERE comment_approved = ‘‘;

Измените # на ID сайта.

Удаление неиспользуемых тегов

Если у вас есть тег, который не связаны ни с одной статьей, удалите его этим запросом:

DELETE FROM wp_terms wtINNER JOIN wp_term_taxonomy wtt ON wt.term_id = wtt.term_id WHERE wtt.taxonomy = ‘post_tag’ AND wtt.count = 0;

Для мультисайта:

DELETE FROM wp_#_terms wtINNER JOIN wp_term_taxonomy wtt ON wt.term_id = wtt.term_id WHERE wtt.taxonomy = ‘post_tag’ AND wtt.count = 0;

Измените # на ID сайта.

Удаление Trackback и Pingback

Если вы выключили эти функции и хотите удалить их записи из базы данных, используйте эти запросы:

Trackback

DELETE FROM wp_comments WHERE comment_type = ‘trackback‘;

Для мультисайта:

DELETE FROM wp_#_comments WHERE comment_type = ‘trackback‘;

Измените # на ID сайта.

Pingback

DELETE FROM wp_comments WHERE comment_type = ‘pingback‘;

Для мультисайта:

DELETE FROM wp_#_comments WHERE comment_type = ‘pingback‘;

Измените # на ID сайта.

Выключить эти функции в WordPress можно в Настройках — Обсуждения.

Удаление ревизий постов

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

DELETE a,b,c FROM wp_posts aLEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id)LEFT JOIN wp_postmeta с ON ( a.ID = c.post_id)LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)WHERE a.post_type = ‘revision’AND d.taxonomy != ‘link_category’

Для мультисайта:

DELETE a,b,c FROM wp_#_posts aLEFT JOIN wp_#_term_relationships b ON ( a.ID = b.object_id)LEFT JOIN wp_#_postmeta с ON ( a.ID = c.post_id)LEFT JOIN wp_#_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)WHERE a.post_type = ‘revision’AND d.taxonomy != ‘link_category’

Замените # на ID сайта.

Удаление шорткодов плагинов и тем

Если вы пользовались каким-то плагином, который вставляет шорткоды, или вы сменили тему, в которой были встроены шорткоды, то теперь шорткоды появятся на сайте в виде текста. Чтобы удалить все записи о шорткодах из базы данных, сделайте этот запрос:

UPDATE wp_post SET post_content = replace(post_content, ‘‘, »);

Для мультисайта:

UPDATE wp_#_post SET post_content = replace(post_content, ‘‘, »);

Измените # на ID сайта.

Удаление постов старше Х дней

Если вы хотите удалить посты старше Х дней, используйте этот запрос:

DELETE FROM ‘wp_posts’WHERE ‘post_type’ = ‘post’AND DATEDIFF(NOW(),’post_date’) > X-дней

Замените X-дней на нужное число дней.

Для мультисайта:

DELETE FROM ‘wp_#_posts’WHERE ‘post_type’ = ‘post’AND DATEDIFF(NOW(),’post_date’) > X-дней

Измените # и X-дней.

Удаление других комментариев

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

SELECT FROM wp_commentsmeta WHERE comment_idNOT IN (SELECT comment_idFROM wp_comments);

Если вы хотите очистить таблицу на другом сайте в сети, используйте этот запрос:

SELECT FROM wp_#_commentsmeta WHERE comment_idNOT IN (SELECT comment_idFROM wp_#_comments);

Замените # на ID сайта.

Кодировка БД PostgreSQL и locale

PostgreSQL поддерживает только общую для всех баз кластера кодировку, которая должна совпадать с локальной кодировкой (Настройка переменных локализации в Linux), иначе не будут работать строковые функции сортировки, upper/lower и т.п. Локаль общая для всех процессов сервера — соответственно он не может создать две базы в разных кодировках — кодировка всегда одна для всего сервера и всех его БД.

Посмотреть кодировку сервера (show server_encoding) и клиента(show client_encoding):

Т.е. создать базу в другой кодировке можно, но тогда в ней будут неправильно работать функции обработки строк и сортировка строк.

Указывать список кодировок нужно не для createdb (create database), а для подключения клиента к серверу (client_encoding), если кодировка символов которую ожидает программа-клиент не совпадает с её (программы-клиента) текущей системной локалью, с которой она была запущена.

Взаимодействие psql с операционной системой

Терминал psql умеет выполнять команды операционной системы. Для этого нужно использовать команду “\!“. Например так:

postgres=# \! hostname
s-pg13

Можно установить переменную окружения в систему с помощью команды \setenv:

postgres=# \setenv TEST Hello
postgres=# \! echo $TEST
Hello

А для того чтобы перевести вывод команд в файл нужно использовать ‘\o имя_файла’. И чтобы вернуть всё обратно используем “\o” без имени файла. Например:

postgres=# \o dba.log

postgres=# SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 5;

postgres=# \! cat dba.log
----------------------
schemaname | pg_catalog
tablename  | pg_statistic
tableowner | postgres
----------------------
schemaname | pg_catalog
tablename  | pg_type
tableowner | postgres
----------------------
schemaname | pg_catalog
tablename  | pg_foreign_table
tableowner | postgres
----------------------
schemaname | pg_catalog
tablename  | pg_authid
tableowner | postgres
----------------------
schemaname | pg_catalog
tablename  | pg_statistic_ext_data
tableowner | postgres

postgres=# \o

postgres=# \x
Expanded display is off.

В предыдущем листинге с помощью последней команды мы выключили расширенный режим.

Помимо вывода в файл psql умеет выполнять команды из файла. Это делается с помощью команды “\i имя файла”. Вот пример:

postgres=# \q

postgres@s-pg13:~$ cat <<EOT >> dba1.log
> SELECT 'pg_statistic: '|| count(*) FROM pg_statistic;
> SELECT 'pg_type: '|| count(*) FROM pg_type;
> SELECT 'pg_foreign_table: '|| count(*) FROM pg_foreign_table;
> EOT

postgres@s-pg13:~$ psql
psql (13.3)
Type "help" for help.

postgres=# \! cat dba1.log
SELECT 'pg_statistic: '|| count(*) FROM pg_statistic;
SELECT 'pg_type: '|| count(*) FROM pg_type;
SELECT 'pg_foreign_table: '|| count(*) FROM pg_foreign_table;

postgres=# \a \t \pset fieldsep ' '
Output format is unaligned.
Tuples only is on.
Field separator is " ".

postgres=# \i dba1.log
pg_statistic: 402
pg_type: 411
pg_foreign_table: 0

postgres=# \a \t \pset fieldsep '|'
Output format is aligned.
Tuples only is off.
Field separator is "|".

В примере выше мы проделали следующее:

• вышли из psql;
• создали скрипт dba1.log, который подсчитывает количество строк из:
  • pg_statistic – статистическая информация о содержимом базы данных;
  • pg_type – информация о типах данных;
  • pg_foreign_table – дополнительная информация о сторонних таблицах.
• обратно вернулись в psql;
• прочитали файл dba1.log;
• изменили формат вывода;
• выполнили скрипт sql команд;
• вернули формат вывода в прежнее состояние.

Слои

Каждый файл занимает не больше 1 GB и кратен 8 KB. Поэтому если таблица больше 1 GB, то она хранится в нескольких файлах. Файлы состоят из 8 KB страниц, которые в случае необходимости помещаются в буферный кэш.

Существуют следующие слои:

• Основной слой (main) – сами данные. Этот слой существует у всех объектов;
• Слой инициализации (init) – существует только для нежурналируемых таблиц. Содержит пустую копию таблицы. В случае сбоя PostgreSQL не пытается восстановить нежурналируемую таблицу, а перезаписывает её пустой таблицей из этого слоя. Поэтому после сбоя нежурналируемые таблицы окажутся пустыми.
• Карта свободного пространства (fsm) – хранит информацию о том, где внутри файлов есть свободное пространство.
• Карта видимости (vm) – отмечает страницы, в которых все версии строк видны. Другими словами VACUUM уже их почистил от неактуальных версий строк. Такой слой существует только для таблиц. Он нужен для оптимизации, чтобы VACUUM знал, какие страницы чистить уже не нужно.

Загрузка и установка PostgreSQL

PostgreSQL поддерживает все основные операционные системы. Процесс установки прост, поэтому я постараюсь рассказать
о нем как можно быстрее.

Для Windows и Mac ты можешь загрузить установщик
с
веб-сайта EDB

.

EDB больше не предоставляет пакеты для систем GNU/Linux. Вместо этого они рекомендуют вам использовать диспетчер
пакетов твоего дистрибутива.

Установщики включают в себя разные компоненты.

Вот самые важные из них:

• Сервер PostgreSQL (очевидно)
• pgAdmin, графический инструмент для управления базами данных
• Менеджер пакетов для загрузки и установки дополнительных инструментов и драйверов

Windows

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

Диалоговое окно «Выбрать компоненты» позволяет выборочно устанавливать компоненты.
Если у тебя нет веской причины что-то менять — оставляй все как есть.

По умолчанию PostgreSQL создает суперпользователя с именем (воспринимай его как учетную запись
администратора сервера базы данных).

Во время установки тебе нужно будет указать пароль для суперпользователя (root).

Позже ты сможешь создать других пользователей и назначать им отдельные доступы и роли.
Мы вернемся к этому позже, а сейчас тебе понадобится учетная запись суперпользователя, чтобы начать использовать СУБД.

Чтобы запустить сервер разработки на твоем компьютере или , необходимо
назначить ему порт.

Порт по умолчанию — 5432. Если ты устанавливаешь PostgreSQL впервые, то он скорее всего свободен.
Если окажется, что этот порт уже занят другим экземпляром PostgreSQL, ты можешь указать другое значение, например 5433.

После завершения установки ты сможешь запустить SQL Shell, поставляемый с Postgres.

Шаг за шагом ты выберешь сервер, какую базу данных использовать, порт, имя пользователя и пароль.

Используй данные, которые ты вводил на предыдущих шагах.

Поздравляю! Настройка для Windows завершена, и скоро мы начнем писать первые SQL запросы.

Ниже список вариантов установки для других операционных систем.

macOS

Для macOS у тебя есть разные варианты. Можно скачать установщик с сайта EDB и запустить его.

Кроме того, можно использовать , простое приложение для macOS.

После запуска у тебя появится сервер PostgreSQL, готовый к использованию.
Завершить работу сервера можно просто закрыв приложение.

Кроме того, ты также можете использовать , менеджер пакетов для macOS.

GNU/Linux

Ты можешь найти PostgreSQL в репозиториях большинства дистрибутивов Linux. Установить его можно одним щелчком мыши
из выбранного графического диспетчера пакетов.

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

Arch

Запуск оболочки PostgreSQL

После установки PostgreSQL, нужно запустить оболочку(shell), с помощью которой ты получишь возможность управлять базой данных.

Открой терминал и введи:

— это оболочка Postgres, аргумент используется для указания пользователя.

Поскольку ты еще не создавал других
пользователей, ты войдешь в систему как суперпользователь .

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

Как только пароль установлен, база данных PostgreSQL готова к работе!

Если сервер PostgreSQL по какой-то причине не запускается, можешь попробовать запустить его вручную.

Показать структуру таблицы и список таблиц в PostgreSQL [дубликат]

этот вопрос уже есть ответ здесь:

Я использовал MySQL для нескольких предыдущих проектов. Но теперь есть решил переключиться на PostgreSQL. Не то, что версия 8 также работает на этом, кхм другая ОС, с которой я застрял на работе.

но увы, два большинство полезных команд отсутствуют:

поскольку мой прототип БД находится на моем сервере NetBSD дома, а мой данные, ожидающие «на основе», работают, так что мне нужно подключиться через Perl / DBI и XML-RPC (не psql, увы). ИТ-отдел здесь просто говорит: «используйте MS-Access», поэтому никакой помощи нет.

пока я на начальном этапе, мне нужен информативный способ ошибиться вокруг и посмотреть, что к чему, пока я пытаюсь. разные способы построить эту штуку. Для этого я всегда полагался на два выше из MySQL.

Я не могу поверить, что PostgreSQL не может сказать мне, что структура таблицы текущей БД осуществляется с помощью простых SQL-запросов, выполняемых удаленно.

наверняка есть. Но я, кажется, не могу узнать от пары книги у меня есть. Все, что я откопал, это какой-то ультра-хромой хак, чтобы получить имена столбцов уже известно имя таблицы на «где 1 != 1» или что-то подобное так что нет фактические строки могут быть возвращены. Не очень информативно. Конечно, я где-то упустил суть.

Так просветите меня, пожалуйста. Что, скажите на милость, такое PostgreSQL-ish SQL запросы, которые используются для изучения структуры таблицы данной БД? Что перевод PostgreSQL для «показать таблицы»и» описать таблицу»?

Источник

Листинговые таблицы

Чтобы сначала вывести список всех таблиц конкретной базы данных, вам необходимо подключиться к ней с помощью c или connect . Пользователь, в который вы вошли в терминал psql, должен иметь возможность подключаться к базе данных.

Например, чтобы подключиться к базе данных с именем «odoo», введите:

После переключения базы данных используйте мета-команду dt вывести список всех таблиц базы данных:

Вывод будет включать количество таблиц, имя каждой таблицы, ее схему, тип и владельца:

Если база данных пуста, вывод будет выглядеть так:

Чтобы получить информацию о размерах таблиц и описаниях, используйте dt+ .