PostgreSQL CLI

 

Ubunt Terminal을 이용하여 PostgreSQL제어


1. DB기능


1) Dump파일 받기

- 해당 RDS에 설치된 postgres서버의 Dump파일을 받습니다(AWS의 RDS에서 터미널로 받음), sql로 받음

$ pg_dump -h [RDS address] -U [DB user] [DB name] > [file name]



2) Dump파일 업로드하기

- 해당 RDS에 설치된 postgres서버의 Dump파일을 업로드 합니다.(AWS의 RDS의 DB는 비어있어야 합니다.), sql로 백업

$ psql -h [RDS address] -U [DB user] -d [upload DB name] -f [file name]


tar확장자로 daump파일 백업하고 업로드 하기(click)


1) Dump파일 받기

- 해당 RDS에 설치된 postgres서버의 Dump파일을 받습니다(AWS의 RDS에서 터미널로 받음), tar로 받음

$ pg_dump -U [DB user] -h [PostgreSQL address] -p [Port Number] -d [DB name] -f [file path with .tar] -F t -W

OR Including Password

$ PGPASSWORD="password" pg_dump -U [DB user] -h [PostgreSQL address] -p [Port Number] -d [DB name] -f [file path with .tar] -F t -W


2) Dump파일 백업

- 해당 RDS에 설치된 postgres서버의 Dump파일을 업로드(AWS의 RDS에서 터미널로 받음), tar로 업로드

주의 : 해당 cli는 기존의 DB의 테이블을 지우고 다시 백업합니다.

$ pg_restore -cC -h [PostgreSQL address] -p [Port Number] -U [DB user] -d [DB name] [file path with .tar]

OR Including Password

$ PGPASSWORD="password" pg_restore -cC -h [PostgreSQL address] -p [Port Number] -U [DB user] -d [DB name] [file path with .tar]

-c : 복원하기 전에 존재하는 DataBase를 삭제(Drop)합니다. (소문자 c)

-C: DataBase를 생성합니다. 위의 -c와 갖이 사용할 경우 DB를 삭제하고 다시 생성합니다. (대문자 C)


링크1 : 참고 자료

링크2 : 참고 영상


3) DB에 연결된 세션 강제 종료

- 해당 DB에 연결된 세션을 강제 종료한다.

PostgreSQL세션 강제 종료하기

참고 링크 : Error : PostgreSQL에서 세션 접속을 강제 종료하기(ERROR:  database "DB Name" is being accessed by other users)

$ SELECT 

    pg_terminate_backend(pid) 

FROM 

    pg_stat_activity 

WHERE 

    -- don't kill my own connection!

    pid <> pg_backend_pid()

    -- don't kill the connections to other databases

    AND datname = 'test2';


4) 쿼리로 출력한 결과를 CSV파일로 저장하기(remote)

- 원격으로 접속한 PC에서 쿼리 결과를 CSV파일로 받기

$ \copy ([SELECT Query]) To [string : file path] With CSV DELIMITER ',';


4) 쿼리로 출력한 결과를 CSV파일로 저장하기(local)

- Local PC에서 쿼리 결과를 CSV파일로 받기

$ COPY (table or sql_query) TO [out_file_name] WITH DELIMITER ',' CSV HEADER;



5) PostgreSQL의 postgresql.conf파일 위치 확인

- Linux터미널을 이용하여 postgresql.conf파일 경로를 확인합니다.

$ sudo -u postgres psql -c 'SHOW config_file'

postgres를 외부에서 접속 가능하게 하기 위해서 해당 링크를 참고해 주시기 바랍니다.


2. DB 기타


1) postgres에 터미널로 연결하기

- 해당 데이터베이스에 설치된 postgres서버로 연결을 합니다.(AWS의 RDS에서 터미널로 받음)

$ psql -h [RDS address] -p [port number] -d [DB name] -U [DB user] -W


- 이후 해당 계정의 비밀번호를 입력해야 합니다.


2) Local에 연결하기

- 해당 Local에 설치된 postgres서버로 연결을 합니다.

$ sudo -i -u postgres


- 이후 Local암호를 입력후 psql을 입력하면 postgres에 접속하게 됩니다.


3. postgres SQL


1) 연결상태 확인하기

- 해당 DB의 접속정보를 확인합니다.

$ \conninfo


- 이후 접속정보, 사용자, 소켓, 포트가 나타납니다.


2) PostgreSQL버전 확인하기

- SQL문을 이용하여 postgres버전을 확인합니다.

$ SELECT version()


- 이후 버전정보가 나타납니다.


3) PostgreSQL의 유저정보 확인하기

- SQL문을 이용하여 postgres에 있는 유저정보를 확인합니다.

$ SELECT * FROM PG_SHOWDOW


- 이후 유저정보가 나타납니다.


4) PostgreSQL의 유저 등록하기

- SQL문을 이용하여 postgres에 있는 유저를 등록합니다.

$ CREATE USER [User Name] PASSWORD '[User Password]'(string) [OPTION]


- OPTION

- [SUPERUSER, NOSUPERUSER]

- [CREATEUSER, NOCREATEUSER]

- [INHERIT, NOINHERIT]

- [CONNECTION LIMIT connlimit]

- [ENCRYPTED, NOCRYPTED]

- [LOGIN, NOLOGIN]

- [CREATEDB, NOCREATEDB]


5) PostgreSQL의 유저 비밀번호 변경

- SQL문을 이용하여 postgres에 있는 유저의 비밀번호를 변경합니다.

$ ALTER USER [User Name] WITH PASSWORD '[User Password]'(string)


4. postgres SQL : CRUD


1) PostgreSQL로 여러 Record UPDATE하기

- SQL문을 이용하여 postgres에 있는 Record들을 UPDATE합니다.

참고 링크

$ UPDATE [UPDATE TABLE]

      SET

      [UPDATE COLUM2] = c.column_b,

      [UPDATE COLUM3]=c.column_c

      FROM(

      VALUES

      ([Value1-1],[Value1-2],[Value1-3]),

      ([Value2-1],[Value2-2],[Value2-3])

      ) AS c(column_a, column_b, column_c)

      WHERE c.column_a=[FIND COLUMN1];


2) PostgreSQL로 여러 Record 문자열을 변경하기

- SQL문을 이용하여 postgres에 있는 table의 Record들의 문자열을 변경합니다.

$ UPDATE [Table_Name] SET [Column_Name] = replace([Column_Name],'Original String','Replace String');


3) 날짜별로 카운팅하기

- SQL문을 이용하여 postgres에 있는 table의 Record들을 날짜별로 GROUP BY한 다음 표로 나타냅니다. 아래 DATE_TRUNCE로 변환시 date를 GROUP BY해야 합니다.

$ SELECT DATE_TRUNCE('day', [Column time -> "created_at"]) AS date , COUNT(*) FROM [Table Name] GROUP BY date

참고링크 : PostgreSQL 날짜별로 횟수 조회하기(DATE_TRUNC)


5. postgres SQL : etc


1) PostgreSQL에서 해당 DB의 용챵 확인

- SQL문을 이용하여 postgres에 있는 해당 DB의 용량을 MB단위로 출력함니다..

$ SELECT pg_size_pretty(pg_database_size('log'))



2) PostgreSQL에서 해당 DB를 임시보관하고 수정하기 : 잘못될 경우 수정하기 전 DB로 되돌릴수 있음

참고 링크 : PostgreSQL DB를 transaction 하고 수정, 삭제, 삽입을 걱정없이 하기

2_1) PostgreSQL에서 해당 DB를 임시보관하기(transaction)

- DB를 조작하기 전에 안전대책으로 Beign transaction을 실행해서 수정을 해도 수정하기 전으로 되돌릴수 있게 한다.

$ Beign transaction


2_2) PostgreSQL에서 해당 수정한 DB를 롤백(수정 이전상태) 하기(rollback)

- DB를 수정했지만 다시 원복

$ rollback


2_3) PostgreSQL에서 해당 수정한 DB를 commit(수정한 상태로 적용) 하기

- DB를 수정하고 해당 commit 를 저장한다

$ commit


3) 기타기능

3_1) PostgreSQL에서 터미널 사용후 지워지거나 자워지지 않게 하기

- 화면 크기에 따라서 postgres는 데이터를 보여주지만 나갈시 지워지는 현상이 있다. 이걸 방지하기 위해서 pager off을한다. (pager on시 기능이 켜짐)

$ \pset pager off



댓글

이 블로그의 인기 게시물

Lesson 12_1 프로퍼티 노드(Property Node)

DAQ로 전압 측정하기-2

Lesson 12_2 참조를 이용한 프로퍼티노드(Property Node)