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세션 강제 종료하기
$ 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
댓글
댓글 쓰기