본 가이드는 sql postgreSQL 핵심 가이드 및 실전 아키텍처의 핵심 개념, 실전 구성 명령어 및 아키텍처 베스트 프랙티스를 다룹니다. 클라우드 네이티브 및 인프라 엔지니어링 실무에 즉시 활용 가능하도록 체계적으로 정리했습니다.
📑 Table of Contents
Basic
CREATE USER keycloak PASSWORD 'keycloak' SUPERUSER;
CREATE DATABASE test_keycloak OWNER keycloak;
-- **DB 데이터베이스 생성**
CREATE DATABASE *dbname* OWNER *ownnerName*DB Access
-- **DB 접속**
psql -U [DB사용자계정] [데이터베이스명]
-- **Postgre SQL shell 진입시**
psql [스키마명]psql #default account postgres
psql -U {user} -d {database} -c "{query}" #postgres 유저, airflow Database(schema)DB System
Grant
GRANT ALL PRIVILEGES ON ALL TABLES IN schema public TO postgres;
GRANT ALL PRIVILEGES ON DATABASE keycloak TO postgres;Show
show stuffs
-- Show CONFIG_PATH
show config_file;Table
-- **DB 실행중인 쿼리 조회**
SELECT * FROM pg_stat_activity ORDER BY query_start ASC;
-- Setting 조회
SELECT name, setting FROM pg_settings;
--
SELECT * FROM PG_DATABASE;
SELECT * FROM PG_USER;
select * from PG_ROLES;
-- 테이블 릴레이션 사이즈 조회
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size( schemaname || '.' || tablename )) AS size
FROM pg_tables
WHERE NOT (schemaname = 'pg_catalog' OR schemaname = 'information_schema')
ORDER BY pg_total_relation_size( schemaname || '.' || tablename ) DESC;표현식
-- **DB 데이터베이스 출력**
\l or \list
-- **DB 데이터베이스 선택**
\c [데이터베이스 명]
-- **DB 테이블 출력**
\dt
-- **테이블 구조 조회**: 오라클의 DESCRIBE TABLE # \d+ 테이블명 # \d+ 테이블명
-- **DB 해당 테이블 정보 출력**
\d [테이블명]
\d+ [테이블명]
-- **DB 나가기(종료)**
\q
-- **DB 사용자 권한 정보**
\du
-- **DB 출력 변경**
\x
-- **DB 쿼리 결과값 파일 저장(쿼리 결과값을 출력하지 않고 파일에 저장됨)**
\o [파일경로]\l # list of databases(schema)
\r # clear query buffer
\q # quit session
\{}+ # additional information
\dt # List of relations(type=table)
\ds # List of relations(type=sequence)
\du # List of roles on Users
\d {table} # Show scheme for Table public.jobBackup & Restore
# **SQL문 파일을 실행** $ psql -U [사용자명] [DB명] < [SQL파일명]
# **파일로부터 DB로 dump, 또는 DB로부터 파일로 dump** # COPY 테이블명 [(컬럼명1, 컬럼명2,...)] FROM '파일명' # COPY 테이블명 [(컬럼명1, 컬럼명2,...)] TO '파일명' 구분자를 지정하지 않으면 기본적으로 탭으로 지정됨. 전체 옵션은 아래와 같음.[http://www.postgresql.org/docs/9.2/static/sql-copy.html](http://www.postgresql.org/docs/9.2/static/sql-copy.html)참고
# **파일로 dump**
pg_dump -d [db명] -U [user] -F t > [파일명.tar]
- d, --dbname : Backup할 Database 명.
- h, --host : Database 주소.
- U, --username : Database 접속 시 User ID
- F, --format : Backup Format. 필자는 주로 tar 파일로 backup하기 때문에 't'를 사용한다.
- f, --file : Backup File Name
- t, --table : 특정 Table만 Backup하려할 때 대상이 되는 Table 명
- j, --jobs : Backup 시 병렬 처리 여부와 그 정도.
- v, --verbose : 진행 과정 표시.
- **dump to restore**
pg_restore -h localhost -U postgres -C -d postgres -F t [dumpFile]
- d, --dbname : Restore하는 Database 명.
- h, --host : Database 주소.
- U, --username : Database 접속 시 User ID
- F, --format : Restore File의 Format.
- t, --table : 특정 Table만 Restore하려할 때 대상이 되는 Table 명
- j, --jobs : Restore 시 병렬 처리 여부와 그 정도.
- v, --verbose : 진행 과정 표시
- C, --create : Target DB를 새로 만들면서 Restoration 진행.
- c, --clean : Restoration 시에 같은 이름의 Database Object가 발견되면 Drop 후에 Create하게 함.
- O, --no-owner : 원본 DB의 Owner가 복구할 위치에 존재하지 않을 경우 복구 시 다량의 에러가 발생한다. 이를 막기 위해 DB 복구시 OWNER를 명시하지 않고 진행하게 함.Spacing
— Delete 이후 DB가 사용하는 storage 클리어
PostgreSQL에서는 ‘VACUUM’과 ‘VACUUM FULL’이 모두 데이터베이스 스토리지를 관리하고 최적화하는 데 사용되지만 동작과 목적이 다릅니다. 둘 사이의 차이점은 다음과 같습니다.
- VACUUM:
‘VACUUM’은 데드 로우 제거, 통계 업데이트 등을 통해 저장 공간을 확보하고 성능을 향상시키는 데 사용되는 명령이다. 데이터 수정(예:
DELETE,UPDATE또는INSERT)을 수행할 때 PostgreSQL은 행의 이전 버전을 “dead”로 표시하지만 즉시 제거하지는 않습니다. 이는 단편화와 비효율성을 초래할 수 있습니다.
‘VACUUM’ 명령에는 여러 가지 모드가 있으며 일상적인 정리를 수행하고 데드 행을 제거하고 데이터베이스의 모든 테이블과 인덱스에 대한 통계를 업데이트하는 옵션 없이 실행할 수 있습니다. 이는 비차단 작업입니다. 즉, 데이터베이스가 온라인이고 액세스 가능한 동안 수행할 수 있습니다.
VACUUM;또한 ‘VACUUM’과 함께 특정 옵션을 사용하여 개별 테이블이나 인덱스를 대상으로 지정할 수 있습니다.
- ‘VACUUM FULL’은 일반적으로 작업 기간 동안만 테이블을 잠그고 전체 테이블을 다시 작성하므로 시간이 많이 걸리고 리소스 집약적일 수 있으므로 일반적으로 일반적인 사용에는 권장되지 않습니다. 이는 일반적으로 주요 데이터 수정 후 상당한 양의 공간을 회수해야 할 때 사용됩니다.
2. VACUUM FULL:VACUUM FULL은VACUUM명령의 보다 공격적인 형태입니다. 테이블의 새 복사본을 생성하고 활성 행만 복사한 다음 원본 테이블을 새 복사본으로 교체하여 스토리지 공간을 회수합니다. 이 프로세스는 조각화를 줄이고 스토리지를 최적화하는 데 도움이 됩니다. 그러나 새 테이블을 생성하기 때문에 VACUUM FULL 프로세스가 진행되는 동안 테이블에 대한 다른 작업을 효과적으로 방지하기 위해 VACUUM되는 테이블에 대한 배타적 잠금이 필요합니다.
VACUUM FULL your_table;배타적 잠금 및 잠재적인 성능 영향으로 인해 ‘VACUUM FULL’은 일반적으로 주의해서 사용되며 상당한 스토리지 재확보가 필요한 상황에서만 사용됩니다.
요약하면, 정기적인 유지 관리 및 공간 회수를 위해 정기적인 VACUUM을 사용하고, VACUUM FULL을 아껴서 일정 기간 동안 독점적으로 테이블을 잠그려는 의지로 대규모 정리를 수행해야 하는 경우에만 사용하는 것을 고려하십시오. 데이터베이스 및 진행 중인 작업에 미치는 영향을 고려하여 항상 제어된 방식으로 이러한 작업을 수행하십시오.
Features
Airflow
- delete failed tasks
# psql node
sudo -i
su -- postgres
psql -U postgres -d airflow -c "delete from dag_run where dag_id='${DAG_ID}' AND state='failed';"
Leave a Reply