Hot Standby is the name for the capability to run queries on
a database that is currently performing archive recovery. Log Shipping
replication allows you to create one or more standby nodes that are replicas of
the primary node (or master node). Standby nodes can then be used for read-only
query access.
NOTE : Both your
primary and your secondary need to be running the same major version of the
postgreSQL database.
Pre-process
recommendations
§
Set up your standby host's
environment and directory structure exactly the same as your primary. Otherwise
you'll need to spend time changing any symlinks you've created on the primary
for xlogs, tablespaces, or whatnot which is really just opportunity for error.
§
Pre-configure both the
postgresql.conf and recovery.conf files for your standby. I usually keep all of
my different config files for all of my different servers in a single,
version-controlled directory that I can then check out and symlink to. Again,
consistent environment & directory setups make symlinks your best friend.
§
Use ssh keys for simply,
and safely, transferring files between hosts. Configure password postgres user primary to secondary
by below link..
§
Follow all of the advice
in the manual with respect to handling errors.
Here Primary server(master) test_db01 and
secondary server (slave) test_db02.
Archive location -- /u01/psqlwarch
PGDATA Location -- /var/lib/pgsql/9.4/data/
1 -- (Primary server(master) test_db01) ( By postgres user ) Modify the /var/lib/pgsql/9.4/data/postgresql.conf. Set the following
parameters
archive_mode = on
archive_command = '
/var/lib/pgsql/9.4/data/archive_command.sh %p %f'
# command to use to archive a logfile
segment
#archive_command = 'rsync -a %p
postgres@test_db02:/u01/psqlwarch/%f'
#archive_timeout = 3600
wal_level = hot_standby
listen_addresses = '*'
max_wal_senders = 3
checkpoint_segments = 8
wal_keep_segments = 8
script for archive mode (cat /var/lib/pgsql/9.4/data/archive_command.sh)
*********************************************************
cat /var/lib/pgsql/9.4/data/archive_command.sh
#!/bin/sh
# archive command to archive the WAL files to the archive
directory
# It also writes the name to a file which can later be
picked up by a backup command
# First argument is the full path to the WAL file and the
second argument is the filename
#REPLICANTS="test_db01"
REPLICANTS2="test_db02"
FULLPATH=$1
FILENAME=$2
echo $FULLPATH $FILENAME >> /usr/tmp/debug
export ARCHIVE_DIR=/u01/psqlwarch
cp -i $FULLPATH ${ARCHIVE_DIR}/${FILENAME}
for REPLICA in $REPLICANTS2; do
scp $FULLPATH
$REPLICA:/u01/psqlwarch
done
echo ${FILENAME}>>${ARCHIVE_DIR}/wal_files.txt
2 -- (Primary server(master) test_db01) ( By postgres user ) Restart postgres to apply the changes
(with out
downtime)
/usr/pgsql-9.4/bin/pg_ctl restart -D /var/lib/pgsql/9.4/data
(with
downtime)
(stop postgres)
/usr/pgsql-9.4/bin/pg_ctl stop -mf -D /var/lib/pgsql/9.4/data
(start postgres) /usr/pgsql-9.4/bin/pg_ctl -D /var/lib/pgsql/9.4/data -l logfile start
Note -- Verify that the WALs are
being shipped to their destination.
postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/7000088
(1 row)
3 -- (Primary server(master) test_db01) ( By postgres user )
start the hot backup
3.1-> echo "SELECT
pg_start_backup('mybackup');" | psql -U postgres
-bash-4.1$ echo "SELECT
pg_start_backup('mybackup');" | psql -U postgres
pg_start_backup
-----------------
0/A000020
(1 row)
3.2 -> Create base
backup by tar or rsync command.
-bash-4.1$ pwd
/var/lib/pgsql/9.4/
-bash-4.1$ tar -cvf
/u01/psqlwarch/hotbackup.tar data
3.3 -> Copy base
backup from primary to secondary server
Cd /u01/psqlwarch/
scp hotbackup.tar
postgres@test_db02:/var/lib/pgsql/9.4/
4 -- (secondary server (slave) test_db02) ( By postgres user ) (Optional ) If postgres running then we'll stop the standby server to prepare standby system.
-bash-4.1$ /usr/pgsql-9.4/bin/pg_ctl stop -mf -D
/var/lib/pgsql/9.4/data
5 -- (secondary server (slave) test_db02) ( By postgres user )
Restore base backup
5.1-> remove old
data directory
Cd /var/lib/pgsql/9.4/
Rm - rf data
5.2-> Restore base
backup
Cd /var/lib/pgsql/9.4/
tar -xvf hotbackup.tar
chown -R postgres:postgres data
6 -- (Primary server(master) test_db01) ( By postgres user ) With the backup completed, log back into the primary system and clear
the backup flag
6.1-> echo
"SELECT pg_stop_backup();" | psql -U postgres
-bash-4.1$ echo "SELECT
pg_stop_backup();" | psql -U postgres
pg_stop_backup
----------------
0/A000088
(1 row)
7 -- (secondary server (slave) test_db02) ( By postgres user )
7.1-> Modify the /var/lib/pgsql/9.4/data/postgresql.conf. Set the following
parameters
archive_mode = off
hot_standby = on
7.2-> create a recovery.conf file in /var/lib/pgsql/9.4/data/ with the following
contents. This tells postgres to start up implement the log files.
Cd /var/lib/pgsql/9.4/data/
vi recovery.conf
standby_mode =on
restore_command = 'cp -i
/u01/psqlwarch/%f %p'
7.3-> Start up postgres on
the standby server. It will start restoring data right away..
-bash-4.1$/usr/pgsql-9.4/bin/pg_ctl
start -D /var/lib/pgsql/9.4/data
server starting
7.4-> Verify standby server
-bash-4.1$ ps -ef | grep postgres
postgres 2483
1 0 14:26 pts/0 00:00:00 /usr/pgsql-9.4/bin/postgres -D
/var/lib/pgsql/9.4/data
postgres 2484
2483 0 14:26 ? 00:00:00 postgres: logger process
postgres
2485 2483 0 14:26 ? 00:00:00 postgres: startup process recovering 000000010000000000000030
postgres 2487
2483 0 14:26 ? 00:00:00 postgres: checkpointer process
postgres 2488
2483 0 14:26 ? 00:00:00 postgres: writer process
postgres 2489
2483 0 14:26 ? 00:00:00 postgres: stats collector
process
Cd /var/lib/pgsql/9.4/data/pg_log
-bash-4.1$ tail -l Tuesday.log
< 2015-07-02 14:29:05.063 IST
>LOG: restored log file
"00000001000000000000002B" from archive
cp: cannot stat
`/u01/psqlwarch/00000001000000000000002C': No such file or directory
cp: cannot stat
`/u01/psqlwarch/00000001000000000000002C': No such file or directory
< 2015-07-02 14:29:45.175 IST
>LOG: restored log file
"00000001000000000000002C" from archive
< 2015-07-02 14:29:45.230 IST
>LOG: restored log file
"00000001000000000000002D" from archive
cp: cannot stat
`/u01/psqlwarch/00000001000000000000002E': No such file or directory
< 2015-07-02 14:29:45.241 IST
>LOG: unexpected pageaddr 0/29000000
in log segment 00000001000000000000002E, offset 0
Note : -- Execute below command on Primary server(master)
test_db01 and check new archive entry
come in standby log.
postgres=# select pg_switch_xlog();
(Primary
server(master) test_db01)
testdb=# create table ho as select * from company;
SELECT 5
testdb=# \dt
List of relations
Schema |
Name | Type |
Owner
--------+---------------+-------+----------
public | company |
table | postgres
public | department | table | postgres
public | ho | table | postgres
public | hot | table | postgres
public | hot1 | table | postgres
public | man | table | postgres
public | manoj_company | table | postgres
public | n_company | table | postgres
(8 rows)
postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/30019428
(1 row)
testdb=# SELECT txid_current_snapshot();
txid_current_snapshot
-----------------------
1240:1240:
(1 row)
(secondary
server (slave) test_db02)
postgres=# SELECT txid_current_snapshot();
txid_current_snapshot
-----------------------
1240:1240:
(1 row)
postgres=# SELECT
pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=# \c testdb
You are now connected to database
"testdb" as user "postgres".
testdb=# \dt
List of relations
Schema |
Name | Type |
Owner
--------+---------------+-------+----------
public | company | table | postgres
public | department | table | postgres
public | ho | table | postgres
public | hot | table | postgres
public | hot1 | table | postgres
public | man | table | postgres
public | manoj_company | table | postgres
public | n_company | table | postgres
(8 rows)
testdb=# \q
Doing the Failover
(Promoting a Standby to
the Main server)
-bash-4.1$ /usr/pgsql-9.4/bin/pg_ctl
promote -D /var/lib/pgsql/9.4/data
server promoting
-bash-4.1$ ps -ef | grep postgres
postgres 2483 1 0
14:26 pts/0 00:00:00
/usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data
postgres 2484 2483 0
14:26 ? 00:00:00 postgres: logger
process
postgres 2487 2483 0
14:26 ? 00:00:00 postgres:
checkpointer process
postgres 2488 2483 0
14:26 ? 00:00:00 postgres: writer
process
postgres 2489 2483 0
14:26 ? 00:00:00 postgres: stats
collector process
postgres 3854 2483 0
15:56 ? 00:00:00 postgres: wal
writer process
postgres 3855 2483 0
15:56 ? 00:00:00 postgres:
autovacuum launcher process
1.
Get your clients pointing to the new server
(change DNS, change IPs in configuration files, etc.)
Note
: Postgres will want to rename the recovery.conf file to recovery.done once it’s finished.
********************************************************************************
No comments:
Post a Comment