Zautomatyzuj eksport rejestrów PostgreSQL do Amazon S3 za pomocą rozszerzeń

15 marca 2022

Czy chcesz skopiować lub zarchiwizować rejestry usługi Amazon Relational Database Service (Amazon RDS) dla PostgreSQL lub Amazon Aurora PostgreSQL-Compatible Edition bezpośrednio do Amazon Simple Storage Service (Amazon S3)?

Czy Twoja organizacja posiada wymagania prawne dotyczące audytu wszystkich działań DDL lub DML względem bazy danych RDS dla PostgreSQL?

Dzięki dodaniu rozszerzenia pg_cron dla Amazon RDS dla PostgreSQL (wersja 12.5 i nowsze) oraz dla Amazon Aurora dla PostgreSQL (wersja 12.6+), możesz teraz zautomatyzować eksport logów silnika PostgreSQL bezpośrednio do Amazon S3 za pomocą rozszerzeń PostgreSQL log_fdw, aws_s3 i pg_cron. Dzięki tym rozszerzeniom możesz kopiować i archiwizować rejestry bazy danych PostgreSQL bezpośrednio do Amazon S3.

W tym artykule twórcy przedstawiają, jak korzystać z rozszerzeń PostgreSQL i zautomatyzować eksport logów PostgreSQL bezpośrednio do Amazon S3. Zostanie zaprezentowany również przykład, w jaki sposób możesz filtrować logi silnika i eksportować tylko instrukcje DML do Amazon S3.

Warunki wstępne

W tym artykule autorzy wykorzystują Amazon RDS dla instancji PostgreSQL. Zanim zaczniesz, upewnij się, że spełniasz następujące wymagania wstępne.

  1. Utwórz lub miej dostęp do konta AWS.
  2. Zainstaluj i skonfiguruj interfejs wiersza poleceń AWS (AWS CLI).
  3. Utwórz RDS dla instancji PostgreSQL (wersja 12.5 lub wyższa). Aby uzyskać instrukcje dotyczące udostępniania instancji, zobacz Tworzenie i łączenie z bazą danych PostgreSQL za pomocą Amazon RDS.
  4. Uruchom instancję Amazon Elastic Compute Cloud (Amazon EC2), aby uzyskać dostęp do instancji bazy danych z zainstalowanym i skonfigurowanym klientem psql.
  5. Utwórz zasobnik S3, w którym przechowujesz logi wyeksportowane z bazy danych.
  6. Utwórz wymagane role i zasady AWS Identity and Access Management (IAM) i dołącz je do swojej instancji RDS dla PostgreSQL, aby zapewnić dostęp do zasobnika S3.
  7. Utwórz następujące rozszerzenia PostgreSQL w bazie danych RDS dla PostgreSQL:
  8. pg_cron – Aby włączyć rozszerzenie pg_cron, zmodyfikuj grupę parametrów bazy danych i dodaj wartość pg_cron do parametru shared_preload_libraries. Uruchom ponownie bazę danych;
  9. log_fdw – Używane jest rozszerzenie log_fdw, aby załadować wszystkie dostępne pliki dzienników RDS dla PostgreSQL lub Aurora PostgreSQL DB jako tabelę;
  10. aws_s3 – Dzięki rozszerzeniu aws_s3 możesz odpytywać dane z RDS o instancję PostgreSQL DB i eksportować je bezpośrednio do plików przechowywanych w wiadrze S3. Wymaga to utworzenia zasobnika S3 i roli uprawnień oraz dołączenia roli do instancji bazy danych.

Omówienie rozwiązania

Aby rozpocząć, najpierw utwórz niezbędne zasoby.

1. Utwórz niestandardowe grupy parametrów i zezwól na obsługę rozszerzeń.

2. Utwórz rolę i politykę uprawnień i dołącz ją do swojego RDS dla instancji PostgreSQL.

Następnie jesteś gotowy do wdrożenia rozwiązania.

3. Użyj rozszerzenia log_fdw do importowania dzienników PostgreSQL do swojej bazy danych RDS for PostgreSQL. Te rejestry są przechowywane w tabeli.

4. Następnie eksportuj te logi do zasobnika S3 za pomocą rozszerzenia aws_s3.

5. Aby zautomatyzować cały proces, użyj rozszerzenia pg_cron i zaplanuj zadanie, które okresowo wysyła te logi do Amazon S3.

Poniższy diagram ilustruje architekturę rozwiązania.

Zautomatyzuj eksport rejestrów PostgreSQL do Amazon S3 za pomocą rozszerzeń

Pora przyjrzeć się szczegółom każdego kroku tego rozwiązania.

Twórz niestandardowe grupy parametrów i zezwalaj na obsługę rozszerzeń.

Utwórz i skojarz grupę parametrów dla instancji RDS dla PostgreSQL (w tym poście log-stream-demo-instance) przy użyciu interfejsu AWS CLI z następującym kodem.

aws rds create-db-parameter-group --db-parameter-group-name log-exporting-pg --db-parameter-group-family postgres12 --description 'Parameter group for allowing automated log exporting to s3 from RDS'
    {
        "DBParameterGroup": {
            "DBParameterGroupArn":
            "arn:aws:rds:us-east-1:<AWS-ACCOUNT-ID>:pg:log-exporting-pg",
            "DBParameterGroupName": "log-exporting-pg",
            "DBParameterGroupFamily": "postgres12",
            "Description": "Parameter group for allowing automated log
             exporting to s3 from RDS"
        }
    }

aws rds modify-db-instance --db-instance-identifier log-stream-demo-instance --db-parameter-group-name log-exporting-pg 
    "DBParameterGroups": [
        {
            "DBParameterGroupName": "log-exporting-pg",
            "ParameterApplyStatus": "applying"
        }

Zmodyfikuj grupę parametrów, aby obsługiwała pg_cron i log_fdw.

aws rds modify-db-parameter-group --db-parameter-group-name log-exporting-pg --parameters "ParameterName=shared_preload_libraries,ParameterValue='pg_cron',ApplyMethod=pending-reboot" --parameters "ParameterName=log_destination,ParameterValue='csvlog',ApplyMethod=pending-reboot"
    {
        "DBParameterGroupName": "log-exporting-pg"
    }

Zrestartuj bazę danych, aby umożliwić wprowadzenie zmian w grupie parametrów.

aws rds reboot-db-instance --db-instance-identifier log-stream-demo-instance
    "DBParameterGroups": [
        {
            "DBParameterGroupName": "log-exporting-pg",
            "ParameterApplyStatus": "pending-reboot"
        }
    ],
    DBInstanceStatus": "rebooting"

Utwórz rolę i zasady IAM i dołącz je do swojego RDS dla instancji PostgreSQL.

Utwórz zasobnik i odpowiednie role uprawnień do konfiguracji aws_s3, umożliwiając Amazon RDS dostęp do Amazon S3.

1. Skonfiguruj zasobnik do eksportu rejestrów.

aws s3api create-bucket --bucket log-export-bucket --region us-east-1
    {
        "Location": "/log-export-bucket"
    }

2. Utwórz zasadę uprawnień z najmniej ograniczonymi uprawnieniami do zasobów w poniższym kodzie i nazwij ją postgres-s3-access-policy. Polityka musi mieć dostęp do zasobnika Amazon S3, w którym przechowywane są pliki (dla tego posta log-export-bucket).

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:AbortMultipartUpload",
                "s3:DeleteObject",
                "s3:ListMultipartUploadParts",
                "s3:PutObject",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::log-export-bucket/*",
                "arn:aws:s3:::log-export-bucket"
            ]
        }
    ]
}

aws iam create-policy --policy-name postgres-s3-access-policy --policy-document "$(cat accessPolicyDocument.txt)"
{
    "Policy": {
        "PolicyName": "postgres-s3-access-policy",
        "PermissionsBoundaryUsageCount": 0,
        "CreateDate": "",
        "AttachmentCount": 0,
        "IsAttachable": true,
        "PolicyId": "",
        "DefaultVersionId": "v1",
        "Path": "/",
        "Arn": "arn:aws:iam::000123456789:policy/postgres-s3-access-policy",
        "UpdateDate": ""
    }
}

3. Utwórz rolę uprawnień o nazwie postgres-s3-export-role i zmodyfikuj relację zaufania.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "rds.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

aws iam create-role --role-name postgres-s3-export-role --assume-role-policy-document "$(cat assumeRolePolicyDocument.txt)" 
{
    "Role": {
        "AssumeRolePolicyDocument": {
            "Version": "2012-10-17",
            "Statement": [
                {
                    "Action": "sts:AssumeRole",
                    "Effect": "Allow",
                    "Principal": {
                        "Service": "rds.amazonaws.com"
                    }
                }
            ]
        },
        "RoleId": "",
        "CreateDate": "",
        "RoleName": "postgres-s3-export-role",
        "Path": "/",
        "Arn": "arn:aws:iam::000123456789:role/postgres-s3-export-role"
    }
}

aws iam attach-role-policy --role-name postgres-s3-export-role --policy-arn arn:aws:iam::000123456789:policy/postgres-s3-access-policy

AssumeRole umożliwia Amazon RDS dostęp do innych usług AWS w Twoim imieniu.

4. Powiąż role uprawnień z instancją i klastrem bazy danych.

aws rds add-role-to-db-instance --db-instance-identifier log-stream-demo-instance  --feature-name s3Export  --role-arn arn:aws:iam::000123456789:role/postgres-s3-export-role
    <No output indicates success>

Importuj rejestry PostgreSQL do tabeli za pomocą rozszerzenia log_fdw

Aby skorzystać z funkcji log_fdw, najpierw musisz utworzyć rozszerzenie na instancji bazy danych. Połącz się z bazą danych za pomocą psql i uruchom następujące polecenie.

postgres=> CREATE EXTENSION log_fdw;
CREATE EXTENSION

Po załadowaniu rozszerzenia możemy utworzyć funkcję, która ładuje wszystkie dostępne pliki dziennika PostgreSQL DB jako tabelę w bazie danych. Definicja funkcji jest dostępna na GitHub.

Po utworzeniu funkcji możesz uruchomić funkcję ładowania rejestrów PostgreSQL do bazy danych. Za każdym razem, gdy uruchamiasz następujące polecenie, tabela logs.postgres_logs jest aktualizowana o najnowsze logi silnika.

postgres=> SELECT public.load_postgres_log_files();
NOTICE:  CSV log format will be used.
NOTICE:  Processing log file - postgresql.log.2021-11-19-21.csv
NOTICE:  Processing log file - postgresql.log.2021-11-19-20.csv
             load_postgres_log_files
--------------------------------------------------
 Postgres logs loaded to table logs.postgres_logs
(1 row)

Możesz wyświetlić rejestry za pomocą polecenia SELECT w tabeli logs.postgres_logs. Zawężając polecenie SELECT, możesz dostosować nasze zapytanie do konkretnego przypadku użycia.

W poniższym przykładzie zwróć uwagę tylko na komunikaty o błędach z dzienników silnika, które wystąpiły w ciągu ostatniej godziny.

SELECT * FROM logs.postgres_logs WHERE message SIMILAR TO '%(INSERT|UPDATE|SELECT|DELETE)%' AND log_time >= NOW() - INTERVAL '1' HOUR ORDER BY log_time DESC;

Eksportuj rejestry PostgreSQL z tabeli do Amazon S3 za pomocą aws_s3

Teraz, gdy posiadasz funkcję do zapytania o nowe instrukcje dziennika, użyj aws_s3 do eksportowania pobranych dzienników do Amazon S3. Z wymagań wstępnych powinieneś już mieć utworzony zasobnik S3 i mieć dołączoną rolę uprawnień do instancji DB, która umożliwia zapisywanie w zasobniku S3.

Utwórz rozszerzenie aws_s3 z następującym kodem:

postgres=> CREATE EXTENSION aws_s3 CASCADE;
CREATE EXTENSION

Uruchom funkcję eksportu zapytania do Amazon S3:

postgres=> SELECT aws_s3.query_export_to_s3('SELECT * FROM logs.postgres_logs WHERE message SIMILAR TO $Q1$%(INSERT|UPDATE|SELECT|DELETE)%$Q1$ AND log_time >= NOW() - INTERVAL $Q2$1$Q2$ HOUR ORDER BY log_time DESC;', 's3-bucket-name', 'exported-file-name', 's3-bucket-region', options := 'format text');

 rows_uploaded | files_uploaded | bytes_uploaded
---------------+----------------+----------------
             5 |              1 |              235
(1 row)

Uwaga: to zapytanie używa „cudzysłowów w dolarach” do obsługi cytatów zagnieżdżonych. Cytaty dolara składają się ze znaku dolara ($), ponumerowanego cytatu Q1, Q2 i innego znaku dolara otaczającego ciąg, który cytują. W tym przykładzie ciągi w cudzysłowie to „%(INSERT|UPDATE|SELECT|DELETE)%” i „1”.

W chwili pisania tego tekstu eksport między regionami jest zablokowany.

Zautomatyzuj eksport rejestrów za pomocą rozszerzenia pg_cron

Teraz, gdy posiadasz już instrukcje umożliwiające przesyłanie rejestrów do Amazon S3 przy użyciu rozszerzeń log_fdw i aws_s3, możesz zautomatyzować te kroki za pomocą pg_cron. Za pomocą pg_cron możesz pisać zapytania do bazy danych, które będą uruchamiane według wybranego przez Ciebie harmonogramu.

W ramach wymagań wstępnych należy dodać pg_cron do parametru shared_preload_libraries w grupie parametrów instancji bazy danych. Po załadowaniu pg_cron do shared_preload_libraries możesz po prostu uruchomić następujące polecenie, aby utworzyć rozszerzenie:

postgres=> CREATE EXTENSION pg_cron;
CREATE EXTENSION

Po utworzeniu pg_cron możesz użyć rozszerzenia do przesyłania dzienników PostgreSQL zgodnie z harmonogramem zdefiniowanym przez cron. Aby to zrobić, musisz zaplanować zadanie cron, przekazując nazwę, harmonogram i zapytanie eksportu dziennika, które chcesz uruchomić. Na przykład, aby zaplanować przesyłanie dziennika co godzinę z tym samym zapytaniem opisanym wcześniej, możesz uruchomić następujące polecenie:

postgres=> SELECT cron.schedule('postgres-s3-log-uploads', '0 * * * *', 'do $$ begin
    PERFORM public.load_postgres_log_files();
    PERFORM aws_s3.query_export_to_s3($Q1$SELECT * FROM 
    logs.postgres_logs WHERE message SIMILAR TO 
    $Q2$%(INSERT|UPDATE|SELECT|DELETE)%$Q2$ AND log_time >= NOW()
    - INTERVAL $Q3$1$Q3$ HOUR ORDER BY log_time DESC;$Q1$, 
    $Q4$s3-bucket-name$Q4$, $Q5$exported-file-name$Q5$, 
    $Q6$s3-bucket-region$Q6$, options := $Q7$format text$Q7$);
end $$;');

 schedule
----------
        1
(1 row)

Po zaplanowaniu przesyłania możesz wysłać zapytanie do tabeli cron.job, aby zobaczyć zaplanowane zadanie crona wraz z jego informacjami:

postgres=# SELECT * FROM cron.job WHERE jobname = 'postgres-s3-log-uploads';
jobid  | schedule  | command | nodename  | nodeport | database | username | active | jobname        
-------+-----------+---------+-----------+----------+----------+----------+-------+-----------
   1   | 0 * * * * | do $$ begin PERFORM public.load_postgres_log_files(); PERFORM aws_s3.query_export_to_s3($Q1$SELECT * FROM logs.postgres_logs WHERE message SIMILAR TO $Q2$%(INSERT|UPDATE|SELECT|DELETE)%$Q2$ AND log_time >= NOW() - INTERVAL $Q3$1$Q3$ HOUR ORDER BY log_time DESC;$Q1$, $Q4$s3-bucket-name$Q4$, $Q5$exported-file-name$Q5$,Q6$s3-bucket-region$Q6$, options := $Q7$format text$Q7$); end $$; | localhost |  5432  |  postgres | rdsadmin |  t  | postgres-s3-log-uploads 
(1 row)

Możesz również wyświetlić historię uruchomień z każdego uruchomienia tego zadania crona, kwestionując tabelę audytu pg_cron. Zapewnia to informacje o czasie wykonywania każdego przesyłania, czy zakończyło się pomyślnie, czy nie i nie tylko. Zobacz następujący kod:

postgres=> SELECT * FROM cron.job_run_details WHERE jobid IN (SELECT jobid FROM cron.job WHERE jobname = 'postgres-s3-log-uploads');

jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
------+-------+---------+----------+----------+---------+--------+------------------------+------------+----------
(0 rows)

Jeśli w dowolnym momencie zdecydujesz, że chcesz anulować automatyczne przesyłanie rejestrów, możesz anulować harmonogram powiązanego zadania cron, podając nazwę zadania określoną wcześniej. W poniższym przykładzie nazwa zadania to postgres-s3-log-uploads:

postgres=> SELECT cron.unschedule('postgres-s3-log-uploads'); 
unschedule
------------
 t
(1 row)

Aby uzyskać więcej informacji na temat planowania zadań za pomocą pg_cron, zobacz Planowanie zadań za pomocą pg_cron w bazach danych Amazon RDS dla PostgreSQL lub Amazon Aurora dla PostgreSQL.

Powszechne błędy i rozwiązywanie problemów

Podczas eksportowania dzienników bazy danych z Amazon RDS do zasobnika S3 może pojawić się następujący błąd:

ERROR:  could not upload to Amazon S3
DETAIL:  Amazon S3 client returned 'curlCode: 28, Timeout was reached'.

Wiadomość zwykle pojawia się, gdy brakuje reguł wychodzących w grupie zabezpieczeń dołączonej do wystąpienia RDS. Po wywołaniu funkcji aws_s3.query_export_to_s3 Amazon RDS wykonuje wywołanie API PutObject HTTPS w celu przesłania pliku do zasobnika S3. Upewnij się, że zasady dołączone do roli zapewniają dostęp do zasobnika S3.

Może wystąpić błąd, jeśli utworzysz rozszerzenie, ale konkretna wersja Amazon RDS dla PostgreSQL nie obsługuje tego rozszerzenia. Na przykład rozszerzenie pg_cron jest obsługiwane począwszy od Amazon RDS dla PostgreSQL w wersji 12.5. Jeśli używasz wersji starszej niż 12,5, wystąpi błąd. Aby uzyskać więcej informacji o obsługiwanych rozszerzeniach, zobacz Rozszerzenia PostgreSQL obsługiwane w Amazon RDS.

Najlepsze praktyki

Autorzy zalecają ustawienie parametru log_destination na „csvlog” w grupie parametrów. Dzięki temu rozszerzenie log_fdw tworzy tabele zawierające instrukcje dziennika z kolumnami, co ułatwia użytkownikom wykonywanie zapytań o dane.

Eksportowanie dzienników PostgreSQL do Amazon S3 przy użyciu rozszerzeń jest zalecane tylko w przypadku eksportu o małej objętości. Eksportowanie dużej ilości danych z Amazon RDS dla PostgreSQL do Amazon S3 to intensywna operacja i może mieć wpływ na inne transakcje działające w klastrze. W przypadku przesyłania większych rejestrów i monitorowania logów zalecamy korzystanie z Amazon CloudWatch. CloudWatch zapewnia różne metryki, alarmy i narzędzia, które upraszczają proces monitorowania Twojej bazy danych. CloudWatch gwarantuje również opcję eksportu do Amazon S3, jeśli ta konkretna funkcjonalność jest również pożądana.

Istnieje wiele sposobów na zwiększenie szybkości przesyłania, które mogą się różnić w zależności od obciążenia pracą. Na przykład, jeśli chcesz przesłać instrukcje dziennika błędów i komunikaty DDL, uruchomienie eksportu równoległego może poprawić szybkość przesyłania. Aby uzyskać więcej informacji na temat najlepszych praktyk podczas eksportowania do Amazon S3, zobacz Eksportowanie i importowanie danych z Amazon S3 do Amazon Aurora PostgreSQL.

Tabela kontroli cron.job_run_details przechowuje historyczne informacje o czasie wykonywania zaplanowanych zadań cron. Najlepszym rozwiązaniem jest zaplanowanie zadania w celu okresowego czyszczenia starszych rekordów z tabeli.

Podsumowanie

W tym artykule twórcy zaprezentowali, w jaki sposób można wykorzystać rozszerzenia PostgreSQL log_fdw, aws_s3 i pg_cron do zautomatyzowania eksportu rejestrów PostgreSQL do Amazon S3. Jest to doskonały przypadek użycia do przechowywania danych historycznych związanych z audytem. Możesz także korzystać z różnych narzędzi do wizualizacji danych przechowywanych w Amazon S3, aby przeprowadzać analizy i tworzyć pulpity nawigacyjne, aby zobaczyć tendencje.

źródło: AWS

 

Case Studies
Referencje

Rekomendujemy firmę Hostersi Sp. z o.o. jako odpowiedzialnego i wykwalifikowanego partnera, dbającego o wysoki poziom obsługi klienta. Zlecenie zostało wykonane profesjonalnie, według najlepszych standardów, w bardzo krótkim czasie.

Paweł Rokicki
Managing Director
W skrócie o nas
Specjalizujemy się w dostarczaniu rozwiązań IT w obszarach projektowania infrastruktury serwerowej, wdrażania chmury obliczeniowej, opieki administracyjnej i bezpieczeństwa danych.