PostgreSQL 7.2.2-1 백업/복구

PostgreSQL 백업 (dump)

* written by skyer9

* 원본출처 : http://www.episode.co.kr/contents/postgresql/dump.jsp

1. 모든 디비를 덤프받는다.

[root@episode root]# mkdir db

[root@episode root]# chmod 777 db

[root@episode root]# su - postgres -c "pg_dumpall > /root/db/backup.sql"

PostgreSQL 복구 (restore)

1. PostgreSQL 을 재설치한다.

이 내용은 다른 페이지에서 설명한다.

2. 백업받은 디비를 복구한다.

[root@episode root]# /etc/rc.d/init.d/postgresql stop

[root@episode root]# rm -rf /var/lib/pgsql/data

[root@episode root]# /etc/rc.d/init.d/postgresql start

[root@episode root]# su - postgres -c "psql -f /root/db/backup.sql template1"

[root@episode root]# vi /var/lib/pgsql/data/pg_hba.conf

[root@episode root]# cat /var/lib/pgsql/data/pg_hba.conf

......

# TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE AUTH_ARGUMENT

local all ident sameuser

host all 127.0.0.1 255.255.255.255 password

host all 서버아이피 255.255.255.255 password

[root@episode root]# vi /var/lib/pgsql/data/postgresql.conf

[root@episode root]# cat /var/lib/pgsql/data/postgresql.conf

......

tcpip_socket = true

......

[root@episode root]# /etc/rc.d/init.d/postgresql start

PostgreSQL 백업자동화 (cron)

1. 스크립트를 생성한다.

[root@episode root]# vi /root/bin/pg_backup

[root@episode root]# chmod 700 /root/bin/pg_backup

[root@episode root]# cat /root/bin/pg_backup

#!/bin/sh

# 이전에 /root/backup/postgresql 이란 디렉토리가 있어야한다.

su - postgres -c "pg_dumpall > /var/lib/pgsql/`date +%Y%m%d`.sql"

mv /var/lib/pgsql/`date +%Y%m%d`.sql /root/backup/postgresql/

2. 크론에 등록한다.

[root@episode root]# crontab -e

5 3 * * * /bin/sh /root/bin/pg_backup [root@episode root]# crontab -l