Thread: Hot standby problems: consistent state not reached, no connection to master server.
Hot standby problems: consistent state not reached, no connection to master server.
From
Ilya Ashchepkov
Date:
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?
Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Adrian Klaver
Date:
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
Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Ilya Ashchepkov
Date:
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. > > > > > > > >
Re: Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Adrian Klaver
Date:
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
Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Ilya Ashchepkov
Date:
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. > > > > >>> > >>> > >>> > >> > >> > > > > > > > > > > > >
Re: Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Adrian Klaver
Date:
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
Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Ilya Ashchepkov
Date:
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. > > > >> > >>> > >>>>> > >>>>> > >>>>> > >>>> > >>>> > >>> > >>> > >>> > >>> > >>> > >> > >> > > > > > > > > > > > >
Re: Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Adrian Klaver
Date:
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
Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Guillaume Lelarge
Date:
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
Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Ilya Ashchepkov
Date:
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. > >
Re: Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Sameer Kumar
Date:
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-VIEW0 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
Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Adrian Klaver
Date:
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
Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Ilya Ashchepkov
Date:
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 >
Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Ilya Ashchepkov
Date:
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! > >
Re: Hot standby problems: consistent state not reached, no connection to master server.
From
Ilya Ashchepkov
Date:
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 >