리눅스에 PostgreSQL 설치하는 방법 + 기초설정 + 명령어 소개

글 작성 시점 기준 PostgreSQL의 최신 버전은 16입니다.(stable 기준)

PostgreSQL 버전 확인하기

먼저, 인스턴스 서버에서 설치가 가능한 버전을 찾아봅니다. "apt search ^postgresql-{버전}$" 명령을 활용합니다.
현재 인스턴스에는 14버전까지만 설치가 가능한 것으로 보입니다.

(django) ubuntu:~$ apt search ^postgresql-16$
Sorting... Done
Full Text Search... Done
(django) ubuntu:~$ apt search ^postgresql-15$
Sorting... Done
Full Text Search... Done
(django) ubuntu:~$ apt search ^postgresql-14$
Sorting... Done
Full Text Search... Done
postgresql-14/jammy-updates,jammy-security 14.12-0ubuntu0.22.04.1 amd64
  The World's Most Advanced Open Source Relational Database

(django) ubuntu:~$

현재 설치가능한 14버전을 설치해서 사용하겠습니다.

(django) ubuntu@ip-172-26-5-228:~$ sudo apt install postgresql-14 -y

최신 버전의 PostgresSQL을 사용하고 싶다면?

(django) ubuntu:~$ sudo apt install curl ca-certificates
(django) ubuntu:~$ sudo install -d /usr/share/postgresql-common/pgdg
(django) ubuntu:~$ sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
(django) ubuntu:~$ sudo apt update

(django) ubuntu:~$ apt search ^postgresql-16$
Sorting... Done
Full Text Search... Done
postgresql-16/jammy-pgdg 16.3-1.pgdg22.04+1 amd64
  The World's Most Advanced Open Source Relational Database

(django) ubuntu:~$ apt search ^postgresql-15$
Sorting... Done
Full Text Search... Done
postgresql-15/jammy-pgdg 15.7-1.pgdg22.04+1 amd64
  The World's Most Advanced Open Source Relational Database

(django) ubuntu:~$

초기 비밀번호 설정하는 방법

PostgresSQL은 기본적으로 "postgres"라는 계정을 가지고 있으며, 초기 비밀번호를 설정해주어야 합니다.
"sudo su -postgres -c psql" 명령으로 postgres 계정을 강제로 실행한 다음, "ALTER USER postgres PASSWORD '{비밀번호}'" 명령을 통해 postgres 계정의 비밀번호를 변경합니다.
다음은 비밀번호를 "aaaa"로 변경하는 예시입니다.

psql은 "\q"(백슬래시 + Q)를 입력하면 종료할 수 있습니다.

(django) ubuntu:~$ sudo su - postgres -c psql
psql (14.12 (Ubuntu 14.12-1.pgdg22.04+1))
Type "help" for help.

postgres=# ALTER USER postgres PASSWORD 'aaaa';
ALTER ROLE
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
postgres=# \q
(django) ubuntu:~$

conf 파일 수정하기

"psql -U {username}" 명령으로 PostgreSQL에 접속할 수 있습니다. 그런데, 설치 초기에는 "failed: FATAL:  Peer authentication failed for user 'postgre'"라는 에러 메세지가 노출되며 로그인할 수 없습니다.
인증방식이 "peer"로 설정되어있기 때문입니다.

:~$ psql -U postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"
:~$

인증 방식 변경을 위해 "pg_hba.conf" 파일을 수정해야 합니다.
전체 경로는 "/etc/postgresql/{버전}/main/pb_hba.conf"입니다.
다음과 같이 "postgres"와 같은 줄에 있는 "peer"를 "md5"로 변경한 다음, psql을 재시작해줍니다.

~$ sudo nano /etc/postgresql/14/main/pg_hba.conf
~$ sudo systemctl restart postgresql

# /etc/postgresql/14/main/pg_hba.conf
...
# Database administrative login by Unix domain socket
# local   all             postgres                                peer
local   all             postgres                                md5
...

이제 postgres 계정으로 psql에 접속할 수 있게 되었습니다.
비밀번호 입력란의 경우 비밀번호를 입력하더라도 문자가 표시되지 않을 수 있습니다.

ubuntu@ip-172-26-5-228:~$ psql -U postgres
Password for user postgres: aaaa
psql (14.12 (Ubuntu 14.12-1.pgdg22.04+1))
Type "help" for help.

postgres=#

기본 설정 변경하기

로그인 설정을 변경하는 김에 postgresql의 자체 설정은 "postgresql.conf"에서 변경할 수 있습시다.
변경할 것은 로그파일의 인코딩 형식과 타임존 정보입니다.
전체 경로는 "/etc/postgresql/{버전}/main/postgresql.conf"이며, 설정 변경시 psql을 재시작해야 합니다.

~$ sudo nano /etc/postgresql/14/main/postgresql.conf
~$ sudo systemctl restart postgresql

주로 찾게 되는 설정은 다음과 같습니다.

# /etc/postgresql/14/main/postgresql.conf
...
# psql 접속에 사용하는 포트번호
port = 5432                             # (change requires restart)
...
# 로그파일 타임존
log_timezone = 'Etc/UTC'
...
# 기본 타임존
timezone = 'Etc/UTC'
...

psql 기초 명령어들

신규 user를 생성한 경우 해당 user명과 동일한 이름을 가진 database를 생성해야 합니다.
그렇지 않으면 해당 user로 psql에 접속하는 것이 불가능합니다.

# 클라이언트 인코딩 확인방법
postgres=# SHOW CLIENT_ENCODING;
 client_encoding
-----------------
 UTF8
(1 row)

# 클라이언트 인코딩 변경방법
postgres=# SET CLIENT_ENCODING TO 'utf-8';
SET

# 타임존 확인방법
postgres=# SHOW TIMEZONE;
 TimeZone
----------
 Etc/UTC
(1 row)

# 타임존 변경방법
postgres=# SET TIMEZONE TO 'Asia/Seoul';
SET
postgres=# SHOW CLIENT_ENCODING;
 client_encoding
-----------------
 UTF8
(1 row)

postgres=# SHOW TIMEZONE;
  TimeZone
------------
 Asia/Seoul
(1 row)

# user 생성방법
postgres=# CREATE USER {username} WITH PASSWORD '{비밀번호}';
# username 변경
postgres=# ALTER USER {기존 username} RENAME TO {변경할 username};
# 비밀번호 변경
postgres=# ALTER USER {username} WITH PASSWORD {변경할 비밀번호};
# user 인코딩 변경
postgres=# ALTER ROLE {username} SET CLIENT_ENCODING TO 'utf-8';
# user 타임존 변경
postgres=# ALTER ROLE {username} SET TIMEZONE TO 'Asia/Seoul';

# db 생성 권한부여
postgres=# ALTER USER {username} CREATEDB;

# dataabse 생성방법
postgres=# CREATE DATABASE {db명};
# owner를 지정해서 생성하는 경우 
postgres=# CREATE DATABASE {db명} OWNER {username};
# owner와 인코딩 형식을 지정하는 경우
postgres=# CREATE DATABASE {db명} WITH OWNER {username} ENCODING 'UTF8';
# 특정 db의 모든 권한 특정 user에게 부여하기
postgres=# GRANT ALL PRIVILEGES ON DATABASE {db명} to {username};

postgres=# \q
~$

이 글의 댓글 기능은 일부러 막아놓았습니다. 궁금한 내용이 있다면 게시판을 이용해주세요!