Steps to setup the streaming replication in postgres without PGPOOL

Step 1 : Login to the primary and ensure the backup has happened.
Step 2 : Login to the secondary and ensure the applications are not connected. Get the details of the hostname and the PGDATA location
Step 3 : Enable password less authentication between primary and secondary/
Step 4 : Go to the PGDATA location and execute the base_backup.sh file with the required parameters as shown below.

-bash-4.1$ echo $PGDATA
/store/pgdata
-bash-4.1$ ./base_backup.sh /store/pgdata lnxpg1b /store/pgdata
+ PGDATA=/store/pgdata
+ REMOTE_HOST=lnxpg1b
+ REMOTE_PGDATA=/store/pgdata
+ RECOVERY_LOG=/var/log/pgpool/recovery.log
+ DSDATA=/store/postgresql/data
+ SSH_OPTIONS='-T -o StrictHostKeyChecking=no'
+ log 'running backup as UID:26'
++ date
+ echo 'Tue Dec 16 04:43:13 EST 2014 running backup as UID:26'
+ log DATA=/store/pgdata
++ date
+ echo 'Tue Dec 16 04:43:13 EST 2014 DATA=/store/pgdata'
+ log RECOVERY_TARGET=lnxpg1b
++ date
+ echo 'Tue Dec 16 04:43:13 EST 2014 RECOVERY_TARGET=lnxpg1b'
+ log RECOVERY_DATA=/store/pgdata
++ date
+ echo 'Tue Dec 16 04:43:13 EST 2014 RECOVERY_DATA=/store/pgdata'
+ log 'on lnxpg1b: removing /store/pgdata and /store/postgresql/data'
++ date
+ echo 'Tue Dec 16 04:43:13 EST 2014 on lnxpg1b: removing /store/pgdata and /sto
+ log 'on lnxpg1b: running pg_basebackup...'
++ date
+ echo 'Tue Dec 16 04:43:13 EST 2014 on lnxpg1b: running pg_basebackup...'
+ ssh -T -o StrictHostKeyChecking=no lnxpg1b '
rm -rf /store/pgdata /store/postgresql/data
/usr/bin/pg_basebackup -h lnxpg1a -D /store/pgdata -U postgres -v -P -x -c fast
ERRCODE=$?
rm -f /store/pgdata/postgresql.trigger /store/pgdata/recovery.done
exit $ERRCODE'
transaction log start point: 0/CD000028 on timeline 3
669922/669922 kB (100%), 7/7 tablespaces
transaction log end point: 0/CD0000F0
pg_basebackup: base backup completed
+ checkResult 0 '/usr/bin/pg_basebackup -h lnxpg1a -D /store/pgdata -U postgres -v -P -x -c fast'
+ '[' 0 -eq 0 ']'
+ log '/usr/bin/pg_basebackup -h lnxpg1a -D /store/pgdata -U postgres -v -P -x -c fast finished with exit code 0'
++ date
+ echo 'Tue Dec 16 04:43:54 EST 2014 /usr/bin/pg_basebackup -h lnxpg1a -D /store/pgdata -U postgres -v -P -x -c fast finished with exit code 0'
+ log 'on lnxpg1b: set up recovery.conf'
++ date
+ echo 'Tue Dec 16 04:43:54 EST 2014 on lnxpg1b: set up recovery.conf'
+ ssh -T -o StrictHostKeyChecking=no lnxpg1b '
cd /store/pgdata;
cat > recovery.conf << EOT
standby_mode = '\''on'\''
primary_conninfo = '\''host=lnxpg1a port=5432 user=postgres password=zantaz'\''
restore_command = '\''cp /store/postgresql/archives/%p %f'\''
trigger_file = '\''/store/pgdata/postgresql.trigger'\''
EOT
'

Step 5: Once this is done, login to the secondary database server as postgres user. Check the recovery.conf file and ensure the standby_mode is set to ON. Also, ensure all the directories are copied to the secondary from primary.
Step 6 : start the database using the pg_ctl start command.
Step 7 : Test the replication process by creating some test table in the primary.


[root@lnxpg1b store]# su - postgres
-bash-4.1$ /usr/pgsql-9.3/bin/pg_ctl start
server starting
-bash-4.1$ 2014-12-16 04:46:36 EST [21937]: [1-1] user=,db=LOG:  redirecting log output to logging collector process
2014-12-16 04:46:36 EST [21937]: [2-1] user=,db=HINT:  Future log output will appear in directory "pg_log".

-bash-4.1$
-bash-4.1$ ps -ef | grep wal
postgres 21946 21937  0 04:46 ?        00:00:00 postgres: wal receiver process
postgres 21948 21902  0 04:46 pts/0    00:00:00 grep wal
-bash-4.1$ psql
psql (9.3.5)
Type "help" for help.

postgres=# \q
-bash-4.1$ psql
psql (9.3.5)
Type "help" for help.

postgres=# SELECT pg_is_in_recovery()
postgres-#
postgres-#
postgres-# ;
 pg_is_in_recovery
-------------------
 t
(1 row)

postgres=# \q
-bash-4.1$ ps -ef | grep wal
postgres 21946 21937  0 04:46 ?        00:00:00 postgres: wal receiver process
postgres 21956 21902  0 04:48 pts/0    00:00:00 grep wal
-bash-4.1$ psql
psql (9.3.5)
Type "help" for help.

postgres=# \c dbac
You are now connected to database "dbac" as user "postgres".
dbac=# create table kt_test (eno int);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
dbac=# \d kt_test
    Table "public.kt_test"
 Column |  Type   | Modifiers
--------+---------+-----------
 eno    | integer |

dbac=# select * from kt_test;
 eno
-----
   1
(1 row)

dbac=#