PostgresSQL

마자나다·2024년 1월 9일

DB

목록 보기

4/4

PostgresSQL이란?

PostgresSQL은 오픈소스로 개발된 관계형 데이터베이스이다.
오픈소스이며, 강력하고 확장 가능한 특징을 가지고 있다.
Oracle, MySQL 등등 유명한 RDBMS에도 밀리지않는 사용량을 보여주고 있다.

MySQL과 차이점

가장 많은 사용량을 보여주는 MySQL과 PostgresSQL의 큰 차이점은 뭘까
1. 확장성과 기능 지원
- MySQL은 간단하고 가볍게 사용할 수 있는 경량 데이터베이스 시스템으로 시작되었다. 그렇기 때문에 확장성 면에서는 몇가지 제약이 있을 수 있다.
- PostgreSQL은 확장성과 고급 기능 지원에서 강력하다. 더 많은 데이터 유형, 인덱스 유형, 트리거, 저장 프로시저 및 사용자 정의 데이터 유형을 포함한 다양한 기능을 제공한다.
2. JSON 지원
- MySQL은 JSON데이터 형식을 지원하지만 PostgreSQL에 비해 지원이 아쉽다.
- PostgreSLQ은 JSON, JSONB데이터 형식을 완전히 지원하면 JSON데이터를 저장하는데 강력한 기능을 제공한다.
3. 읽기와 쓰기
- MySQL은 읽기 전용 명령을 관리하는데 선호된다. 반면에 PostgreSQL은 읽기-쓰기, 대규모 데이터 세트 및 복잡한 쿼리를 관리하는경우 선호된다.
- PostgreSQL은 ACID를 준수하도록 구축되었으며, 동시 트랜잭션이 필요한 경우에 최적이지만 읽기 전용 작업의 경우에는 속도가 느리고 안정성이 떨어진다.

Postgres의 장점

표준 SQL을 준수한다.
앞서말한 차이점과 같이 다른 DB에 비해서 ACID가 뛰어나다.
소스를 변경하고 그 소스를 숨긴 채 재배포해도 법적으로 문제가 없다. - 라이선스에 대한 비용문제가 없다.
가볍게 돌아가고 대용량 처리에도 큰 문제가 없다.

Postgres의 단점

UPDATE 쿼리에 약하다. 때문에 UPDATE를 반복적으로 수행하는 서비스에 제약이 크다.
메모리 성능이 떨어진다. Postgre는 새로운 클라 연결에대해 새로운 프로세스를 일으킨다. 때문에 메모리 사용량이 빠르게 증가한다. 따라서 읽기가 많은 간단한 작업의 경우 MySQL과 같은 다른 DB에 비해 성능이 떨어진다.

PostgresSQL의 구조

각 기능은 다음과 같다

Users/Groups : 사용자 정보 관리
Databases : 실제 레코드가 저장되는 Database 관리
Tablespaces : Database의 Object가 저장 된 파일 시스템의 경로 관리
Schemas : PostgreSQL 내부에서 Database를 논리적으로 구분하여 관리
Tables : Row와 Column으로 구성된 실제 레코드가 저장되는 공간
Views : 읽기 전용의 가상 테이블로 제한된 정보만 제공하기 위한 공간.

PSQL 사용법

psql은 PostgreSQL 데이터베이스에 대한 명령줄 인터페이스를 제공하는 유틸리티이다. 그 중에 내가 자주 사용했던 psql 명령어를 알아보자

접속 및 연결
- psql -U username -d databasename : PostgresSQL DB에 특정 사용자로 로그인하고 특정 데이터베이스를 연결한다.
- psql -h hostname -p port -U username -d dbname: 호스트 및 포트를 지정하여 연결한다
기본 명령어
- \q : psql 세션을 종료합니다.
- \c dbname username : 다른 데이터베이스로 연결하거나 다른 사용자로 변경합니다.
- \l : 현재 PostgreSQL 서버에 있는 데이터베이스 목록을 표시합니다.
- \dt : 현재 데이터베이스에 있는 테이블 목록을 표시합니다.
쿼리 및 데이터 검색
- SELECT * FROM table_name; : 특정 테이블에서 모든 데이터를 선택합니다.
- \d table_name : 특정 테이블의 구조(열 및 데이터 유형)를 표시합니다.
사용자 및 보안 관리
- \du : 현재 데이터베이스에 정의된 사용자 목록을 표시합니다.
- \dp : 현재 데이터베이스의 테이블 및 다른 객체에 대한 권한을 표시합니다.

간단한 쿼리문

테이블 구성에 따른 간단한 쿼리문을 적어보겠다!

테이블 생성

CREATE TABLE table_name (
    column1 datatype1,
    column2 datatype2,
    column3 datatype3,
    -- 추가적인 열 및 데이터 유형 정의 가능
);

테이블 삭제

DROP TABLE IF EXISTS table_name;

컬럼 추가

ALTER TABLE table_name
ADD COLUMN new_column datatype;

컬럼 삭제

ALTER TABLE table_name
DROP COLUMN column_name;

이 외에도 다양하게 key설정, 데이터 업데이트,삭제 등등이 있다.

PostgreSQL 리눅스 설정

local에서 PostgreSQL을 사용할 땐 딱히 문제가 되지 않았다. 하지만 local이 아닌 다른 환경, ip에 있는 DB를 PostgreSQL을 설정하고 데이터를 저장하기 위해 접근하면 계속 오류가 발생했다.
알고보니 conf를 따로 설정해주지 않아 접속할때마다 ip가 거절되고 저장이 되지않았다 ㅠㅠ 해당 문제를 해결하기 위해 사용한 방법이다.

postgresql.conf 설정

postgresql.conf파일은 PostgreSQL의 설정관련 내용들을 정의하는 파일이다. 여기서 모든 ip가 자유롭게 이용하기 위해 사용한 설정하였다.
파일 내부에 '#'되어있는 설정값은 디폴트 값이며 변경시 주석을 제거하여 사용한다.
listen_addresses = 'localhost'라고 되어있는 코드를 listen_addresses = '*' 로 바꾸어 주었다. local만 이용할 수 있던 ip가 모든 ip에서 수신하용 하도록 바꾸었다.
그 외에 postgresql.conf파일 내부에서 DB의 Port번호 설정, 최대 동시 연결 수 등등을 설정할 수 있다.

pg_hba.conf 설정

pg_hba.conf파일은 클라이언트의 인증관련 설정을 정의하는 파일이다. 이곳에서 특정 IP를 허용하도록 수정할 수 있다.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

이렇게 주소와 역할 이름을 지정하고 DB에 연결을 허용할지 여부를 설정하는데 사용된다.
local에서만 접속이 가능한 127.0.0.1으로 되어있던 설정을 0.0.0.0/0으로 바꾸어 모든 IP에서 접속할 수 잇도록 수정하였다.
METHOD는 인증방법을 지정한다. 설정 값과 인증 방식은 다음을 참조한다.

예를 들어 192.168.1.0/24 네트워크에서 데이터베이스 mydb에 대한 연결을 사용자 devkuma에 대해 인증 방식 md5에서 허용하는 경우는 다음과 같이 작성한다.

  # TYPE  DATABASE        USER            ADDRESS                 METHOD
  # IPv4 local connections:
	host  mydb 		     devkuma 	      192.168.1.0/24 		  md5

그리고 해당 DB를 사용중 하기위한 Port번호도 열려있나 꼭 확인해야한다!

참고 블로그
https://www.bearpooh.com/138
https://www.bearpooh.com/122
https://berasix.tistory.com/entry/PostgreSQL-설치와-운영-2-postgresqlconf-설정하기
https://www.devkuma.com/docs/postgresql/pghba-conf-파일-설정-방법/

마자나다

우왕좌왕 개발

이전 포스트