PostgreSQL 기본 테스트 - psql

  이전 포스트에서 PostgreSQL 12를 설치해보았습니다.

PostgreSQL 12 설치 on Windows 10

  이제 SQL Shell (psql)을 사용한 기본 테스트 작업으로 데이터베이스가 정상적으로 작동하는지 확인해보도록 하겠습니다.

 

1. SQL Shell (psql) 실행

  윈도우 시작 메뉴의 PostgreSQL 디렉토리 내 Command line 기반으로 데이터베이스를 컨트롤할 수 있는 SQL Shell (psql)이 존재합니다.

  이 SQL Shell을 사용하여 사용자, 테이블스페이스, 데이터베이스, 테이블을 생성하고 샘플 데이터를 입력하는 테스트를 진행해보도록 하겠습니다.

  SQL Shell을 클릭하여 프로그램을 실행하면 아래와 같이 접속하고자 하는 PostgreSQL의 접속 정보를 입력해야 합니다.

  접속하고자 하는 PostgreSQL의 Server IP(혹은 도메인), Database 이름, Port 번호, User명, 비밀번호 등을 입력합니다. 만약 대괄호 안에 명시된 기본 값이 접속 정보와 일치한다면 별다른 입력없이 엔터만 입력하면 됩니다.

  비밀번호를 입력하면 데이터베이스 명이 표시된 프롬프트를 만날 수 있습니다.

 

  참고로 아래 예제와 같이 명령 프롬프트에서 직접 psql을 실행할 수도 있습니다.

> psql -h 호스트명 -d 데이터베이스명 -p 포트번호 -U 사용자명

 

 

 

2. 사용자 계정 생성

  데이터베이스를 설치한 후에는 운영을 위한(테스트 용도인 경우에도 마찬가지입니다.) 사용자 계정과 데이터베이스를 신규로 생성하여 사용하는 것이 좋습니다. DBMS에서 기본적으로 제공해주는 데이터베이스나 사용자를 사용하는 것은 보안상의 이슈가 될 수 있으며, 관리적인 측면에서도 효율성이 떨어지기 때문입니다.

 

  그럼 사용자 계정을 생성해보도록 하겠습니다.

  'CREATE USER' SQL을 이용하여 사용자 계정을 생성할 수 있으며, 비밀번호를 지정하여 사용자 계정을 생성하는 문법은 아래와 같습니다.

CREATE USER 사용자명
  [PASSWORD '비밀번호'];

 

  아래는 'postadmin'이라는 사용자 계정을 생성하는 SQL 예제입니다. 계정의 비밀번호는 'postAdmin'로 지정하였으며, SUPERUSER 권한을 부여해줬습니다.

 CREATE USER postadmin PASSWORD 'postAdmin' SUPERUSER;

  참고로 psql에서 '\du' 명령을 사용하면 접속한 데이터베이스 서버 내 사용자 목록과 각 사용자에게 부여된 롤을 확인하실 수 있습니다.

 

  더 자세한 CREATE USER 구문 문법은 아래 링크에서 확인하실 수 있습니다.

* URL : https://www.postgresql.org/docs/12/sql-createuser.html

 

 

 

3. 테이블스페이스 생성

  'CRAETE TABLESPACE' 구문으로 테이블스페이스를 생성할 수 있으며, 필수적으로 LOCATION 키워드를 사용하여 데이터 파일이 저장될 위치(디렉토리)를 명시해줘야 합니다.

CREATE TABLESPACE 테이블스페이스명
  [OWNER 사용자명]
  LOCATION '데이터파일명';

 

  아래는 TBS_TEST라는 테이블스페이스를 생성하는 예제입니다. 테이블스페이스의 소유자는 postadmin으로 지정하였으며, 데이터 파일이 저장될 위치는 'D:\tbs_postgresql'로 설정하였습니다.

CREATE TABLESPACE TBS_TEST
OWNER postadmin
LOCATION 'D:\tbs_postgresql';

  테이블스페이스 생성이 완료되면 '\db' 명령어를 사용하여 생성된 테이블스페이스 목록을 확인하실 수 있습니다.

  더 자세한 CREATE TABLESPACE 구문 문법은 아래 링크에서 확인하실 수 있습니다.

* URL : https://www.postgresql.org/docs/12/sql-createtablespace.html

 

 

 

4. 데이터베이스 생성

  'CRAETE DATABASE' 구문으로 데이터베이스를 생성할 수 있습니다.

CREATE DATABASE 데이터베이스명
    [ [ WITH ]
      [ OWNER = 사용자명 ]
      [ ENCODING = 캐릭터셋인코딩 ]
      [ TABLESPACE = 테이블스페이스명 ] ]; 

  아래는 testdb라는 데이터베이스를 생성하는 예제입니다. 데이터베이스의 소유자는 postadmin으로 지정하였으며, 인코딩은 'UTF8', 테이블스페이스는 tbs_test로 설정하였습니다.

CREATE DATABASE testdb
    WITH  
    OWNER = postadmin 
    ENCODING = 'UTF8' 
    TABLESPACE = tbs_test;

 

  데이터베이스 목록은 '\list' 명령어를 사용하여 확인할 수 있으며, '\c 데이터베이스명' 명령어를 사용하여 접속 데이터베이스를 변경할 수 있습니다.

  더 자세한 CREATE DATABASE 구문 문법은 아래 링크에서 확인하실 수 있습니다.

* URL : https://www.postgresql.org/docs/12/sql-createdatabase.html

 

 

 

5. 테이블 생성

  'CREATE TABLE' 구문으로 테이블을 생성할 수 있습니다. 

CREATE TABLE 테이블명 (
  [ { 컬럼명 데이터타입 } [ , ... ] ]

 

  테스트용으로 테이블 test_tab을 생성해보겠습니다. test_tab 테이블은 숫자형 ID 컬럼과 문자형 NAME 컬럼으로 구성됩니다.

CREATE TABLE test_tab (
  id INTEGER,
  name VARCHAR(30)
);

  '\dt' 명령을 통해 데이터베이스 내 테이블 목록을 확인할 수 있습니다.

  더 자세한 CREATE TABLE 구문 문법은 아래 링크에서 확인하실 수 있습니다.

* URL : https://www.postgresql.org/docs/12/sql-createtable.html

 

 

6. 데이터 입력 및 조회

  INSERT 구문으로 생성된 테이블에 데이터를 입력하며, 입력된 데이터는 SELECT 구문으로 조회가 가능합니다.

 

  아래는 test_tab 테이블에 테스트용 데이터 1 row를 삽입하는 예제입니다.

INSERT INTO test_tab VALUES (1, 'ABCDE');

  입력된 데이터는 아래 예제와 같이 SELECT문으로 조회가 가능합니다.

 SELECT * FROM test_tab;

 

 

 

7. pgAdmin 사용

  pgAdmin은 PostgreSQL에서 제공해주는 그래픽 기반 관리 툴 프로그램입니다.

시작 메뉴를 통해 pgAdmin을 실행하실 수 있으며, 웹 브라우저에서 URL(http://127.0.0.1:59594/browser)을 입력하여 직접 접속할 수도 있습니다.

 

  pdAdmin에 접속하면 아래와 같이 화면 왼쪽의 Browser를 통해서 데이터베이스 서버 내 데이터베이스, 사용자 롤, 테이블스페이스 등을 계층 구조로 탐색할 수 있습니다.

 

  직접 쿼리문을 사용하여 데이터 관리/조회 작업도 가능합니다.

  pgAdmin 페이지 상단 메뉴 중 'Tools'을 클릭하면 아래와 같이 하위 메뉴가 펼쳐집니다.

 

  위 하위 메뉴 중 'Query Tool'을 클릭하면 아래와 같이 쿼리를 작성하여 실행하고 결과도 확인할 수 있는 패널이 생성됩니다.

 

  Query editor에 TEST_TAB 테이블을 조회하는 SELECT문을 작성한 후, 도구 모음 중 실행 버튼(▶)을 클릭하거나 'F5'를 입력하면 쿼리가 실행됩니다.

  쿼리 실행 결과는 위와 같이 브라우저 하단에서 그리드 형태로 확인이 가능합니다.

 

 

  이상으로 postgreSQL의 기본적인 사용법을 살펴보았습니다. 더 자세한 내용은 아래 URL을 참고하시면 됩니다.

[Ref.] https://www.postgresql.org/docs/12/index.html

        https://www.postgresql.org/docs/current/app-psql.html