how to run postgresql data replication

steps.md

1. install postgres (on both servers)

2. omnipitr (on both servers)

• download and extract omnipitr archive
• cd /var/lib/postgresql
• as postgres, wget https://codeload.github.com/omniti-labs/omnipitr/zip/v1.3.3
• as ubuntu, sudo apt-get install unzip
• as postgres, go to ~ and unzip v1.3.3
• as postgres, mkdir ~/.omnipitr
• as postgres, run sanity check (see evernote for details) go to ~/omnipitr-v1.3.3/bin` then run . sanity-check.sh`
• as ubuntu, create sudo mkdir /var/log/omnipitr
• switch grp for /var/log/omnipitr/ to postgres sudo chgrp postgres -R /var/log/omnipitr
• give write access to group for /var/log/omnipitr/ to postgres sudo chmod 775 -R /var/log/omnipitr

backup the postgres database if need be

1. on master

• as postgres, add settings to postgresql.conf

wal_level=hot_standby
archive_mode=on
archive_command='/var/lib/postgresql/omnipitr-1.3.3/bin/omnipitr-archive -D /var/lib/postgresql/9.5/main -l /var/log/omnipitr/archive-^Y-^m-^d.log -dr slave:/var/lib/postgresql/master_wal_archive -s /var/lib/postgresql/.omnipitr/ "%p"'
archive_timeout=300
max_wal_senders=2

archive_command='/var/lib/postgresql/omnipitr-1.3.3/bin/omnipitr-archive -D /var/lib/postgresql/9.5/main -l /var/log/omnipitr/archive-^Y-^m-^d.log -dl /var/lib/postgresql/master_wal_archive -s /var/lib/postgresql/.omnipitr/ "%p"'

• see tail of /etc/postgresql/9.5/main/postgresql.conf (must do this before run pg_basebackup)
• as postgres, uncomment this line 3rd from bottom inside /etc/postgresql/9.5/main/pg_hba.conf local replication postgres peer
• as ubuntu, restart postgresql service using sudo service postgresql restart
• make sure that omnipitr-archive is trying to send archives to slave by checking sudo tail /var/log/postgresql/post... -n 30
• you should see something like

Cannot open /var/log/omnipitr/archive-2018-02-20.log for writing: Permission denied at /var/lib/postgresql/omnipitr-1.3.3/lib/OmniPITR/Program/Archive.pm line 69.
2018-02-20 07:34:11.124 UTC [6388] LOG:  archive command failed with exit code 13
2018-02-20 07:34:11.124 UTC [6388] DETAIL:  The failed archive command was: /var/lib/postgresql/omnipitr-1.3.3/bin/omnipitr-archive -D /var/lib/postgresql/9.5/main -l /var/log/omnipitr/archive-^Y-^m-^d.log -dr willie:/var/lib/postgresql/master_wal_archive -s /var/lib/postgresql/.omnipitr/ "pg_xlog/000000010000000000000001"
Cannot open /var/log/omnipitr/archive-2018-02-20.log for writing: Permission denied at /var/lib/postgresql/omnipitr-1.3.3/lib/OmniPITR/Program/Archive.pm line 69.
2018-02-20 07:34:12.212 UTC [6388] LOG:  archive command failed with exit code 13
2018-02-20 07:34:12.212 UTC [6388] DETAIL:  The failed archive command was: /var/lib/postgresql/omnipitr-1.3.3/bin/omnipitr-archive -D /var/lib/postgresql/9.5/main -l /var/log/omnipitr/archive-^Y-^m-^d.log -dr willie:/var/lib/postgresql/master_wal_archive -s /var/lib/postgresql/.omnipitr/ "pg_xlog/000000010000000000000001"
2018-02-20 07:34:12.212 UTC [6388] WARNING:  archiving transaction log file "000000010000000000000001" failed too many times, will try again later

• note that even if archive is successful it won't show up under postgres log strangely
• as postgres, at ~ run pg_basebackup -D backup -Ft -z -P -x

2. setup connection and move files

• as postgres, generate ssh public key on master ssh-keygen -t rsa -b 4096
• place the public key on slave
• as postgres under slave, create authorized_keys
• as postgres under master, create config and state

 Host slave
   IdentityFile ~/.ssh/id_rsa
   HostName ip_address
   User postgres

• check ssh works
• as postgres under slave, create destination directory ~/master_wal_archive
• check if wal archiving works on by checking destination directory
• move base backup tar to slave using scp sourcefile to slave:/var/lib/postgresql/9.5

3. on slave

• as ubuntu, stop postgresql service (must do this before untar basebackup)
• as postgres, make a copy of old cluster (9.5/main) cp -R ~/9.5/main ~/9.5/main.backup/
• as postgres, delete old cluster (9.5/main/*) but keep main rm -Rf ~/9.5/main/*
• as postgres inside main, move the base mv ~/base.tar.gz ~/9.5/main/ then untar base backup into 9.5/main using tar xvzf base.tar.gz
• as postgres, add a line to vim /etc/postgresql/9.5/main/postgresql.conf (hot_standby=on)
• as postgres, create /var/lib/postgresql/9.5/main/recovery.conf

standby_mode=on
restore_command='/var/lib/postgresql/omnipitr-1.3.3/bin/omnipitr-restore -l /var/log/omnipitr/restore.log -s /var/lib/postgresql/master_wal_archive/ %f %p'
archive_cleanup_command='/var/lib/postgresql/omnipitr-1.3.3/bin/omnipitr-cleanup -l /var/log/omnipitr/cleanup.log -a /var/lib/postgresql/master_wal_archive/ %r'
recovery_target_timeline=latest

• start postgresql service, check /var/log/postgresql/, it should contain lines like 'restored log file "00000001000000000000009B" from archive'