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

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.

>
> >
> >>>
> >>>
> >>>
> >>
> >>
> >
> >
> >
> >
> >
>
>