Pg_stat_statments 설치 방법

주요 콘텐츠로 건너뛰기

이 브라우저는 더 이상 지원되지 않습니다.

최신 기능, 보안 업데이트, 기술 지원을 이용하려면 Microsoft Edge로 업그레이드하세요.

Azure Database for PostgreSQL - 유연한 서버의 PostgreSQL 확장

• 아티클
• 11/16/2022
• 읽는 데 32분 걸림

이 문서의 내용

적용 대상:

PostgreSQL은 확장을 사용하여 데이터베이스의 기능을 확장하는 방법을 제공합니다. 확장은 명령을 사용하여 데이터베이스에서 로드하거나 제거할 수 있는 단일 패키지에서 여러 관련 SQL 개체를 함께 번들로 묶습니다. 데이터베이스에 로드한 확장은 기본 제공 기능처럼 작동합니다.

PostgreSQL 확장을 사용하는 방법

Azure Database for PostgreSQL - 유연한 서버에 확장을 설치하려면 먼저 이러한 확장을 사용하도록 허용 목록에 추가해야 합니다.

Azure Portal 사용:

1. Azure Database for PostgreSQL - 유연한 서버를 선택합니다.
2. 사이드바에서 서버 매개 변수를 선택합니다.
3. azure.extensions 매개 변수를 검색합니다.
4. 허용 목록을 만들 확장을 선택합니다.
   

Azure CLI 사용:

CLI 매개 변수 설정 명령을 통해 확장을 허용 목록에 추가할 수 있습니다.

az postgres flexible-server parameter set --resource-group <your resource group>  --server-name <your server name> --subscription <your subscription id> --name azure.extensions --value <extension name>,<extension name>

ARM 템플릿 사용: 아래 예제에서는 mypostgreserver 서버의 dblink, dict_xsyn, pg_buffercache 확장을 허용 목록으로 지정합니다.

{

    "$schema": https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#,

    "contentVersion": "1.0.0.0",

    "parameters": {

        "flexibleServers_name": {

            "defaultValue": "mypostgreserver",

            "type": "String"

        },

        "azure_extensions_set_value": {

            "defaultValue": " dblink,dict_xsyn,pg_buffercache",

            "type": "String"

        }

    },

    "variables": {},

    "resources": [

        {

            "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",

            "apiVersion": "2021-06-01",

            "name": "[concat(parameters('flexibleServers_name'), '/azure.extensions')]",

            "properties": {

                "value": "[parameters('azure_extensions_set_value')]",

                "source": "user-override"

            }

        }

    ]

}

Shared_Preload_Libraries는 PostgreSQL이 시작될 때 로드할 라이브러리를 결정하는 서버 구성 매개 변수입니다. 공유 메모리를 사용하는 모든 라이브러리는 이 매개 변수를 통해 로드해야 합니다. 공유 미리 로드 라이브러리에 확장을 추가해야 하는 경우 다음을 수행할 수 있습니다.

Azure Portal 사용:

1. Azure Database for PostgreSQL - 유연한 서버를 선택합니다.
2. 사이드바에서 서버 매개 변수를 선택합니다.
3. shared_preload_libraries 매개 변수를 검색합니다.
4. 추가하려는 확장을 선택합니다.
   

Azure CLI 사용:

CLI 매개 변수 설정 shared_preload_libraries명령을 통해 을 설정할 수 있습니다.

az postgres flexible-server parameter set --resource-group <your resource group>  --server-name <your server name> --subscription <your subscription id> --name shared_preload_libraries --value <extension name>,<extension name>

확장을 허용 목록으로 나열하고 로드한 후에는 데이터베이스에 설치해야 사용할 수 있습니다. 특정 확장을 설치하려면 CREATE EXTENSION 명령을 실행해야 합니다. 이 명령은 패키지 개체를 데이터베이스에 로드합니다.

Azure Database for PostgreSQL은 아래에 나열된 대로 주요 확장의 일부만 지원합니다. 이 정보를 SHOW azure.extensions;를 실행하여 얻을 수도 있습니다. 이 문서에 나열되지 않은 확장은 Azure Database for PostgreSQL - 유연한 서버에서 지원되지 않습니다. Azure Database for PostgreSQL에서 사용자 고유의 확장을 만들거나 로드할 수는 없습니다.

Postgres 14 확장

다음 확장은 Postgres 버전 14가 있는 Azure Database for PostgreSQL - 유연한 서버에서 사용할 수 있습니다.

Postgres 13 확장

다음 확장은 Postgres 버전 13이 있는 Azure Database for PostgreSQL - 유연한 서버에서 사용할 수 있습니다.

Postgres 12 확장

다음 확장은 Postgres 버전 12가 있는 Azure Database for PostgreSQL - 유연한 서버에서 사용할 수 있습니다.

Postgres 11 확장

다음 확장은 Postgres 버전 11이 있는 Azure Database for PostgreSQL - 유연한 서버에서 사용할 수 있습니다.

dblink 및 postgres_fdw

dblink 및 postgres_fdw를 사용하면 한 PostgreSQL 서버에서 다른 PostgreSQL 서버로 또는 동일한 서버의 다른 데이터베이스로 연결할 수 있습니다. 유연한 서버는 PostgreSQL 서버에 대한 수신/발신 연결을 모두 지원합니다. 발신 서버는 수신 서버에 대한 아웃바운드 연결을 허용해야 합니다. 마찬가지로, 수신 서버는 발신 서버의 연결을 허용해야 합니다.

이와 같은 두 가지 확장을 사용할 계획인 경우 VNet 통합을 이용하여 서버를 배포하는 것이 좋습니다. 기본적으로 VNet 통합은 VNET의 서버 간 연결을 허용합니다. 또한 VNet 네트워크 보안 그룹을 사용하여 액세스를 사용자 지정하도록 선택할 수 있습니다.

pg_prewarm

pg_prewarm 확장은 관계형 데이터를 캐시에 로드합니다. 캐시를 사전 준비하면 다시 시작한 후 쿼리를 처음 실행할 때 더 나은 응답 시간을 보입니다. 자동 사전 준비 기능은 현재 Azure Database for PostgreSQL - 유연한 서버에서 사용할 수 없습니다.

pg_cron

pg_cron은 데이터베이스 내부에서 확장으로 실행되는 간단한 PostgreSQL용 cron 기반 작업 스케줄러입니다. pg_cron 확장을 사용하여 PostgreSQL 데이터베이스 내에서 예약된 유지 관리 작업을 실행할 수 있습니다. 예를 들어, 테이블을 주기적으로 정리하거나 오래된 데이터 작업을 제거할 수 있습니다.

pg_cron은 여러 작업을 병렬로 실행할 수 있지만 한 번에 최대 하나의 작업 인스턴스를 실행합니다. 첫 번째 실행이 완료되기 전에 두 번째 실행이 시작되어야 하는 경우 첫 번째 실행이 완료되는 즉시 두 번째 실행이 대기되고 시작됩니다. 이렇게 하면 작업이 예약된 횟수만큼 정확하게 실행되고 동시에 실행되지 않습니다.

몇 가지 예는 다음과 같습니다.

토요일 오전 3시 30분(GMT)에 오래된 데이터를 삭제하려면

SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

기본 데이터베이스 'postgres'에서 매일 오전 10:00(GMT)에 진공을 실행하려면

SELECT cron.schedule('0 10 * * *', 'VACUUM');

pg_cron에서 모든 작업의 예약을 취소하려면

SELECT cron.unschedule(jobid) FROM cron.job;

현재 pg_cron 예약된 모든 작업을 보려면

SELECT * FROM cron.job;

azure_pg_admin 역할 계정의 데이터베이스 'testcron'에서 매일 오전 10:00(GMT)에 vaccuum을 실행하려면

SELECT cron.schedule_in_database('VACUUM','0 10 * * * ','VACUUM','testcron',null,TRUE)

참고

pg_cron 확장은 postgres 데이터베이스 내의 모든 Azure Database for PostgreSQL -유연한 서버에 대해 Shared_Preload_Libraries 미리 로드되어 보안을 손상시키지 않고 PostgreSQL DB 인스턴스 내의 다른 데이터베이스에서 실행되도록 작업을 예약할 수 있는 기능을 제공합니다. 그러나 보안상의 이유로 목록 pg_cron 확장을 허용 하고 CREATE EXTENSION 명령을 사용하여 설치해야 합니다.

pg_stat_statements

pg_stat_statements 확장은 모든 Azure Database for PostgreSQL 유연한 서버에 미리 로드되어 SQL 문의 실행 통계를 추적하는 수단을 제공합니다. 확장을 통해 어떤 명령문을 계산할지 제어하는 pg_stat_statements.track 설정의 기본값은 top이며 이것은 클라이언트가 직접 실행하는 모든 명령문이 추적된다는 의미입니다. 다른 두 가지 추적 수준은 none과 all입니다. 이 설정은 서버 매개 변수로 구성할 수 있습니다.

pg_stat_statements를 통해 제공되는 쿼리 실행 정보와 각 SQL 문을 기록할 때 서버 성능에 미치는 영향 사이에는 상충 관계가 있습니다. pg_stat_statements 확장을 적극적으로 사용하지 않는 경우에는 pg_stat_statements.track을 none으로 설정하는 것이 좋습니다. 일부 타사 모니터링 서비스가 pg_stat_statements에 기반하여 쿼리 성능 정보를 제공할 수 있으므로 여기에 해당하는 경우인지 여부를 확인하십시오.

TimescaleDB

TimescaleDB는 PostgreSQL용 확장으로 패키지된 시계열 데이터베이스입니다. TimescaleDB는 시계열 워크로드에 대한 시간 기반 분석 함수, 최적화 및 스케일링 Postgres를 제공합니다. Timescale, Inc.의 등록 상표인 TimescaleDB에 대한 자세한 정보를 알아봅니다. Azure Database for PostgreSQL은 TimescaleDB Apache-2 버전을 제공합니다.

TimescaleDB 설치

TimescaleDB를 설치하려면 이를 먼저 서버의 미리 로드된 공유 라이브러리에 포함시켜야 합니다. Postgres의 shared_preload_libraries 매개 변수를 변경하려면 서버를 다시 시작해야 합니다. 매개 변수는 Azure Portal 또는 Azure CLI를 사용해서 바꿀 수 있습니다.

다음과 같이 Azure Portal을 사용합니다.

1. Azure Database for PostgreSQL 서버를 선택합니다.

2. 사이드바에서 서버 매개 변수를 선택합니다.

3. shared_preload_libraries 매개 변수를 검색합니다.

4. TimescaleDB를 선택합니다.

5. 저장을 선택하여 변경 내용을 보존합니다. 변경 내용이 저장되면 알림을 받습니다.

6. 알림이 오면 서버를 다시 시작하여 변경 내용을 적용합니다.

이제 Postgres 데이터베이스에서 TimescaleDB를 사용할 수 있습니다. 데이터베이스에 연결하여 다음 명령을 실행합니다.

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

팁

오류가 표시되면 shared_preload_libraries를 저장한 후 서버를 다시 시작했는지 확인합니다.

이제 TimescaleDB 하이퍼테이블을 처음부터 새로 만들거나 PostgreSQL에서 기존 시계열 데이터를 마이그레이션할 수 있습니다.

pg_dump 및 pg_restore를 사용하여 Timescale 데이터베이스 복원

pg_dump 및 pg_restore를 사용하여 Timescale 데이터베이스를 복원하려면 대상 데이터베이스에서 timescaledb_pre_restore() 및 timescaledb_post restore()라는 두 개의 도우미 프로시저를 실행해야 합니다.

먼저 대상 데이터베이스를 준비합니다.

--create the new database where you'll perform the restore
CREATE DATABASE tutorial;
\c tutorial --connect to the database 
CREATE EXTENSION timescaledb;

SELECT timescaledb_pre_restore();

이제 원본 데이터베이스에서 pg_dump를 실행하고 그 다음 pg_restore를 실행합니다. 복원 후 복원된 데이터베이스에서 다음 명령을 반드시 실행하세요.

SELECT timescaledb_post_restore();

Timescale 지원 데이터베이스의 복원 방법에 대한 자세한 내용은 Timescale 설명서를 참조하세요.

timescaledb-backup을 사용하여 Timescale 데이터베이스 복원

위에 나열된 SELECT timescaledb_post_restore() 프로시저를 실행하는 동안 timescaledb.restoring 플래그를 업데이트하는 동안 권한이 거부될 수 있습니다. 이는 Cloud PaaS 데이터베이스 서비스에서 제한된 ALTER DATABASE 권한 때문입니다. 이 경우 timescaledb-backup 도구를 사용하여 Timescale 데이터베이스를 백업 및 복원하는 대체 방법을 수행할 수 있습니다. Timescaledb-backup은 TimescaleDB 데이터베이스를 더 간단하고 오류가 발생하기 쉬우며 성능이 향상되도록 덤프 및 복원하기 위한 프로그램입니다. 이렇게 하려면 다음을 수행해야 합니다.

1. 여기에 설명된 대로 도구 설치
2. 대상 Azure Database for PostgreSQL 서버 및 데이터베이스 만들기
3. 위와 같이 Timescale 확장 사용
4. ts-restore에서 사용할 사용자에게 azure_pg_admin 역할 부여
5. ts-restore를 실행하여 데이터베이스 복원

이러한 유틸리티에 대한 자세한 내용은 여기에서 확인할 수 있습니다.

참고

timescale-backup 유틸리티를 사용하여 Azure로 복원하는 경우 유연하지 않은 Azure Database for PostgresQL의 데이터베이스 사용자 이름은 <user@db-name> 형식을 사용해야 하므로 @를 %40 문자 인코딩으로 바꿔야 합니다.

다음 단계

사용하려는 확장이 표시되지 않으면 알려주세요. 피드백 포럼에서 기존 요청에 투표하거나 새 피드백 요청을 작성하세요.