Hot standby problems: consistent state not reached, no connection to master server.

On 04/12/2015 07:47 AM, Ilya Ashchepkov wrote:

Hello.

I'm setting up hot standby slave.
It recovers from wal archive files, but I can't connect to it:
$ psql
psql: FATAL: the database system is starting up

On master:
# select name,setting from pg_settings where name like 'wal_level';
name | setting
-----------+-------------
wal_level | hot_standby

My slave recovery.conf:
$ cat recovery.conf
# Note that recovery.conf must be in $PGDATA directory.
# It should NOT be located in the same directory as postgresql.conf

# Specifies whether to start the server as a standby. In streaming replication,
# this parameter must to be set to on.
standby_mode = 'on'

# Specifies a connection string which is used for the standby server to connect
# with the primary.
primary_conninfo = 'host=192.168.0.101 port=5432 user=replication password=*'

# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/media/psqlbak/101/main/standup'

# Specifies a command to load archive segments from the WAL archive. If
# wal_keep_segments is a high enough number to retain the WAL segments
# required for the standby server, this may not be necessary. But
# a large workload can cause segments to be recycled before the standby
# is fully synchronized, requiring you to start again from a new base backup.
restore_command = '/usr/lib/postgresql/9.3/bin/pg_standby -t /tmp/pgsql.trigger.5432 /media/psqlbak/101/wals/main/ %f %p %r'

I tried to comment 'restore_command' in recovery.conf on slave, then slave connects
to master and starts receiving data, but I think it's not very good way.
What should I change to receive data through connection and reach consistent
state on slave?

What have you set for hot_standby on the standby server?:

http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-STANDBY

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, 12 Apr 2015 08:10:48 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/12/2015 07:47 AM, Ilya Ashchepkov wrote:

Hello.

I'm setting up hot standby slave.
It recovers from wal archive files, but I can't connect to it:
$ psql
psql: FATAL: the database system is starting up

On master:
# select name,setting from pg_settings where name like 'wal_level';
name | setting
-----------+-------------
wal_level | hot_standby

My slave recovery.conf:
$ cat recovery.conf
# Note that recovery.conf must be in $PGDATA directory.
# It should NOT be located in the same directory as postgresql.conf

# Specifies whether to start the server as a standby. In streaming
replication, # this parameter must to be set to on.
standby_mode = 'on'

# Specifies a connection string which is used for the standby
server to connect # with the primary.
primary_conninfo = 'host=192.168.0.101 port=5432
user=replication password=*'

# Specifies a trigger file whose presence should cause streaming
replication to # end (i.e., failover).
trigger_file = '/media/psqlbak/101/main/standup'

# Specifies a command to load archive segments from the WAL
archive. If # wal_keep_segments is a high enough number to retain
the WAL segments # required for the standby server, this may not be
necessary. But # a large workload can cause segments to be recycled
before the standby # is fully synchronized, requiring you to start
again from a new base backup. restore_command =
'/usr/lib/postgresql/9.3/bin/pg_standby
-t /tmp/pgsql.trigger.5432 /media/psqlbak/101/wals/main/ %f %p %r'

I tried to comment 'restore_command' in recovery.conf on slave,
then slave connects to master and starts receiving data, but I
think it's not very good way. What should I change to receive data
through connection and reach consistent state on slave?

What have you set for hot_standby on the standby server?:

http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-STANDBY

Oh! I missed this! Thank you!
Now slave reached consistent state some time after start, but still no
connection to master server and still restoring wal-files.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/12/2015 08:25 AM, Ilya Ashchepkov wrote:

On Sun, 12 Apr 2015 08:10:48 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/12/2015 07:47 AM, Ilya Ashchepkov wrote:

Hello.

I'm setting up hot standby slave.
It recovers from wal archive files, but I can't connect to it:
$ psql
psql: FATAL: the database system is starting up

On master:
# select name,setting from pg_settings where name like 'wal_level';
name | setting
-----------+-------------
wal_level | hot_standby

My slave recovery.conf:
$ cat recovery.conf
# Note that recovery.conf must be in $PGDATA directory.
# It should NOT be located in the same directory as postgresql.conf

# Specifies whether to start the server as a standby. In streaming
replication, # this parameter must to be set to on.
standby_mode = 'on'

# Specifies a connection string which is used for the standby
server to connect # with the primary.
primary_conninfo = 'host=192.168.0.101 port=5432
user=replication password=*'

# Specifies a trigger file whose presence should cause streaming
replication to # end (i.e., failover).
trigger_file = '/media/psqlbak/101/main/standup'

# Specifies a command to load archive segments from the WAL
archive. If # wal_keep_segments is a high enough number to retain
the WAL segments # required for the standby server, this may not be
necessary. But # a large workload can cause segments to be recycled
before the standby # is fully synchronized, requiring you to start
again from a new base backup. restore_command =
'/usr/lib/postgresql/9.3/bin/pg_standby
-t /tmp/pgsql.trigger.5432 /media/psqlbak/101/wals/main/ %f %p %r'

I tried to comment 'restore_command' in recovery.conf on slave,
then slave connects to master and starts receiving data, but I
think it's not very good way. What should I change to receive data
through connection and reach consistent state on slave?

What have you set for hot_standby on the standby server?:

http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-STANDBY

Oh! I missed this! Thank you!
Now slave reached consistent state some time after start, but still no
connection to master server and still restoring wal-files.

Not quite sure what you are getting at.

You are not seeing the streaming connection happening?

If a connection is not being made:

1) Dose user replication have REPLICATION rights?
2) Is the pg_hba.conf on the master set up to allow a connection from
the standby for user replication and database replication?

Where are the WAL files coming from?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, 12 Apr 2015 17:30:44 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/12/2015 08:25 AM, Ilya Ashchepkov wrote:

On Sun, 12 Apr 2015 08:10:48 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/12/2015 07:47 AM, Ilya Ashchepkov wrote:

Hello.

I'm setting up hot standby slave.
It recovers from wal archive files, but I can't connect to it:
$ psql
psql: FATAL: the database system is starting up

On master:
# select name,setting from pg_settings where name like
'wal_level'; name | setting
-----------+-------------
wal_level | hot_standby

My slave recovery.conf:
$ cat recovery.conf
# Note that recovery.conf must be in $PGDATA directory.
# It should NOT be located in the same directory as
postgresql.conf

# Specifies whether to start the server as a standby. In streaming
replication, # this parameter must to be set to on.
standby_mode = 'on'

# Specifies a connection string which is used for the standby
server to connect # with the primary.
primary_conninfo = 'host=192.168.0.101 port=5432
user=replication password=*'

# Specifies a trigger file whose presence should cause streaming
replication to # end (i.e., failover).
trigger_file = '/media/psqlbak/101/main/standup'

# Specifies a command to load archive segments from the WAL
archive. If # wal_keep_segments is a high enough number to retain
the WAL segments # required for the standby server, this may not
be necessary. But # a large workload can cause segments to be
recycled before the standby # is fully synchronized, requiring
you to start again from a new base backup. restore_command =
'/usr/lib/postgresql/9.3/bin/pg_standby
-t /tmp/pgsql.trigger.5432 /media/psqlbak/101/wals/main/ %f %p %r'

I tried to comment 'restore_command' in recovery.conf on slave,
then slave connects to master and starts receiving data, but I
think it's not very good way. What should I change to receive data
through connection and reach consistent state on slave?

What have you set for hot_standby on the standby server?:

http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-STANDBY

Oh! I missed this! Thank you!
Now slave reached consistent state some time after start, but still
no connection to master server and still restoring wal-files.

Not quite sure what you are getting at.

You are not seeing the streaming connection happening?

Yes, no streaming connection.

If a connection is not being made:

1) Dose user replication have REPLICATION rights?
2) Is the pg_hba.conf on the master set up to allow a connection from
the standby for user replication and database replication?

I commented 'restore_command' in recovery.conf and after start slave
connected to master.
Then I uncomment it back. Is it possible to have a both, streaming
connection and restoring from wal files from NFS share?

Where are the WAL files coming from?

NFS share on master.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/13/2015 09:42 AM, Ilya Ashchepkov wrote:

On Sun, 12 Apr 2015 17:30:44 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

Oh! I missed this! Thank you!
Now slave reached consistent state some time after start, but still
no connection to master server and still restoring wal-files.

Not quite sure what you are getting at.

You are not seeing the streaming connection happening?

Yes, no streaming connection.

If a connection is not being made:

1) Dose user replication have REPLICATION rights?
2) Is the pg_hba.conf on the master set up to allow a connection from
the standby for user replication and database replication?

I commented 'restore_command' in recovery.conf and after start slave
connected to master.
Then I uncomment it back. Is it possible to have a both, streaming
connection and restoring from wal files from NFS share?

Yes:

http://www.postgresql.org/docs/9.3/interactive/warm-standby.html#STREAMING-REPLICATION

I wonder if your master is recycling WALs fast enough that the streaming
can't find them and the standby has to go to the archive instead.

What is your wal_keep_segments on the master set to?:

http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER

Where are the WAL files coming from?

NFS share on master.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, 13 Apr 2015 10:06:05 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/13/2015 09:42 AM, Ilya Ashchepkov wrote:

On Sun, 12 Apr 2015 17:30:44 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

Oh! I missed this! Thank you!
Now slave reached consistent state some time after start, but
still no connection to master server and still restoring
wal-files.

Not quite sure what you are getting at.

You are not seeing the streaming connection happening?

Yes, no streaming connection.

If a connection is not being made:

1) Dose user replication have REPLICATION rights?
2) Is the pg_hba.conf on the master set up to allow a connection
from the standby for user replication and database replication?

I commented 'restore_command' in recovery.conf and after start slave
connected to master.
Then I uncomment it back. Is it possible to have a both, streaming
connection and restoring from wal files from NFS share?

Yes:

http://www.postgresql.org/docs/9.3/interactive/warm-standby.html#STREAMING-REPLICATION

I wonder if your master is recycling WALs fast enough that the
streaming can't find them and the standby has to go to the archive
instead.

What is your wal_keep_segments on the master set to?:

# select name,setting from pg_settings where name like 'wal_keep_segments';
name | setting
-------------------+---------
wal_keep_segments | 128

I run tcpdump -ni eth0 port 5432 on slave and didn't see any packet
from slave to master after restart.

http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER

Where are the WAL files coming from?

NFS share on master.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/13/2015 11:25 AM, Ilya Ashchepkov wrote:

On Mon, 13 Apr 2015 10:06:05 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/13/2015 09:42 AM, Ilya Ashchepkov wrote:

On Sun, 12 Apr 2015 17:30:44 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

If a connection is not being made:

1) Dose user replication have REPLICATION rights?
2) Is the pg_hba.conf on the master set up to allow a connection
from the standby for user replication and database replication?

I commented 'restore_command' in recovery.conf and after start slave
connected to master.
Then I uncomment it back. Is it possible to have a both, streaming
connection and restoring from wal files from NFS share?

Yes:

http://www.postgresql.org/docs/9.3/interactive/warm-standby.html#STREAMING-REPLICATION

I wonder if your master is recycling WALs fast enough that the
streaming can't find them and the standby has to go to the archive
instead.

What is your wal_keep_segments on the master set to?:

# select name,setting from pg_settings where name like 'wal_keep_segments';
name | setting
-------------------+---------
wal_keep_segments | 128

I run tcpdump -ni eth0 port 5432 on slave and didn't see any packet
from slave to master after restart.

Just to be clear:

1) When you comment out the restore_command the standby connects to the
master, correct?

2) When you uncomment restore_command you do not see a standby
connection, correct?

So:

1) When you are changing the restore_command status do you restart the
standby server?

2) What does select * from pg_stat_replication show, in either case?

www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW

3) I may have missed it, but what is your archive_command on the master?

http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER

Where are the WAL files coming from?

NFS share on master.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Le 12 avr. 2015 16:50, "Ilya Ashchepkov" <koctep@gmail.com> a écrit :

Hello.

I'm setting up hot standby slave.
It recovers from wal archive files, but I can't connect to it:
$ psql
psql: FATAL: the database system is starting up

On master:
# select name,setting from pg_settings where name like 'wal_level';
name | setting
-----------+-------------
wal_level | hot_standby

My slave recovery.conf:
$ cat recovery.conf
# Note that recovery.conf must be in $PGDATA directory.
# It should NOT be located in the same directory as postgresql.conf

# Specifies whether to start the server as a standby. In streaming

replication,

# this parameter must to be set to on.
standby_mode = 'on'

# Specifies a connection string which is used for the standby server to

connect

# with the primary.
primary_conninfo = 'host=192.168.0.101 port=5432 user=replication

password=*'

# Specifies a trigger file whose presence should cause streaming

replication to

# end (i.e., failover).
trigger_file = '/media/psqlbak/101/main/standup'

# Specifies a command to load archive segments from the WAL archive. If
# wal_keep_segments is a high enough number to retain the WAL segments
# required for the standby server, this may not be necessary. But
# a large workload can cause segments to be recycled before the standby
# is fully synchronized, requiring you to start again from a new base

backup.

restore_command = '/usr/lib/postgresql/9.3/bin/pg_standby -t

/tmp/pgsql.trigger.5432 /media/psqlbak/101/wals/main/ %f %p %r'

Don't use pg_standby if you want to use streaming. Use cp, scp, rsync, or
anything else but not pg_standby. Streaming starts when archive recovery
fails to get next archive.

I tried to comment 'restore_command' in recovery.conf on slave, then

slave connects

to master and starts receiving data, but I think it's not very good way.
What should I change to receive data through connection and reach

consistent

On Mon, 13 Apr 2015 12:24:11 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/13/2015 11:25 AM, Ilya Ashchepkov wrote:

On Mon, 13 Apr 2015 10:06:05 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/13/2015 09:42 AM, Ilya Ashchepkov wrote:

On Sun, 12 Apr 2015 17:30:44 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

If a connection is not being made:

1) Dose user replication have REPLICATION rights?
2) Is the pg_hba.conf on the master set up to allow a connection
from the standby for user replication and database replication?

I commented 'restore_command' in recovery.conf and after start
slave connected to master.
Then I uncomment it back. Is it possible to have a both, streaming
connection and restoring from wal files from NFS share?

Yes:

http://www.postgresql.org/docs/9.3/interactive/warm-standby.html#STREAMING-REPLICATION

I wonder if your master is recycling WALs fast enough that the
streaming can't find them and the standby has to go to the archive
instead.

What is your wal_keep_segments on the master set to?:

# select name,setting from pg_settings where name like
'wal_keep_segments'; name | setting
-------------------+---------
wal_keep_segments | 128

I run tcpdump -ni eth0 port 5432 on slave and didn't see any packet
from slave to master after restart.

Just to be clear:

1) When you comment out the restore_command the standby connects to
the master, correct?

Yes.

2) When you uncomment restore_command you do not see a standby
connection, correct?

Yes.

So:

1) When you are changing the restore_command status do you restart
the standby server?

Yes.

2) What does select * from pg_stat_replication show, in either case?

www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW

0 rows on master
0 rows on slave

3) I may have missed it, but what is your archive_command on the
master?

# select name,setting from pg_settings where name like 'archive_command';
name | setting
-----------------+----------------------------------------------------------------------------
archive_command | test ! -f /media/psqlbak/wals/main/%f && cp %p /media/psqlbak/wals/main/%f

http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER

Where are the WAL files coming from?

NFS share on master.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, 14 Apr 2015 11:59 Ilya Ashchepkov <koctep@gmail.com> wrote:

On Mon, 13 Apr 2015 12:24:11 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/13/2015 11:25 AM, Ilya Ashchepkov wrote:

On Mon, 13 Apr 2015 10:06:05 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/13/2015 09:42 AM, Ilya Ashchepkov wrote:

On Sun, 12 Apr 2015 17:30:44 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

If a connection is not being made:

1) Dose user replication have REPLICATION rights?
2) Is the pg_hba.conf on the master set up to allow a connection
from the standby for user replication and database replication?

I commented 'restore_command' in recovery.conf and after start
slave connected to master.
Then I uncomment it back. Is it possible to have a both, streaming
connection and restoring from wal files from NFS share?

Yes:

http://www.postgresql.org/docs/9.3/interactive/warm-standby.html#STREAMING-REPLICATION

I wonder if your master is recycling WALs fast enough that the
streaming can't find them and the standby has to go to the archive
instead.

What is your wal_keep_segments on the master set to?:

# select name,setting from pg_settings where name like
'wal_keep_segments'; name | setting
-------------------+---------
wal_keep_segments | 128

I run tcpdump -ni eth0 port 5432 on slave and didn't see any packet
from slave to master after restart.

Just to be clear:

1) When you comment out the restore_command the standby connects to
the master, correct?

Yes.

2) When you uncomment restore_command you do not see a standby
connection, correct?

Yes.

So:

1) When you are changing the restore_command status do you restart
the standby server?

Yes.

2) What does select * from pg_stat_replication show, in either case?

www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW

0 rows on master
0 rows on slave

3) I may have missed it, but what is your archive_command on the
master?

# select name,setting from pg_settings where name like 'archive_command';
name | setting
-----------------+----------------------------------------------------------------------------
archive_command | test ! -f /media/psqlbak/wals/main/%f && cp %p
/media/psqlbak/wals/main/%f

http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER

Where are the WAL files coming from?

NFS share on master.

Can you share the cluster log for your standby database and also your
primary database? Sorry if you have already shared it and I have missed it.
If there is an error in connection there are good chances that some hint
about it must be logged. Generally the logs will be placed in pg_log inside
your data directory. And log_collector must be set to on.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/13/2015 01:05 PM, Guillaume Lelarge wrote:

Le 12 avr. 2015 16:50, "Ilya Ashchepkov" <koctep@gmail.com
<mailto:koctep@gmail.com>> a écrit :

restore_command = '/usr/lib/postgresql/9.3/bin/pg_standby -t

/tmp/pgsql.trigger.5432 /media/psqlbak/101/wals/main/ %f %p %r'

Don't use pg_standby if you want to use streaming. Use cp, scp, rsync,
or anything else but not pg_standby. Streaming starts when archive
recovery fails to get next archive.

I have not used pg_standby, so the above was helpful. I now see that
warning in the docs:

http://www.postgresql.org/docs/9.3/static/warm-standby.html

25.2.4. Setting Up a Standby Server

"Note: Do not use pg_standby or similar tools with the built-in standby
mode described here. restore_command should return immediately if the
file does not exist; the server will retry the command again if
necessary. See Section 25.4 for using tools like pg_standby."

Going to section 25.4 explained a lot. Thanks for the information.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, 13 Apr 2015 22:05:40 +0200
Guillaume Lelarge <guillaume@lelarge.info> wrote:

Thank you!

Le 12 avr. 2015 16:50, "Ilya Ashchepkov" <koctep@gmail.com> a écrit :

Hello.

I'm setting up hot standby slave.
It recovers from wal archive files, but I can't connect to it:
$ psql
psql: FATAL: the database system is starting up

On master:
# select name,setting from pg_settings where name like 'wal_level';
name | setting
-----------+-------------
wal_level | hot_standby

My slave recovery.conf:
$ cat recovery.conf
# Note that recovery.conf must be in $PGDATA directory.
# It should NOT be located in the same directory as postgresql.conf

# Specifies whether to start the server as a standby. In streaming

replication,

# this parameter must to be set to on.
standby_mode = 'on'

# Specifies a connection string which is used for the standby
server to

connect

# with the primary.
primary_conninfo = 'host=192.168.0.101 port=5432
user=replication

password=*'

# Specifies a trigger file whose presence should cause streaming

replication to

# end (i.e., failover).
trigger_file = '/media/psqlbak/101/main/standup'

# Specifies a command to load archive segments from the WAL
archive. If # wal_keep_segments is a high enough number to retain
the WAL segments # required for the standby server, this may not be
necessary. But # a large workload can cause segments to be recycled
before the standby # is fully synchronized, requiring you to start
again from a new base

backup.

restore_command = '/usr/lib/postgresql/9.3/bin/pg_standby -t

/tmp/pgsql.trigger.5432 /media/psqlbak/101/wals/main/ %f %p %r'

Don't use pg_standby if you want to use streaming. Use cp, scp,
rsync, or anything else but not pg_standby. Streaming starts when
archive recovery fails to get next archive.

I tried to comment 'restore_command' in recovery.conf on slave, then

slave connects

to master and starts receiving data, but I think it's not very good
way. What should I change to receive data through connection and
reach

consistent

state on slave?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, 14 Apr 2015 07:08:50 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/13/2015 01:05 PM, Guillaume Lelarge wrote:

Le 12 avr. 2015 16:50, "Ilya Ashchepkov" <koctep@gmail.com
<mailto:koctep@gmail.com>> a écrit :

restore_command = '/usr/lib/postgresql/9.3/bin/pg_standby -t

/tmp/pgsql.trigger.5432 /media/psqlbak/101/wals/main/ %f %p %r'

Don't use pg_standby if you want to use streaming. Use cp, scp,
rsync, or anything else but not pg_standby. Streaming starts when
archive recovery fails to get next archive.

I have not used pg_standby, so the above was helpful. I now see that
warning in the docs:

http://www.postgresql.org/docs/9.3/static/warm-standby.html

25.2.4. Setting Up a Standby Server

"Note: Do not use pg_standby or similar tools with the built-in
standby mode described here. restore_command should return
immediately if the file does not exist; the server will retry the
command again if necessary. See Section 25.4 for using tools like
pg_standby."

Going to section 25.4 explained a lot. Thanks for the information.

Oh! I'm confused. Answer was in the manual and I can't find it.
Thanks you!

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, 14 Apr 2015 10:34:29 +0000
Sameer Kumar <sameer.kumar@ashnik.com> wrote:

Problem solved, thanks for attention.

On Tue, 14 Apr 2015 11:59 Ilya Ashchepkov <koctep@gmail.com> wrote:

On Mon, 13 Apr 2015 12:24:11 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/13/2015 11:25 AM, Ilya Ashchepkov wrote:

On Mon, 13 Apr 2015 10:06:05 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/13/2015 09:42 AM, Ilya Ashchepkov wrote:

On Sun, 12 Apr 2015 17:30:44 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

If a connection is not being made:

1) Dose user replication have REPLICATION rights?
2) Is the pg_hba.conf on the master set up to allow a
connection from the standby for user replication and database
replication?

I commented 'restore_command' in recovery.conf and after start
slave connected to master.
Then I uncomment it back. Is it possible to have a both,
streaming connection and restoring from wal files from NFS
share?

Yes:

http://www.postgresql.org/docs/9.3/interactive/warm-standby.html#STREAMING-REPLICATION

I wonder if your master is recycling WALs fast enough that the
streaming can't find them and the standby has to go to the
archive instead.

What is your wal_keep_segments on the master set to?:

# select name,setting from pg_settings where name like
'wal_keep_segments'; name | setting
-------------------+---------
wal_keep_segments | 128

I run tcpdump -ni eth0 port 5432 on slave and didn't see any
packet from slave to master after restart.

Just to be clear:

1) When you comment out the restore_command the standby connects to
the master, correct?

Yes.

2) When you uncomment restore_command you do not see a standby
connection, correct?

Yes.

So:

1) When you are changing the restore_command status do you restart
the standby server?

Yes.

2) What does select * from pg_stat_replication show, in either
case?

www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW

0 rows on master
0 rows on slave

3) I may have missed it, but what is your archive_command on the
master?

# select name,setting from pg_settings where name like
'archive_command'; name |
setting
-----------------+----------------------------------------------------------------------------
archive_command | test ! -f /media/psqlbak/wals/main/%f && cp
%p /media/psqlbak/wals/main/%f

http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER

Where are the WAL files coming from?

NFS share on master.

Can you share the cluster log for your standby database and also your
primary database? Sorry if you have already shared it and I have
missed it. If there is an error in connection there are good chances
that some hint about it must be logged. Generally the logs will be
placed in pg_log inside your data directory. And log_collector must
be set to on.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general