PostgreSQL 설치 및 사용 방법 정리 (리눅스 기준)

https://m.blog.naver.com/PostView.nhn?blogId=alice_k106&logNo=220847310053&proxyReferer=https%3A%2F%2Fwww.google.com%2F

본 포스트는 MySQL, MongoDB 등 다른 DB를 사용해 본 사람들을 대상으로 작성되었다. 왜냐면 필자가 써본 DB라고 해봤자 MySQL, MongoDB, SQLite(이건 SQL이라고 하기도 좀...) NoSQL은 Hive, HBase 등등.. 이기 때문이다. 이정도면 기본적으로 사용하는 것에는 문제가 없겠지 하는 정도로 postgreSQL을 정리해 보려고 한다. 필자도 익숙하지 않은 DB라서 삽질을 했으니 여러분은 안그러길 기원하며...

1. PostgreSQL

PostgreSQL은 한국어로 '포스트그레스큐엘' 이라고 읽는다. 이 이름도 유래가 있는 듯 하다. Postgre와 SQL을 나눠서 생각해보면 SQL을 사용할 수 있는 관계형 데이터베이스(RDBMS) 이라는 것을 암시하고 있는 듯 하다. Postgre 라는 것도 Post + Ingres의 합성어같다. Ingres DB의 후속작이라는 느낌이 있다.

즉, PostgreSQL는 MySQL, MariaDB와 비슷한 관계형 데이터베이스이다. 그러나 사용법은 꽤 상이하므로 따로 익혀두는 것이 좋다.

2. PostgreSQL 설치 방법

Ubuntu 14.04 에서 아래를 입력해 설치한다. 이 글을 작성하는 시점에서 설치되는 버전은 9.3.14 이다.

# apt-get install postgresql postgresql-contrib

MySQL Workbench 처럼 PG Admin 이라고 하는 PostgreSQL 전용 Web Workbench가 있는 듯 한데, 버그가 많은 모양이다. 굳이 다루지 않기로 했다.

3. CLI 사용하기

설치가 완료됬으면 postgreSQL의 CLI를 사용해 볼 차례다.

psql이라는 명령어로 postgreSQL의 CLI를 사용할 수 있다. 그러나 psql 명령어를 사용할 때, 데이터베이스 내의 사용자를 명시해야 한다. 그냥 root 상태에서 psql을 입력하면 아래와 같은 메시지를 뿜는다.

root@ip-172-31-5-35:~# psql   psql: FATAL:  role "root" does not exist

postgreSQL은 root 사용자를 보안상의 이유로 좋아하지 않는 듯 하다. postgres 사용자로 전환해야만 psql 명령어를 사용할 수 있다.

root@ip-172-31-5-35:~# su - postgres postgres@ip-172-31-5-35:~$ psql psql (9.3.14) Type "help" for help.     postgres=#

postgreSQL의 CLI로 접속했다. MySQL, MongoDB, HBase, Hive에서 했던 것처럼 똑같이 DB를 제어하면 된다.

4. postgreSQL의 구조

postgreSQL은 우리가 알던 DB와 조금 다른 구조가 있다. 중간에 스키마라고 하는 계층이 존재한다.

MySQL과 비교해보면 이해가 쉽다. 최상위 계층에 Database 라고 하는 레이어가 있다. 이는 우리가 흔히 아는 데이터베이스이다. create database .... 명령어로 생성하던 것이다. 이 데이터베이스는물리적으로 데이터를 구분해 놓은 것이다.

스키마는 데이터베이스 아래에 위치한 계층이다. 데이터베이스를 생성하면 기본적으로public 스키마가 존재한다. 스키마 레벨은 데이터베이스 내에서논리적으로 구분된 계층이다. 그리고 이 아래에 테이블들이 생성된다.

정리하면MySQL에서 쓰던 데이터베이스의 개념은 PostgreSQL의 스키마와 유사하다.1그러나 조금 다른 점은, PostgreSQL의 데이터베이스는 데이터의 물리적 집합을 정의하기 때문에 데이터베이스 내의 스키마들은 조인(JOIN) 이 가능하다는 점이다. 예를 들어 위 그림에서 public 스키마의 mytable은 myscheme의 mytable3과 조인이 가능하다(고 한다).

그럼 이제 스키마가 왜 있냐.. 라는 의문이 들 법 하다. 스키마를 사용하면 1. 여러 사용자가 서로 충돌 없이 하나의 데이터베이스를 사용하는 것이 가능하며 2. 데이터베이스를 여러 개의 논리적인 그룹으로 만들어 더욱 관리하기 쉽게 하고 3. Third-Party 애플리케이션이 서로 충돌할 일 없게 다른 스키마를 사용하도록 한다고 한다.2 또는 다른 사람 소유의 데이터베이스에 테이블을 생성할 수 있다 라는 이유라고도 하는데..3 확실하지는 않다.

이와 별도로, 데이터가 저장될 공간을 뜻하는 tablespace 라는 것이 있지만 아직은 이 단계까지 자세히 보지 않기로 했다. 각 데이터베이스는 tablespace라는 단위에 저장될 수 있는데, tablespace는 실제로 파일시스템에서 저장되는 위치를 의미한다고 한다.

5. postgreSQL 사용하기

(1) 데이터베이스 목록 확인하기

현재 데이터베이스의 목록을 확인하는 명령어는 \l (역슬래시와 소문자 L) 이다. 존재하는 데이터베이스를 확인해보자.

3개의 데이터베이스가 존재한다. postgres, template0, template1 이다. 우리는 지금 postgres 라는 데이터베이스에 접속해 있는데, 이는 쉘에서 확인할 수 있는postgres=# 에서 알 수 있다.

(2) 사용하는 데이터베이스 변경하기

사용하는 데이터베이스를 다른 데이터베이스로 변경해보자. 아래의 명령어를 입력한다. 이번에는 \c 이다.

정상적으로 데이터베이스가 변경되었으며, 쉘의 template1=# 에서 이를 확인할 수 있다.

(3) 데이터베이스 생성하기

새로운 데이터베이스를 생성하고 이 데이터베이스로 변경한다. 데이터베이스 생성은 MySQL과 동일하다.create database (DB명)이다.

(4) 유저 생성하기 권한주기

CREATE DATABASE yourdbname;
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;

(5) 스키마 목록 확인, 생성하기

\dn 을 입력해 스키마 목록을 확인할 수 있다. 데이터베이스를 생성하면 기본적으로 스키마는 public 하나밖에 없다.

create SCHEMA (스키마 명) 을 입력해 새로운 스키마를 생성한다. 스키마 명, 디비 명, 테이블 명은 모두 대소문자를 구별하지 않는 듯 하다. Owner는 스키마의 소유자를 뜻하는데,create SHCEMA (스키마 명) authorization (사용자 명)으로 설정할 수 있다.

(6) 테이블 목록 확인, 생성하기

테이블을 생성하는 명령어는 MySQL과 동일하다.create table (테이블 명) (test varchar(20) ... )이런 형식이다. 조금 다른 점은, 테이블을 생성할 때, 테이블이 속하게 될 스키마를 지정할 수 있다는 것이다. 스키마를 따로 지정하지 않으면 public 스키마에 테이블이 생성된다.

아래는 create tablealiceschema.alicetable(id varchar(20) primary key, pw varchar(20)); 으로 테이블을 생성했다. aliceschema 라는 스키마에 테이블을 생성한 경우이다.

• 예제코드(http://www.postgresqltutorial.com/postgresql-insert/)

  CREATE TABLE link (
      ID serial PRIMARY KEY,
     url VARCHAR (255) NOT NULL,
     name VARCHAR (255) NOT NULL,
     description VARCHAR (255),
     rel VARCHAR (50)
  );

생성된 테이블들은\dt명령어로 확인할 수 있다.

?!? 테이블이 없다고 나온다. \dt 명령어는 public 스키마에 속한 테이블만 보여주는 것 같다. 정확히는 search_path 에 설정된 스키마를 사용한다고 한다.4 스키마에 속한 테이블을 보려면\dt aliceschema.*를 입력한다.

테이블에 대한 자세한 정보를 보려면\d를 사용한다. \d는 public 스키마에 있는 테이블만 보여줄 것이므로, 특정 스키마에 존재하는 테이블을 보려면\d aliceschema.alicetable과 같은 형식으로 입력해야 한다.

(7) 테이블에 내용 넣기 (CRUD)

• 예제코드(http://www.postgresqltutorial.com/postgresql-insert/)

  INSERT INTO link (url, name)
  VALUES
  ('http://www.google.com','Google'),
  ('http://www.yahoo.com','Yahoo'),
  ('http://www.bing.com','Bing');

CRUD는 모두 기존의 SQL과 동일하므로 insert 하나만 설명한다. 딱히 특별할 것이 없기 때문이다.

insert into (테이블 명) values(....)를 쓰는데, 한 가지 다른 점이 있다면, 스키마를 지정하지 않으면 search_path의 스키마를 지정한다는 점이다. 따라서 특정 스키마의 테이블을 쓰고 싶다면 insert into(스키마 명).(테이블 명)... 처럼 쓴다. 아래는 aliceschema의 alicetable에 데이터를 넣는 예시이댜.