PostgreSQL 다뤄보기

Dev.Hammy·2023년 11월 4일

Etc

목록 보기

19/21

(1) PostgreSQL 설치하고 연결

ubuntu 22.04 사용자는 공식 홈페이지의 설치 매뉴얼을 따라하지 마시고 아래 링크를 참고하여 설치하세요

GPG 키를 다운로드하고 sources.d.list에서 postgreSQL의 리스트에 아키텍처 구조와 서명여부 표시하기

sudo apt update
sudo apt install curl lsb-release

curl -L https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql-archive-keyring.gpg >/dev/null

echo "deb [arch=amd64 signed-by=/usr/share/keyrings/postgresql-archive-keyring.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee  /etc/apt/sources.list.d/postgresql.list

sudo apt update

echo: 텍스트를 출력하는 명령어입니다.
"deb [arch=amd64 signed-by=/usr/share/keyrings/postgresql-archive-keyring.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main": 이 부분은 저장소 정보를 정의하는 부분입니다. 여러 필드로 구성되어 있으며, 각 필드는 다음과 같이 해석됩니다.
- deb: 저장소의 유형을 나타냅니다. deb는 Debian 패키지를 나타냅니다.
- [arch=amd64]: 이 저장소는 amd64 아키텍처와 관련이 있음을 나타냅니다. 즉, amd64 아키텍처의 시스템에 해당합니다.
- signed-by=/usr/share/keyrings/postgresql-archive-keyring.gpg: 이 저장소에 서명된 패키지를 사용하기 위한 GPG 키링 경로를 지정합니다. 이 경로는 패키지가 안전하게 서명되었음을 확인하는 데 사용됩니다.
- http://apt.postgresql.org/pub/repos/apt: PostgreSQL APT 저장소의 주소입니다. 여기에서 패키지를 다운로드합니다.
- $(lsb_release -cs)-pgdg: 릴리스 코드명에 따라 저장소의 서브 경로를 동적으로 생성합니다. lsb_release -cs는 현재 시스템의 릴리스 코드명을 가져오는 명령입니다. 이것은 시스템의 릴리스에 따라 저장소 경로를 자동으로 조정합니다.
- main: 저장소의 구성 요소(component)를 나타냅니다. main은 주요 패키지를 나타내며, 주로 사용하는 구성 요소 중 하나입니다.
- |: 파이프(pipe) 기호는 명령어의 출력을 다음 명령어로 전달하는 데 사용됩니다.
sudo tee /etc/apt/sources.list.d/postgresql.list: 이 명령어는 이전 명령의 출력을 /etc/apt/sources.list.d/postgresql.list 파일에 기록하도록 합니다. 즉, PostgreSQL 저장소 정보가 이 파일에 저장됩니다.

설치하고 PostgreSQL 데이터베이스 서버 시작하기

# podstgresql 설치
sudo apt install postgresql
# 유틸리티와 유용한 기능이 더해진 `-contrib` 패키지 설치
sudo apt install postgresql-contrib

# PostgreSQL 데이터베이스 서버에 연결
sudo systemctl start postgresql.service
# systemd가 알아서 .service 확장자를 추가해주는 축약된 방식
sudo systemctl start postgresql

(2) role과 account

기본적으로 Postgres는 "역할"이라는 개념을 사용하여 인증 및 권한 부여를 처리합니다. 이는 어떤 면에서는 일반 Unix 스타일 계정과 유사하지만 Postgres는 사용자와 그룹을 구분하지 않고 대신 보다 유연한 용어인 "역할"을 선호합니다.

설치 시 Postgres는 피어 인증을 사용하도록 설정됩니다 . 즉, Postgres 역할을 일치하는 Unix/Linux 시스템 계정과 연결합니다. Postgres 내에 역할이 존재하는 경우 동일한 이름을 가진 Unix/Linux 사용자 이름이 해당 역할로 로그인할 수 있습니다.

postgres 계정으로 전환

sudo -i -u postgres

-i: 이 옵션은 "login shell"을 시작하도록 하는 옵션입니다. 즉, 사용자 스위치를 할 때 새로운 사용자로 로그인한 것처럼 쉘을 초기화하고 환경 변수를 설정합니다. 이것은 일반적으로 사용자 간의 전환 시에 필요한 환경을 새로 설정하기 위해 사용됩니다.
-u <other_user>: -u 옵션 다음에 오는 <other_user>는 사용자 이름을 나타내며, 현재 사용자를 <other_user> 사용자로 변경합니다. 따라서 명령어를 실행할 때 현재 사용자 대신 <other_user> 사용자의 권한과 환경을 사용합니다.
postgres : postgreSQL 설치 중 기본적으로 생성된 사용자 계정.

postgres 계정으로 전환하면 프롬프트가 postgres@treadmill:~$ 로 변경된다.

# PostgreSQL 프롬프트에 접근한다
psql

postgres 계정으로 전환 후 psql 명령어를 실행하면 PostgreSQL 프롬프트에 액세스 하게 되어 데이터베이스 관리 시스템과 상호작용 할 수 있게 된다. (프롬프트 모양이 podstgres=#로 바뀜) /q를 입력하여 프롬프트를 종료할 수 있다. 종료하면 다시 프롬프트는 postgres@treadmill:~$ 로 변경된다. 그 상태에서 exit를 입력하면 다시 원래 계정으로 돌아간다.

postgres=# \q
postgres@treadmill:~$ exit

postgres 계정으로 전환하지 않고 PostgreSQL 프롬프트에 액세스

sudo -u postgres psql

이 명령어를 사용하면 postgres@treadmill:~$ 로 프롬프트가 변경되는 중간 셸을 거치지 않고 바로 PostgreSQL 프롬프트를 띄울 수 있다.

새 역할(role) 만들기

https://browndwarf.tistory.com/3
postgres 사용자로 계정 전환 후 다음과 같이 새 사용자를 만들 수 있다.

createuser --interactive
# 새 사용자의 이름과 슈퍼유저 권한 여부를 묻는 프롬프트에 응답

새 데이터베이스 생성

Postgres 인증 시스템이 기본적으로 설정하는 또 다른 가정은 로그인에 사용되는 모든 역할에 대해 해당 역할이 액세스할 수 있는 동일한 이름의 데이터베이스를 갖게 된다는 것입니다. 즉, 마지막 섹션에서 생성한 사용자가 sammy 라면 해당 역할은 기본적으로 "sammy"라고도 하는 데이터베이스에 연결을 시도합니다.

createdb <role 이름>

새 역할로 PostgreSQL 프롬프트 열기

https://www.lesstif.com/dbms/postgresql-61899197.html

postgres@treadmill:~$ cat /etc/passwd
root:x:0:0:root:/root:/bin/bash
...중략
Dev-Hammy:x:1000:1000:Dev-Hammy,,,:/home/Dev-Hammy:/bin/bash
postgres:x:129:137:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

postgres 중간 셸에서 cat /etc/passwd로 사용자 조회를 했을 때는 아직 role이 우분투 사용자로 등록이 안된 상태이다. role을 가진 사용자를 우분투 유저로 등록해야 피어 인증이 가능한데, 이 때 adduser는 postgres 중간 셸에서 하는 것이 아니라 일반 사용자 계정에서 sudo 권한을 이용해서 수행해야 한다.

postgres@treadmill:~$ exit
Dev-Hammy@treadmill:~$ sudo adduser <role 이름>

Dev-Hammy@treadmill:~$ sudo -i -u npm_user
npm_user@treadmill:~$ psql

... 중략
npm_user=# \conninfo

sudo adduser로 npm_user라는 role 이름에 맞는 리눅스 유저를 생성하였고 sudo -i -u로 npm_user로 계정을 전환한 다음 psql로 PostgreSQL 프롬프트를 실행한다. PostgreSQL 프롬프트를 켠 상태에서 \conninfo 명령을 입력하면 데이터베이스, 사용자, 소켓, 포트 등 접속 정보를 확인할 수 있다.

앞으로 PodstgreSQL을 줄여서 psql이라고 부르겠다. psql에서는 사용자 계정을 role이라고 한다. psql은 리눅스의 시스템 계정을 role과 연결하므로 리눅스에 hammy라는 계정이 존재하고 psql에 hammy 라는 role이 존재한다면 리눅스 hammy 계정이 psql hammy 역할로 로그인 할 수 있다.

psql 설치 직후에는 기본 사용자 계정인 postgres가 생성되어 있는데 이를 사용자의 서버로 옮기려면 아래와 같이 입력한다.

sudo -i -u postgres

현재 사용자의 패스워드를 묻는 프롬프트에 응답하고 엔터 키를 누른다. 이제 psql을 입력하여 psql에 접근할 수 있다.

psql

postgres 프롬프트에는 아래와 같이 입력하여 접근하거나 종료할 수 있다.

# access
postgres=#

# quit
podstgre=# \q

podstgre=# \q 를 입력하면 psql을 종료하고 postgres 리눅스 커맨드 프롬프트를 보이는데

postgres@ubuntu-dev:~$

이 때, 당신의 일반 시스템 사용자로 돌아가려면 exit 를 입력하면 된다.

postgres@ubuntu-dev:~$ exit

(2) 샘플 DB 열기

psql 데이터베이스 서버에 샘플 데이터베이스를 로드하려면 다음과 같은 단계를 거친다 :

postgres 계정으로 전환한다.

sudo -i -u postgres

curl 도구로 샘플 db를 다운 받는다

curl -O https://sp.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip

dvdrental.zip의 tar 파일을 얻기 위해 압축을 해제한다.

unzip dvdrental.zip

psql 명령어로 psql에 접근한다

psql

CREATE DATABASE문으로 dvdrental 데이터베이스를 생성한다.

postgres=# create database dvdrental;

\q 명령으로 psql을 종료한다

postgres=# \q

PostgreSQL에서 "restore"는 데이터베이스에서 이전 상태로 복구하거나 백업된 데이터를 다시 데이터베이스로 가져오는 작업을 의미합니다. pg_restore를 사용해서 dvdrental 데이터베이스를 복구합니다.

pg_restore --dbname=dvdrental --verbose dvdrental.tar

psql로 다시 psql 데이터베이스 서버에 접속한다.

psql

dvdrental 데이터베이스로 전환한다

postgres=# \c dvdrental

dvdrental 데이터베이스와 연결하면 프롬프트가 바뀐다

dvdrental=#

film 테이블의 필름 수를 구하기 위한 명령어를 입력해본다

dvdrental=# select count(*) from film;

출력 결과

count
-------
1000
(1 row)

Dev.Hammy