PostgreSQL 8.4 replication of more than 100 tables

The most up-to-date version is always available with the most-up-to date version of postgres. So the coming 1.14 of pgAdmin support 9.1 with it's features, 1.12 -> 9.0, 1.10 -> >8.4. I noticed that pgAdmin will disable (or disallow you to use) certain features that do not support your installed pg version.

Sidenote: I made bad experiences with using the build-in backup/restore tools from pgAdmin (on Linux), no idea why this wasn't working but one machine just wasn't able to import a backup made by another one with same version, specs,... Had this issue many times, so I always prefer the command line export/import. The latter has never failed and offers more options for exporting and importing.

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.

1. A stable GNU/Linux Distribution (Recommended OS: CentOS 6.2 x86-64)

2. PostgreSQL 9.1 – you may install from here: http://yum.postgresql.org/ (also make sure that the following packages or their equivalnets are installed)

3. postgresql

4. postgresql-client

5. postgresql-contrib

6. postgresql-server

7. postgresql-server-dev

8. 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)

1. Run /etc/init.d/postgresql start on both systems to check if PostgreSQL is functional or not.

2. 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.

3. 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.

4. Run sudo passwd postgres on both systems and type a new Unix password for both.

1. On the pgmaster do the following:

2. su postgres

3. ssh-keygen -t rsa (press enter at every prompt)

4. ssh-copy-id -i ~/ssh/id_rsa.pub pgslave (you need to enter pgslave's postgres password)

5. ssh pgslave and see if you are able to login without password

6. Repeat the above steps on pgslave

7. su postgres

8. sh-keygen -t rsa

9. ssh-copy-id -i ~/ssh/id_rsa.pubaster

10. ssh pgmaster and see if you are able to login without password

11. 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

1. Execute locate pgbench

2. The output will be something like /usr/lib/postgresql/9.1/bin/pgbench

3. Excluding final word pgbench, copy the above path and add it to your path variable.

4. Execute nano ~/.bashrc

5. add line export PATH+=:/usr/lib/postgresql/9.1/bin/ (or whatever location the locate command revealed)

6. Close the file and save changes

7. You may need to log out and login (or open a new shell) for changes to take effect.

1. On pgmaster start the PostgreSQL server first: /etc/init.d/postgresql start

2. We create a test database and load it with some dummy data with the following commands:

   1. su postgres

   2. createdb pgbench

   3. pgbench -i -s 10 pgbench

3. Alternatively open the database pgbench yourself, create a sample table and insert sample data into it.

1. We are going to erase data directory of pgslave, so execute following on that machine:

2. First stop PostgreSQL server /etc/init.d/postgresql stop

3. Move into the PostgreSQL default data directory folder: cd /var/lib/pgsql/data (or to the data directory that is default for your installation)

4. Empty the directory completely with rm -rf *

5. 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:

1. 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.

2. Have the following packages installed to ensure that compiling repmgr is possible:

3. make

4. gcc

5. postgresql-devel

6. libxslt-devel

7. pam-devel

8. libopenssl-devel

9. krb5-devel