If I have a master node 1 (with database A) and a master node 2 (with database B) can I use repmgr and have both A and B replicated to a hot standby node 3 ?
[ EDIT: They would all be running Postgres 9.3 ]
postgresqlrepmgr
If I have a master node 1 (with database A) and a master node 2 (with database B) can I use repmgr and have both A and B replicated to a hot standby node 3 ?
[ EDIT: They would all be running Postgres 9.3 ]
I searched around and realized that the problem was due to not enabling hot_standby on the standby server. I wrote a corrected, updated and simplified version of the above article as an odt for future personal reference. Here is the text in its entirety (converted to html from odt using openoffice.org) for those who are interested in setting up a single read-only clone of a database using repmgr:
Single Slave Streaming Replication with
PostgreSQL
Introduction
This guide aims to quickly help you configure a PostgreSQL 9.1 server with a database, and have it replicated to a slave that can be used for read-only queries. There is no concept of failover involved here, and the slave will only have a read-only copy of the master's data.
A stable GNU/Linux Distribution (Recommended OS: CentOS 6.2 x86-64)
PostgreSQL 9.1 – you may install from here: http://yum.postgresql.org/ (also make sure that the following packages or their equivalnets are installed)
postgresql
postgresql-client
postgresql-contrib
postgresql-server
postgresql-server-dev
2 workstations – a master that runs the primary database and a slave that runs the replicated read-only database (for the purpose of this document, their respective IP addresses have been replaced with pgmaster and pgslave so make those changes in /etc/hosts of both machines if you want to follow the following instructions word for word)
Run /etc/init.d/postgresql start on both systems to check if PostgreSQL is functional or not.
Run /etc/init.d/postgresql stop on both systems to stop PostgreSQL. We will not be using PostgreSQL till we finish some configuration related tasks.
Set a password for user postgres on both the systems. This user by default has no password, but we need a password to help create an SSH tunnel between pgmaster and pgslave.
Run sudo passwd postgres on both systems and type a new Unix password for both.
On the pgmaster do the following:
su postgres
ssh-keygen -t rsa (press enter at every prompt)
ssh-copy-id -i ~/ssh/id_rsa.pub pgslave (you need to enter pgslave's postgres password)
ssh pgslave and see if you are able to login without password
Repeat the above steps on pgslave
su postgres
sh-keygen -t rsa
ssh-copy-id -i ~/ssh/id_rsa.pubaster
ssh pgmaster and see if you are able to login without password
Make sure you log out from the remote machine after you finish checking connectivity
You need to make the following changes in the file postgresql.conf that resides in the configuration directory inside /etc/postgresql/ on your machine pgmaster:
listen_addresses = '*'
wal_level = hot_standby
checkpoint_segments=30
archive_mode=on
archive_command='cd .'
max_wal_senders=2
wal_keep_segments=5000
You need to make the following change in the file postgresql.conf that resides in the configuration directory inside /etc/postgresql/ on machine pgslave:
hot_standby=on
You need to make the following changes in the file pg_hba.conf that resides in the configuration directory inside /etc/postgresql/ on machine pgmaster:
host all all 192.168.5.0/24 trust
host replication all 192.168.5.0/24 trust
There are a bunch of nifty postgresql utilities we will be using here, so lets set the PATH variable so that it knows where to find them
Execute locate pgbench
The output will be something like /usr/lib/postgresql/9.1/bin/pgbench
Excluding final word pgbench, copy the above path and add it to your path variable.
Execute nano ~/.bashrc
add line export PATH+=:/usr/lib/postgresql/9.1/bin/ (or whatever location the locate command revealed)
Close the file and save changes
You may need to log out and login (or open a new shell) for changes to take effect.
On pgmaster start the PostgreSQL server first: /etc/init.d/postgresql start
We create a test database and load it with some dummy data with the following commands:
su postgres
createdb pgbench
pgbench -i -s 10 pgbench
We are going to erase data directory of pgslave, so execute following on that machine:
First stop PostgreSQL server /etc/init.d/postgresql stop
Move into the PostgreSQL default data directory folder: cd /var/lib/pgsql/data (or to the data directory that is default for your installation)
Empty the directory completely with rm -rf *
Now execute psql -h pgmaster -d pgbench and see if you are able to access the database on pgmaster through pgslave.
Though single slave replication is fairly straightforward, we are using a tool called repmgr to help make the process even more simpler. Here is how you install it:
Grab repmgr source code from http://projects.2ndquadrant.it/sites/default/files/repmgr-1.1.0.tar.gz and copy it to /tmp for installation.
Have the following packages installed to ensure that compiling repmgr is possible:
make
gcc
postgresql-devel
libxslt-devel
pam-devel
libopenssl-devel
krb5-devel
Extract the downloaded archive and enter the source code folder.
Compile with make USE_PGXS=1
Install with make USE_PGXS=1 install
Execute below two commands to check if repmgr is installed correctly:
repmgr --version
repmgrd --version
Execute su postgres and login as the postgres user on pgslave
Run this to clone database: repmgr -D /var/lib/pgsql/data -d pgbench -p 5432 -R postgres --verbose standby clone pgmaster
Note that the -D parameter /var/lib/pgsql/data should be replaced with the appropriate location of the data folder on pgslave
Also note that 5432 is the default port PostgreSQL runs in.
When the command finishes executing (it is going to take several seconds to finish if you had used pgbench to insert random data) you may start PostgreSQL on pgslave with /etc/init.d/postgresql start
Congratulations! You have successfully configured pgslave to copy pgmaster's database via streaming replication. Note that due to being in continuous recovery mode, pgslave can only be used to execute read-only queries and no insertions or modifications are possible.
Insert some values into sample_table on pgmaster.
execute select * from sample_table; on pgslave's psql prompt after connecting to pgbench database.
If everything worked properly, you should be able to view newly added tupples on the output from pgslave.
We just learned how to quickly configure a PostgreSQL database to act as a read-only mirror to another database. Note that in case the slave goes offline, it will automatically recover data from the master and would be soon up-to-date. Streaming replication is at database level, so you need to execute the command in part 11for each new database that needs to be replicated via streaming replication.
Since we're using streaming replication, writes to the master will no longer go through when the standby fails.
Not necessarily the case. This is only the case when you are setting up synchronous streaming replication. Normally the master will return immediately after it writes its own log files to disk and not wait for the slave. It is then replicated out when the slave requests.
See the synchronous_commit parameter.
I don't see why you need to futz with repmgr for this.
Best Answer
PostgreSQL's built-in replication (as used by repmgr) replicates entire PostgreSQL instances - with all databases. The transaction log is shared between all databases.
PostgreSQL can't merge transaction logs from another instance. Nor can it replicate only a subset of databases. You have to replicate exactly one instance to exactly one other instance.
If you want something other than that, there are a variety of other replication options such as Londiste, Slony-I, BDR, etc.