Project

General

Profile

Actions
History

How To Configure Warm Standby with PITRTools » History » Revision 2

« Previous | Revision 2/51 (diff) | Next »
Ivan Lezhnjov, 12/04/2012 09:19 AM

How To Configure Warm Standby with PITRTools

This how-to demonstrates how to configure a warm standby using PostgreSQL 8.4 and PITRtools.

What Is PITRtools and Why Would You Want To Use It?

Essentially, PITRtools is a wrapper around standard tools, such as rsync and PostgreSQL's internal functionality, that makes, among other things, creating and managing standby configurations and subsequent failover to a standby a snap.

With the help of PITRtools you could do more, namely:
  • secure shipping of log files to configured standby server over SSL protected link;
  • streaming replication;
  • enable/disable archiving without the need to restart PostgreSQL;
  • stay informed by generating alerts based on various levels of severity of events happening during the process on both ends of configuration;
  • automatically take a base backup, including table spaces, restore archives and purge old ones (if PostgreSQL >8.3);
  • failover to the latest restore point and point-in-time recovery (restore using timestamps);
  • etc.

Obviously, PITRtools attempts to make things simpler, more secure and easier to manage.

Naming Conventions

Master server can be referred to also as archiver.
Slave is often called standby.

These names are used interchangeably.

The Test Setup

It's a good idea to first setup PITRtools and play with them before you actually go ahead and change configuration of your production servers. To show how PITRtools is configured and work, I'll describe the entire process using a 2 hosts test setup as an example.

So, there are 2 hosts named bitarena and bitarena-clone, both being virtualized instances of Debian Squeeze. I assume you're experienced enough to install Debian yourself and know how to find your way around the system.

bitarena is designated role of master server aka archiver, bitarena-clone is a slave aka standby server. PITRtools is installed on both hosts, but each host uses different tools from the package.

The Process

These are major steps in actual order of execution that one would have to follow to get PITRtools-enabled setup running:

On master server

  • Turn on archiving
  • cmd_archiver -C $CONFIG -I

On slave server

  • cmd_standby -C $CONFIG -I
  • cmd_standby -C $CONFIG -B
  • cmd_standby -C $CONFIG -S

Installing and Configuring PITRtools

PITRtools project page can be found at https://public.commandprompt.com/projects/pitrtools. Of particular interest is a Wiki page https://public.commandprompt.com/projects/pitrtools/wiki where you can find information on how to obtain PITRtools, other useful notes and links.

You should use GIT version, because currently tarball offers an outdated version of PITRtools. I was told this is going to be fixed soon, but until then you should rely on the GIT repository.

Note that essentially you need to have contents of this repository on both master and slave server. For the sake of brevity, I'm going to show the layout I use only on master server. You would then need to repeat the steps for slave server, because all that is going to change is the host where you clone PITRtools repository to.

root@bitarena ~/GIT# git clone git://github.com/commandprompt/PITRTools.git
Cloning into 'PITRTools'...
remote: Counting objects: 284, done.
remote: Compressing objects: 100% (183/183), done.
remote: Total 284 (delta 182), reused 203 (delta 101)
Receiving objects: 100% (284/284), 68.51 KiB, done.
Resolving deltas: 100% (182/182), done.
root@bitarena ~/GIT# ls
PITRTools
root@bitarena ~/GIT# cd PITRTools/
root@bitarena ~/GIT/PITRTools# ls
cmd_archiver             cmd_archiver.README  cmd_standby.ini.sample  cmd_standby.sql
cmd_archiver.ini.sample  cmd_standby          cmd_standby.README      cmd_worker.py
root@bitarena ~/GIT/PITRTools# mkdir -p /var/lib/postgresql/PITRtools/bin
root@bitarena ~/GIT/PITRTools# cp cmd_archiver cmd_standby cmd_worker.py /var/lib/postgresql/PITRtools/bin/
root@bitarena ~/GIT/PITRTools# cp *.ini.sample /var/lib/postgresql/PITRtools/
root@bitarena ~/GIT/PITRTools# cd /var/lib/postgresql/PITRtools/
root@bitarena /var/lib/postgresql/PITRtools# mv cmd_archiver.ini.sample cmd_archiver.ini
root@bitarena /var/lib/postgresql/PITRtools# mv cmd_standby.ini.sample cmd_standby.ini
root@bitarena /var/lib/postgresql/PITRtools# cd ~/GIT/PITRtools/
root@bitarena ~/GIT/PITRTools# chown -R postgres.postgres /var/lib/postgresql/PITRtools
root@bitarena:~#

SSH Key-based Login for PITRtools

PITRtools relies on rsync and SSH heavily to do its work, e.g. making a base backup and shipping WAL log files from master to slave server -- all that happens over an SSL-protected communication channel. This is the area where PITRtools makes one's life easier, because otherwise you'd have to find a way to copy log files to a slave server somehow (most likely it would be some sort of networking mount point).

Therefore one of the prerequisites is to configure SSH key-based logins between the two hosts for postgres system user that don't require a password or a passphrase.

Master

root@bitarena:~# su - postgres

postgres@bitarena:~$ ssh-keygen -t rsa
... 
postgres@bitarena:~$ ls -la /var/lib/postgresql/.ssh/
total 16
drwx------ 2 postgres postgres 4096 Sep 28 09:17 .
drwxr-xr-x 5 postgres postgres 4096 Sep 28 09:17 ..
-rw------- 1 postgres postgres 1675 Sep 28 09:17 id_rsa
-rw-r--r-- 1 postgres postgres  399 Sep 28 09:17 id_rsa.pub
postgres@bitarena:~#

Slave

root@bitarena-clone:~# su - postgres
postgres@bitarena-clone:~$ ssh-keygen -t rsa
...

Answer all the questions and ssh-keygen will create both private and public (*.pub file) RSA keys. For now just create the key pair, one on on each host (master and slave). When done, proceed to exchanging public keys between the hosts like this:

Master

postgres@bitarena:~$ exit
root@bitarena:~# scp /var/lib/postgresql/.ssh/id_rsa.pub root@bitarena-clone:/var/lib/postgresql/.ssh/authorized_keys2
root@bitarena-clone's password: 
id_rsa.pub                                                                               100%  399     0.4KB/s   00:00
root@bitarena:~#

This will copy postgres system user's public SSH key on master to standby into a file authorized_keys2, which will allow postgres on master to login to slave host without a password and also in a secure fashion.

Here we check that authorized_keys2 has indeed been created on slave host, and we also set correct ownership information for the file.

Slave

root@bitarena-clone:~# ls -la /var/lib/postgresql/.ssh/
total 20
drwx------ 2 postgres postgres 4096 Sep 28 09:30 .
drwxr-xr-x 5 postgres postgres 4096 Sep 28 09:23 ..
-rw-r--r-- 1 root     root      399 Sep 28 09:30 authorized_keys2
-rw------- 1 postgres postgres 1675 Sep 28 09:23 id_rsa
-rw-r--r-- 1 postgres postgres  405 Sep 28 09:23 id_rsa.pub
root@bitarena-clone:~# chown postgres.postgres /var/lib/postgresql/.ssh/authorized_keys2
root@bitarena:~#

Now, to check if it actually works we go back to master server, become postgres user and try to log into slave. As you can see RSA key is used this time and no password is prompted for. This is exactly what we need to make sure is configured on both hosts for PITRtools to work properly.

Master

root@bitarena:~# su - postgres

postgres@bitarena:~$ ssh bitarena-clone
The authenticity of host 'bitarena-clone (10.0.3.3)' can't be established.
RSA key fingerprint is dd:78:8e:fb:d6:c4:25:88:d9:57:b4:fb:49:22:de:de.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'bitarena-clone,10.0.3.3' (RSA) to the list of known hosts.
Linux bitarena 2.6.32-5-686 #1 SMP Sun May 6 04:01:19 UTC 2012 i686

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
postgres@bitarena-clone:~$ exit
logout
Connection to bitarena-clone closed.
root@bitarena:~#

Remember that this has been done on master server only. In a similar manner, you should take care of the slave host. The steps are identical, you just copy *.pub file to a master host and do the rest as described above.

It's worth noting that PITRtools runs some actions remotely. For example, when a base backup action is run on a slave host, cmd_standby script establishes SSH session to a master host and runs various psql commands to deal with checkpoints, copy log files, etc. This also requires PostgreSQL user password to access the database, and it is often needed to be entered 4 and more times for a base backup action to complete.

Once you played around with PITRtools enough to get hang of things, you could avoid having to enter password manually each time by either using .pgpass file (which is a standard feature of PostgreSQL) or make sure there's a trust relationship configured for localhost in pg_hba.conf file.

In the console output examples you're going to see below I entered password manually, but this, of course, is really inconvenient in production environment.

Master Configuration File

Sample configuration files are pretty good as they are with the defaults they come. Chances are you're not going to change a lot in there. Make sure you read all *.README files, though, because they contain helpful extra information about PITRtools and configuration parameters that will help you decide how to best configure your servers.

We start with the master host by editing PITRtools configuration file for master/archiver.

Master

postgres@bitarena:~$ vim PITRtools/cmd_archiver.ini

    [DEFAULT]
    ; online or offline
    state: online                    

    ; The base database directory

    pgdata: /var/lib/postgresql/8.4/main

    ; where to remotely copy archives
    r_archivedir: /var/lib/postgresql/archive

    ; where to locally copy archives
    l_archivedir: /var/lib/postgresql/archive

    ; where is rsync                
    rsync_bin: /usr/bin/rsync            

    ; extra rsync flags
    rsync_flags: -z

    ; option 2 or 3, if running RHEL5 or similar it is likely 2
    ; if you are running something that ships remotely modern software
    ; it will be 3

    rsync_version = 3

    ; IP of slave                    
    slaves: bitarena-clone

    ; the user that will be using scp                
    user: postgres                

    ; if scp can't connect in 10 seconds error
    ssh_timeout: 10

    ; command to process in ok                    
    notify_ok: echo OK

    ; command to process in warning
    notify_warning:  echo WARNING

    ; command to process in critical
    notify_critical: echo CRITICAL

    ; if you want to debug on/off only             
    debug: on

    ; if you want ssh debug (warning noisy)
    ssh_debug: off

Note that you can use domain names instead of IP addresses for slaves: parameter, it works either way just fine. You might also want to turn debugging on while you're learning PITRtools. It helps to see what software does, if you try to understand better how it works.

Turn On and Configure Archiving

When your master configuration file has been created, we need to turn on archiving functionality in PostgreSQL. This is purely a PostgreSQL feature, but we can also take advantage of using PITRtools thanks to flexible design of PostgreSQL.

postgres@bitarena:~$ vim /etc/postgresql/8.4/main/postgresql.conf

...
archive_mode=on
archive_command = '/var/lib/postgresql/PITRtools/bin/cmd_archiver -C /var/lib/postgresql/PITRtools/cmd_archiver.ini -F %p'
...

Here we explicitly turn archiving mode on and tell PostgreSQL to use cmd_archiver, part of PITRtools, as the archiving command.
It must be provided with a path to a configuration file (-C switch), and a path to a log file (-F switch). %p is substituted by PostgreSQL with the actual log file location in the file system. To learn more about how to use cmd_archiver run

postgres@bitarena:~$ PITRtools/bin/cmd_archiver --help

Please, keep in mind that PITRtools isn't supposed to be run by root user. Technically, it can be run by any system user other than root, and unless you have a customized configuration your default PostgreSQL user will be postgres, so PITRtools are expected to be run as that system user.

Restart PostgreSQL for changes to take effect.

postgres@bitarena:~$ /etc/init.d/postgresql restart
Restarting PostgreSQL 8.4 database server: main.
postgres@bitarena:~#

Install Helper Scripts

Apply cmd_standby.sql to the database of PITRtools user (usually postgres). This is required for master server only.

root@bitarena:~# psql -U postgres < ~/GIT/PITRTools/cmd_standby.sql
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
root@bitarena:~#

Initialize Master Environment

At this point we're pretty much done configuring the master server. All that is left to do is initialize master environment by running

postgres@bitarena:~$ PITRtools/bin/cmd_archiver -C PITRtools/cmd_archiver.ini -I
We are initializing queues, one moment.

NOTICE: init_env_func()
NOTICE: generate_slave_list_func()
NOTICE: Your slaves are: ['bitarena-clone']
postgres@bitarena:~$ ls -lah
total 40K
drwxr-xr-x  7 postgres postgres 4.0K Sep 28 10:25 .
drwxr-xr-x 34 root     root     4.0K Sep 27 02:32 ..
drwxr-xr-x  3 postgres postgres 4.0K Sep 24 15:10 8.4
drwx------  2 postgres postgres 4.0K Sep 27 02:23 .aptitude
drwxr-xr-x  3 postgres postgres 4.0K Sep 28 10:25 archive
-rw-------  1 postgres postgres 1001 Sep 28 10:03 .bash_history
drwxr-xr-x  2 postgres postgres 4.0K Sep 28 10:25 PITRtools
-rw-------  1 postgres postgres 1.4K Sep 28 05:08 .psql_history
drwx------  2 postgres postgres 4.0K Sep 28 09:29 .ssh
-rw-------  1 postgres postgres 3.6K Sep 28 10:25 .viminfo
postgres@bitarena:~$ ls -lah archive/bitarena-clone/
total 8.0K
drwxr-xr-x 2 postgres postgres 4.0K Sep 28 10:25 .
drwxr-xr-x 3 postgres postgres 4.0K Sep 28 10:25 ..
postgres@bitarena:~#

As you can see -I switch tells cmd_archiver to do a couple of internal actions, as well as prepare file system layout by creating necessary directories.

archive/ directory has been created automatically by cmd_archiver and contains sub-directory named after IP address or DNS name of a slave, bitarena-clone in this example. This sub-directory is used in those circumstances when master fails to successfully transfer WAL log files to slave and stores them temporarily in this local directory. Once slave is back online, these files should be transferred to slave.

Effectively, after you've initialized master it starts trying to ship WAL files to slave. However, the slave host isn't configured yet, so the log delivery will fail and the log files should be found in l_archivedir/slave_FQDNorIP/ folder on master host. Once we configure slave, these will be shipped as soon as a new WAL segment is created on master.

Slave aka Standby Server

Now we can prepare the slave host. SSH key-based login for postgres system user should be working in both directions, from master to slave, as well as from slave to master.

Assuming you've arranged for this as it was suggested before, we can now go on and edit slave configuration file to perform first important step and initialize slave environment.

One of the configuration file parameters, namely pgdata:, will ask you to specify PostgreSQL data directory. You could look it up in postgresql.conf, or, If your PostgreSQL is already running, you could see this information like this:

root@bitarena:~# ps axuwf |grep postgre
root      4233  0.0  0.0   3304   756 pts/1    S+   10:56   0:00          \_ grep postgre
postgres  3352  0.0  0.4  46452  5464 ?        S    09:59   0:03 /usr/lib/postgresql/8.4/bin/postgres 
-D /var/lib/postgresql/8.4/main -c config_file=/etc/postgresql/8.4/main/postgresql.conf
...

Remember, note down the path somewhere or copy it to the clipboard. We'll need it in a minute.

Slave Configuration File

Now edit slave configuration file. Most defaults are good and safe options, but your setup may be different from what I have in this how-to. So, be careful to understand what you're doing.

Slave

postgres@bitarena-clone:~$ vim PITRtools/cmd_standby.ini
    [DEFAULT]
    ; what major version are we using?
    pgversion: 8.4

    ; Used for 8.2 (8.1?), should be set to something > than checkpoint_segments on master                
    numarchives: 10            

    ; Whether or not to use streaming replication.  If this is set to "on" 
    ; PITRTools will configure the standby server to replicate from master
    ; using streaming replication.
    ; This can only be used with PostgreSQL 9.0 and up.
    use_streaming_replication: off

    ; File to touch to end replication when using streaming replication.
    trigger_file: /var/lib/postgresql/PITRtools/cmd_end_recovery

    ; User to connect to master DB while using streaming replication,
    ; ignored if not using streaming replication.
    repl_db_user: replication

    ; Password for the user repl_db_user.
    repl_db_password: secret

    ; sslmode to use when connecting for streaming replication. 
    ; Accepted values: the same as libpq: disable, allow, prefer, require, verify-ca and verify-full
    ; Default: sslmode: prefer
    sslmode: prefer

    ; Commands needed for execution
    ; absolute path to ssh
    ssh: /usr/bin/ssh        

    ; absolute path to rsync
    rsync: /usr/bin/rsync

    ; extra rsync flags
    rsync_flags: -z

    ; the path to to the postgres bin        
    pg_standby: /usr/lib/postgresql/8.4/bin/pg_standby
    pg_ctl: /usr/lib/postgresql/8.4/bin/pg_ctl

    ; path to psql on the master
    r_psql: /usr/lib/postgresql/8.4/bin/psql    

    ; Generalized information

    ; the port postgresql runs on (master)
    port: 5432

    ; ip or name of master server             
    master_public_ip: bitarena

    ; the ip address we should use when processing remote shell        
    master_local_ip: 127.0.0.1

    ; the user performed initdb    
    user: postgres

    ; on or off            
    debug: on

    ; on or off
    ssh_debug: off

    ; the timeout for ssh before we throw an alarm
    ssh_timeout: 30            

    ; should be the same as r_archivedir for archiver
    archivedir: /var/lib/postgresql/archive

    ; where you executed initdb -D to    
    pgdata: /var/lib/postgresql/8.4/main

    ; Confs

    ; This is the postgresql.conf to be used when not in standby
    postgresql_conf: /etc/postgresql/8.4/main/postgresql.conf        

    ; This is the pg_hba.conf to be used when not in standby
    pg_hba_conf: /etc/postgresql/8.4/main/pg_hba.conf        

    ; By default postgresql.conf and pg_hba.conf will be copied from the
    ; locations specified above to pgdata directory on failover.
    ;
    ; Uncomment the following to make postgres actually use the above conf
    ; files w/o copying them to pgdata.
    ;no_copy_conf: true

    ; The recovery.conf file to create when starting up
    ; Defaults to %(pgdata)/recovery.conf
    recovery_conf: /var/lib/postgresql/8.4/main/recovery.conf

    ; Useful when postgresql.conf doesn't specify log destination
    ; Will be passed with -l to pg_ctl when starting the server.
    ;
    ; If you're worried about having complete logs, either make sure
    ; postgresql.conf points to a log file, or use the logfile: parameter.
    ;
    ; Otherwise postgresql will print on standard stdout and nothing
    ; will be recorded in the logs
    ;
    ;logfile: /var/log/postgresql/postgresql.log

    ; Alarms

    notify_critical: echo CRITICAL
    notify_warning: echo WARNING
    notify_ok: echo OK

    ; On failover action

    ; Whatever is placed here will be executed on -FS must return 0

    action_failover: /var/lib/postgresql/PITRtools/failover.sh

action_failover: script has to exist and have permissions of at least chmod u+x equivalent, it could be just a placeholder script with a simple action of:

#!/bin/bash
touch /var/lib/postgresql/PITRtools/failover_happened

but it's meant as a way to let you do certain actions on failover, those would be very specific for each given setup. It's good to know, though, that PITRtools lets you take actions automatically when failover happens. Use this feature to make your setup more sophisticated.

Initialize Slave Environment

First stop PostgreSQL, then initialize slave environment.

postgres@bitarena-clone:~$ /etc/init.d/postgresql stop
postgres@bitarena-clone:~$ PITRtools/bin/cmd_standby -C PITRtools/cmd_standby.ini -I
NOTICE: check_pgpid_func()
DEBUG: executing query /usr/bin/ssh -o ConnectTimeout=30 -o StrictHostKeyChecking=no  postgres@bitarena  "/usr/lib/postgresql/8.4/bin/psql -A -t -Upostgres -p5432 -dpostgres -h127.0.0.1  by 'SELECT * FROM cmd_get_data_dirs()'
Password for user postgres: postgrespass

DEBUG: /var/lib/postgresql/8.4/main
postgres@bitarena-clone:~$

Please, note that if archivedir: /var/lib/postgresql/archive hasn't been created, you should do so manually as postgres system user (or set postgres user and group as the ownership information for this directory). PITRtools should do this automatically for you, but earlier versions were known not to do so. This is important, and the next step in slave configuration, which is base backup, will fail if archivedir: doesn't exist.

Updated by Ivan Lezhnjov about 12 years ago · 2 revisions