Postgresql 설치및 간단한 사용방법정리!

 

alter user postgres with password  'password';

 

systemctl restart postgresql-12

참고 URL : https://valuefactory.tistory.com/491

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

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 이다.

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

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

새로운 데이터베이스를 생성하고 이 데이터베이스로 변경한다. 데이터베이스 생성은 MySQL과 동일하다.

create database (DB명)이다.

create database cym; 이다.

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

CREATE DATABASE cym;
CREATE USER tommypagy1 WITH ENCRYPTED PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE cym TO tommypagy1;

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

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

create SCHEMA (스키마 명) 을 입력해 새로운 스키마를 생성한다. 스키마 명, 디비 명, 테이블 명은 모두 대소문자를 구별하지 않는 듯 하다. Owner는 스키마의 소유자를 뜻하는데,
# create SChEMA cymschema; (스키마 명) 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 tommypagy (
      ID serial PRIMARY KEY,
     url VARCHAR (255) NOT NULL,
     name VARCHAR (255) NOT NULL,
     description VARCHAR (255),
     rel VARCHAR (50)
  );

생성된 테이블들은\dt명령어로 확인할 수 있다. 삭제는 drop table 진행

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

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

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

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

# insert 후 select 까지 진행!

 

select * from tommypagy ;

PostgreSQL을 사용하면서 관리 측면에서 자주 사용되는 쿼리를 모아보았습니다.

​

접속된 사용자 확인

SELECT pid, datname, usename, query FROM pg_stat_activity;

Active 세션 확인

SELECT datname, usename, state, query FROM pg_stat_activity WHERE state='active';

현재 실행중인 SQL 상태정보 확인

SELECT current_timestamp - query_start AS runtime, datname, usename, query FROM pg_stat_activity WHERE state='active' ORDER BY 1 DESC;

1분 이상 실행되는 쿼리 확인

SELECT current_timestamp - query_start AS runtime, datname, usename, query FROM pg_stat_activity WHERE state='active' AND current_timestamp - query_start > '1 min' ORDER BY 1 DESC;

쿼리를 process title에 보이도록 설정

@postgresql.conf -> update_process_title = on

현재 수행중인 SQL 전체 조회  select datname, pid, usename, application_name, client_addr, client_port, backend_start, query_start, wait_event_type, state, backend_xmin query from pg_stat_activity;

 

---

PostgreSQL도 다른 DBMS와 마찬가지로 Database에 대한 export와 import 기능을 제공하고 있습니다. PGDATA 경로를 그대로 복사하는 방법도 있으나 개인적으로 이관할때 신규로 설치하고 export/import 기능을 활용하는 방법을 좋아합니다.

​

Export: pg_dump 명령어 이용

pg_dump -p {포트번호} {database명} > {dump파일명}

Import: psql 명령어 이용

psql -f {dump파일명} {database명}

---

DB전체를 이관하지 않고 테이블 단위로 export / import하는 방법은 다음의 방법을 주로 사용합니다. (csv 파일생성) 이 방법은 테이블 단위 백업용도로도 자주 사용됩니다.

​

Export: psql로 접속

\copy 테이블명 to ‘파일명' csv;

Import: psql로 접속

 

\copy 테이블명 from ‘파일명' delimiter ‘,’ csv;

[출처] PostgreSQL 관리 쿼리 및 export / import|작성자 카르마

펄코나 모니터링 툴  : https://rastalion.me/%EC%98%A4%ED%94%88%EC%86%8C%EC%8A%A4-%EB%AA%A8%EB%8B%88%ED%84%B0%EB%A7%81-%ED%88%B4-pmm2-mysql-mariadb-postgresql-mongo/

오픈소스 모니터링 툴 PMM2 (MySQL, MariaDB, PostgreSQL, Mongo) - RastaLion IT Blog