Geek asks and answers

How to set up synchronous streaming replication in postgres?

I am trying to set up synchronous replication using Postgres 9.1, but I cannot get it to work. I was able to configure thread replication, but not synchronously. I hope I haven’t missed anything. I carefully read several sections of ch 17, 18, 14, 25, 26, and 29 in the admin guide.

I am running ubuntu 12.04, and my postgresql.conf wizard has these, among all other standard settings:

listen_addresses = '*'              # what IP address(es) to listen on;
wal_level = archive                 # minimal, archive, or hot_standby
archive_mode = on                   # allows archiving to be done
archive_command = 'test ! -f /data/pgWalArchive/%f && cp %p /data/pgWalArchive/%f'
wal_keep_segments = 100             # in logfile segments, 16MB each; 0 disables ??? What should this be ???? 
max_wal_senders = 3                 # max number of walsender processes

For my pg_hba.conf, this is in addition to the standard stuff:

host    all             all             XX.6.35.0/24            md5
host    replication     postgres        XX.6.35.0/24            md5

My db master has only one sequence, so it is tiny. I successfully created a backup of the wizard in the primary and restored it:

sudo -u postgres pg_basebackup -D ~/backup -F tar -x -z -l ~/backup/base1 -v -h XX.6.35.51 -U postgres

I also copied the WAL archive files to standby. My recovery.conf backup file has the following:

restore_command = '/usr/lib/postgresql/9.1/bin/pg_standby /data/pgWalArchive %f %p %r'
archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /data/pgWalArchive %r'
standby_mode = on
primary_conninfo = 'host=XX.6.35.51 port=5432' # e.g. 'host=masterIpAddressOrName port=5432'

, . :

2012-06-08 10:23:51 MDT LOG:  shutting down
2012-06-08 10:23:51 MDT LOG:  database system is shut down
2012-06-08 10:23:53 MDT LOG:  database system was shut down in recovery at 2012-06-08 10:23:51 MDT
2012-06-08 10:23:53 MDT LOG:  entering standby mode
2012-06-08 10:23:53 MDT LOG:  consistent recovery state reached at 0/1D000078
2012-06-08 10:23:53 MDT LOG:  record with zero length at 0/1D000078
2012-06-08 10:23:53 MDT LOG:  streaming replication successfully connected to primary
2012-06-08 10:23:53 MDT LOG:  incomplete startup packet
2012-06-08 10:23:54 MDT FATAL:  the database system is starting up
2012-06-08 10:23:54 MDT FATAL:  the database system is starting up
2012-06-08 10:23:55 MDT FATAL:  the database system is starting up
2012-06-08 10:23:55 MDT FATAL:  the database system is starting up
2012-06-08 10:23:56 MDT FATAL:  the database system is starting up
2012-06-08 10:23:56 MDT FATAL:  the database system is starting up
2012-06-08 10:23:57 MDT FATAL:  the database system is starting up
2012-06-08 10:23:57 MDT FATAL:  the database system is starting up
2012-06-08 10:23:58 MDT FATAL:  the database system is starting up
2012-06-08 10:23:58 MDT FATAL:  the database system is starting up
2012-06-08 10:23:59 MDT FATAL:  the database system is starting up
2012-06-08 10:23:59 MDT LOG:  incomplete startup packet
2012-06-08 10:24:40 MDT LOG:  redo starts at 0/1D000078

, , . - ?

+5
4

, Streaming Replication (AKA "Binary Replication" ) , 9.0 , 9,1, Postgres . , , 9,2 - !

9.2 !

! , . , , ! " ", .

, , , . 9.2:

Postgres 9.2 ,

, , -, : Science Tools , "Dual Mode" PolyglotSQL. PolyglotSQL SQL . , " " , (!!), Postgres, .

Postgres v 9.2.

+7

, , - , postgresql. .

+3

, Postgres 9.3 hot_standby = on postgresql.conf, /.

0

More articles:


All Articles