[PostgreSQL] Back up 및 Recovery 방법

BackUp 및 Recovery 방법 #

• pg_dumpall -U admin > /var/lib/postgresql/dump/backup_20231205.sql

• psql -U admin > -f /var/lib/postgresql/dump/backup_20231205.sql testdb

   1command:
   2- /bin/bash
   3- -c
   4- |
   5  ln -snf /usr/share/zoneinfo/Asia/Seoul /etc/localtime
   6  su
   7  cd
   8  echo "test-svc.test-ns.svc.cluster.local:5432:postgres:admin:admin1!" > .pgpass
   9  echo "Save pgpass"
  10  chmod 600 ~/.pgpass
  11  BACKUP_DIR="/var/lib/postgresql/dump"
  12  BACKUP_FILE="(date +"%Y-%m-%d_%H-%M-%S")_day
  13  echo $BACKUP_FILE
  14  echo "Start BackUp"
  15  pg_dump -U admin -w -h test-svc.test-ns.svc.cluster.local -p 5432 -T tb_test -F c postgres > ${BACKUP_DIR}/${BACKUP_FILE}.sql
  16  echo "Finish BackUp"
  17  echo "Start Compress"
  18  cd ..
  19  tar -zcvf /var/lib/postgresql/$BACKUP_FILE.tar.gz /var/lib/postgresql/$BACKUP_FILE.sql
  20  echo "Finish Compress"
  21  rm -rf /var/lib/postgresql/$BACKUP_FILE.sql
  22  echo "Deleted SQL File"

• pg_restore -U admin -C -Fc -d postgres 20231205010801.sql

   1command:
   2- /bin/bash
   3- -c
   4- |
   5  ln -snf /usr/share/zoneinfo/Asia/Seoul /etc/localtime
   6  su
   7  cd
   8  echo "test-svc.test-ns.svc.cluster.local:5432:postgres:admin:admin1!" > .pgpass
   9  echo "Save pgpass"
  10  chmod 600 ~/.pgpass
  11  BACKUP_DIR="/backups"
  12  BACKUP_FILE="(date +"%Y-%m-%d_%H-%M-%S")_day
  13  echo $BACKUP_FILE
  14  echo "Start BackUp"
  15  pg_dump -U admin -w -h test-svc.test-ns.svc.cluster.local -p 5432 -T tb_test -F c postgres > ${BACKUP_DIR}/${BACKUP_FILE}.sql
  16  echo "Finish BackUp"
  17  echo "Start Compress"
  18  cd ..
  19  tar -zcvf /backups/$BACKUP_FILE.tar.gz /backups/$BACKUP_FILE.sql
  20  echo "Finish Compress"
  21  rm -rf /backups/$BACKUP_FILE.sql
  22  echo "Deleted SQL File"