Rocky Linux 9 PostgreSQL15 sql 파일 활용하기(+ 외부연결)

이전글

2024.04.10 - [SERVER] - Rocky Linux postgreSQL 설치

Rocky Linux postgreSQL 설치

 

Rocky Linux 9 오프라인 환경에서 postgreSQL15 버전을 설치해보았다.
다음은, 설치한 postgreSQL 에 데이터베이스 생성 및 sql 파일을 활용하여 구축하고 포트를 열어
DBeaver 에 연결하여 확인하는 방법을 작성해보겠다.

 

Rocky Linux 9.3 (UTM 가상 머신 사용 중, 오프라인 환경)
PostgreSQL 15
DBeaver

 

 

Postgres 계정 접속

이전글과 같이 세팅을 했다면 다음 명령어를 통해 postgres 계정에 접속하자

# 계정 접속
su - postgres

# 경로확인
pwd

# 경로 내 파일 목록 확인
ls

postgres 계정

sql 파일을 활용할 것이라서 sql 파일을 따로 관리할 수 있게 'sql' 폴더를 생성했다.

폴더 생성

 

SQL 파일 가져와 데이터 삽입

sql 폴더에 sql 을 넣어두었다.
첫번째로 실행할 sql 은 유저 생성, 데이터베이스 생성, 권한에 대한 sql 이었다

# 예시
psql -U 유저명 -a -f ./파일명

# 실제
psql -U postgres -a -f ./create.sql

 

두번째로 실행할 sql 은 데이터베이스에 ddl 을 생성하는 sql 파일과 데이터 Insert 인 dml sql 파일이었다.

# 예시
psql -U [데이터베이스소유유저] -d [데이터베이스] -a -f ./sql파일명

# 실제
psql -U testuser -d testdb -a -f ./ddl.sql
psql -U testuser -d testdb -a -f ./insert.sql

 

이때, 해당 오류가 생길 수 있다. 

psql: 오류: "/var/run/postgresql/.s.PGSQL.5432" 소켓으로 서버 접속 할 수 없음: 치명적오류: 사용자 "testuser"의 peer 인증을 실패했습니다.

sql 유저 권한 문제이므로 root 로 변경한 후 아래 파일을 연다.

# pg_hba.conf 수정을 위해 열기
vi /var/lib/pgsql/data/pg_hba.conf

아래 이미지와 같이 local 에 METHOD 부분이 peer 로 되어 있다면, md5 로 수정하자.
+) host IPv4, IPv6 도 METHOD 영역을 md5 로 수정한다.

local METHOD peer -> md5 로 수정

서비스를 재시작 한다.

systemctl stop postgresql.service
systemctl start postgresql.service

 

다시 ddl 및 dml sql 파일을 실행하자

# 예시
psql -U [데이터베이스소유유저] -d [데이터베이스] -a -f ./sql파일명

# 실제
psql -U testuser -d testdb -a -f ./ddl.sql
psql -U testuser -d testdb -a -f ./insert.sql

 

정상적으로 삽입되는 것을 볼 수 있다.

 

postgreSQL sql 접속하여 확인하기

해당 명령어를 입력하면 sql에 접속할 수 있다. 내가 생성한 유저, db, 테이블이나 데이터를 조회하고 싶다면 해당 명령어로 접속하자

psql postgres

비밀번호를 정상적으로 입력했음에도 아래와 같이 오류가 난다면,

[postgres@localhost data]$ psql postgres
postgres 사용자의 암호: psql: 오류: "/var/run/postgresql/.s.PGSQL.5432" 소켓으로 서버 접속 할 수 없음: 치명적오류: 사용자 "postgres"의 password 인증을 실패했습니다

다시 한 번 pg_hba.conf 파일을 열어 md5 -> trust 로 변경하고

md5 -> trust 변경

postgreSQL 재시작을 한 뒤, 접속을 해보자. 쿼리를 사용할 수 있을 것이다.
확인이 끝난 후에는 trust -> md5 로 변경한 다음, 다시 postgreSQL 을 재시작하자. 
**
(이때 외부 연결을 위한 replication privailege 목록에 있는 두개의 host 의 method 를 ident -> md5 로 변경하고 바로 하단에
host    all     all     0.0.0.0/0       md5 를 넣어두자)

 

만약 생성한 데이터베이스로 접속하고 싶다면 아래와 같이 입력하면 된다.

psql postgres -d 데이터베이스명
psql postgres -d testdb

 

 

DBeaver 로 확인하기(+ 외부연결)

root 로 접속하여 postgreSQL 포트를 열어준다.

# port 열기
firewall-cmd --zone=public --add-port=5432/tcp --permanent
# 재실행
firewall-cmd --reload
# 정보 확인하기
firewall-cmd --list-all

DBeaver 를 열어 데이터 베이스 연결을 시작한다.

데이터베이스 선택

 

연결 내용 설정

• Host
• Port
• Database
• Username
• Password

다섯가지의 항목을 입력 후 좌하단에 있는 Test Connection ... 을 누르면!

Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. Connection refused Connection refused

이런 에러가 뜰 것이다. 이 내용은 postgre 커넥션 설정 주소가 localhost 로 되어 있어서 그럴 것이다. postgresql.conf 파일을 열어 수정하자

# postgres 접속 일 때
vi ./data/postgresql.conf

# root 일 때
vi /var/lib/pgsql/data.postgresql.conf

#listen_addresses = 'localhost' 부분이 보일텐데, 해당 부분에 주석을 해제하고 * 로 변경하자.

주석 해제 후 localhost -> * 로 수정

postgresql 을 재시작 후, DBeaver 에서 다시 테스트 커넥션을 해보면...!

connection 성공

이제 외부에서도 연결하여 한결 더 편하게 디비를 만질 수 있게 되었다!

 

 

참고

외부 연결 에러 해결