Install PostgreSQL on CentOS 6.3 [ 설치 , 백업 , 복구 ]

Install PostgreSQL  on CentOS 6.3

 

1.1 Change root user

su - ## OR ## sudo -i

1.2 Exclude CentOS own PostgreSQL Packages

Add exclude to /etc/yum.repos.d/CentOS-Base.repo file [base] and [updates] sections:[base] ... exclude=postgresql*   [updates] ... exclude=postgresql*

1.3 Install PostgreSQL 9.2 Repository

## CentOS 6 - i386 - 32-bit ## rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-i386/pgdg-centos92-9.2-6.noarch.rpm   ## CentOS 6 - x86_64 - 64-bit ## rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm   ## CentOS 5 - i386 - 32-bit ## rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-i386/pgdg-centos92-9.2-6.noarch.rpm   ## CentOS 5 - x86_64 - 64-bit ## rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-centos92-9.2-6.noarch.rpm

1.4 Install PostgreSQL 9.2 with YUM

yum install postgresql postgresql-server postgresql-contrib

2. Configure PostgreSQL 9.2

2.1 Initialize Cluster with initdb Command

su - postgres -c /usr/pgsql-9.2/bin/initdb

2.2 Set PostgreSQL Server to Listen Addresses and Set Port

/var/lib/pgsql/9.2/data/postgresql.conf

 

listen_addresses = '*' port = 5432
------
listen_addresses = 'localhost' port = 5432-------listen_addresses = '192.1.2.33' port = 5432

2.3 Set PostgreSQL Permissions

/var/lib/pgsql/9.2/data/pg_hba.conf

 

# Local networks host all all xx.xx.xx.xx/xx md5 # Example host all all 10.20.4.0/24 md5 # Example 2 host test testuser 127.0.0.1/32 md5

2.4 Start PostgreSQL Server and Autostart PostgreSQL on Boot

## Start PostgreSQL 9.2 ## service postgresql-9.2 start ## OR ## /etc/init.d/postgresql-9.2 start   ## Start PostgreSQL 9.2 on every boot ## chkconfig --levels 235 postgresql-9.2 on
[root@rfid data]# service postgresql-9.2 start
Starting postgresql-9.2 service: [ OK ]
[root@rfid data]#

[root@rfid data]#
[root@rfid data]# service postgresql-9.2 restart
Stopping postgresql-9.2 service: [ OK ]
Starting postgresql-9.2 service: [ OK ]
[root@rfid data]#
[root@rfid data]#

2.5 Create Test Database and Create New User

Change to postgres user

[root@rfid pgsql]#su postgresCreate test database (as postgres user)

Login test database (as postgres user)

Create New “testuser” Role with Superuser and Password

test=# CREATE ROLE testuser WITH SUPERUSER LOGIN PASSWORD 'test';

Test Connection from localhost (as Normal Linux User)

bash-4.1$ psql -h localhost -U [유저명] [DB명]
bash-4.1$ psql -h localhost -U ctrtopen ctrtopen
bash-4.1$ psql -h localhost -U testuser test

3.2. Add following line before -A INPUT -j REJECT

 

3.3 Restart Iptables Firewall

service iptables restart ## OR ## /etc/init.d/iptables restart

 

3.4 Test remote connection

psql -h dbserver_name_or_ip_address -U testuser -W test

 

 

 

----------- windows 백업 -----------------------------

1. pgpass 스크립트 작성

   pgpass 스크립트 파일 생성

    c:\backup\pgpass.conf

         ##### 백업 스크립트를 위한 pgpass 파일

         #hostname:port:database:username:password
          localhost:5432:DB_NAME:USERNAME:PASSWORD

 

2. 백업스크립트 작성

#### c:\backup\pg_backup.bat 

####

@echo off
SET PGPASSFILE=c:\backup\pgpass.conf

 

 

set hour=%time:~0,2%

if "%hour:~0,1%" == " " set hour=0%hour:~1,1%

echo hour=%hour%

set min=%time:~3,2%

if "%min:~0,1%" == " " set min=0%min:~1,1%

echo min=%min%

set secs=%time:~6,2%

if "%secs:~0,1%" == " " set secs=0%secs:~1,1%

echo secs=%secs%

 

set year=%date:~-4%

echo year=%year%

set month=%date:~3,2%

if "%month:~0,1%" == " " set month=0%month:~1,1%

echo month=%month%

set day=%date:~0,2%

if "%day:~0,1%" == " " set day=0%day:~1,1%

echo day=%day%

 

set datetimef=%year%%month%%day%_%hour%%min%%secs%

set hms=%hour%%min%%secs%

echo datetimef=%datetimef%

 

set datestr=%month%_%day%_%year%

set YYYYMMDD=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%

set BACKUP_FILE=%YYYYMMDD%_%hms%.backup

 

"C:\Program Files\PostgreSQL\9.2\bin\pg_dump"  -i -h localhost -p 5432 -U ctrtopen  -F c -b -v --file=c:\temp\ctrtopen_%BACKUP_FILE% ctrtopen

 

3. 작업예약설정하기

   매일 새벽 1시에 백업 받기

rem schtasks /create /tn "1.pg_backup_daily" /tr  c:\backup\pg_backup.bat     /sc daily  /st 01:00:00 /sd 2008/06/12

 

 

----------- linux 백업 ----------------------------

1. 백업 script작성

  ## vi  /var/lib/pgsql/backup/pg_backup.sh
  ##  chmod +x    /var/lib/pgsql/backup/pg_backup.sh
  ## backup script
  pg_dump  -i -h localhost -p 5432 -U ctrtopen  -F c -b -v --file=ctrtopen_`date +'%Y%m%d'`  ctrtopen
  pg_dumpall -f dball_`date +'%Y%m%d'`

 

2. crontab 에 등록

### crontab -e
#### backup start every day 2:10
10 2 * * * /var/lib/pgsql/backup/pg_backup.sh

 

 

 

 

 

 

--------------------복구 -------------------------------

1. 위의  설치과정을 동일하게 작업한다.

    DB명  , 유저명 , 패스워드등을 동일하게 설정함.

 

2. 백업파일을 확보함 ( 가장 최신파일 또는 문제없는 최신파일로 ... )

  

3. DB를 백업파일로 부터 스키마 및 데이터를 생성함.

bash-4.1$ pg_restore -h [localhost] -U [DB유저명]  -d [DB명]    [파일명]
bash-4.1$ pg_restore -h localhost   -U ctrtopen    -d ctrtopen  ctrtopen_20130107.backup
bash-4.1$
bash-4.1$

 

----------------------------------------------------------------------------------------------