PostgreSQL Installer Download
Reference Document link
- https://wiki.postgresql.org/wiki/Streaming_Replication
- http://dmurthy-pgsql.blogspot.com/
- http://dba.stackexchange.com/questions/71515/streaming-replication-postgresql-9-3-using-two-different-servers
- http://prongs.org/blog/postgresql-replication
- https://www.digitalocean.com/community/tutorials/how-to-set-up-ssh-keys--2
Action Plan
- Install Postgres in both the Server
- Configure Password less ssh on both the server for Postgres user
- Modify Primary Side configuration
- Take Primary side Backup from Standby side
- Configure Standby Side
Environment Details
- Operating System Version : Redhat Linux 6.4 (64 Bit)
- PostgreSQL Version : 9.3.5
- RAM : 4 GB
- HDD : 120 GB.
Configuration Details
Server 1 (Primary)->
- IP Address : 192.168.0.21
- Hostname : postgres1.spectrum-bd.com
Server 2 (Standby)->
- IP Address : 192.168.0.22
- Hostname : postgres2.spectrum-bd.com
Postgres Installation Step
Install Postgres software on both the server's using below command
[root@postgres1 u01]# ./postgresql-9.3.5-3-linux-x64.run Installation Directory [/opt/PostgreSQL/9.3]: Data Directory [/opt/PostgreSQL/9.3/data]: Please provide a password for the database superuser (postgres). A locked Unix user account (postgres) will be created if not present. Password : Retype password : Port [5432]: Please choose an option [1] : Setup is now ready to begin installing PostgreSQL on your computer. Do you want to continue? [Y/n]: Please wait while Setup installs PostgreSQL on your computer. Installing 0% ______________ 50% ______________ 100% ######################################### Setup has finished installing PostgreSQL on your computer.
By default Postgres user will be created with the installation. Change the password for the postgres user using below command
[root@postgres1 ~]# passwd postgres Changing password for user postgres. New password: BAD PASSWORD: it is based on a dictionary word Retype new password: passwd: all authentication tokens updated successfully.
Primary Site Configuration
Setting up Streaming Replication with WAL Archiving
PostgreSQL tracks changes made to the database data files via a log called the write ahead log (WAL). The log exists mainly in case there is a system crash, so the database can be restored to a correct state by “replaying” the log entries made since the last checkpoint.
This method of relaying changes gives PostgreSQL users yet another option for backing up a database, in that one can combine the regular file system level backups with the backup of of the WAL files.
To enable the archiving of the WAL files onto another machines, whether it’s a slave database, an NFS mount or other media, PostgreSQL does not make any assumptions on how it is done and gives the flexibility for the administrator to decide.
Here we will be shipping the logs to the Standby machine. On the Primary server we will have to activate the archive mode. In conjunction with this method, we will also set up PostgreSQL's internal streaming replication service. Within master’s postgresql.conf file, we can begin to change some of the defaults:
[root@postgres1 ~]# su - postgres [postgres@postgres1 u01]# cd /opt/PostgreSQL/9.3/data [postgres@postgres1 data]$ vi postgresql.conf listen_addresses = '*' port = 5432 max_connections = 100 wal_level = hot_standby checkpoint_segments = 8 archive_mode = on archive_command = '/opt/PostgreSQL/9.3/data/ship_logs.sh %p %f < /dev/null' max_wal_senders = 3 wal_keep_segments = 8 hot_standby = on max_standby_archive_delay = 30s max_standby_streaming_delay = 30s
Create ship_logs.sh file required for archive backup and modify the parameters accordingly
[postgres@postgres1 data]$ vi ship_logs.sh #!/bin/sh # ## This is called to back up the WAL files to the slave. ## This is on top of replication and is used as another ## method to secure data successfully transferring from one ## database server to another. ARCHIVE_DIR_ON_SLAVE="/opt/PostgreSQL/9.3/walfiles" LOG=1 LOG_FILE="/tmp/postgres_wal_archiving.log" log() { echo "`date --rfc-3339=ns` $1" >> "$LOG_FILE"; } log_error() { echo "`date --rfc-3339=ns` $1" >> "$LOG_FILE"; exit 1; } wal_path="$1" wal_file="$2" backup_server="192.168.0.22" if [ $LOG -eq 1 ] then log "Transfering file to backup server, filename: $wal_file" fi rsync -e "ssh -p 22" "$wal_path" "$backup_server:$ARCHIVE_DIR_ON_SLAVE" if [ $LOG -eq 1 ] then if [ "$?" -eq 0 ]; then log "Transfer to slave server completed" else log_error "Sending $wal_file failed." fi fi
Once the script has been saved on the primary, from the shell, make it executable
[postgres@postgres1 data]$ chmod +x /opt/PostgreSQL/9.3/data/ship_logs.sh
Modify pg_hba.conf file and add below parameter value,
[postgres@postgres1 data]$ vi pg_hba.conf local all all trust host all all 192.168.0.0/24 trust host all all ::1/128 trust host replication postgres 192.168.0.22/24 trust
Create a directory for archive backup using below command
[postgres@postgres1 ~]$ mkdir /opt/PostgreSQL/9.3/walfiles [postgres@postgres1 ~]$ chmod 700 /opt/PostgreSQL/9.3/walfiles
After a restart of the PostgreSQL engine on the Primary server, PostgreSQL will start the continuous archiving of logs and moving them to the archive directory /opt/PostgreSQL/9.3/walfiles over on the Standby server. This will occur when the logs reach the check point size which is usually at 16MB (this setting can be tweaked on postgresql.conf on master but the defaults should be fine). At this point, PostgreSQL on Primary can be restarted.
Upon the restart, if you kept the LOG=1 variable on in the ship_logs.sh script, you should see this in /tmp/postgres_wal_archiving.log, and confirm the files are over on the Standby:
2015-02-11 13:59:31.376895207+06:00 Transfering file to backup server, filename: 00000001000000000000002B 2015-02-11 13:59:31.705610928+06:00 Transfer to slave server completed 2015-02-16 20:20:10.562084442+06:00 Transfering file to backup server, filename: 00000001000000000000002C 2015-02-16 20:20:10.836316969+06:00 Transfer to slave server completed 2015-02-23 18:20:17.241729500+06:00 Transfering file to backup server, filename: 00000001000000000000002D 2015-02-23 18:20:17.540416879+06:00 Transfer to slave server completed
Once you see that, it's probably okay to change the LOG value from a 1 to a 0.
Standby Side Configuration
Create a directory both in Primary and Standby for archive backup using below command
[postgres@postgres2 ~]$ mkdir /opt/PostgreSQL/9.3/walfiles [postgres@postgres2 ~]$ chmod 700 /opt/PostgreSQL/9.3/walfiles
Prior to finishing the setup on the Standby, we need to copy the data directory from Primary to catch the Standby up, and then start up replication. We will need pg_basebackup to help us copy the data directory from Primary.
$ sudo service postgresql stop # This will remove the data directory on the slave - careful! $ rm -rf /opt/PostgreSQL/9.3/data $ mkdir /opt/PostgreSQL/9.3/data $ chown postgres.postgres /opt/PostgreSQL/9.3/data $ chmod 700 /opt/PostgreSQL/9.3/data
Use the below command to backup primary database backup from standby side
[postgres@postgres2 data]$ ./pg_basebackup -x -R -P -D /opt/PostgreSQL/9.3/data -h 192.168.0.21
PostgreSQL will show the status of the base backup from master as it is going. When completed, you should see the output as follows:
36924/36924 kB (100%), 1/1 tablespace NOTICE: pg_stop_backup complete, all required WAL segments have been archived pg_basebackup: base backup completed
One last step is to create a recovery.conf file which will essentially turn on replication on the standby. This file, recovery.conf, should be within /opt/PostgreSQL/9.3/data on the standby
[postgres@postgres2 data]$ vi recovery.conf standby_mode = 'on' primary_conninfo = 'user=postgres password=postgres host=192.168.0.21 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres' #archive_cleanup_command = '/opt/PostgreSQL/9.3/bin/pg_archivecleanup /opt/PostgreSQL/9.3//walfiles %r' restore_command='cp /opt/PostgreSQL/9.3/walfiles/%f "%p"' recovery_target_timeline='latest'
Ensure standby database postgresql.conf file has the below parameter values
[postgres@postgres2 data]$ vi postgresql.conf listen_addresses = '*' port = 5432 max_connections = 100 wal_level = hot_standby checkpoint_segments = 8 archive_mode = on archive_command = '/opt/PostgreSQL/9.3/data/ship_logs.sh %p %f < /dev/null' max_wal_senders = 3 wal_keep_segments = 8 hot_standby = on max_standby_archive_delay = 30s max_standby_streaming_delay = 30s
Ensure standby database pg_hba.conf file has the below property
[postgres@postgres2 data]$ vi pg_hba.conf local all all trust host all all 192.168.0.0/24 trust host all all ::1/128 trust host replication postgres 192.168.0.21/24 trust
Cluster Verification
PostgreSQL can now be started on the Standby. If all goes well, you should see this in the logfile:
2014-07-02 14:01:13 CDT LOG: consistent recovery state reached at 0/A041870 2014-07-02 14:01:13 CDT LOG: redo starts at 0/A041870 2014-07-02 14:01:13 CDT LOG: record with zero length at 0/A041910 2014-07-02 14:01:13 CDT LOG: database system is ready to accept read only connections 2014-07-02 14:01:13 CDT LOG: started streaming WAL from primary at 0/A000000 on timeline 1
If a table is created on the Standby machine, PostgreSQL will automatically complain, since the Standby are read only:
# create table test_this_replication (some_col text); ERROR: cannot execute CREATE TABLE in a read-only transaction
At this Point we can Say PostgreSQL Cluster configuration Completed Successfully.
Some Useful command
- Use the below command to Start / Stop / Restart Postgres Service
[root@postgres1 u01]# cd /etc/init.d [root@postgres1 init.d]# service postgresql-9.3 stop [root@postgres1 init.d]# service postgresql-9.3 start [root@postgres1 init.d]# service postgresql-9.3 restart
- Log File Location
cd /opt/PostgreSQL/9.3/data/pg_log
- Database Shutdown / Startup
Shutdown ->
su - postgres cd /opt/PostgreSQL/9.3/bin ./pg_ctl stop -D /opt/PostgreSQL/9.3/data/ -m fast
Startup ->
su - postgres cd /opt/PostgreSQL/9.3/bin ./pg_ctl start -D /opt/PostgreSQL/9.3/data/
Very useful doc.
ReplyDelete