Streaming replication Postgresql 9.3 using two different servers

Settings in master server:

max_wal_senders = 1
wal_level = 'archive'
archive_mode = on
archive_command = 'cd .'
wal_keep_segments = 10000

Settings in slave server:
in recovery.conf file:

Standby_mode = 'on'
primary_conninfo = 'host=ipaddress of master user=repuser'
trigger_file = '/tmp/postgresql.trigger.5432'

log_connections=on is set up on both master and slave server

entry is made for replication user in pg_hba.conf file in master server

host replication repuser ipaddress/32 trust

when trying to replicate i get the following error

2014-07-14 19:28:22 IST LOG: database system was shut down in recovery at 2014- 07-14 19:28:21 IST
2014-07-14 19:28:22 IST LOG: entering standby mode
2014-07-14 19:28:22 IST WARNING: WAL was generated with wal_level=minimal, data may be missing
2014-07-14 19:28:22 IST HINT: This happens if you temporarily set wal_level=min imal without taking a new base backup.
2014-07-14 19:28:22 IST LOG: consistent recovery state reached at 0/19FFE28
2014-07-14 19:28:22 IST LOG: record with zero length at 0/19FFE28
2014-07-14 19:28:22 IST FATAL: database system identifier differs between the p rimary and standby
2014-07-14 19:28:22 IST DETAIL: The primary's identifier is 6022019027749040119 , the standby's identifier is 6033562405193904122.
2014-07-14 19:28:23 IST LOG: connection received: host=[local]
2014-07-14 19:28:23 IST FATAL: the database system is starting up
2014-07-14 19:28:24 IST LOG: connection received: host=[local]
2014-07-14 19:28:24 IST FATAL: the database system

You seem to be trying to replicate from one server to another that wasn't set up using a copy of the original server. That's why:


database system identifier differs between the primary and standby. The primary's identifier is 6022019027749040119, the standby's identifier is 6033562405193904122.

Because each newly initdb'd PostgreSQL gets a new random system identifier. When you copy an existing PostgreSQL install, it keeps the same system identifier. That's how PostgreSQL can keep track of whether one server can replay WAL from another.

You can only use physical replication if the replica is a copy (file-system level backup e.g. pg_basebackup) of the master. See the manual's detailed coverage on replication for more information.

Update:

The instructions shown above should be fine, but they're not as clear as they could be.

The standby server's data directory is supposed to be replaced by the base backup you create at step 8, if it exists in the first place.

You can't make an existing PostgreSQL instance into a standby for another without replacing its data directory. You need a copy of the master's data directory to run a standby. A common way to set that up is to take an existing standby, delete its data directory, replace it with a copy of the master's data directory, and then configure it as a replication slave. That's what I think step 8 is supposed to be doing.

Instead of doing that I think you probably used an existing data directory for the slave and tried to start it up as a replica of the master. That will not work, and will result in the errors you showed.

The main PostgreSQL documentation on replication is the recommended and primary resource for information. I suggest going there first.

You might also want to check out repmgr, which helps automate replication and failover tasks.

StackExchange Database Administrators Q#71515, answer score: 17