upgrade backup and restore postgres with streaming replication

back up :

/usr/local/pgsql/bin/pg_dumpall > /opt/db/backupfile.dat


shutdown old server

/usr/local/pgsql/bin/pg_ctl -D /opt/db/data stop -m fast

rename directory

mv /usr/local/pgsql /usr/local/pgsql_old

mv /opt/db/data /opt/db/data_old

------------------------------------------------------------
prepare primary
install postgresql 9.02

./configure --with-openssl --with-tcl --enable-thread-safety ----enable-multibyte=UNICODE
make
make install
mkdir /opt/db/data
chown postgres /opt/db/data
su - postgres
/usr/local/pgsql/bin/initdb -D /opt/db/data

create folder
archive
pg_log
create pghba.conf  ---> see the old pg_hba.conf
create postgresql.conf (SR)  ---> see the old postgresql.conf

for streaming replication see :http://masis-9.blogspot.com/2011/12/streaming-replikasi-di-postgresql.html

start postgres

/usr/local/pgsql/bin/pg_ctl -D /opt/db/data start

---------------------------------------------------------------
prepare the slave

./configure
make
make install
mkdir /opt/db/data
chown postgres /opt/db/data
/usr/local/pgsql/bin/initdb -D /opt/db/data

config the slave for streaming replication see : http://masis-9.blogspot.com/2011/12/streaming-replikasi-di-postgresql.html

primary :
su - postgres
$/usr/local/pgsql/bin/psql -c "SELECT pg_start_backup('label',true)"
$rsync -a -v -e ssh /opt/db/data/ 192.168.168.210:/opt/db/data/ --exclude postmaster.pid
$ /usr/local/pgsql/bin/psql -c "SELECT pg_stop_backup()"

edit postgresql.conf at slave :
hot_standby = on

copy recovery.conf to slave
edit recovery.conf tersebut :
restore_command = 'cp  /opt/db/data/archive/%f "%p"'
standby_mode = 'on'
primary_conninfo = 'host=192.168.1.210 port=5432 user=postgres'         #ip primary
trigger_file = '/opt/db/data/failover'

start postgres at slave
--------------------------------------------------------------

make sure steaming running well

di standby :
$/usr/local/pgsql/bin/psql -c "SELECT pg_last_xlog_receive_location()"
di primary  :
$/usr/local/pgsql/bin/psql -c "SELECT pg_current_xlog_location()"

di primary :
$ ps -ef | grep sender
di standby
$ ps -ef | grep receiver

-----------------------------------------------------------
streaming replication running well

at primary restore database
contoh restore dr 192.168.188.31 ke 192.168.188.131
/usr/local/pgsql/bin/psql -f backupfile160112f.dat postgres

/usr/local/pgsql/bin/ls
 /opt/db/backupfile.dat

===================================================================
if anything going to worst  you can 
prepare back to old postgres

shuttingdown slave
/usr/local/pgsql/bin/pg_ctl -D /opt/db/data stop -m fast

shuttingdown primary
/usr/local/pgsql/bin/pg_ctl -D /opt/db/data stop -m fast

make sure both ( slave and primary ) database not running

rename new database
mv /usr/local/pgsql /usr/local/pgsql_new
mv /opt/db/data /opt/drop database jsxdfeed;db/data_new

rename back the old database

mv /usr/local/pgsql_old  /usr/local/pgsql
mv /opt/db/data_old  /opt/db/data

start the old postgres
/usr/local/pgsql/bin/pg_ctl -D /opt/db/data start
-------------------------------------------------------
=====================================================
======================================================

/usr/local/pgsql/bin/pg_dump -h 192.168.88.23 -U lima -Fc jsxdfeed -f jsxdfeed.dump -v
=========================================================================================
/usr/local/pgsql/bin/pg_dumpall -h 192.168.1.220 -f bacupdata.dat  -U totok

/usr/local/pgsql/bin/pg_dumpall -h 192.168.1.220 -f bacupdata.dat -v /
/usr/local/pgsql/bin/pg_dump -h 192.168.1.220 -f bacupdata.dat  -U totok -Fc jsxdfeed
---------------------------------------------------------------
/usr/local/pgsql/bin/psql -f backupfile.dat postgres