Postgres

Всем привет. Сегодня я бы хотел поделиться рецептом установки утилиты PGHero с подключением нескольких баз данных. PGHero это простенькая утилита, написанная на Ruby, с минималистичным дашбордом для мониторинга производительности БД PostgreSQL.

Что может показать нам PGHero:

• статистику по запросам: количество вызовов, среднее и суммарное время выполнения (с возможностью хранения истории);

• активные в данный момент запросы;

• информацию о таблицах: занимаемое на диске место, даты последних запусков VACUUM и ANALYSE;

• информацию об индексах: занимаемое на диске место, наличие дублируемых/неиспользуемых индексов. Также может порекомендовать добавить индекс при наличии сложных запросов с Seq Scan;

• статистику по открытым подключениям к БД;

• вывод основных настроек БД, влияющих на производительность (shared_buffers, work_mem, maintenance_work_mem и т.д.)

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

Выглядит это в интерфейсе PGHero вот так:

Настройка баз данных

Следующие шаги нужно проделать для каждой БД, которую мы собираемся подключать к PGHero.

Запросы нужно выполнять под суперпользователем.

1. Устанавливаем расширение pg_stat_statements (если еще не установлено):

Откройте файл postgresql.conf в текстовом редакторе и измените строку shared_preload_libraries:

shared_preload_libraries = 'pg_stat_statements'pg_stat_statements.track_utility = false

Перезапускаем сервер PostgreSQL:

sudo service postgresql restart

Создаем расширение и сбрасываем статистику:

create extension pg_stat_statements;select pg_stat_statements_reset();

2. Создаем в БД отдельного пользователя для PGHero (чтобы не давать утилите полные права над базой).

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

<pghero_password> пароль для пользователя pghero;

<db_name> имя вашей БД;

<migrations_user> имя основной роли с доступом к текущей БД.

CREATE SCHEMA pghero;-- view queriesCREATE OR REPLACE FUNCTION pghero.pg_stat_activity() RETURNS SETOF pg_stat_activity AS$$  SELECT * FROM pg_catalog.pg_stat_activity;$$ LANGUAGE sql VOLATILE SECURITY DEFINER;CREATE VIEW pghero.pg_stat_activity AS SELECT * FROM pghero.pg_stat_activity();-- kill queriesCREATE OR REPLACE FUNCTION pghero.pg_terminate_backend(pid int) RETURNS boolean AS$$  SELECT * FROM pg_catalog.pg_terminate_backend(pid);$$ LANGUAGE sql VOLATILE SECURITY DEFINER;-- query statsCREATE OR REPLACE FUNCTION pghero.pg_stat_statements() RETURNS SETOF pg_stat_statements AS$$  SELECT * FROM public.pg_stat_statements;$$ LANGUAGE sql VOLATILE SECURITY DEFINER;CREATE VIEW pghero.pg_stat_statements AS SELECT * FROM pghero.pg_stat_statements();-- query stats resetCREATE OR REPLACE FUNCTION pghero.pg_stat_statements_reset() RETURNS void AS$$  SELECT public.pg_stat_statements_reset();$$ LANGUAGE sql VOLATILE SECURITY DEFINER;-- improved query stats reset for Postgres 12+ - delete for earlier versionsCREATE OR REPLACE FUNCTION pghero.pg_stat_statements_reset(userid oid, dbid oid, queryid bigint) RETURNS void AS$$  SELECT public.pg_stat_statements_reset(userid, dbid, queryid);$$ LANGUAGE sql VOLATILE SECURITY DEFINER;-- suggested indexesCREATE OR REPLACE FUNCTION pghero.pg_stats() RETURNSTABLE(schemaname name, tablename name, attname name, null_frac real, avg_width integer, n_distinct real) AS$$  SELECT schemaname, tablename, attname, null_frac, avg_width, n_distinct FROM pg_catalog.pg_stats;$$ LANGUAGE sql VOLATILE SECURITY DEFINER;CREATE VIEW pghero.pg_stats AS SELECT * FROM pghero.pg_stats();-- create userCREATE ROLE pghero WITH LOGIN ENCRYPTED PASSWORD '<pghero_password>';GRANT CONNECT ON DATABASE <db_name> TO pghero;ALTER ROLE pghero SET search_path = pghero, pg_catalog, public;GRANT USAGE ON SCHEMA pghero TO pghero;GRANT SELECT ON ALL TABLES IN SCHEMA pghero TO pghero;-- grant permissions for current sequencesGRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO pghero;-- grant permissions for future sequencesALTER DEFAULT PRIVILEGES FOR ROLE <migrations_user> IN SCHEMA public GRANT SELECT ON SEQUENCES TO pghero;

Установка и запуск PGHero

Допустим, у нас есть три таблицы: db_one, db_two и db_three. Мы хотим по всем трем отображать статистику в PGHero (вместе с историей запросов и размеров таблиц). Важный момент: для хранения истории запросов и размеров таблиц нужно завести в одной из баз данных отдельные таблицы, где будет храниться эта статистика.

CREATE TABLE "pghero_query_stats" (  "id" bigserial primary key,  "database" text,  "user" text,  "query" text,  "query_hash" bigint,  "total_time" float,  "calls" bigint,  "captured_at" timestamp);CREATE INDEX ON "pghero_query_stats" ("database", "captured_at");CREATE TABLE "pghero_space_stats" (  "id" bigserial primary key,  "database" text,  "schema" text,  "relation" text,  "size" bigint,  "captured_at" timestamp);CREATE INDEX ON "pghero_space_stats" ("database", "captured_at");

Мы будем хранить эти таблицы в БД db_one (хотя можно завести отдельную базу для этой статистики). Далее создаем на сервере файл конфигурации pghero.yml со следующим содержимым (подставляем актуальные настройки):

# Конфигурационные урлы для наших БДdatabases:  db_one:    url: postgres://pghero:secret_pass@mydomain.ru:53001/db_one  db_two:    url: postgres://pghero:secret_pass@mydomain.ru:53001/db_two    capture_query_stats: db_one  db_three:    url: postgres://pghero:secret_pass@mydomain.ru:53001/db_three    capture_query_stats: db_one# Минимальная длительность запросов (в секундах), которые будут считаться долгимиlong_running_query_sec: 60# Минимальная длительность запросов (в миллисекундах), которые будут считаться медленнымиslow_query_ms: 250# Минимальное кол-во вызовов запросов, которые будут считаться медленнымиslow_query_calls: 100# Минимальное количество соединений для показа предупрежденияtotal_connections_threshold: 100# Таймаут для explain-запросовexplain_timeout_sec: 10# Нормализация запросов (замена значений запроса нумерованными параметрами)filter_data: true# Basic авторизацияusername: pgheropassword: secret_pass# Таймзонаtime_zone: "Europe/Moscow"

Переходим к установке. Документация предлагает нам несколько способов:

1. Docker-контейнер;

2. отдельная служба на Linux;

3. gem-пакет Ruby;

Мы будем использовать первый способ запуск в виде Docker-контейнера. Для этого в папке с файлом конфигурации pghero.yml нужно добавить Docker-файл с таким содержимым:

docker build -t mypghero .docker run -ti -p 12345:8080 mypghero

Теперь собираем образ на основе Docker-файла и запускаем контейнер на нужном порту:

docker build -t mypghero .docker run -ti -p 12345:8080 mypghero

Теперь дашборд должен быть доступен по адресу http://123.45.67.89/12345. Не забывайте про basic-авторизацию, логин и пароль мы указывали в pghero.yml.

Запуск cron-jobs для сохранения истории

Последний этап: нужно настроить автозапуск по крону скриптов для сохранения в БД истории по запросам (capture_query_stats) и размерам таблиц (capture_space_stats).

Документация рекомендует запускать capture_query_stats раз в 5 минут, а capture_space_stats раз в сутки (но тут нужно решать по ситуации). Запускаем в командной строке crontab -e и добавляем строки для запуска скриптов:

*/5 * * * *     /usr/bin/docker run --rm my-pghero bin/rake pghero:capture_query_stats15 2 * * *     /usr/bin/docker run --rm my-pghero bin/rake pghero:capture_space_stats

Вот и всё. Спасибо за внимание.

Демо-версию утилиты можно посмотреть здесь. Исходный код и документация.

Регистрируемся в Scrutinizer

Проще, наверное, войти через вашу учетную запись в GitHub, т.к. это избавит вас от необходимости линковать ваш GitHub профиль, а делать это все равно придется.

Для установки приложения Scrutinizer переходим по ссылке, этот шаг можно пропустить, но имейте ввиду, что в дальнейшем у вас не будут работать интерактивные плюшки и будет висеть нотификация: Scrutinizer GitHub App Is Not Installed...

Даем ему все скоупы, которые требует. Не бойтесь, Scrutinizer не удалит ваши репозитории и никак вам не навредит. Это нужно для того, чтобы приложение могло общаться с вашим GitHub по АПИ и в real-time управлять вашим Check Suite, давая ему обратную связь, и кодом в ваших PR для удобства ревью и тд.

Создаем глобальную конфигурацию

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

Чтобы создать глобальную конфигурацию перейдите по ссылке.

build:    environment:        php: 7.3.15build_failure_conditions:  - 'project.metric_change("scrutinizer.quality", < -0.10)'  - 'elements.rating(<= D).exists'                                # No classes/methods with a rating of D or worse  - 'elements.rating(<= D).new.exists'                            # No new classes/methods with a rating of D or worse allowed  - 'issues.label("coding-style").exists'                         # No coding style issues allowed  - 'issues.label("coding-style").new.exists'                     # No new coding style issues allowed  - 'issues.severity(>= MAJOR).new.exists'                        # New issues of major or higher severity                          - 'project.metric("scrutinizer.quality", < 9)'                  # Code Quality Rating drops below 9  - 'patches.label("Doc Comments").exists'                        # No doc comments patches allowed  - 'patches.label("Spacing").exists'                             # No spacing patches allowedchecks:    php:        verify_property_names: true        verify_argument_usable_as_reference: true        verify_access_scope_valid: true        variable_existence: true        useless_calls: true        use_statement_alias_conflict: true        unused_variables: true        unused_properties: true        unused_parameters: true        unused_methods: true        unreachable_code: true        too_many_arguments: true        symfony_request_injection: true        switch_fallthrough_commented: true        sql_injection_vulnerabilities: true        simplify_boolean_return: true        security_vulnerabilities: true        return_in_constructor: true        return_doc_comments: true        return_doc_comment_if_not_inferrable: true        require_scope_for_methods: true        require_php_tag_first: true        remove_extra_empty_lines: true        property_assignments: true        properties_in_camelcaps: true        precedence_mistakes: true        precedence_in_conditions: true        phpunit_assertions: true        parse_doc_comments: true        parameters_in_camelcaps: true        parameter_non_unique: true        parameter_doc_comments: true        param_doc_comment_if_not_inferrable: true        overriding_private_members: true        overriding_parameter: true        non_commented_empty_catch_block: true        no_trait_type_hints: true        no_trailing_whitespace: true        no_short_variable_names:            minimum: '3'        no_short_open_tag: true        no_short_method_names:            minimum: '3'        no_property_on_interface: true        no_non_implemented_abstract_methods: true        no_long_variable_names:            maximum: '20'        no_goto: true        no_exit: true        no_eval: true        no_error_suppression: true        no_debug_code: true        naming_conventions:            local_variable: '^[a-z][a-zA-Z0-9]*$'            abstract_class_name: ^Abstract|Factory$            utility_class_name: 'Utils?$'            constant_name: '^[A-Z][A-Z0-9]*(?:_[A-Z0-9]+)*$'            property_name: '^[a-z][a-zA-Z0-9]*$'            method_name: '^(?:[a-z]|__)[a-zA-Z0-9]*$'            parameter_name: '^[a-z][a-zA-Z0-9]*$'            interface_name: '^[A-Z][a-zA-Z0-9]*Interface$'            type_name: '^[A-Z][a-zA-Z0-9]*$'            exception_name: '^[A-Z][a-zA-Z0-9]*Exception$'            isser_method_name: '^(?:is|has|should|may|supports)'        more_specific_types_in_doc_comments: true        missing_arguments: true        method_calls_on_non_object: true        instanceof_class_exists: true        foreach_usable_as_reference: true        foreach_traversable: true        fix_use_statements:            remove_unused: true            preserve_multiple: false            preserve_blanklines: false            order_alphabetically: false        fix_line_ending: true        fix_doc_comments: true        encourage_shallow_comparison: true        duplication: true        deprecated_code_usage: true        deadlock_detection_in_loops: true        comparison_always_same_result: true        code_rating: true        closure_use_not_conflicting: true        closure_use_modifiable: true        check_method_contracts:            verify_interface_like_constraints: true            verify_documented_constraints: true            verify_parent_constraints: true        catch_class_exists: true        call_to_parent_method: true        avoid_superglobals: true        avoid_length_functions_in_loops: true        avoid_entity_manager_injection: true        avoid_duplicate_types: true        avoid_closing_tag: true        assignment_of_null_return: true        argument_type_checks: true

Обратите внимание, что проверки, описанные ниже, будут приводить к фейлу сборки, поэтому, если для вас некоторые из них не нужны, удалите или понизьте лимиты:

build_failure_conditions:  - 'project.metric_change("scrutinizer.quality", < -0.10)'  - 'elements.rating(<= D).exists'  - 'elements.rating(<= D).new.exists'   - 'issues.label("coding-style").exists'   - 'issues.label("coding-style").new.exists'                - 'issues.severity(>= MAJOR).new.exists'                                       - 'project.metric("scrutinizer.quality", < 9)'  - 'patches.label("Doc Comments").exists'  - 'patches.label("Spacing").exists' 

Хотел бы отметить, всегда указывайте ваше окружение (версию PHP, базы данных и тд), т.к. значения по умолчанию могут меняться и ваши сборки в один прекрасный день будут зафейлены. Что собственно и приключилось со мной буквально на днях, когда релизнулся PHP 8.0.1.

Настройка репозитория

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

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

Далее идем в настройки вашего репозитория, которые находятся вот тут:
https://scrutinizer-ci.com/g/ваш-логин/название-репозитория/settings

Настройка Check Suite

Тут немного остановимся и я расскажу почему.

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

За это время ваши обычные проверки в GitHub CI уже могут быть пройдены, линтеры показали ваши ошибки или вы вспомнили, что что-то забыли, и успеели толкнуть пару-тройку коммитов до завершения сборки Scrutinizer.

Tracking Settings

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

• Pull-Request Tracking - отвечает за запуск сборок только для Pull-реквестов в основную ветку.

• Pull-Request Notification - отвечает за то, как вы хотите узнавать о результатах сборки и анализа, например, через GitHub Check Suite.

• Tracked Branches - отвечает за запуск сборок при пушах. Рекомендую, выставить опцию "Track only branches listed below" и указать вашу основную ветку.

Auto-Cancel Non-Finished Inspections

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

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

Настройка конфигурации репозитория

Переходим по ссылке:
https://scrutinizer-ci.com/g/ваш-логин/название-репозитория/settings/build-config

Важно выбрать вашу глобальную конфигурацию.

build:    nodes:        coverage:            services:                postgres: 12            tests:                override:                    -                        command: |                            sed -e "s/\${USERNAME}/scrutinizer/" \                                -e "s/\${PASSWORD}/scrutinizer/" \                                -e "s/\${DATABASE}/scrutinizer/" \                                -e "s/\${HOST}/127.0.0.1/" \                                phpunit.xml.dist > phpunit.xml                            ./vendor/bin/phpunit \                                --verbose  \                                --stderr  \                                --coverage-clover build/logs/clover.xml \                                --coverage-text                        coverage:                            file: build/logs/clover.xml                            format: clover        analysis:            tests:                override:                    - php-scrutinizer-run                    -                        command: phpcs-run                        use_website_config: true    cache:        disabled: true        directories:            - vendor/filter:    excluded_paths:        - 'tests/*'

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

<?xml version="1.0"?><ruleset>    <file>./</file>    <exclude-pattern>./vendor/*</exclude-pattern>    <exclude-pattern>./tests/*</exclude-pattern>    <exclude-pattern>./.github/*</exclude-pattern>    <rule ref="PSR1" /></ruleset>

А в ваш composer.json в секцию require-dev добавьте:

"squizlabs/php_codesniffer": "^3.5"

Начало работы

Теперь, давайте толкнем коммит в основную ветку и посмотрим, как выглядит Check Suite со Scrutinizer.

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

Интерактив от Scrutinizer в ревью

После интеграции и успешно пройденной сборки в Scrutinizer появится три кнопки: Code Intelligence, Issues и Coverage.

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

При наведении на методы и переменные Code Intelligence подсвечивает типы возвращаемых значений, а зеленые маркеры справа от нумератора строк сообщают о покрытии тестами.

В целом, это удобно, хотя я к Code Intelligence еще не привык.

Итог

Я буду рад, если мой опыт был вам полезен и вот такие красивые отчеты вас будут мотивировать писать совершенный код.

Если у вас что-то не получилось с первого раза, не отчаивайтесь.

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

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

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

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

Сегодня, как вы уже заметили, мы будем говорить про Postgres, про миграции Laravel, как это все вместе подружить, в общем, обо всем том, чего нам не хватает в стандартных миграциях Лары.

Ну а для тех, кто не хочет погружаться в тонкости внутреннего устройства Laravel, может просто скачать пакет, расширяющий возможности миграций Laravel и Postgres по этой ссылке и использовать его в своих проектах.

Но я все же рекомендую не пролистывать, а прочитать все до конца.

Миграции

Вот как выглядит стандартная миграция в Laravel:

<?phpdeclare(strict_types=1);use Illuminate\Database\Migrations\Migration;use Illuminate\Database\Schema\Blueprint;use Illuminate\Support\Facades\Schema;class CreateDocuments extends Migration{    private const TABLE = 'documents';    public function up()    {        Schema::create(static::TABLE, function (Blueprint $table) {            $table->bigIncrements('id');            $table->timestamps();            $table->softDeletes();            $table->string('number');            $table->date('issued_date')->nullable();            $table->date('expiry_date')->nullable();            $table->string('file');            $table->bigInteger('author_id');            $table->bigInteger('type_id');            $table->foreign('author_id')->references('id')->on('users');            $table->foreign('type_id')->references('id')->on('document_types');        });    }    public function down()    {        Schema::dropIfExists(static::TABLE);    }}

Но, тут вы прочитали статью на хабре про новые типы в Postgres, например, tsrange и захотели добавить в миграцию что-то вроде этого...

$table->addColumn('tsrange', 'period')->nullable();

Казалось бы, все просто, но ваш перфекционизм наводит вас на мысль, а почему бы не залезть в сорцы и не пропатчить Laravel, ведь я буду часть использовать это, хочется чтобы можно было использовать это примерно так:

$table->tsRange('period')->nullable();

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

<?phpBlueprint::macro('tsRange', function (string $columnName) {  return $this->addColumn('tsrange', $columnName);});

<?phpPostgresGrammar::macro('typeTsrange', function () {  rerurn 'tsrange';});

<?phpuse Illuminate\Support\ServiceProvider;class DatabaseServiceProvider extends ServiceProvider{    public function register()    {        Blueprint::macro('tsRange', function (string $columnName) {            return $this->addColumn('tsrange', $columnName);        });        PostgresGrammar::macro('typeTsrange', function () {            return 'tsrange';        });    }}

И не важно, переопределили ли вы половину компонентов Laravel для работы с БД или воспользовались макросами и миксинами из MacroableTrait, круги ада еще не закончились.

Круги ада (часть 1)

И вот вы локально все это крутите, все работает как часы, написали +100500 строк кода, и решили выкатить готовую таску в гитлаб. Мы же идем в ногу со временем и там у нас Докер, CI, тесты и тд...

И вот мы замечаем, что наши миграции в "не локальном" окружении не работают из-за ошибки:

Doctrine\DBAL\Driver\PDOException: SQLSTATE[08006] [7]FATAL:  sorry, too many clients already

И вот ты сидишь и думаешь, что ты не так делаешь, начинаешь подпихивать в локальный .env окружения из CI вашего GitLab, переписывать код, вместо макросов переопределять разные классы, распихивать везде и всюду дебаги, все идеально, локально ошибки нет. А в пайплайнах CI не так-то просто дебажить, начинаешь пихать везде и всюду логирование, и это не помогает, ведь ошибка где-то внутри, в vendor. Но ты об этом еще пока не знаешь.

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

Начинаешь ощущать по-тихоньку себя идиотом (простите за мой французский), который не способен ни на что, в прямом смысле этого слова, словно ты школьник, который только-только выпустился из школы, день убит, проблема не решена.

Важен контекст

Ошибка sorry, too many clients already может быть совершенно по любой причине.

Ты возвращаешься к началу, гуглишь то с чего начинал, с внедрения макросами в PostgresGrammar нового типа и вот чудо, ты натыкаешься на похожую проблему, но с первого взгляда она немного другая.... где кто-то, как и ты добавлял новый тип и у него не заводится БД. Но у него ошибка другая:

Doctrine\DBAL\DBALException: Unknown database type tsrange requested,Doctrine\DBAL\Platforms\PostgreSQL100Platform may not support it.

Отчаявшись, первая мысль, а чем черт не шутит... ты пробуешь чужие решения, даже самые абсурдные и в один прекрасный день все начинает работать. Вы уже догадались в чем дело?

Барабанная дробь

Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType().

You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap().

If this error occurs during database introspection then you might have forgotten to register all database types for a Doctrine Type.

Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes().

If the type name is empty you might have a problem with the cache or forgot some mapping information.

Иными словами, нужно успеть зарегистрировать тип в Doctrine\Dbal прежде, чем до вашего Database Connection дойдет информация, что вы используете кастомные типы (под кастомными я подразумеваю те, которые есть в Postgres, но отсутствуют в заветном getTypesMap в недрах Doctrine.

Круги ада (часть 2)

Вы лезете в исходники, куда-то очень глубоко в vendor в недры doctrine\dbal...

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

О боже, часть из них приватные!

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

Руки опускаются окончательно..

Спасительный круг

Не буду ходить вокруг, да около.

Это, как вы уже поняли, был мой личный опыт, мои руки не опустились, все-таки я победил этот великий и ужасный Doctrine.

Подумаем о будущем

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

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

Представим себе такой DatabaseProvider, который мы внедряем в свой проект вместо стандартного от Laravel, опишем структуру будущих Extension-ов в виде маленьких библиотек с похожей структурой, чтобы они легко коннектились к нашему провайдеру, и забыть, как страшный сон исходники Doctrine.

Основные компоненты

Эти объекты нам надо модифицировать, но сделать это в стиле ООП, сбоку, по типу как трейты иньектятся в классы:

• Blueprint - объект, использующийся в миграциях, по сути билдер

• Builder - он же фасад Schema

• PostgresGrammar - объект для компиляции Blueprint-а в SQL-выражения

• Types - наши типы

Давайте, придумаем объект, который будет подмешивать объекты расширений во внутренние объекты Laravel таким образом, чтобы и овцы были целы и волки сыты, имею ввиду, чтобы IDE был счастлив, все работало, а наш код был понятным.

<?phpnamespace Umbrellio\Postgres\Extensions;use Illuminate\Support\Traits\Macroable;use Umbrellio\Postgres\Extensions\Exceptions\MacroableMissedException;use Umbrellio\Postgres\Extensions\Exceptions\MixinInvalidException;abstract class AbstractExtension extends AbstractComponent{    abstract public static function getMixins(): array;    abstract public static function getName(): string;    public static function getTypes(): array    {        return [];    }    final public static function register(): void    {        collect(static::getMixins())->each(static function ($extension, $mixin) {            if (!is_subclass_of($mixin, AbstractComponent::class)) {                throw new MixinInvalidException(sprintf(                    'Mixed class %s is not descendant of %s.',                    $mixin,                    AbstractComponent::class                ));            }            if (!method_exists($extension, 'mixin')) {                throw new MacroableMissedException(sprintf('Class %s doesnt use Macroable Trait.', $extension));            }            /** @var Macroable $extension */            $extension::mixin(new $mixin());        });    }}

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

<?phpnamespace Umbrellio\Postgres;use DateTimeInterface;use Doctrine\DBAL\Connection;use Doctrine\DBAL\Events;use Illuminate\Database\PostgresConnection as BasePostgresConnection;use Illuminate\Support\Traits\Macroable;use PDO;use Umbrellio\Postgres\Extensions\AbstractExtension;use Umbrellio\Postgres\Extensions\Exceptions\ExtensionInvalidException;use Umbrellio\Postgres\Schema\Builder;use Umbrellio\Postgres\Schema\Grammars\PostgresGrammar;use Umbrellio\Postgres\Schema\Subscribers\SchemaAlterTableChangeColumnSubscriber;class PostgresConnection extends BasePostgresConnection{    use Macroable;    private static $extensions = [];    final public static function registerExtension(string $extension): void    {        if (!is_subclass_of($extension, AbstractExtension::class)) {            throw new ExtensionInvalidException(sprintf(                'Class %s must be implemented from %s',                $extension,                AbstractExtension::class            ));        }        self::$extensions[$extension::getName()] = $extension;    }    public function getSchemaBuilder()    {        if ($this->schemaGrammar === null) {            $this->useDefaultSchemaGrammar();        }        return new Builder($this);    }    public function useDefaultPostProcessor(): void    {        parent::useDefaultPostProcessor();        $this->registerExtensions();    }    protected function getDefaultSchemaGrammar()    {        return $this->withTablePrefix(new PostgresGrammar());    }    private function registerExtensions(): void    {        collect(self::$extensions)->each(function ($extension) {            /** @var AbstractExtension $extension */            $extension::register();            foreach ($extension::getTypes() as $type => $typeClass) {                $this                    ->getSchemaBuilder()                    ->registerCustomDoctrineType($typeClass, $type, $type);            }        });    }}

А также необходимо переопределить провайдер и фабрику для работы с БД:

<?phpnamespace Umbrellio\Postgres;use Illuminate\Database\DatabaseManager;use Illuminate\Database\DatabaseServiceProvider;use Umbrellio\Postgres\Connectors\ConnectionFactory;class UmbrellioPostgresProvider extends DatabaseServiceProvider{    protected function registerConnectionServices(): void    {        $this->app->singleton('db.factory', function ($app) {            return new ConnectionFactory($app);        });        $this->app->singleton('db', function ($app) {            return new DatabaseManager($app, $app['db.factory']);        });        $this->app->bind('db.connection', function ($app) {            return $app['db']->connection();        });    }}

<?phpnamespace Umbrellio\Postgres\Connectors;use Illuminate\Database\Connection;use Illuminate\Database\Connectors\ConnectionFactory as ConnectionFactoryBase;use Umbrellio\Postgres\PostgresConnection;class ConnectionFactory extends ConnectionFactoryBase{    protected function createConnection($driver, $connection, $database, $prefix = '', array $config = [])    {        if ($resolver = Connection::getResolver($driver)) {            return $resolver($connection, $database, $prefix, $config);        }        if ($driver === 'pgsql') {            return new PostgresConnection($connection, $database, $prefix, $config);        }        return parent::createConnection($driver, $connection, $database, $prefix, $config);    }}

Начало работы

Представим что нам надо добавить поддержку нового типа tsrange в наши миграции. Как будет выглядеть наше расширение в нашем проекте теперь.

<?phpnamespace App\Extensions\TsRange;use App\Extensions\TsRange\Schema\Grammars\TsRangeSchemaGrammar;use App\Extensions\TsRange\Schema\TsRangeBlueprint;use App\Extensions\TsRange\Types\TsRangeType;use Umbrellio\Postgres\Extensions\AbstractExtension;use Umbrellio\Postgres\Schema\Blueprint;use Umbrellio\Postgres\Schema\Grammars\PostgresGrammar;class TsRangeExtension extends AbstractExtension{    public const NAME = TsRangeType::TYPE_NAME;    public static function getMixins(): array    {        return [            TsRangeBlueprint::class => Blueprint::class,            TsRangeSchemaGrammar::class => PostgresGrammar::class,            // ... список миксинов может включать в себя почти любой внутренний компонент Laravel        ];    }    public static function getName(): string    {        return static::NAME;    }    public static function getTypes(): array    {        return [            static::NAME => TsRangeType::class,        ];    }}

<?phpnamespace App\Extensions\TsRange\Schema;use Illuminate\Support\Fluent;use App\Extensions\TsRange\Types\TsRangeType;use Umbrellio\Postgres\Extensions\Schema\AbstractBlueprint;class TsRangeBlueprint extends AbstractBlueprint{    public function tsrange()    {        return function (string $column): Fluent {            return $this->addColumn(TsRangeType::TYPE_NAME, $column);        };    }}

<?phpnamespace App\Extensions\TsRange\Schema\Grammars;use App\Extensions\TsRange\Types\TsRangeType;use Umbrellio\Postgres\Extensions\Schema\Grammar\AbstractGrammar;class TsRangeSchemaGrammar extends AbstractGrammar{    protected function typeTsrange()    {        return function (): string {            return TsRangeType::TYPE_NAME;        };    }}

<?phpnamespace App\Extensions\TsRange\Types;use Doctrine\DBAL\Platforms\AbstractPlatform;use Doctrine\DBAL\Types\Type;class TsRangeType extends Type{    public const TYPE_NAME = 'tsrange';        public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform): string    {        return static::TYPE_NAME;    }    public function convertToPHPValue($value, AbstractPlatform $platform): ?array    {        //...        return $value;      }    public function convertToDatabaseValue($value, AbstractPlatform $platform): ?string    {        //...              return $value;    }    public function getName(): string    {        return self::TYPE_NAME;    }}

Теперь необходимо зарегистрировать наше расширение TsRangeExtension в нашем провайдере для работы с БД:

<?phpnamespace App\TsRange\Providers;use Illuminate\Support\ServiceProvider;use App\Extensions\TsRange\TsRangeExtension;use Umbrellio\Postgres\PostgresConnection;class TsRangeExtensionProvider extends ServiceProvider{    public function register(): void    {        PostgresConnection::registerExtension(TsRangeExtension::class);    }}

Итог

Вы можете писать свои расширения для Postgres имплементируя AbstractExtension, на мой взгляд, очень быстро и просто, не вникая в тонкости работы Laravel и Doctrine.

Это мой первый опыт, сделать что-то полезное для PHP сообщества, для тех кто использует в своих проектах Laravel / Postgres, не судите строго, пожалуйста.

Но я буду рад обратной связи, в любом ее проявлении, в Issues / Pull-реквестах, или в комментах относительно не только моей публикации, но и пакета в целом приму любую критику.

Пощупать данный пакет можно на GitHub: laravel-pg-extensions.

Спасибо за внимание.

Введение

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

Наш клиент - klara.com - коммуникационная телемедицинская платформа, упрощающая взаимодействие пациентов с врачами в США, столкнулась со стремительным ростом на волне пандемии 2020 года. Одним из вызовов на которые пришлось отвечать инженерам klara.com в это непростое время стало автоматизированное нагрузочное тестирование, способное обнаружить проблемы с производительностью до того как они проявят себя в production среде.

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

Постановка задачи выглядела так: спроектировать и реализовать пайплайн нагрузочного тестирования таким образом, чтобы его можно было легко поддерживать силами QA-инженеров, использовать как часть CI/CD процессов компании, расширять по необходимости и иметь возможность реализовать различные сценарии нагрузочного тестирования. Критерием успешности проекта стала точная реализация одного из кейса по производительности из production в среде нагрузочного тестирования с помощью инструментов, которые мы разработали.

Технологическая платформа

Основные языки программирования в команде - Ruby, JS. В качестве основного хранилища klara.com использует Postgres. Безопасность персональных данных пациентов (PHI - Protected Health Information) является ключевым аспектом бизнеса klara.com. Для обеспечения надежного хранения и обработки данных платформа использует HIPPA совместимую SaaS платформу - Aptible. Aptible покупает ресурсы у AWS, поэтому для дальнейшего описания будет достаточно считать Aptible сильно урезанной и зарегулированной версией AWS.

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

В этой статье я расскажу как мы готовили данные для нагрузочного тестирования.

Чтобы воспроизвести похожее поведение приложения во время тестов нам нужно иметь максимально похожую на production базу данных с точки зрения объема данных и их распределения. Из-за того, что klara хранит в том числе персональные данные, нам понадобится обфускация базы. Дополнительное условие - скорость работы обфускатора, хотелось бы быстро.

Обзор решений

Сейчас существует несколько инструментов для решения этой задачи в postgres, мы провели краткий сравнительный анализ, который приведен ниже:

postgresql_anonymizer

+ Самое популярное по количеству звезд на github решение из имеющихся

+ Очень много функций для разных типов данных с разными стратегиями, которые можно применять точечно для выбранных полей (http://personeltest.ru/aways/postgresql-anonymizer.readthedocs.io/en/latest/masking_functions/)

+ Можно выгружать сразу в *.sql дамп

- Нужно устанавливать как расширение рядом с бд

- Для каждого поля нужно прописывать security labels с масками

- Маски работают только с одной схемой

- Нет данных по производительности

Это решение мы не рассматривали из-за того, что оно должно устанавливаться как расширение, запихивать, что-то в production - так себе идея.

triki

+ Заявлена высокая скорость работы (1.4гб mysql dump 17sec)

+ Много типов данных + можно определить свои (форк + правки т.к. кристал руби-френдли язык)

+ Можно выгрузить конфигурацию всей схемы для дальнейшей настройки обфускатора (таблицы-поля)

+ Не требуется никаких правок в исходной бд, только настроить коннект

+ Выгружает в *.sql дамп

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

pgdump-obfuscator (форк)

+ есть возможность задавать параметры полей через cli (только в форке)

- нет информации о производительности

- обновлялся 8 лет назад

clickhouse-obfuscator

+ Заявлена высокая скорость работы (в докладе говорилось, что 1тб данных обфусцирован за 1.5 дня)

- Написан на C++ (нашей команде сложно вносить изменения)

- Принимает только csv-формат

- Нет внятной документации (только статья на Хабре)

Высокая скорость, сохранение распределения данных после обфускации, поддержка большой компанией и комьюнити - по этим причинам мы выбрали обфускатор от clickhouse.

pg_obfuscator

К сожалению для нашей задачи, clickhouse obfuscator не поддерживал прямое подключение к postgresql. Поэтому нам пришлось написать утилиту, которая решает задачу обфускации postgresql с учетом всей специфики работы именно с этой базой. Исходный код доступен по адресу.

Утилита представляет собой wrapper над psql client и clickhouse obfuscator и реализует следующую функциональность

• выгрузка схемы базы с сохранением внешних ключей и проверок ссылочной целостности

• исключение из обфускации таблиц, полей таблиц

• использование предопределенных шаблонов для генерации фейковых значений для полей

• маппинг типов данных postgres на типы данных clickhouse

• генерацию конфигурации со значениями по умолчанию

Из-за специфики работы clickhouse-obfuscator утилита требует дискового пространства для работы равного двойному размеру базы данных. Поставляется в виде docker image и доступна по адресу.

В настоящий момент есть ограничения, которые следует иметь в виду:

• утилита поддерживает только базовые типы postgres и не поддерживает вложенные: hstore, json, jsonb

• несмотря на автоматическую генерацию конфига, для первого запуска он нуждается в правках

• объем docker image составляет почти 700Mb

В рамках поставленной задачи мы наблюдали следующие скоростные характеристики: тестовая база 10Гб обфусцировалась за 40 минут, продуктовая в 50 Гб - 6..8 часов. Чем обусловлена нелинейность работы мы не выясняли.

Ниже я продемонстрирую работу c pg_obfuscator на примере работы с devrimgunduz/pagila: PostgreSQL Sample Database.

Демо

Развернем контейнер с postgres и создадим в нем 2 базы для демонстрации:

docker run --rm --name=db -e POSTGRES_PASSWORD=password -p5432:5432 postgresdocker exec -i db psql -U postgres postgres -c 'create database pagila;'docker exec -i db psql -U postgres postgres -c 'create database pagila_o;'

Посмотрим IP-адрес базы - он понадобится для работы обфускатора:

docker inspect db | grep IPAdd

"SecondaryIPAddresses": null,

"IPAddress": "172.17.0.2"

И зальем в контейнер скрипты из проекта pagila:

cd /tmpgit clone git@github.com:devrimgunduz/pagila.gitdocker exec -i db psql -U postgres pagila < /tmp/pagila/pagila-schema.sqldocker exec -i db psql -U postgres pagila < /tmp/pagila/pagila-data.sql

Убедимся, что там появились данные:

docker exec -i db psql -U postgres pagila -c 'select a.first_name, a.last_name, f.film_id, f.title, f.description from film f join film_actor fa on f.film_id = fa.film_id join actor a on a.actor_id=fa.actor_id where f.film_id = 7;'

first_name | last_name | film_id |   title   |                  description------------+-----------+---------+-----------------+-----------------------------------------------------------------------------------JIM    | MOSTEL  |    7 | AIRPLANE SIERRA | A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet BoatRICHARD  | PENN   |    7 | AIRPLANE SIERRA | A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet BoatOPRAH   | KILMER  |    7 | AIRPLANE SIERRA | A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet BoatMENA    | HOPPER  |    7 | AIRPLANE SIERRA | A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet BoatMICHAEL  | BOLGER  |    7 | AIRPLANE SIERRA | A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat(5 rows)

База для экспериментов готова. Теперь расчехлим pg_obfuscator, принципиальным моментом является монтирования тома для конфига, чтобы иметь возможность его потом поправить.

mkdir /tmp/configdocker run -it --rm -v /tmp/config:/opt/pg_obfuscator/config pg_obfuscator sh

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

bundle exec ruby pg_obfuscator.rb --configure --source-db-host 172.17.0.2 --source-db-port 5432 --source-db-name pagila --source-db-user postgres --source-db-password password.......I, [2021-04-02T08:47:54.682868 #9] INFO -- : Processed 20 tablesI, [2021-04-02T08:47:54.683243 #9] INFO -- : Check config before run export tables and obfuscation!I, [2021-04-02T08:47:54.696328 #9] INFO -- : Config saved to: config/config.yml

Обфускатор говорит, что нужно проверить конфиг и внести необходимые изменения. Конфиг для 20 таблиц получился около 400 строк, секции, которые нуждаются в правках отмечены - need_fix: true. Для того, чтобы вам было легче повторить я выложил исправленный конфиг сюда.

Для демонстрации генерации фейковых данных посмотрим на секцию в таблице actor:

last_name:db_data_type: textnot_null: trueobfuscator_data_type: Stringfake_data:type: patternvalue: "%{first_name}SON"

В качестве фамилии мы используем имя и постфикс SON.

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

ruby pg_obfuscator.rb --export-schema --source-db-host 172.17.0.2 --source-db-port 5432 --source-db-name pagila --source-db-user postgres --source-db-password passwordruby pg_obfuscator.rb --export-tables --source-db-host 172.17.0.2 --source-db-port 5432 --source-db-name pagila --source-db-user postgres --source-db-password passwordbundle exec ruby pg_obfuscator.rb --obfuscateruby pg_obfuscator.rb --import --target-db-host 172.17.0.2 --target-db-port 5432 --target-db-name pagila_o --target-db-user postgres --target-db-password password

и выйдя из контейнера с обфускатором посмотрим на результат

docker exec -i db psql -U postgres pagila_o -c 'select  a.first_name, a.last_name, f.film_id, f.title, f.description from film f join film_actor fa on f.film_id = fa.film_id join actor a on a.actor_id=fa.actor_id where f.film_id = 7;'

first_name | last_name | film_id |  title  |                           description------------+-----------+---------+-----------+------------------------------------------------------------------------------------------------------------------------SA     | SASON   |    7 | ROON SUIT | A Amazing Display of a Database Administ And a Dog And a Database a Pastry Chef And a Car And a Manned Mine Shark TankRURA    | RURASON  |    7 | ROON SUIT | A Amazing Display of a Database Administ And a Dog And a Database a Pastry Chef And a Car And a Manned Mine Shark TankBER    | BERSON  |    7 | ROON SUIT | A Amazing Display of a Database Administ And a Dog And a Database a Pastry Chef And a Car And a Manned Mine Shark TankCA     | CASON   |    7 | ROON SUIT | A Amazing Display of a Database Administ And a Dog And a Database a Pastry Chef And a Car And a Manned Mine Shark TankMER    | MERSON  |    7 | ROON SUIT | A Amazing Display of a Database Administ And a Dog And a Database a Pastry Chef And a Car And a Manned Mine Shark Tank(5 rows)

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

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

Благодарим команду clickhouse obfuscator за отличный продукт!

Однажды мне потребовалось забирать регулярно относительно большие объемы данных в MS SQL из PostgreSQL. Неожиданно выяснилось, что самый очевидный способ, через Linked Server на родные ODBC к PostgreSQL, очень медленный.

История вопроса

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

SET STATISTICS TIME ONDECLARE  @sql_str nvarchar(max)DROP TABLE IF EXISTS #tCREATE TABLE #t (  N int,  T datetime)SELECT @sql_str='  SELECT N, T  FROM generate_series(1,1000,1) N  CROSS JOIN generate_series($$2020-01-01$$::timestamp,    $$2020-12-31$$::timestamp, $$1 day$$::interval) T'INSERT #t (N, T)EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Такой простейший пример выборки всего 366 тысяч записей оказался жутко медленным:

SQL Server Execution Times:   CPU time = 8187 ms,  elapsed time = 14793 ms.

Решение

В первую очередь, захотелось исключить самый подозрительный элемент - ODBC. К тому времени MS уже предоставлял утилиту bcp для Linux. Поэтому bcp был установлен на сервер, где работал PostgreSQL и проведен следующий тест:

SET STATISTICS TIME ONDECLARE  @sql_str        nvarchar(max),  @proxy_account  sysname='proxy_account',  @proxy_password sysname='111111'DROP TABLE IF EXISTS ##tCREATE TABLE ##t (  N int,  T datetime)SELECT @sql_str='  COPY (    SELECT N, T    FROM generate_series(1,1000,1) N    CROSS JOIN generate_series($$2020-01-01$$::timestamp,      $$2020-12-31$$::timestamp, $$1 day$$::interval) T )  TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '    +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##t'' '    +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'','\')    +' -U '+@proxy_account+' -P '''    +@proxy_password+''' -c -b 10000000 -a 65535; '    +'rm $tmp_file $pgm$ NULL $nil$$nil$;'EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Результат сразу порадовал, причем сильно:

SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 881 ms.

Реализация

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

Вторая проблема в том, что в обычную временную таблицу bcp записать не может. Он ее просто не увидит. Значит нужно использовать постоянную таблицу или глобальную временную.

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

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

Итоговое решение следующее:

DECLARE  @sql_str        nvarchar(max),  @proxy_account  sysname='proxy_account',  @proxy_password sysname='111111'SELECT @sql_str='  DROP TABLE IF EXISTS ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+'  CREATE TABLE ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+' (    N int,    T datetime  )'EXEC (@sql_str)SELECT @sql_str='  COPY (    SELECT N, T    FROM generate_series(1,1000,1) N    CROSS JOIN generate_series($$2020-01-01$$::timestamp,      $$2020-12-31$$::timestamp, $$1 day$$::interval) T )  TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '    +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##proxy_table_'''    +CONVERT(nvarchar(max),@@SPID)+' '    +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'\','\\')    +' -U '+@proxy_account+' -P '''    +@proxy_password+''' -c -b 10000000 -a 65535; '    +'rm $tmp_file $pgm$ NULL $nil$$nil$;'EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Пояснения

В PostgreSQL команда COPY может писать в файл или на стандартный ввод вызываемой ей программы. В данном случае вместо программы использован скрипт на sh. Вывод COPY, поступающий на стандартный ввод, записывается во временный файл с уникальным именем, форимруемым mktemp. К сожалению, bcp не умеет читать данные со стандартного ввода, поэтому приходится ему создавать файл.

Для совместимости формата, формируемого командой COPY и формата, ожидаемого bcp, обязательно следует указывать в COPY параметр NULL $nil$$nil$

Остальные параметры bcp:

• -c - символьный формат, так как бинарный формат PostgreSQL не совместим с бинарным форматом MS SQL и мы вынуждены использовать только символьный;

• -b - количество записей, вставляемых одной транзакцией. В моей конфигурации десять миллионов оказалось оптимальным значением. В иной конфигурации это число, скорее, может потребоваться уменьшить, чем увеличить;

• -a - размер пакета. В нашем случае лучше указывать сразу максимальный. Если сервер не поддерживает указанную длину пакета, то просто будет использована максимальная длина пакета, поддерживаемая сервером.

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

У каждого из нас есть задача, над решением которой ты долго и безуспешно бьешься, которую изучил уже и вдоль и поперек, но все равно не можешь получить нужный результат. При этом одни уверены, что твоя задача в принципе не имеет решения, другие - что проблемы вообще нет. Такой задачей для меня была длительность выполнения запросов к хранилищу данных на Greenplum. И как же приятно наконец-то написать, что я эту проблему победил!

Из раза в раз при работе с хранилищем на ожидание результата запроса уходило по несколько минут. Причем не важно, запрос выбирает данные из таблицы на десять строк, или на миллион - время на ожидание будет соизмеримым.А любой простой в таком творческом процессе, как аналитика - это просто пытка!

Я много раз заходил на исправление такого поведения. Начал с тюнинга sql клиента. DBeaver предлагает пользователям целую кучу разных тонких настроек - кажется, я перепробовал их все, но успеха не достиг. Следом перепробовал настройки самого драйвера PostgreSQL - эффект тот же.

Все это время перед глазами был пример специализированного клиента для Greenplum - Aginity Workbench, в котором из коробки скорость выполнения аналогичных запросов ощутимо выше. Эта прога была дополнительным раздражающим фактором, так как найти причины такого ускорения у меня не получалось - ее исходный код закрыт и работает она только по лицензии, а подключение к greenplum в ней происходит при помощи нативных инструментов, а не обычных jdbc драйверов.

Озарение пришло, когда заглянул в системную вьюху pg_stat_activity - оказалось, что от клиента к базе в действительности летит не один запрос на данные, а два: сначала происходит выборка данных по запросу, а затем - чтение метаданных для этой выборки из pg_catalog. Причем из общего времени выполнения большую часть занимает именно чтение метаданных.

В этот момент я испытал целую бурю эмоций - от радости, что наконец-то нашел проблему, до недоумения от самой сути находки. То, что каталог прилично нагружен, я знал и раньше: к хранилищу сейчас подключено уже больше 250 систем-источников, в каждом не одна сотня таблиц, а в самих таблицах может быть двухуровневое партиционирование. Greenplum учитывает каждую партицию как отдельный объект, поэтому системные представления вроде pg_class прилично раздуваются. Но неужели действительно нужно обращаться к каталогу при любом запросе данных?

К счастью, драйвер постгреса имеет открытый исходный код, поэтому можно самостоятельно посмотреть, что же происходит под капотом. Заглянул в код, нашел нужный класс PgResultSetMetaData и его метод, который запрашивает метаданные - fetchFieldMetaData. Содержимое обнадеживает - похоже, без метаданных можно обойтись: fetchFieldMetaData вызывается из нескольких других методов, которые для моих целей не так важны, при этом сами эти методы допускают возврат пустого объекта, что намекает на возможность выпилить из кода вообще все обращения к fetchFieldMetaData, а значит избежать медленного запроса к каталогу.

Проверяю влияние по коду, комментирую вызов метода fetchFieldMetaData, собираю jar файл драйвера, подсоединяюсь с его помощью к базе, иииии

Сказать, что это дало результат - это не сказать ничего. Запросы теперь просто летают. Ускорение - в разы. Отправил коллегам на тест - отзывы примерно такие:

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

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

P.S. В итоговой версии кода создал новый параметр подключения драйвера runtimeMetaDisable. Вызов метаданных и выбрасывание исключений привязал к его значению. Такой подход более гибок, чем жестко закомментированный вызов метода и позволяет управлять поведением драйвера в зависимости от потребностей. Код выложил на гитхаб. Если у вашей базы тяжелый каталог и вы хотите попробовать драйвер в деле, но не знакомы с миром java и не знаете, как собрать jar файл драйвера - напишите в комментариях!

Данные это один из наиболее важных компонентов геопространственных технологий и, пожалуй, любой другой отрасли. К управлению данными сейчас относятся серьезно во всех отраслях, поэтому знания по этой дисциплине имеют важное значение для карьеры ИТ-специалистов. Этот цикл статей задуман как универсальное руководство, в котором мы рассмотрим тему от и до, начиная с вопроса Что такое данные? и заканчивая изучением и применением геопространственных запросов.

Основные понятия баз данных

Что такое данные?

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

Что такое база данных?

Множество записей данных, собранных вместе, образуют базу данных. Базы данных обычно создаются для того, чтобы пользователи могли обращаться к большому количеству данных и массово выполнять с ними определенные операции.База данных может хранить что угодно: представьте себе, например, блокнот вашей бабушки со всеми ее вкусными рецептами, учетную книгу ваших родителей, куда они записывают все доходы и расходы, или свою страницу в Facebook со списком всех ваших друзей. Из этих примеров видно, что все данные в базе данных относятся более-менее к одному типу.

Зачем нужна база данных?

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

Система управления базами данных (СУБД)

Итак, мы уже знаем, что данные и базы данных важны, но как осуществляется работа с базами данных в компьютерных системах? Вот тут на сцену и выходит СУБД. СУБД это программное обеспечение, предоставляющее нам способ взаимодействия с базами данных на компьютере для выполнения различных операций, таких как создание, редактирование, вставка данных и т.д. Для этого СУБД предоставляет нам соответствующие API. Редко какие программы не используют СУБД для работы с данными, хранящимися на диске.Помимо операций с данными СУБД также берет на себя резервное копирование, проверку допуска, проверку состояния базы данных и т.д. Поэтому рекомендуется всегда использовать СУБД при работе с базами данных.

Пространственные данные и база данных

Особое внимание мы уделим обработке пространственных данных, поэтому я хотел бы обсудить здесь этот тип данных. Пространственные данные несколько отличаются от остальных. Координаты необходимо сохранять в особом формате, который обычно указан в документации на веб-сайте о базе данных. Этот формат позволяет базе считывать и правильно воспринимать координаты. Если обычно для поиска данных мы используем запросы типа Получить все результаты, где возраст>15, то пространственный запрос выглядит как-то так: Получить все результаты в радиусе 10км от определенной точки.Поэтому пространственные данные необходимо хранить в надлежащем формате.

Типы баз данных

Базы данных обычно делятся на два типа: реляционные и нереляционные. Оба типа имеют свои плюсы и минусы. Было бы глупо утверждать, что один лучше другого, поскольку это будет зависеть от варианта использования. Конкретно для пространственных данных я в 99% случаев использую реляционные базы данных, и вы скоро поймете почему.

Реляционные базы данных и РСУБД

Допустим, ваш начальник просит вас создать электронную таблицу с важной информацией, включающей имена, местонахождения, адреса электронной почты, номера телефонов и должности всех сотрудников. Вы сразу же откроете таблицу Excel или Google Spreadsheets, напишете все эти названия столбцов и начнете собирать информацию.

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

Взаимосвязи в реляционных базах данных мы подробно рассмотрим позже.

По сравнению с базами данных NoSQL, недостатком реляционных баз данных является относительно медленное получение результатов, когда количество данных стремительно увеличивается (по мнению автора статьи прим. пер.). Еще один недостаток заключается в том, что при добавлении каждой записи нужно следовать определенным правилам (типы столбцов, количество столбцов и т.д.), мы не можем просто добавить отдельный столбец только для одной записи.В реляционных базах данных используется SQL(Structured Query Language язык структурированных запросов), с помощью которого пользователи могут взаимодействовать с данными, хранящимися в таблицах. SQL стал одним из наиболее широко используемых языков для этой цели. Мы подробнее поговорим об SQL чуть позже.Вот примеры некоторых известных и часто используемых реляционных баз данных: PostgreSQL, MySQL, MSSQL и т.д. У каждой крупной компании, занимающейся реляционными базами данных, есть собственная версия SQL. В большинстве аспектов они выглядят одинаково, но иногда требуется немного изменить какой-нибудь запрос, чтобы получить те же результаты в другой базе данных (например, при переходе из PostgreSQL в MySQL).

Нереляционные базы данных (NoSQL)

Все базы данных, не являющиеся реляционными, относятся к категории нереляционных баз данных. Обычно данные хранятся в нетабличном формате, например:

1. Пара ключ-значение

2. Формат JSON, XML

3. Графовый формат

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

База данных NoSQL реального времени в Google Firebase

При использовании баз данных NoSQL пользователям иногда приходится прописывать собственную логику, чтобы добавить уникальный ключ к каждой записи и тем самым обеспечить доступ к записям. В большинстве стандартных баз данных NoSQL, таких как Firebase и MongoDB, для хранения данных используется формат JSON. Благодаря этому очень легко и удобно выполнять операции с данными из веб-приложений, используя JavaScript, Python, Ruby и т.д.

Рекомендации по выбору типа базы для хранения пространственных данных

Очевидно, что нам хотелось бы сохранить точку, линию, многоугольник, растры и т.д. так, чтобы это имело смысл, вместо того чтобы сохранять просто координаты. Нам нужна СУБД, которая позволяет не только сохранять данные, но и запрашивать их пространственными методами (буфер, пересечение, вычисление расстояния и т.д.). На сегодняшний день для этого лучше всего подходят реляционные базы данных, поскольку в SQL есть функции, помогающие выполнять подобные операции. Использование таких дополнительных средств, как PostGIS для PostgreSQL, открывает разработчикам возможности для написания сложных пространственных запросов. С другой стороны, NoSQL тоже работает в области геопространственных технологий: например, MongoDB предоставляет кое-какие функции для выполнения геопространственных операций. Однако реляционные базы данных все же лидируют на рынке с большим отрывом.

Работа с РСУБД

Основное внимание мы уделим РСУБД, так как именно эти системы в большинстве случаев мы будем использовать для хранения пространственных данных и работы с ними. В качестве примера мы будем использовать PostgreSQL, поскольку это самая перспективная реляционная база данных с открытым исходным кодом, а ее расширение PostGIS позволяет работать и с пространственными данными. Вы можете установить PostgreSQL, следуя инструкциям из документации. Помимо PostgreSQL рекомендуется также загрузить и установить pgAdmin. Платформа pgAdmin предоставляет веб-интерфейс для взаимодействия с базой данных. Также для этого можно загрузить и установить какое-либо другое совместимое ПО или использовать командную строку.

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

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

В инструменте запросов (Query Tool) база данных создается следующим образом:

CREATE DATABASE <database_name>

Создание таблиц. Создание таблицы требует некоторых дополнительных соображений, поскольку именно здесь нам нужно определить все столбцы и типы данных в них. Все типы данных, которые можно использовать в PostgreSQL, вы найдете здесь.

pgAdmin позволяет нам выбрать в таблице различные ключи и ограничения, например Not Null (запрет на отсутствующие значения), Primary Key (первичный ключ) и т.д. Обсудим это подробнее чуть позже.

Заметьте, что мы не добавляли столбец первичного идентификатора в список столбцов, поскольку PostgreSQL делает это автоматически. Мы можем создать сколько угодно таблиц в одной базе данных. После того как таблицы созданы, мы можем установить связи между разными таблицами, используя определенные столбцы (обычно столбцы с идентификаторами).В инструменте запросов таблица создается следующим образом:

CREATE TABLE <table_name> (<column_1> <datatype>,<column_2> <datatype>,.....<column_n> <datatype>PRIMARY KEY (<column>));

CRUD-операции с данными в таблицах

CRUD-операции (создание, чтение, обновление и удаление Create, Retrieve, Update, Delete) это своего рода hello world в мире СУБД. Поскольку эти операции используются наиболее часто, команды для их выполнения одинаковы во всех РСУБД. Мы будем писать и выполнять запросы в инструменте запросов в pgAdmin, который вызывается следующим образом:

1. Создание новой записи

Для добавления новой записи в таблицу используйте следующую команду:

INSERT INTO <tablename> (column1, column2, column3,...) VALUES (value1, value2, value3,...);

INSERT, INTO, VALUE являются ключевыми словами в SQL, поэтому их нельзя использовать в качестве переменных, значений и т.д. Чтобы добавить новую запись в нашу таблицу пользователей, мы напишем в инструменте запросов следующий запрос:

INSERT INTO users(name, employed, address) VALUES ('Sheldon Cooper', true, 'Pasadena');

Обратите внимание: строки всегда следует заключать в'' (одинарные кавычки), а не в"" (двойные кавычки).

2. Получение записей (всех или нескольких)

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

select <column1, column2 ,...> from <tablename> 

Этот код извлекает весь набор данных. Если вы хотите получить только 20записей, напишите:

select <column1, column2 ,...> from <tablename> limit 20

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

select * from <tablename>

Если вы хотите получить результат с определенным условием, используйте ключевое слово WHERE, как показано ниже:

select * from <tablename> where <key> = <value>

Вы можете создавать даже сложные запросы, о которых мы поговорим позже.В нашем примере мы можем получить нужные нам данные:

--Retrieving Specific columns for all usersselect name,employed from users--Retrieving all columns for all usersselect * from users--Retrieving all columns for first 3 usersselect * from users limit 3--Retrieving all columns for all users where employed = trueselect * from users where employed = true

3. Обновление записей (всех или нескольких)РСУБД позволяет нам обновить все или только некоторые записи данных, указав новые значения для столбцов.

UPDATE <tablename> SET <column1> = <value1>, <column2> = <value2> 

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

UPDATE <tablename> SET <column1> = <value1>, <column2> = <value2>WHERE <column> = <value> 

В нашем случае мы обновим таблицы с помощью следующих запросов:

-- Make all rows as  employed = trueupdate users set employed = true-- change employed = false for entries with address = 'nebraska'update users set employed = false where address = 'nebraska'

4. Удаление записей (всех или нескольких)Удалять записи в SQL легко. Пользователь может удалить либо все строки, либо только определенные строки, добавив условие WHERE.

-- Deleting all entries Delete from <tablename> -- Deleting entries based on conditionsDelete from <tablename> where <column> = <value> 

-- Deleting all entries Delete from users-- Deleting entries based on conditionsDelete from users where employed = false

CRUD-операции используются очень часто, поскольку выполняют основные функции в базе данных.

Перевод подготовлен в рамках курса Базы данных. Все желающих приглашаем на бесплатный двухдневный онлайн-интенсив Бэкапы и репликация PostgreSQL. Практика применения. Цели занятия: настроить бэкапы; восстановить информацию после сбоя. Регистрация здесь.