postgres 명령어 정리

들어가며

필자가 평소에 가장 많이 사용하는 데이터베이스는 postgreSQL이라고 부르는 데이터베이스이다. 무료로 사용할 수 있는 데이터베이스이며, mysql과 함께 많이 사용하는 관계형 데이터베이스이다.

 

보통 일반적으로는 pgAdmin이라고 불리는 데이터베이스 툴을 통해서 GUI 환경에서 DB를 관리할 수 있으나, 필자는 젯브레인즈 학생 라이선스 인증을 통해서 datagrip을 사용하기 때문에, Terminal의 psql 명령어를 통한 CLI 조작 + datagrip을 통한 GUI 조작을 통한 개발 환경을 갖추고 있다.

 

이번 포스팅에서는 기본적인 데이터베이스 생성 방법에 datagrip을 통해 모니터링하는 과정까지를 포스팅하려고 한다. MAC 환경을 기준으로 설명한다.

 

본론

postgresql 설치

먼저 postgres를 다운로드한다. homebrew를 사용하는 경우 다음과 같이 설치하고서 실행할 수 있다.

# brew 업데이트
$ brew update

# postgresql 설치
$ brew install postgresql

# postgresql 실행 
$ brew services start postgresql

 

위 명령어를 통해 postgresql을 설치할 수 있다. 이제 psql 명령어를 통해서 데이터베이스에 접근할 수 있게 된다. 맨 처음에는 postgres 유저를 통해서 데이터베이스에 접근할 수 있다. postgres 유저는 full access 권한이 있기 때문에 조심해서 사용하도록 하자.

 

psql 콘솔 사용

psql postgres

 

위 명령어를 통해서 psql 콘솔에 postgres 계정으로 접속하면 다양한 명령어를 통해서 cli로 관리할 수 있다. 아래의 명령어들은 자주 사용하는 기본적인 명령어들이다.

\list # 전체 데이터베이스 인스턴스 조회
\dt # 접속한 데이터베이스의 테이블 목록
\ds # sequence 목록
\df # function 목록
\dv # view 목록
\du # user 목록
\q # psql 콘솔 종료

 

데이터베이스에 연결하는 방법은 다양하게 있지만, 가장 일반적인 방법은 username과 password를 통해서 DB에 접근할 수 있는 권한이 있는 user를 통해서 데이터베이스에 연결하는 것이다. 따라서 이번 포스팅에서도 먼저 user를 생성하고, 데이터베이스를 생성하면서 데이터베이스에 연결할 수 있는 권한을 유저에게 물려주자.

 

유저 생성

유저를 생성하는 명령어는 다음과 같다. 주로 사용하는 명령어와 옵션은 다음과 같다.

postgres=# CREATE USER name [option..];

• SUPERUSER | NOSUPERUSER - superuser의 여부, 기본 값은 오른쪽이다.
• CREATEDB | NOCREATEDB - DB 생성 권한 여부, 기본 값은 오른쪽이다.
• CREATEUSER | NOCREATEUSER - user 생성 권한 여부, 기본 값은 오른쪽이다.
• PASSWORD 'password' - 'password'라는 비밀번호를 부여

예를 들어 다음과 같이 Superuser 권한을 가진 user를 패스워드가 blue인 red라는 이름의 유저를 생성할 수 있다.

postgres=# CREATE USER red PASSWORD 'blue' SUPERUSER;

 

데이터베이스 생성

유저의 생성을 마쳤다면 데이터베이스를 생성해줄 수 있다. 주로 사용하는 명령어와 옵션은 다음과 같다.

postgres=# CREATE DATABASE name [option..];

• OWNER - 데이터베이스 소유자 설정
• TEMPLATE - DB template에 의해 생성될 때 template 이름이다. 기본 값은 template1이다.
• ENCODING - data encoding 방법을 명시한다.
• TABLESPACE - Table Space를 임의로 설정할 때 사용한다.
• ALLOW_CONNECTION - 외부에서 DB 접속의 가능 여부를 설정한다.
• COONECTION_LIMIT - DB 접속 제한을 설정한다.

예를 들어 데이터베이스 이름이 green인 데이터베이스를 유저 red가 접근할 수 있게 설정하려면 다음과 같은 명령어를 쓴다.

postgres=# CREATE DATABASE green OWNER red;

 

해당 명령어를 통해 데이터베이스를 생성할 수 있다. 이렇게 만든 데이터베이스에 대하여 psql 명령어를 통해서 접근할 수 있게 된다. 위와 같은 과정을 통해서 패스워드가 stock이고 이름이 stock인 유저를 만들고, stock 데이터베이스를 만들어서 stock이라는 user에게 물려두었다. 그렇게 되면 다음과 같은 psql 콘솔 화면을 볼 수 있다.

 

$ psql stock
psql (14.9 (Homebrew))
Type "help" for help.

stock=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 stock     | Superuser                                                  | {}
 
 stock=# \dt
           List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | migrations | table | stock
 public | order      | table | stock
 public | stock      | table | stock
 public | user       | table | stock
(4 rows)

 

간단하게 local에서 개발하는 경우 데이터베이스 이름, 유저이름, 패스워드를 다 통일해서 DB 연결을 간단하게 하기 위해서 이렇게 설정해 두었는데, 실제 프로덕트에서는 절대 이렇게 하면 안 된다!!

 

datagrip을 사용하는 경우

datagrip이라는 데이터베이스 툴이 있다. IDE를 개발하는 Jetbrains에서 제공하는 툴인데, 대학생 라이센스 인증을 하면 jetbrains의 프로덕트들을 사용할 수 있게 되는데, 여기서 datagrip을 사용하는 경우 손쉽게 GUI를 통해 다양한 데이터베이스를 확인할 수 있게 된다.

 

윈도우 혹은 맥에서 설치를 마친 후 프로그램을 실행시키면 datagrip에서 좌측 상단에 있는 + 버튼을 눌러 연결하고자 하는 데이터베이스를 추가할 수 있다. 아래의 옵션은 현재 컴퓨터(localhost)에 있는 postgresql db 서버에 연결하는 부분인데, stock이라는 user의 역할을 통해서 stock이라고 하는 데이터베이스에 접근하겠다는 의미가 된다. 앞서 user, password, database를 모두 stock으로 설정해 두었기 때문에 stock으로 다 채운 후 연결을 시도하면 로컬 데이터베이스에 연결된다.

 

연결에 성공하고 나면 다음과 같이 테이블 다이어그램을 표시할 수 있는 기능 및 쿼리 기능 등 강력한 기능을 GUI로 사용할 수 있게 된다. 아래는 임시로 만들어 둔 stock 테이블들을 다이어그램으로 표시한 것이다.

마치며

이렇게 터미널에서 CLI를 통해서 postgresql 데이터베이스를 생성하고, datagrip을 통해서 GUI로 데이터베이스를 확인할 수 있게 하는 개발환경 세팅이 끝났다. 

참고

• [browndwarf] 알아두면 유용한 psql 명령어 정리 : https://browndwarf.tistory.com/51

감사합니다.