PostgreSQL

1 install
2 config
- 2.1 Django 함께 사용할 경우
- 2.2 timezone
- 2.3 원격 접속 허용
3 참고사이트

install

https://www.postgresql.org/download/linux/ubuntu/ : 현재 postgresql-12 version

https://www.digitalocean.com/community/tutorials/how-to-set-up-django-with-postgres-nginx-and-gunicorn-on-ubuntu-20-04#creating-the-postgresql-database-and-user

https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-20-04

PostgreSQL install

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql.service

패키지 확인

# dpkg -l | grep postgresql
ii  postgresql                     12+214ubuntu0.1                   all          object-relational SQL database (supported version)
ii  postgresql-12                  12.7-0ubuntu0.20.04.1             amd64        object-relational SQL database, version 12 server
ii  postgresql-client-12           12.7-0ubuntu0.20.04.1             amd64        front-end programs for PostgreSQL 12
ii  postgresql-client-common       214ubuntu0.1                      all          manager for multiple PostgreSQL client versions
ii  postgresql-common              214ubuntu0.1                      all          PostgreSQL database-cluster manager
ii  postgresql-contrib             12+214ubuntu0.1                   all          additional facilities for PostgreSQL (supported version)

postgres user로 전환하여 DB 생성, user 생성

sudo -i -u postgres
CREATE DATABASE myproject;
CREATE USER myprojectuser WITH PASSWORD 'password';

이제 특정 user에게 특정 db에 대한 권한을 준다.

위 내용으로 설치 한 후 table 만들어서 확인

config

https://noweaver.github.io/posts/Journal/2020/05/13-Ubuntu-PostgreSQL-Installation/ 설치하고 원격 접속할 ip 허용, Listen 설정 변경

Django 함께 사용할 경우

https://docs.djangoproject.com/en/3.0/ref/databases/#optimizing-postgresql-s-configuration

the Django project itself 에서 권장하는 DB 설정이 있음.

기본 인코딩을 UTF8로 설정, default transaction isolation scheme 을 ‘read committed’로 설정(기본값임). timezone을 UTC로 설정.

timezone 은 개발 필요성에 따라서 GMT(UTC)로 설정하는 것이 필요할 수 있음.

Django needs the following parameters for its database connections:

client_encoding: 'UTF8',
default_transaction_isolation: 'read committed' by default, or the value set in the connection options (see below),
timezone: 'UTC' when USE_TZ is True, value of TIME_ZONE otherwise.

If these parameters already have the correct values, Django won’t set them for every new connection, which improves performance slightly. You can configure them directly in postgresql.conf or more conveniently per database user with ALTER ROLE.

Django will work just fine without this optimization, but each new connection will do some additional queries to set these parameters.

PostgreSQL에서 default_transaction_isolation 은 기본이 read committed 임. https://www.postgresql.org/docs/12/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-STATEMENT , https://www.postgresql.org/docs/12/transaction-iso.html#XACT-READ-COMMITTED

아래와 같은 sql 문으로 설정을 할 수도 있음.

timezone

timezone https://www.postgresql.org/docs/12/datatype-datetime.html#DATATYPE-TIMEZONES

timezone 설정 설명

log_timezone : server log 의 timestamps 설정과 연관이 됨.

https://www.postgresql.org/docs/12/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

log_timezone (string)

Sets the time zone used for timestamps written in the server log. Unlike TimeZone, this value is cluster-wide, so that all sessions will report timestamps consistently. The built-in default is GMT, but that is typically overridden in postgresql.conf; initdb will install a setting there corresponding to its system environment. See Section 8.5.3 for more information. This parameter can only be set in the postgresql.conf file or on the server command line.

원격 접속 허용

IPv4 local connections: 영역에서 IP 주소를 127.0.0.1/32를 0.0.0.0/0 으로 수정한다.

Listen 주소도 *로 되어 있는지 확인 한다.

DB 재시작

주소를 127.0.0.1/32 에서 0.0.0.0/0으로 바꾸면 psql 접속시 host를 지정해 주어야 함.

참고사이트

https://postgresql.kr/ / https://postgresql.kr/docs/12/

http://www.gurubee.net/postgresql/basic 기초 사용법.