PostgreSQL란?
PostgreSQL은 오픈 소스 객체-관계형 데이터베이스 시스템(ORDBMS)으로, Enterprise급 DBMS의 기능과 차세대 DBMS에서나 볼 수 있을 법한 기능들을 제공한다. 약 20여년의 오랜 역사를 갖는 PostgreSQL은 다른 관계형 데이터베이스 시스템과 달리 연산자, 복합 자료형, 집계 함수, 자료형 변환자, 확장 기능 등 다양한 데이터베이스 객체를 사용자가 임의로 만들 수 있는 기능을 제공함으로써 마치 새로운 하나의 프로그래밍 언어처럼 무한한 기능을 손쉽게 구현할 수 있다.
PostgreSQL의 구조
PostgreSQL은 클라이언트/서버 모델을 사용한다. 서버는 데이터베이스 파일들을 관리하며, 클라이언트 애플리케이션으로부터 들어오는 연결을 수용하고, 클라이언트를 대신하여 데이터베이스 액션을 수행한다. 서버는 다중 클라이언트 연결을 처리할 수 있는데, 서버는 클라이언트의 연결 요청이 오면 각 커넥션에 대해 새로운 프로세스를 fork한다. 그리고 클라이언트는 기존 서버와의 간섭 없이 새로 생성된 서버 프로세스와 통신하게 된다.
PostgreSQL의 기능
PostgreSQL은 관계형 DBMS의 기본적인 기능인 트랜잭션과 ACID(Atomicity, Consistency, Isolation, Durability)를 지원한다. ANSI:2008 규격을 상당 부분 만족시키고 있으며, 전부 지원하는 것을 목표로 계속 기능을 추가하고 있다. PostgreSQL은 기본적인 신뢰도와 안정성을 위한 기능 뿐만 아니라 진보적인 기능이나 학술적 연구를 위한 확장 기능도 많이 가지고 있는데, PostgreSQL의 주요 기능을 열거해보면 아래와 같다.
- Nested transactions (savepoints)
- Point in time recovery
- Online/hot backups, Parallel restore
- Rules system (query rewrite system)
- B-tree, R-tree, hash, GiST method indexes
- Multi-Version Concurrency Control (MVCC)
- Tablespaces
- Procedural Language
- Information Schema
- I18N, L10N
- Database & Column level collation
- Array, XML, UUID type
- Auto-increment (sequences),
- Asynchronous replication
- LIMIT/OFFSET
- Full text search
- SSL, IPv6
- Key/Value storage
- Table inheritance
Template 데이터베이스
PostgreSQL에서 "Create Database"로 테이블을 생성할 때, 기본으로 생성되어 있는 Template1 Database를 복사하여 생성한다. 즉 Template Database는 표준 시스템 데이터베이스로 원본 데이터베이스에 해당하는데, 만약 template1에서 프로시저 언어 PL/Perl을 설치하는 경우 해당 데이터베이스를 생성할 때 추가적인 작업 없이 사용자 데이터베이스가 자동으로 사용 가능하다.
PostgresQL에는 Template0라는 2차 표준 시스템 데이터베이스가 있는데, 이 데이터베이스에는 template1의 초기 내용과 동일한 데이터가 포함되어 있다. Template 0은 수정하지 않고 원본 그대로 유지하여 무수정 상태의 데이터베이스를 생성할 수 있으며, pg_dump 덤프를 복원할 때 유용하게 사용할 수 있다.
일반적으로 template1에는 인코딩이나 로케일 등과 같은 설정들을 해주고, 템플릿을 복사하여 데이터베이스를 생성한다. 그리고 template0을 통해서는 새로운 인코딩 및 로케일 설정을 지정할 수 있다.
template0을 복사하여 데이터베이스를 생성하려면
CREATE DATABASE dbname TEMPLATE template0;SQL 환경에서 다음을 사용해야 한다.
createdb -T template0 dbname
Vacuum
Vacuum은 PostgreSQL에만 존재하는 고유 명령어로, 오래된 영역을 재사용 하거나 정리해주는 명령어이다. PostgreSQL에서는 MVCC(Multi-Version Concurrency Control, 다중 버전 동시성 제어) 기법을 활용하기 때문에 특정 Row를 추가 또는 업데이트 할 경우, 디스크 상의 해당 Row를 물리적으로 업데이트 하여 사용하지 않고, 새로운 영역을 할당해서 사용한다. 예를 들어 전체 테이블을 Update하는 경우에는 자료의 수만큼 자료 공간이 늘어나게 된다. 그러므로 Update, Delete, Insert가 자주 일어나는 Database 경우는 물리적인 저장 공간이 삭제되지 않고 남아있게 되므로, vacuum을 주기적으로 해주는 것이 좋다. Vacuum을 사용하면 어느 곳에서도 참조되지 않고, 안전하게 재사용할 수 있는 행을 찾아 FSM(Free Space Map)이라는 메모리 공간에 그 위치와 크기를 기록한다. 그리고 Insert 및 Update 등 새로운 행을 추가하는 경우, FSM에서 새로운 데이터를 저장할 수 있는 적당한 크기의 행을 찾아 사용한다.
Vacuum Command
vacuumdb를 활용하여 주기적으로 정리할 수 있는데, 관련 옵션들은 아래와 같다. full 옵션 없이 vacuumdb를 실행할 경우는 단순히 사용가능한 공간만을 반환한다. 하지만 full옵션을 추가하는 경우에는 빈 영역에 tuple을 옮기는 등 디스크 최적화 작업을 하게 된다. 디스크 최적화를 위해 table에는 LOCK이 걸리게 되고, 시간이 오래 걸리게 되므로 사용 시 주의해야 한다.
사용법:
``vacuumdb [옵션]... [DB이름]
옵션들:
-a,--all 모든 데이터베이스 청소
-d,--dbname=DBNAME DBNAME 데이터베이스 청소
-e,--echo 서버로 보내는 명령들을 보여줌
-f,--full 대청소
-F,--freeze 행 트랜잭션 정보 동결
-q,--quiet 어떠한 메시지도 보여주지 않음
-t,--table='TABLE[(COLUMNS)]' 지정한 특정 테이블만 청소
-v,--verbose 작업내역의 자세한 출력
-V,--version output version information, then exit
-z,--analyze update optimizer statistics
-Z,--analyze-only only update optimizer statistics
-?,--help show this help, then exit
연결 옵션들:
-h,--host=HOSTNAME 데이터베이스 서버 호스트 또는 소켓 디렉터리
-p,--port=PORT 데이터베이스 서버 포트
-U,--username=USERNAME 접속할 사용자이름
-w,--no-password 암호 프롬프트 표시 안 함
-W,--password 암호 프롬프트 표시함--maintenance-db=DBNAME alternate maintenance database
기본 명령어
사용자 생성
CREATE USER TEST_USER PASSWORD 'TEST_PASSWD' CREATEDB;데이터베이스 생성
CREATE DATABASE MY_DB OWNER TEST_USER데이터베이스 삭제
DROPDB MY_DB데이터베이스 접속
PSQL MY_DBPostgreSQL이 지원하는 표준 SQL 타입
int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp, interval
SQL 문법
https://velog.io/@ygh7687/SQL-%EB%AC%B8%EB%B2%95-%EC%A0%95%EB%A6%AC
SELECT
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
SELECT절에서 쓰일 수 있는 Option
| Option | 기능 |
|---|---|
| SELECT ALL | 중복되는 열까지 결과값으로 출력해 주는 역할 |
| SELECT DISTINCT ON | 중복되는 열들을 결과값에서 제거하는 역할 |
| AS | alias의 약자로 선택한 column의 이름을 AS를 사용하면 새로운 이름으로 지정 |
| FROM | 한 개 또는 그 이상의 테이블을 지정할 수 있음 |
| WHERE | FROM절의 테이블 내에서 조건을 만족하는 행을 검사 |
| GROUP BY | 동일한 값을 가진 데이터를 집계해서 조회하고자 할 때 사용 |
| HAVING | 그룹을 가지고 조건을 비교할 때 사용 |
| UNION | 열의 개수가 동일하고 타입이 동일한 2개의 집합들의 결과값을 결합하여 반환 |
| INTERSECT | 교집합과 같은 원리로 두 집합모두에 있는 열들을 반환 |
| EXCEPT | 차집합과 같은 원리로 두 집합모두에 있는 열들을 반환 |
| ORDER BY | 파일을 정렬하는데 ASC와 DESC를 사용하여 오름차순과 내림차순을 설정 |
| LIMIT | 쿼리 결과의 개수를 제한 |
| OFFSET | 시작 행을 반환하기 전에 많은 행을 건너뛰는 역할 |
https://programmers.co.kr/learn/challenges?tab=sql_practice_kit
Django 프로젝트 설정
Django 애플리케이션과 PostgreSQL 데이터베이스가 모두 컨테이너로 실행은 되고 있지만 Django 애플리케이션은 디폴트로 SQLite 데이터베이스를 사용하기 때문에 PostgreSQL 데이터베이스 사용하도록 설정이 필요함
settings.py을 열고 DATABASES 부분 수정
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": "postgres",
"USER": "postgres",
"PASSWORD": "postgres",
"HOST": "db",
"PORT": 5432,
}
}
터미널에서 docker-compose up 명령어를 -d 옵션으로 실행하여 백그라운드에서 두 개의 서비스가 돌아가도록 하려면
$ docker-compose up -d
Starting django-app_db_1 ... done
Starting django-app_web_1 ... doneDB 마이그레이션
Migration models.py에서 바꾼 내용을(예를 들면 field를 추가하거나, model을 삭제하는 것 등) 데이터베이스 스키마에 전파시키는 장고의 기능
# 마이그레이션 파일 생성
$ python manage.py makemigrations <app-name>
# 마이그레이션 적용
$ python manage.py migrate <app-name>
# 마이그레이션 적용 현황
$ python manage.py showmigrations <app-name>
# 지정 마이그레이션의 SQL 내역
python manage.py sqlmigrate <app-name> <migration-name>
Django 애플리케이션을 정상적으로 사용하려면 먼저 DB 마이그레이션(migration)을 실행하여 관련 테이블과 생성하고 필요한 데이터를 적재해줘야 함.
다음과 같이 python manage.py migrate 명령어를 현재 백그라운드에서 돌아가고 있는 web 서비스를 대상으로 날려줌.
$ docker-compose exec web python manage.py migrate
Operations to perform:
Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
Applying contenttypes.0001_initial... OK
Applying auth.0001_initial... OK
Applying admin.0001_initial... OK
Applying admin.0002_logentry_remove_auto_add... OK
Applying admin.0003_logentry_add_action_flag_choices... OK
Applying contenttypes.0002_remove_content_type_name... OK
Applying auth.0002_alter_permission_name_max_length... OK
Applying auth.0003_alter_user_email_max_length... OK
Applying auth.0004_alter_user_username_opts... OK
Applying auth.0005_alter_user_last_login_null... OK
Applying auth.0006_require_contenttypes_0002... OK
Applying auth.0007_alter_validators_add_error_messages... OK
Applying auth.0008_alter_user_username_max_length... OK
Applying auth.0009_alter_user_last_name_max_length... OK
Applying auth.0010_alter_group_name_max_length... OK
Applying auth.0011_update_proxy_permissions... OK
Applying sessions.0001_initial... OKmigrate와 migrations의 차이
migrations는 변경 내용을 저장하고, migarte는 그 내용을 테이블에 적용시킨다.
git의 commit, merge와 비슷한 개념
- makemigrations는 내장 앱이나 새로 만든 앱의 SQL command를 생성. 하지만 변경 사항만 저장할 뿐이지 내 데이터베이스에서 실행되는 것은 아님. 그래서 makemigrations를 한 뒤에 테이블이 생기거나 하지 않음.
- 1번 뒤에 sqlmirate를 하면 makemigrations를 통해 생성된 것들을 모든 SQL commands를 볼 수 있다.
- migrate는 2번의 SQL commands를 데이터베이스 파일에서 실행. 그래서 migrate를 하고 나면 내 데이터베이스 파일 안의 앱의 테이블들이 생성. migrate command를 실행하고 db.sqlite3를 실행하면 모든 테이블이 생긴 것을 볼 수 있음.
DB 테이블 확인
DB 마이그레이션이 잘 되었는지 확인하려면, PostgreSQL 데이터베이스에 접속하여 테이블을 확인해봐야 함. db 서비스를 대상으로 psql 커맨드를 실행한 후 \z를 입력해보면 다음과 같이 생성된 테이블들 확인 가능.
$ docker-compose exec db psql postgres postgres
psql (12.2 (Debian 12.2-2.pgdg100+1))
Type "help" for help.
postgres=# \z
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-----------------------------------+----------+-------------------+-------------------+----------
public | auth_group | table | | |
public | auth_group_id_seq | sequence | | |
public | auth_group_permissions | table | | |
public | auth_group_permissions_id_seq | sequence | | |
public | auth_permission | table | | |
public | auth_permission_id_seq | sequence | | |
public | auth_user | table | | |
public | auth_user_groups | table | | |
public | auth_user_groups_id_seq | sequence | | |
public | auth_user_id_seq | sequence | | |
public | auth_user_user_permissions | table | | |
public | auth_user_user_permissions_id_seq | sequence | | |
public | django_admin_log | table | | |
public | django_admin_log_id_seq | sequence | | |
public | django_content_type | table | | |
public | django_content_type_id_seq | sequence | | |
public | django_migrations | table | | |
public | django_migrations_id_seq | sequence | | |
public | django_session | table | | |
(19 rows)'Web > Backend' 카테고리의 다른 글
| [백엔드 기초] 6. Web server(Nginx, Gunicorn, Dramatiq) (0) | 2021.06.01 |
|---|---|
| [백엔드 기초] 5. Docker (0) | 2021.06.01 |
| [백엔드 기초] 3. Django REST Framework (0) | 2021.06.01 |
| Django - Locust를 이용한 로드 테스트(Load test) (0) | 2021.06.01 |
| [백엔드 기초] 2-1. Django (0) | 2021.06.01 |