• 설정하기
• Backup
• Install
• Table Description
• Recursive Query - Oracle의 START WITH, CONNECT BY PRIOR와 유사
• postgresql 서비스
• Table, Column Comment보기
• Backup And Restore
• Upgrade
• Replication 구축하기
• Apt 등록
• pg_upgrade를 이용한 Version Up
• VACUUM

• psql 사용하기

macOS에서 Server.app이 있으면 postgres가 기본으로 설치되어 있다. 9.4, 12.4 → 9.4는 잘되는데 12.4는 어떻게 하는지 모르겠다. brew을 이용하여 설치하ㅡㄴ 것을 추천한다.

• initDB

  initdb --locale=C -E UTF-8 /usr/local/var/postgres
  initdb --locale=ko_KR -E UTF-8 /usr/local/var/postgres
  initdb -D /mnt/maro/postgreSQL/garam@16 --auth-local peer --auth-host scram-sha-256 --no-instructions

• Start the PostgreSQL Server.

  postgres -D /usr/local/var/postgres
  pg_ctl -D /usr/local/var/postgres start
  pg_ctl -D /usr/local/var/postgres stop
  pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

• Stop the PostgreSQL Server.

  pg_ctl -D /usr/local/var/postgres -m smart stop      # oracle shutdown
  pg_ctl -D /usr/local/var/postgres -m fast stop       # oracle immediate
  pg_ctl -D /usr/local/var/postgres -m immediate stop  # oracle abort
   
  // 서비스 중지
  -w 작업이 끝날때 까지 기다림
  -W 작업이 끝날때 까지 기다리지 않음
  // stop, ,restart 때 사용할 수 있는 옵션들
  -m 중지방법 (smart, fast, immediate) 중 하나
   
  smart : 모든 클라이언트가 연결이 끊기게 되면 중지
  fast : 클라이언트의 연결을 강제로 끊고 정상적으로 중지
  immediate : 무조건 중지. 다시 시작할때 복구 작업을 할 수도 있음
   
  pg_ctl stop -mf -w -D /usr/local/var/postgres
  pg_ctl -D /usr/local/var/postgres stop -s -m fast

• 현재 사용자 확인

SELECT * FROM PG_SHADOW;
SELECT * FROM pg_user;

• \du를 입력하면 USER들이 가지고 있는 ROLE들을 확인 할 수 있습니다. SUPERUSER인 postgres는 SUPERUSER, CREATE ROLE, CREATE DB, REPLICATION기능을 가지고 있습니다.

\du
\du+

• SQL statement

SELECT u.usename AS "Role name",
  CASE WHEN u.usesuper AND u.usecreatedb THEN
            CAST('superuser, create database' AS pg_catalog.text)
       WHEN u.usesuper THEN
            CAST('superuser' AS pg_catalog.text)
       WHEN u.usecreatedb THEN
            CAST('create database' AS pg_catalog.text)
       ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;

• Create User

CREATE SCHEMA test;
CREATE USER xxx PASSWORD 'yyy';
ALTER USER xx password 'yyy';
GRANT ALL ON SCHEMA test TO xxx;
GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;
ALTER schema test owner TO xxx;
ALTER DATABASE mydb SET search_path TO boen;

• Sample

CREATE ROLE testuser LOGIN PASSWORD 'password';
CREATE TABLESPACE testtablespace OWNER testuser LOCATION '/pgdata/testdbtablespace';
CREATE SCHEMA testschema AUTHORIZATION testuser;
ALTER SCHEMA testschema OWNER TO testuser;
CREATE DATABASE testdb WITH ENCODING 'UNICODE' OWNER testuser;
ALTER DATABASE testdb SET search_path TO testschema;
ALTER ROLE testuser SET search_path TO testschema;
GRANT ALL ON DATABASE testdb TO testuser;
ALTER ROLE testuser SET default_tablespace = testtablespace;
ALTER DATABASE testdb SET default_tablespace = testtablespace;

• 사용자생성

$ sudo -u postgres createuser --interactive

• 사용자생성

postgres=# CREATE ROLE postgres SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION BYPASSRLS;

• postgresql.conf - 가장 중요한 설정화일입니다. 할당되는 메모리량, 데이터화일의 물리적 위치, 리스닝하고 있는 IP 정보등을 설정합니다. 오라클의 init<SID>.ora 과 같은 역할

listen_addresses = '*'

• pg_hba.conf - 데이터베이스에 접속 시도하는 유저들을 제어합니다. (쉽게 말하면 방화벽 같은 역할..비유가 적절한가..쩝). 굳이 비유하자면, 오라클에서도 sqlnet.ora 화일에서 특정 IP 를 블럭킹할수 있죠.

host all all 192.168.1.1/24 md5

• pg_ident.conf - os 인증유저와 PostgreSQL 유저와의 매핑 설정을 하는 화일입니다. 근데, 잘 안쓰여요.

SELECT name, setting, category
FROM pg_settings
WHERE category = 'File Locations';

psql -h repository.mama.kr -U postgres -d postgres

Configuring postgresql.conf

$ find \ -name "postgresql.conf"
/var/lib/pgsql/9.4/data/postgresql.conf

apt-get install postgresql

10 main 5432 down postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

$ sudo -u postgres psql

Database 생성

CREATE DATABASE garam;

Database 삭제

DROP DATABASE garam;

• postgresql.conf –> pg_hba.conf