Postgresql 9.0.4 Streaming Replication

I had a tough time configuring this, but finally got it running on centos6 with postgresql 9.0.4

Its pretty simple. Hope this helps you.

1. download centos 6.0

2. install centos 6.0 base on both servers

3. update centos with yum update

4. download the postgresql yum  rpm repository from http://www.pgrpms.org/howtoyum.php pgdg-centos-9.0-2.noarch.rpm

5. Install the rpm to create a .repo file under /etc/yum.repo.d

ON MASTER

[root@centos6pg1 vmware]# cd /etc/yum.repos.d/

[root@centos6pg1 yum.repos.d]# ls -lh

total 20K

-rw-r--r--. 1 root root 1.9K Jul  3 12:16 CentOS-Base.repo

-rw-r--r--. 1 root root  637 Jul  3 12:16 CentOS-Debuginfo.repo

-rw-r--r--. 1 root root  626 Jul  3 12:16 CentOS-Media.repo

-rw-r--r--. 1 root root 4.6K Aug  8 10:45 pgdg-centos-9.0-2.noarch.rpm

[root@centos6pg1 yum.repos.d]# rpm -ivh pgdg-centos-9.0-2.noarch.rpm

warning: pgdg-centos-9.0-2.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 442df0f8: NOKEY

Preparing...                ########################################### [100%]

   1:pgdg-centos            ########################################### [100%]

[root@centos6pg1 yum.repos.d]# ls -lh

total 24K

-rw-r--r--. 1 root root 1.9K Jul  3 12:16 CentOS-Base.repo

-rw-r--r--. 1 root root  637 Jul  3 12:16 CentOS-Debuginfo.repo

-rw-r--r--. 1 root root  626 Jul  3 12:16 CentOS-Media.repo

-rw-r--r--. 1 root root  428 Mar  3  2010 pgdg-90-centos.repo

-rw-r--r--. 1 root root 4.6K Aug  8 10:45 pgdg-centos-9.0-2.noarch.rpm

[root@centos6pg1 yum.repos.d]#

pg9 repo file is created. This will help install postgresql version 90

5. Install postgresql90

#yum install postgresql90-server postgresql90-libs postgresql90 postgresql90-devel

6. Initialize the masterdb on centos6pg1 and create the sample bpsimple database.

-bash-4.1$ ls -lh

total 0

-bash-4.1$ pwd

/var/lib/pgsql/9.0/data

-bash-4.1$ /usr/bin/initdb -D /var/lib/pgsql/9.0/data

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.

The default database encoding has accordingly been set to UTF8.

The default text search configuration will be set to "english".

fixing permissions on existing directory /var/lib/pgsql/9.0/data ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 24MB

creating configuration files ... ok

creating template1 database in /var/lib/pgsql/9.0/data/base/1 ... ok

initializing pg_authid ... ok

initializing dependencies ... ok

creating system views ... ok

loading system objects' descriptions ... ok

creating conversions ... ok

creating dictionaries ... ok

setting privileges on built-in objects ... ok

creating information schema ... ok

vacuuming database template1 ... ok

copying template1 to template0 ... ok

copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the -A option the

next time you run initdb.

Success. You can now start the database server using:

    /usr/bin/postgres -D /var/lib/pgsql/9.0/data

or

    /usr/bin/pg_ctl -D /var/lib/pgsql/9.0/data -l logfile start

-bash-4.1$ ls -lh

total 68K

drwx------. 5 postgres postgres 4.0K Aug  8 13:22 base

drwx------. 2 postgres postgres 4.0K Aug  8 13:22 global

drwx------. 2 postgres postgres 4.0K Aug  8 13:22 pg_clog

-rw-------. 1 postgres postgres 3.6K Aug  8 13:22 pg_hba.conf

-rw-------. 1 postgres postgres 1.6K Aug  8 13:22 pg_ident.conf

drwx------. 4 postgres postgres 4.0K Aug  8 13:22 pg_multixact

drwx------. 2 postgres postgres 4.0K Aug  8 13:22 pg_stat_tmp

drwx------. 2 postgres postgres 4.0K Aug  8 13:22 pg_subtrans

drwx------. 2 postgres postgres 4.0K Aug  8 13:22 pg_tblspc

drwx------. 2 postgres postgres 4.0K Aug  8 13:22 pg_twophase

-rw-------. 1 postgres postgres    4 Aug  8 13:22 PG_VERSION

drwx------. 3 postgres postgres 4.0K Aug  8 13:22 pg_xlog

-rw-------. 1 postgres postgres  17K Aug  8 13:22 postgresql.conf

7. change postgresql.conf file

60 listen_addresses = '*'

65 max_connections = 500

109 shared_buffers = 32MB

155 wal_level = hot_standby

179 archive_mode = on

181 archive_command ='cp %p /var/lib/pgsql/9.0/data/pg_xlog/pg_xlogarch%f'

156 max_wal_senders = 1

157 wal_keep_segments = 32

8. create the pg_xlogarch directory

-bash-4.1$ mkdir /var/lib/pgsql/9.0/data/pg_xlogarch

-bash-4.1$ chown postgres:postgres pg_xlogarch

9. edit pg_hba.conf file

host     replication         all        192.168.234.0/24          trust

10. start the postgresql database

#service postgresql-9.0 start

11. start backup mode on database

-bash-4.1$ psql -c "SELECT pg_start_backup('initial backup for SR')"

 pg_start_backup

-----------------

 0/2000020

(1 row)

12. tar compress the data folder

-bash-4.1$ tar -cvf data.tar.gz data

data/

data/pg_log/

data/pg_log/postgresql-Mon.log

data/pg_twophase/

data/pg_multixact/

data/pg_multixact/offsets/

data/pg_multixact/offsets/0000

data/pg_multixact/members/

data/pg_multixact/members/0000

data/pg_xlogarch/

data/pg_ident.conf

data/postgresql.conf

data/PG_VERSION

data/postmaster.pid

data/pg_notify/

data/pg_notify/0000

data/postmaster.opts

data/pg_tblspc/

data/pg_subtrans/

data/pg_subtrans/0000

data/postgresql.conf.orig

data/pg_xlog/

data/pg_xlog/000000010000000000000002

data/pg_xlog/archive_status/

data/pg_xlog/archive_status/000000010000000000000001.done

data/pg_xlog/000000010000000000000001

data/pg_xlog/pg_xlogarch000000010000000000000001

data/global/

data/global/11786

data/global/11797_vm

data/global/11872

data/global/11788

data/global/11784

data/global/11792

data/global/11797

data/global/11801_fsm

data/global/11799

data/global/11871

data/global/11867

data/global/11604

data/global/11793

data/global/11790_fsm

data/global/11797_fsm

data/global/11786_fsm

data/global/11796

data/global/11873

data/global/11603

data/global/11601

data/global/11789

data/global/pg_control

data/global/11805

data/global/pg_internal.init

data/global/pg_filenode.map

data/base/11866/11820

data/base/11866/11625

data/base/11866/11592

data/base/11866/11682

data/pg_hba.conf

data/backup_label

13. exit the backup mode

-bash-4.1$ psql -c "SELECT pg_stop_backup()"

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

 pg_stop_backup

----------------

 0/20000D8

(1 row)

ON SLAVE

14. download and install postgresql-9.0 but don't initialize the database.

15. do the following

[root@centos6pg2 9.0]# pwd

/var/lib/pgsql/9.0

scp root@192.168.234.139:/var/lib/pgsql/9.0/data.tar.gz .

15. edit postgresql.conf

remove max_wal_senders, wal_level, wal_keep_segments, archive_mode, archive_command lines. These are for master db and when you copied the data folder even the master specific configuration files got copied (namely postgresql.conf and pg_hba.conf).

add entry hot_standby = on

16. create a recovery.conf file

standby_mode = 'on'

primary_conninfo = 'host=192.168.234.139 port=5432 user=postgres'

restore_command = 'cp /var/lib/pgsql9.0/data/pg_xlog/%f  %p'

17. initialize db

#service postgresql-9.0 initdb (might give an error saying data folder is not empty)

#service postgresql-9.0 start

18. if successful master db should have in log the following:-

[root@centos6pg1 pg_log]# tail -f /var/lib/pgsql/9.0/data/pg_log/postgresql-Mon.log

LOG:  autovacuum launcher started

LOG:  received fast shutdown request

LOG:  aborting any active transactions

LOG:  autovacuum launcher shutting down

LOG:  shutting down

LOG:  database system is shut down

LOG:  database system was shut down at 2011-08-08 14:34:54 EST

LOG:  database system is ready to accept connections

LOG:  autovacuum launcher started

LOG:  replication connection authorized: user=postgres host=192.168.234.140 port=35882

and on slave

cp: cannot stat `/var/lib/pgsql9.0/data/pg_xlog/000000010000000000000002': No such file or directory

cp: cannot stat `/var/lib/pgsql9.0/data/pg_xlog/000000010000000000000002': No such file or directory

LOG:  streaming replication successfully connected to primary

LOG:  consistent recovery state reached at 0/3000000

LOG:  database system is ready to accept read only connections

19. now do some testing

a. on masterdb check was the current state

[root@centos6pg1 pg_log]# su - postgres

-bash-4.1$ psql -d template1

psql (9.0.4)

Type "help" for help.

template1=# \dt

No relations found.

template1=# \dn

        List of schemas

        Name        |  Owner

--------------------+----------

 information_schema | postgres

 pg_catalog         | postgres

 pg_toast           | postgres

 pg_toast_temp_1    | postgres

 public             | postgres

(5 rows)

b. on slave check the current state

-bash-4.1$ psql -d template1

psql (9.0.4)

Type "help" for help.

template1=#

template1=# \dt

No relations found.

template1=# \dn

        List of schemas

        Name        |  Owner

--------------------+----------

 information_schema | postgres

 pg_catalog         | postgres

 pg_toast           | postgres

 pg_toast_temp_1    | postgres

 public             | postgres

(5 rows)

c. create database on master

template1=# CREATE DATABASE bpsimple;

CREATE DATABASE

template1=# \l

                                  List of databases

   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 bpsimple  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres

                                                             : postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres

                                                             : postgres=CTc/postgres

(4 rows)

d. slave will also have database created on itself.

NOTE: you notice the error (cp: cannot stat `/var/lib/pgsql9.0/data/pg_xlog/000000010000000000000002': No such file or directory)

This is just the standby server periodically checks if new WAL has arrived by executing the restore_command. Your restore_command uses 'cp', which prints out that message if the target file can't be found.You could silence it by replacing plain 'cp' with a shell script that checks if the file exists first and calls 'cp' only if it does.