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;

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

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

[증상]

JDK24에서 mvn 실행시 아래와 같은 경고가 발생하고 프로젝트 빌드에 실패함

$ mvn clean spring-boot:run -Dspring-boot.run.profiles=local
WARNING: A terminally deprecated method in sun.misc.Unsafe has been called
WARNING: sun.misc.Unsafe::objectFieldOffset has been called by com.google.common.util.concurrent.AbstractFuture$UnsafeAtomicHelper (file:/opt/homebrew/Cellar/maven/3.9.9/libexec/lib/guava-33.2.1-jre.jar)
WARNING: Please consider reporting this to the maintainers of class com.google.common.util.concurrent.AbstractFuture$UnsafeAtomicHelper
WARNING: sun.misc.Unsafe::objectFieldOffset will be removed in a future release
[INFO] Scanning for projects...
[INFO] 
[INFO] ---------------< com.demo:sample >----------------
[INFO] Building sample 3.4.3-SNAPSHOT
[INFO]   from pom.xml
[INFO] --------------------------------[ jar ]---------------------------------
[WARNING] Could not transfer metadata com.soundicly:jnanoid-enhanced:main-SNAPSHOT/maven-metadata.xml from/to maven-default-http-blocker (http://0.0.0.0/): Blocked mirror for repositories: [kpc-private (http://nexus.kpcard.co.kr/repository/maven-kpc-private-repositories/, default, releases+snapshots), kpc-public (http://nexus.kpcard.co.kr/repository/maven-public/, default, releases+snapshots)]
[WARNING] com.soundicly:jnanoid-enhanced:main-SNAPSHOT/maven-metadata.xml failed to transfer from http://0.0.0.0/ during a previous attempt. This failure was cached in the local repository and resolution will not be reattempted until the update interval of maven-default-http-blocker has elapsed or updates are forced. Original error: Could not transfer metadata com.soundicly:jnanoid-enhanced:main-SNAPSHOT/maven-metadata.xml from/to maven-default-http-blocker (http://0.0.0.0/): Blocked mirror for repositories: [kpc-private (http://nexus.kpcard.co.kr/repository/maven-kpc-private-repositories/, default, releases+snapshots), kpc-public (http://nexus.kpcard.co.kr/repository/maven-public/, default, releases+snapshots)]
[INFO] 
[INFO] --- clean:3.4.1:clean (default-clean) @ sample ---
[INFO] Deleting /Users/deepfree/development/workspace-java/sample/target
[INFO] 
[INFO] >>> spring-boot:3.4.3:run (default-cli) > test-compile @ sample >>>
[INFO] 
[INFO] --- spring-boot:3.4.3:build-info (build-info) @ sample ---
[INFO] 
[INFO] --- resources:3.3.1:resources (default-resources) @ sample ---
[INFO] Copying 19 resources from src/main/resources to target/classes
[INFO] Copying 71 resources from src/main/resources to target/classes
[INFO] 
[INFO] --- compiler:3.13.0:compile (default-compile) @ sample ---
[INFO] Recompiling the module because of changed source code.
[INFO] Compiling 101 source files with javac [debug parameters release 21] to target/classes
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  1.145 s
[INFO] Finished at: 2025-04-02T17:57:08+09:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:3.13.0:compile (default-compile) on project sample: Fatal error compiling: java.lang.ExceptionInInitializerError: com.sun.tools.javac.code.TypeTag :: UNKNOWN -> [Help 1]
[ERROR] 
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR] 
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

[원인]

경고내용은 maven 3.9.9가 guava 33.2.1-jre.jar를 사용하는데 해당 버전의 guava가 terminally deprecated된 sun.misc.Unsafe를 호출한다는 내용입니다

guava에는 이미 이슈 "Migrate off Unsafe (guava-jre)"(https://github.com/google/guava/issues/6806)가 보고되어 guava-jre에처 처리되었는데 maven 3.9.9가 오래된 guava를 사용해서 발생하는 문제로 보입니다

해당 maven의 이슈도 Apache에 보고되어 https://issues.apache.org/jira/browse/MNG-8399?page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel&focusedCommentId=17939366 에서 찾을 수 있고, 4일전에 아래와 같은 댓글이 달려있고 maven에서 사용하는 guava 종속성을 업드레이드 할것으로 보입니다

[해결]

현재는 maven 하위의 file:/opt/homebrew/Cellar/maven/3.9.9/libexec/lib/guava-33.2.1-jre.jar 에 있는 guava library를 최신 버전으로 교체해볼수 있겠지만, maven 도구가 JDK 24에서 문제가 없게 라이브러리 종속성을 업데이트하면 maven을 업그레이드하면 될것으로 판단됩니다

추후 mavan 3.3.9에서 3.3.10이 릴리즈되면 확인해보고 이 게시물을 수정해 놓아야 겠습니다

[증상]

maven으로 잘 빌드되던 프로젝트에서 JDK 24를 설치후 빌드시 아래와 같은 경고가 발생하고 빌드에 실패함

$ mvn clean install -U -Dmaven.test.skip=true
WARNING: A restricted method in java.lang.System has been called
WARNING: java.lang.System::load has been called by org.fusesource.jansi.internal.JansiLoader in an unnamed module (file:/opt/homebrew/Cellar/maven/3.9.9/libexec/lib/jansi-2.4.1.jar)
WARNING: Use --enable-native-access=ALL-UNNAMED to avoid a warning for callers in this module
WARNING: Restricted methods will be blocked in a future release unless native access is enabled

WARNING: A terminally deprecated method in sun.misc.Unsafe has been called
WARNING: sun.misc.Unsafe::objectFieldOffset has been called by com.google.common.util.concurrent.AbstractFuture$UnsafeAtomicHelper (file:/opt/homebrew/Cellar/maven/3.9.9/libexec/lib/guava-33.2.1-jre.jar)
WARNING: Please consider reporting this to the maintainers of class com.google.common.util.concurrent.AbstractFuture$UnsafeAtomicHelper
WARNING: sun.misc.Unsafe::objectFieldOffset will be removed in a future release
[INFO] Scanning for projects...
[INFO] 
[INFO] --------------< com.demo:sample >--------------
[INFO] Building sample 3.4.3-SNAPSHOT
[INFO]   from pom.xml
[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] --- clean:3.4.1:clean (default-clean) @ sample ---
[INFO] Deleting /Users/deepfree/development/workspace-java/sample/target
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  4.171 s
[INFO] Finished at: 2025-04-02T17:40:52+09:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.apache.maven.plugins:maven-clean-plugin:3.4.1:clean (default-clean) on project sample: Failed to clean project: Failed to delete /Users/deepfree/development/workspace-java/sample/target/generated-sources/annotations -> [Help 1]
[ERROR] 
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR] 
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

뿐만 아니라 mvn의 버전만 확인해도 동일한 경고 발생

// mvn 도구에서 제한된 메소드 java.lang.System이 호출되었다는 경고
$ mvn --version                                                                                          1 ↵  10037  17:35:22
WARNING: A restricted method in java.lang.System has been called
WARNING: java.lang.System::load has been called by org.fusesource.jansi.internal.JansiLoader in an unnamed module (file:/opt/homebrew/Cellar/maven/3.9.9/libexec/lib/jansi-2.4.1.jar)
WARNING: Use --enable-native-access=ALL-UNNAMED to avoid a warning for callers in this module
WARNING: Restricted methods will be blocked in a future release unless native access is enabled

Apache Maven 3.9.9 (8e8579a9e76f7d015ee5ec7bfcdc97d260186937)
Maven home: /opt/homebrew/Cellar/maven/3.9.9/libexec
Java version: 24, vendor: Eclipse Adoptium, runtime: /Library/Java/JavaVirtualMachines/temurin-24.jdk/Contents/Home
Default locale: ko_KR, platform encoding: UTF-8
OS name: "mac os x", version: "15.3.2", arch: "aarch64", family: "mac"

// root 권한으로 실행시 정상
$ sudo mvn --version
Apache Maven 3.9.9 (8e8579a9e76f7d015ee5ec7bfcdc97d260186937)
Maven home: /opt/homebrew/Cellar/maven/3.9.9/libexec
Java version: 23.0.2, vendor: Homebrew, runtime: /opt/homebrew/Cellar/openjdk/23.0.2/libexec/openjdk.jdk/Contents/Home
Default locale: ko_KR, platform encoding: UTF-8
OS name: "mac os x", version: "15.3.2", arch: "aarch64", family: "mac"

[원인]

maven 도구의 jansi가 console에서 Ansi를 사용하기 위해 시스템에 접근해서 해당 이슈가 발생하는 것으로 판단됨

maven/3.9.9/libexec/lib/jansi-2.4.1.jar

[해결방안]

maven 3에서:

# MAVEN_OPTS 지정
export MAVEN_OPTS="--enable-native-access=ALL-UNNAMED"

zsh에 기본으로 적용하려면 ~/.zshrc 에 위의 설정을 적용하면 기본 쉘환경에 적용됨

현재 열려있는 쉘에 적용하려면 source ~/.zshrc 를 실행하여 적용

maven 4에서:

해당 이슈가 Apache Maven 프로젝트에 보고되어 https://issues.apache.org/jira/browse/MNG-8399?page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel&focusedCommentId=17939366 에서 찾을 수 있고, jansi는 maven 4에서 더이상 이슈가 되지 않는다는 4일전에 작성된 댓글에서 확인이 가능합니다

maven 4에서는 "--enable-native-access=ALL-UNNAMED" 옵션 지정이 필요없을 것으로 보입니다

하지만 아직 brew에는 maven 3.9.9까지만 배포되고 있습니다

[증상]

Bastion의 Shell에서 curl로 WAS에 요청시 "No route to host" 에러 발생

[bastion]$ curl -X GET http://WAS-IP:8080
curl: (7) Failed to connect to WAS-IP port 8080: No route to host

[해결방안]

WAS에서 filewall-cmd로 방화벽 허용포트 추가

# 영구적으로 TCP 8080을 개방
[WAS]$ sudo firewall-cmd --add-port=8080/tcp --permanent

# 현재 방화벽에 열려있는 포트 나열
[WAS]$ sudo firewall-cmd --list-ports
8080/tcp

Linux Shell에서 JDK를 통해서 ojdbc11.jar등을 로드하여 직접 Connection을 생성하고 간단한 executeQuery를 실행해보는 방법입니다

# 실행하는 방법 (classpath에 ojdbc11.jar를 포함하여 Main.java 실행)
$ java -cp ojdbc11.jar Main.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * Oracle JDBC 드라이버 로드 및 DB 접속 테스트
 * <p>java -cp ojdbc11.jar Main.java</p>
 */
public class Main {

    public static void main(String[] args) {
        //JDBC 드라이버 로드 테스트
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("oracle.jdbc.driver.OracleDriver Found!");
        } catch (ClassNotFoundException e) {
            System.out.println("Oracle JDBC Driver not found.");
            e.printStackTrace();
            //return;
        }
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            System.out.println("oracle.jdbc.OracleDriver Found!");
        } catch (ClassNotFoundException e) {
            System.out.println("Oracle JDBC Driver not found.");
            e.printStackTrace();
            //return;
        }

        //접속테스트할 DB URL들
        // jdbc:oracle:thin:@(DESCRIPTION=(RETRY_COUNT=20)(RETRY_DELAY=3)(ADDRESS=(PROTOCOL=tcp)(HOST=X.X.X.X)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XXXXXX))(SECURITY=(SSL_SERVER_DN_MATCH=yes)))
        // jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=yes)(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=X.X.X.X)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=X.X.X.X)(PORT=1521)))(CONNECT_DATA=(FAILOVER_MODE=(TYPE=select)(METHOD=basic))(SERVER=dedicated)(SERVICE_NAME=XXXXXX)))
        List<String> urls = List.of(
                "jdbc:oracle:thin:@(...)",
                "jdbc:oracle:thin:@(...)");
        for (String url : urls) {
            testConnectAndQuery(url);
        }
    }

    /**
     * 주어진 URL을 사용하여 Oracle 데이터베이스에 연결하고 간단한 쿼리를 실행합니다.
     * @param url 데이터베이스에 연결하기 위한 JDBC URL
     */
    private static void testConnectAndQuery(String url) {
        System.out.println("Try Connect... " + url);
        try (Connection connection = DriverManager.getConnection(
                url,
                "USERNAME",
                "PASSWORD")) {
            System.out.println("Connected to Oracle database!");
            //쿼리실행
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT sysdate FROM dual");
            if (resultSet.next()) {
                System.out.println("SELECT sysdate FROM dual => " + resultSet.getString(1));
            }
            resultSet.close();
            statement.close();
        } catch (SQLException e) {
            System.out.println("Connection failed.");
            e.printStackTrace();
        }
    }
}

[증상]

IDE (vscode, sts, eclipse)가 아닌 cli에서 maven compile시 annotationProcessor가 적용되지 않아 compile error가 발생하는 경우

$ mvn clean compile

공식홈페이지에는 아래와 같이 설정하면 된다고 나와 있는데, 해당 방식으로 동작하지 않음

<dependencies>
	<dependency>
		<groupId>org.projectlombok</groupId>
		<artifactId>lombok</artifactId>
		<version>1.18.36</version>
		<scope>provided</scope>
	</dependency>
</dependencies>

<annotationProcessorPaths>
	<path>
		<groupId>org.projectlombok</groupId>
		<artifactId>lombok</artifactId>
		<version>1.18.36</version>
	</path>
</annotationProcessorPaths>

공식사이트: https://projectlombok.org/setup/maven

[해결방안]

pom.xml의 maven-compiler-plugin에 명시적으로 annotationProcessorPaths를 적용

<project>
  <build>
    <plugins>
      
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <configuration>
          <source>${java.version}</source>
          <target>${java.version}</target>
          <annotationProcessorPaths>
            <path>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
              <version>${lombok.version}</version>
            </path>
          </annotationProcessorPaths>
        </configuration>
      </plugin>
      
    <plugins>      
  <build>
<project>

[증상]

"Spring Boot 3.4"로 업데이트 후 "java.lang.IllegalStateException: No target Validator set" 에러가 발생

Caused by: java.lang.IllegalStateException: No target Validator set
	at org.springframework.util.Assert.state(Assert.java:79) ~[spring-core-6.2.3.jar:6.2.3]
Assert.java:79
	at org.springframework.validation.beanvalidation.SpringValidatorAdapter.forExecutables(SpringValidatorAdapter.java:402) ~[spring-context-6.2.3.jar:6.2.3]
SpringValidatorAdapter.java:402
	at org.springframework.validation.beanvalidation.MethodValidationAdapter.invokeValidatorForArguments(MethodValidationAdapter.java:259) ~[spring-context-6.2.3.jar:6.2.3]
	at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:168) ~[spring-context-6.2.3.jar:6.2.3]
MethodValidationInterceptor.java:168
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.2.3.jar:6.2.3]
ReflectiveMethodInvocation.java:184
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:727) ~[spring-aop-6.2.3.jar:6.2.3]
CglibAopProxy.java:727

[분석]

Spring Boot 3.4에서 Configuration Properties의 Bean Validation이 변화됨

https://github.com/spring-projects/spring-boot/wiki/Spring-Boot-3.4-Release-Notes#bean-validation-of-configuration-properties

[해결]

기존에 포함되어 있던 org.hibernate.validator:hibernate-validator 종속성에 

추가로 org.apache.tomcat.embed:tomcat-embed-el 종속성을 추가하여 해결함

    <dependency>
      <groupId>org.hibernate.validator</groupId>
      <artifactId>hibernate-validator</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.tomcat.embed</groupId>
      <artifactId>tomcat-embed-el</artifactId>
    </dependency>

[참고]

spring-boot-starter-validation의 소스를 참고 했음

https://github.com/spring-projects/spring-boot/blob/3.4.x/spring-boot-project/spring-boot-starters/spring-boot-starter-validation/build.gradle