PostgreSQL

psql 사용하기

Start / Stop

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

Create User

현재 사용자 확인

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;

Config file

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 Remote Connect

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

Configuring postgresql.conf

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

Install

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 생성

Database 생성

CREATE DATABASE garam;

Database 삭제

DROP DATABASE garam;

접속

postgresql.conf –> pg_hba.conf

목차