Re: [GENERAL] Re: Hot standby problems: consistent state not reached, no connection to master server.
On Tue, 14 Apr 2015 11:59 Ilya Ashchepkov wrote: On Mon, 13 Apr 2015 12:24:11 -0700 Adrian Klaver wrote: > On 04/13/2015 11:25 AM, Ilya Ashchepkov wrote: > > On Mon, 13 Apr 2015 10:06:05 -0700 > > Adrian Klaver wrote: > > > >> On 04/13/2015 09:42 AM, Ilya Ashchepkov wrote: > >>> On Sun, 12 Apr 2015 17:30:44 -0700 > >>> Adrian Klaver 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
Re: [GENERAL] Re: Hot standby problems: consistent state not reached, no connection to master server.
On 04/13/2015 11:25 AM, Ilya Ashchepkov wrote: On Mon, 13 Apr 2015 10:06:05 -0700 Adrian Klaver wrote: On 04/13/2015 09:42 AM, Ilya Ashchepkov wrote: On Sun, 12 Apr 2015 17:30:44 -0700 Adrian Klaver 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.kla...@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
Re: [GENERAL] Re: Hot standby problems: consistent state not reached, no connection to master server.
On 04/13/2015 09:42 AM, Ilya Ashchepkov wrote: On Sun, 12 Apr 2015 17:30:44 -0700 Adrian Klaver 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.kla...@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
Re: [GENERAL] Re: Hot standby problems: consistent state not reached, no connection to master server.
On 04/12/2015 08:25 AM, Ilya Ashchepkov wrote: On Sun, 12 Apr 2015 08:10:48 -0700 Adrian Klaver 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.kla...@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
