Software Developer/AWS

Aurora PostgreSQL "Read replica write forwarding"으로 인한 RDS Proxy의 Read-only Endpoint 접속불가 이슈 2025.08.12
AWS Aurora PostgreSQL에서 aws_s3 확장으로 query_export_to_s3를 통한 쿼리결과 S3에 Export 2025.06.11

Aurora PostgreSQL "Read replica write forwarding"으로 인한 RDS Proxy의 Read-only Endpoint 접속불가 이슈

deepfree 2025. 8. 12. 16:08

2025. 8. 12. 16:08

RDS Proxy Read-only endpoint를 통해 Aurora PostgreSQL 접속시 Connection attempt timed out 에러 발생

환경

Aurora Postgre SQL 17.4
pgJDBC Driver 42.7.7

에러내용

Try Connect... jdbc:postgresql://{RDS Proxy ReadOnly Endpoint}:5432/{데이터베이스}?sslmode=disable&loginTimeout=3&connectTimeout=3&socketTimeout=3
Connection failed.
onnection failed.
org.postgresql.util.PSQLException: Connection attempt timed out.
        at org.postgresql.Driver$ConnectThread.getResult(Driver.java:412)
        at org.postgresql.Driver.connect(Driver.java:305)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:229)
        at Main.testConnectAndQuery(Main.java:113)
        at Main.main(Main.java:103)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:569)
        at jdk.compiler/com.sun.tools.javac.launcher.Main.execute(Main.java:419)
        at jdk.compiler/com.sun.tools.javac.launcher.Main.run(Main.java:192)
        at jdk.compiler/com.sun.tools.javac.launcher.Main.main(Main.java:132)

에러분석

관련하여 검색해보면 SSL 연결관련 이슈가 많은데, SSL 이슈와 분리하기 위해 Aurora PostgreSQL의 cluster parameter group에서 rds.force_ssl를 0으로 설정하고, Client에서 접속시 sslMode를 disable로 접속을 시도하여 SSL을 사용하지 않고 연결하게 테스트
자세한 pgJDBC Driver 로그를 보기위해 로깅설정 [https://jdbc.postgresql.org/documentation/logging/]

2025-08-12 06:51:28 FINE org.postgresql.Driver loadDefaultProperties Loading driver configuration via classloader jdk.internal.loader.ClassLoaders$AppClassLoader@1dbd16a6
2025-08-12 06:51:28 FINE org.postgresql.Driver connect Connecting with URL: jdbc:postgresql://{RDS Proxy ReadOnly Endpoint}:5432/{데이터베이스}?sslmode=disable&loginTimeout=3&connectTimeout=3&socketTimeout=3
2025-08-12 06:51:28 FINE org.postgresql.jdbc.PgConnection <init> PostgreSQL JDBC Driver 42.7.7
2025-08-12 06:51:28 FINE org.postgresql.jdbc.PgConnection setDefaultFetchSize   setDefaultFetchSize = 0
2025-08-12 06:51:28 FINE org.postgresql.jdbc.PgConnection setPrepareThreshold   setPrepareThreshold = 5
2025-08-12 06:51:28 FINE org.postgresql.core.v3.ConnectionFactoryImpl openConnectionImpl Trying to establish a protocol version 3 connection to {RDS Proxy ReadOnly Endpoint}:5432
2025-08-12 06:51:28 FINEST org.postgresql.core.Encoding <init> Creating new Encoding UTF-8 with fastASCIINumbers true
2025-08-12 06:51:28 FINEST org.postgresql.core.Encoding <init> Creating new Encoding UTF-8 with fastASCIINumbers true
2025-08-12 06:51:28 FINE org.postgresql.core.v3.ConnectionFactoryImpl tryConnect Receive Buffer Size is 117,372
2025-08-12 06:51:28 FINE org.postgresql.core.v3.ConnectionFactoryImpl tryConnect Send Buffer Size is 166,400
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.ConnectionFactoryImpl sendStartupPacket  FE=> StartupPacket(user=dbuser, database=sampledb, client_encoding=UTF8, DateStyle=ISO, TimeZone=UTC)
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.ScramAuthenticator initializeScramClient channelBinding( PREFER )
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.ScramAuthenticator advertisedMechanisms  <=BE AuthenticationSASL( [SCRAM-SHA-256] )
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.ScramAuthenticator initializeScramClient  Using SCRAM mechanism: SCRAM-SHA-256
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.ScramAuthenticator handleAuthenticationSASL  FE=> SASLInitialResponse( n,,n=*,r=Kjx]I+7-~O+e$c#fd+k_7&Pp )
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.ScramAuthenticator handleAuthenticationSASLContinue  <=BE AuthenticationSASLContinue( r=Kjx]I+7-~O+e$c#fd+k_7&PpfK#ZQ$FT"V|E2\'5`7,s=MDeU4sdPwhbNzxL/bi+Rwg==,i=4096 )
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.ScramAuthenticator handleAuthenticationSASLContinue  FE=> SASLResponse( c=biws,r=Kjx]I+7-~O+e$c#fd+k_7&PpfK#ZQ$FT"V|E2\'5`7,p=/vm8cKTkC5j/t2y80EdqNAf8Ty+WiEmCffKWq1VlQxE= )
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.ScramAuthenticator handleAuthenticationSASLFinal  <=BE AuthenticationSASLFinal( v=VMMqy0zv0wWQw2J2JrkpLH32oqvUBZ1Q+6pCAIMIqbE= )
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.ConnectionFactoryImpl doAuthentication  <=BE AuthenticationOk
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveParameterStatus  <=BE ParameterStatus(application_name = )
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveParameterStatus  <=BE ParameterStatus(client_encoding = UTF8)
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveParameterStatus  <=BE ParameterStatus(DateStyle = ISO)
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveParameterStatus  <=BE ParameterStatus(integer_datetimes = on)
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveParameterStatus  <=BE ParameterStatus(IntervalStyle = postgres)
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveParameterStatus  <=BE ParameterStatus(is_superuser = off)
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveParameterStatus  <=BE ParameterStatus(server_version = 17.4)
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveParameterStatus  <=BE ParameterStatus(standard_conforming_strings = on)
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveParameterStatus  <=BE ParameterStatus(TimeZone = UTC)
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveParameterStatus  <=BE ParameterStatus(server_encoding = UTF8)
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveParameterStatus  <=BE ParameterStatus(session_authorization = dbuser)
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveRFQ  <=BE ReadyForQuery(I)
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl execute   simple execute, handler=org.postgresql.core.SetupQueryRunner$SimpleResultHandler@3522a048, maxRows=0, fetchSize=0, flags=1,047
2025-08-12 06:51:28 FINEST org.postgresql.core.v3.QueryExecutorImpl sendSimpleQuery  FE=> SimpleQuery(query="SET application_name = 'PostgreSQL JDBC Driver'")
2025-08-12 06:51:31 FINE org.postgresql.Driver connect Connection error: 
org.postgresql.util.PSQLException: Connection attempt timed out.
    at org.postgresql.Driver$ConnectThread.getResult(Driver.java:412)
    at org.postgresql.Driver.connect(Driver.java:305)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:229)
    at Main.testConnectAndQuery(Main.java:113)
    at Main.main(Main.java:103)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:569)
    at jdk.compiler/com.sun.tools.javac.launcher.Main.execute(Main.java:419)
    at jdk.compiler/com.sun.tools.javac.launcher.Main.run(Main.java:192)
    at jdk.compiler/com.sun.tools.javac.launcher.Main.main(Main.java:132)

위 로그를 살펴보면 SET application_name = 'PostgreSQL JDBC Driver' 을 실행하려고 하는데 Connection 획득에 실패하고 있음
SET 명령을 통한 RDS Proxy Session Pinning 이슈를 피하기 위해 접속시 assumeMinServerVersion=15.0&ApplicationName=any/your-application-name를 우선 설정해 봄
참고: https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/rds-proxy-connecting.html#rds-proxy-connecting-postgresql
위와 같이 설정하면 SET 명령없이 실행되지만, "SELECT 1" 전송시도 후 FE(프론트엔드) Sync로 대기하지만 BE(벡엔드)의 응답이 없고 여전히 접속에 실패함 (Read time out 발생)

Try Connect... jdbc:postgresql://{RDS Proxy ReadOnly Endpoint}:5432/{데이터베이스}?sslmode=disable&loginTimeout=3&connectTimeout=3&socketTimeout=3&assumeMinServerVersion=15.0&ApplicationName=any/your-application-name
Connected to database!
Connection failed.
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:399)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:518)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:435)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:357)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:342)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:318)
        at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:267)
        at Main.testConnectAndQuery(Main.java:120)
        at Main.main(Main.java:103)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:569)
        at jdk.compiler/com.sun.tools.javac.launcher.Main.execute(Main.java:419)
        at jdk.compiler/com.sun.tools.javac.launcher.Main.run(Main.java:192)
        at jdk.compiler/com.sun.tools.javac.launcher.Main.main(Main.java:132)
Caused by: java.net.SocketTimeoutException: Read timed out
        at java.base/sun.nio.ch.NioSocketImpl.timedRead(NioSocketImpl.java:288)
        at java.base/sun.nio.ch.NioSocketImpl.implRead(NioSocketImpl.java:314)
        at java.base/sun.nio.ch.NioSocketImpl.read(NioSocketImpl.java:355)
        at java.base/sun.nio.ch.NioSocketImpl$1.read(NioSocketImpl.java:808)
        at java.base/java.net.Socket$SocketInputStream.read(Socket.java:966)
        at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:192)
        at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:159)
        at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:144)
        at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:76)
        at org.postgresql.core.PGStream.receiveChar(PGStream.java:477)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2175)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
        ... 15 more

2025-08-12 06:56:02 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveRFQ  <=BE ReadyForQuery(I)
2025-08-12 06:56:02 FINEST org.postgresql.jdbc.PgConnection <init>     types using binary send = OID_ARRAY,UUID,BYTEA,INT8,INT2,INT4,POINT,TIMESTAMP,BOX,TIMESTAMPTZ,NUMERIC,BYTEA_ARRAY,INT2_ARRAY,INT4_ARRAY,TEXT_ARRAY,TIMETZ,VARCHAR_ARRAY,INT8_ARRAY,TIME,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY
2025-08-12 06:56:02 FINEST org.postgresql.jdbc.PgConnection <init>     types using binary receive = OID_ARRAY,UUID,BYTEA,INT8,INT2,INT4,POINT,TIMESTAMP,BOX,TIMESTAMPTZ,NUMERIC,BYTEA_ARRAY,INT2_ARRAY,INT4_ARRAY,TEXT_ARRAY,TIMETZ,VARCHAR_ARRAY,INT8_ARRAY,DATE,TIME,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY
2025-08-12 06:56:02 FINEST org.postgresql.jdbc.PgConnection <init>     integer date/time = true
2025-08-12 06:56:02 FINEST org.postgresql.core.v3.QueryExecutorImpl execute   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@4c9f8c13, maxRows=0, fetchSize=0, flags=17
2025-08-12 06:56:02 FINEST org.postgresql.core.v3.QueryExecutorImpl sendParse  FE=> Parse(stmt=null,query="SELECT 1",oids={})
2025-08-12 06:56:02 FINEST org.postgresql.core.v3.QueryExecutorImpl sendBind  FE=> Bind(stmt=null,portal=null)
2025-08-12 06:56:02 FINEST org.postgresql.core.v3.QueryExecutorImpl sendDescribePortal  FE=> Describe(portal=null)
2025-08-12 06:56:02 FINEST org.postgresql.core.v3.QueryExecutorImpl sendExecute  FE=> Execute(portal=null,limit=0)
2025-08-12 06:56:02 FINEST org.postgresql.core.v3.QueryExecutorImpl sendSync  FE=> Sync
2025-08-12 06:56:05 FINEST org.postgresql.core.QueryExecutorCloseAction abort  FE=> close socket
2025-08-12 06:56:05 FINEST org.postgresql.core.QueryExecutorCloseAction close  FE=> Terminate

Reader Reboot 하면서 RDS Proxy의 Cloud Watch Log 살펴보기

Aurora Reader Instance를 재기동 해보면 read/write 인스턴스로 파악되어 Read Only Endpoint용이 아닌 default Endpoint의 인스턴스로 추가됨

Message
2025-08-12T06:45:20.490Z [INFO] [dbConnection=2627205290] The database connection closed. Reason: The TCP channel was closed by either the database server or the proxy.
2025-08-12T06:45:20.546Z [WARN] Database "service-dev-2" at 172.31.27.207:5432 became unavailable for access from 172.31.21.113. Check the status of the database if this condition persists.
2025-08-12T06:45:28.880Z [INFO] Database "service-dev-2" at 172.31.27.207:5432 is now available for read/write access from 172.31.2.83.
2025-08-12T06:45:28.880Z [INFO] Database "service-dev-2" at 172.31.27.207:5432 is now available for read/write access from 172.31.21.113.

Aurora PostgreSQL Reader가 RDS Proxy에서 Writer로 인식되는 이슈

Aurora PostgreSQL Cluster에 Failover후에 Java Application JDBC Pool의 Connection이 Reader/Writer 역할이 바뀐후에도 처리 가능성을 높이기 위해 Write Forwarding 기능을 켜놓았는데 해당 기능을 켜면 RDS Proxy에서 Writer로 인식되는 것으로 보임

-- Writer Instance Test Query: Pool에서 Writer인지를 테스트하기 위한 쿼리
SELECT 1 WHERE pg_is_in_recovery() = false;

-- RDS Proxy가 Writer 인스턴스를 식별하는 방법으로 추론됨 => Write Forwarding이 켜져 있으면 Reader에서도 false로 인식됨
SHOW transaction_read_only;

Write Forwarding 기능을 끄고 Aurora Reader 재기동해보면 아래와 같이 read-only 인스턴스로 인식됨

2025-08-12T07:04:24.210Z [INFO] Database "service-dev-2" at 172.31.27.207:5432 is now available for read-only access from 172.31.21.113.
2025-08-12T07:04:24.535Z [INFO] Database "service-dev-2" at 172.31.27.207:5432 is now available for read-only access from 172.31.2.83.

Application도 정상적으로 RDS Proxy ReadOnly Endpoint에 쿼리 실행됨

2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveRFQ  <=BE ReadyForQuery(I)
2025-08-12 07:06:28 FINEST org.postgresql.jdbc.PgConnection <init>     types using binary send = OID_ARRAY,UUID,BYTEA,INT8,INT2,INT4,POINT,TIMESTAMP,BOX,TIMESTAMPTZ,NUMERIC,BYTEA_ARRAY,INT2_ARRAY,INT4_ARRAY,TEXT_ARRAY,TIMETZ,VARCHAR_ARRAY,INT8_ARRAY,TIME,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY
2025-08-12 07:06:28 FINEST org.postgresql.jdbc.PgConnection <init>     types using binary receive = OID_ARRAY,UUID,BYTEA,INT8,INT2,INT4,POINT,TIMESTAMP,BOX,TIMESTAMPTZ,NUMERIC,BYTEA_ARRAY,INT2_ARRAY,INT4_ARRAY,TEXT_ARRAY,TIMETZ,VARCHAR_ARRAY,INT8_ARRAY,DATE,TIME,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY
2025-08-12 07:06:28 FINEST org.postgresql.jdbc.PgConnection <init>     integer date/time = true
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl execute   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@4c9f8c13, maxRows=0, fetchSize=0, flags=17
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl sendParse  FE=> Parse(stmt=null,query="SELECT 1",oids={})
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl sendBind  FE=> Bind(stmt=null,portal=null)
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl sendDescribePortal  FE=> Describe(portal=null)
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl sendExecute  FE=> Execute(portal=null,limit=0)
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl sendSync  FE=> Sync
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl processResults  <=BE ParseComplete [null]
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl processResults  <=BE BindComplete [unnamed]
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveFields  <=BE RowDescription(1)
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveFields         Field(?column?,INT4,4,T)
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl processResults  <=BE DataRow(len=1)
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveCommandStatus  <=BE CommandStatus(SELECT 1)
2025-08-12 07:06:28 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveRFQ  <=BE ReadyForQuery(I)
2025-08-12 07:06:28 FINEST org.postgresql.jdbc.PgResultSet getString   getString columnIndex: 1
2025-08-12 07:06:28 FINEST org.postgresql.core.QueryExecutorCloseAction close  FE=> Terminate

Connected to database!
SELECT 1 => 1

위와 같이 Application Log, pgJDBC Log, RDS Proxy Cloudwatch 로그를 보면서 찬찬히 살펴보면서 Root Cause를 진단함
실제로 Aurora PostgreSQL의 SQL을 모니터링 해보면 RDS Proxy는 아래와 같은 쿼리로 Write/Read인지 ReadOnly인지 판별함


select
    CONCAT(current_setting('transaction_read_only'), '###', pg_is_in_recovery()) as transaction_read_only,
    current_setting('max_connections') as max_connections,
    extract(EPOCH from now() - pg_postmaster_start_time()) as uptime,
    durable_lsn,
    current_read_lsn
from
    aurora_replica_status()
where
    server_id = '{Aurora Node 서버 ID}'
order by
    (session_id = 'MASTER_SESSION_ID') asc
limit 1;

추가참고: SET ApplicationName='...' 때문에 발생하는 RDS Proxy의 Session Pinning 방지

JDBC Driver가 PostgreSQL 17에 Connection 접속시 SET ApplicationName='PostgreSQL JDBC Driver'을 실행하게 되면서 RDS Proxy에서 Session Pinning 발생
JDBC Driver 설정에 ApplicationName을 빈값 또는 다른 값을 설정하면 해당값으로 SET 명령이 전송되어 마찬가지로 Session Pinning 발생
JDBC Driver에 서버버전이 최소 9.0이상이라고 명시하면 SET ApplicationName 명령을 전송하지 않게됨
dataSource.url에 "assumeMinServerVersion=15.0" 파라미터를 추가함
RDS Proxy의 Target의 Initialization query를 "SET application_name='app-rds-proxy'"등과 같이 설정하는는 방식이 안전함. SET은 Application에서 실행하지 않고, RDS Proxy가 커넥션 생성시 SET하고, Application에서는 SET을 피함으로서 Session Pinnig을 방지하는게 좋음

-- Aurora PostgreSQL에서 세션연결 확인
SELECT pid, usename, datname, client_addr, application_name, state, query_start, query
FROM pg_stat_activity;

저작자표시 비영리 동일조건 (새창열림)

'Software Developer > AWS' 카테고리의 다른 글

AWS Aurora PostgreSQL에서 aws_s3 확장으로 query_export_to_s3를 통한 쿼리결과 S3에 Export (0)	2025.06.11

AWS Aurora PostgreSQL에서 aws_s3 확장으로 query_export_to_s3를 통한 쿼리결과 S3에 Export

deepfree 2025. 6. 11. 18:05

2025. 6. 11. 18:05

Aurora PostgreSQL에 aws_s3 확장으로 S3에 데이터 Export하는 방법을 설명합니다

Postgres에서 COPY TO 스트림을 받아서 프로그래밍에서 S3에 업로드하는 방식이 아닌

AWS PostgreSQL 확장을 이용하여 DB에 Select문을 쿼리하면 DB가 S3에 쿼리결과를 Export하는 방식을 이용합니다

이렇게 처리하면 어플리케이션에서 쿼리결과를 스트림등을 다룰 필요가 없어서, 어플리케이션에 이용하는 PostgreSQL 접속 드라이버가 스트림을 처리할 필요가 없어, Select 문을 처리할 수 있는 정도의 PostgreSQL 접속 드라이버만으로도 S3에 데이터의 Export처리가 가능합니다

1. AWS IAM에서 S3에 접근하기 위한 "rds-s3-export-policy" 정책을 정의합니다

//rds-s3-export-policy 정책
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "s3export",
            "Action": [
                "s3:PutObject*",
                "s3:ListBucket",
                "s3:GetObject*",
                "s3:DeleteObject*",
                "s3:GetBucketLocation",
                "s3:AbortMultipartUpload"
            ],
            "Effect": "Allow",
            "Resource": [
                "arn:aws:s3:::*/*" // 모든 S3 버킷에 대한 권한 부여
                // "arn:aws:s3:::버킷명/*" // 특정 버킷에 대한 권한 부여
            ]
        }
    ]
}

2. AWS IAM에서 Aurora PostgreSQL에 부여할 "rds-s3-export-role" 역할을 정의합니다

// rds-s3-export-role 역할
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "rds.amazonaws.com"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "aws:SourceAccount": "nnnnnnnnnnnn", // AWS 계정 ID
                    "aws:SourceArn": "arn:aws:rds:ap-northeast-2:nnnnnnnnnnnn:cluster:test-db-cluster" // RDS 클러스터 ARN
                }
            }
        }
    ]
}

위의 rds-s3-export-role 역할에 rds-s3-export-policy 정책을 연결합니다

3. Aurora PostgreSQL 클러스터에 IAM 역할 연결

Aurora PostgreSQL 클러스터를 선택하고 "Manage IAM roles"에서 "rds-s3-export-role" 역할을 클러스터에 연결합니다

4. PostgreSQL 마스터계정에서 aws_s3 확장설치 및 권한설정

aws_s3 확장을 PostgreSQL에 설치하고, DB에 접속하여 export처리 할 접속계정에 해당 확장의 함수실행 권한을 부여합니다

-- 확장조회
SELECT * FROM pg_extension;

-- aws_s3 확장 설치
CREATE EXTENSION aws_s3 CASCADE;

-- query_export_to_s3 함수 조회
SELECT * FROM pg_proc WHERE proname LIKE '%query_export_to_s3%';

-- 사용자에게 확장 사용 권한 부여
GRANT USAGE ON SCHEMA aws_s3 TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA aws_s3 TO app_user;
GRANT USAGE ON SCHEMA aws_commons TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA aws_commons TO app_user;

5. S3에 데이터 Export하도록 쿼리

아래와 같이 해당함수를 이용하여 쿼리결과를 s3에 Export 처리합니다

-- S3에 데이터 Export
SELECT * 
FROM aws_s3.query_export_to_s3(
	query => 'SELECT * from 테이블',
	s3_info => aws_commons.create_s3_uri(
	   '버킷명',
	   '경로',
	   '리전명'
	),
	options => 'format csv, HEADER'
	-- ,kms_key =>
);

참고:

aws_s3.query_export_to_s3 함수를 사용하여 쿼리 데이터 내보내기

https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.InstallExtension.html

aws_s3.query_export_to_s3 함수를 사용하여 쿼리 데이터 내보내기 - Amazon Relational Database Service

이 페이지에 작업이 필요하다는 점을 알려 주셔서 감사합니다. 실망시켜 드려 죄송합니다. 잠깐 시간을 내어 설명서를 향상시킬 수 있는 방법에 대해 말씀해 주십시오.

docs.aws.amazon.com

함수 참조 > aws_s3.query_export_to_s3

https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/UserGuide/postgresql-s3-export-functions.html#aws_s3.export_query_to_s3

함수 참조 - Amazon Relational Database Service

docs.aws.amazon.com

저작자표시 비영리 동일조건 (새창열림)

'Software Developer > AWS' 카테고리의 다른 글

Aurora PostgreSQL "Read replica write forwarding"으로 인한 RDS Proxy의 Read-only Endpoint 접속불가 이슈 (0)	2025.08.12

PREV 이전 1 NEXT 다음

deepfree's archive

Software Developer/AWS

Aurora PostgreSQL "Read replica write forwarding"으로 인한 RDS Proxy의 Read-only Endpoint 접속불가 이슈

RDS Proxy Read-only endpoint를 통해 Aurora PostgreSQL 접속시 Connection attempt timed out 에러 발생

환경

에러내용

에러분석

Reader Reboot 하면서 RDS Proxy의 Cloud Watch Log 살펴보기

Aurora PostgreSQL Reader가 RDS Proxy에서 Writer로 인식되는 이슈

추가참고: SET ApplicationName='...' 때문에 발생하는 RDS Proxy의 Session Pinning 방지

'Software Developer > AWS' 카테고리의 다른 글

AWS Aurora PostgreSQL에서 aws_s3 확장으로 query_export_to_s3를 통한 쿼리결과 S3에 Export

'Software Developer > AWS' 카테고리의 다른 글

+ Recent posts

티스토리툴바