Initial setup on Master server:
1)
Create
ssh key on master and copy it to standby
Syntax : ssh-keygen
Copy the ssh key to standby server
Syntax : ssh-copy-id
postgres@ipadress
e.g. ssh-copy-id
postgres@192.168.1.125
2)
Test
copying a file from master to standby without entering password.
3)
Shutdown
Master database
4)
Navigate
to location /var/lib/pgsql/15/data and modify the content of postgres.conf file
and edit parameters archive_on, archive_command and archive_timeout.
Archive_command:
rsync -a %p postgres@ipaddress:/location of archive folder
cd /var/lib/pgsql/15/data
vi postgres.conf
Below the specimen of
postgres.conf file and we have to make the changes as highlighted.
|
File andarchive_mode = on # enables archiving; off, on, or always |
|
# (change
requires restart) |
|
#archive_library
= '' # library to use to
archive a logfile segment |
|
# (empty
string indicates archive_command should |
|
# be used) |
|
archive_command = 'rsync -a %p
postgres@192.168.1.125:/var/lib/pgsql/15/archive/%f' # command to use to archive a logfile
segment |
|
#
placeholders: %p = path of file to archive |
|
# %f = file name only |
|
# e.g. 'test
! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' |
|
archive_timeout = 60 # force a logfile segment switch
after this |
|
# number of
seconds; 0 disables |
Initial Setup Standby Database:
1)
Create a archive directory on
standby which is accessible by Master user.
cd /var/lib/pgsql/15
mkdir archive
2)
Login as
root and Shutdown postgresql on standby
-bash-4.2$ su -
Password:**********
[root@srv2 ~]# systemctl
stop postgresql-15
3)
Delete all the contents of
/Data directory
cd /var/lib/pgsql/15/data
rm –rf *
Steps:
1)
Startup postgresql on Master
database
2)
Connect to the database using
psql shell
3)
Issue connect pg_start_backup
Syntax : select pg_backup_start(‘dbrep’);
postgres=# select
pg_backup_start('dbrep');
pg_backup_start
-----------------
0/3000028
(1 row)
4)
Copy Data directory for master
to standby.
Syntax : rsync -avz
/var/lib/pgsql/15/data/* postgres@192.168.1.125:/var/lib/pgsql/15/data/
5)
End backup on master.
Syntax : select
pg_backup_stop();
6)
Comment out archive_on,
archive_command and archive_timeout parameters in postgresql.conf in standby
server.
7)
Setup recovery command in
postgresql.conf
Restore_command = ‘cp
/var/lib/pgsql/12/archive/%f %p’
SPECIMEN of
postgresql.conf in standby. Commentout highlighted in red and add the parameter
marked in green.
|
# - Archiving - |
|
|
|
#archive_mode = on # enables archiving; off, on,
or always |
|
# (change
requires restart) |
|
#archive_library = ''
# library to use to archive a logfile segment |
|
# (empty string indicates archive_command should |
|
# be used) |
|
#archive_command
= 'rsync -a %p postgres@192.168.1.125:/var/lib/pgsql/15/archive/%f' # command to use to archive a
logfile segment |
|
# placeholders: %p = path of file to archive |
|
# %f = file name
only |
|
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p
/mnt/server/archivedir/%f' |
|
#archive_timeout = 60 # force a logfile segment switch
after this |
|
# number of seconds; 0 disables |
|
|
|
# - Archive Recovery - |
|
|
|
# These are only used in recovery mode. |
|
|
|
restore_command = 'cp
/var/lib/pgsql/15/archive/%f %p' #
command to use to restore an archived logfile segment |
|
# placeholders: %p = path of file to restore |
|
# %f = file name only |
8)
Create a standby.signal file in
data directory.
touch standby.signal
9)
Start postgres on standby
server
10)
Check the log files whether the
postgres has entered standby mode and accepting read only connections.
cd /var/lib/pgsql/15/data/log
-bash-4.2$ ls -lrth
total 224K
-rw-------. 1 postgres
postgres 5.1K Aug 17 04:36 postgresql-Sat.log
-rw-------. 1 postgres
postgres 213K Dec 8 02:49 postgresql-Sun.log
Test Standby Database:
1)
Try to create table and insert
few records on master and check whether they are populated
On the standby .
2 )
try to create a table on standby or delete any records on standby
Failover:
1)
Shutdown postgresql on master
and check whether it affects standby
2)
Promote your standby database
to primary. Use any of the 3 methods
Pg_ctl promote -D
/var/lib/pgsql/15/data
3)
Check signal.standy exist or
not.
4)
Bring up the postgresql old
master server and try to create a table there and check whether
Its replicated.
No comments:
Post a Comment