Thread: postgres standby won't start

postgres standby won't start

From
"Ramalingam, Sankarakumar"
Date:

We have a standby set up between two sites in two different locations. The replication was going on well and suddenly it stopped due to error

2015-09-08 16:07:51 EDT LOG:  streaming replication successfully connected to primary

2015-09-08 16:07:51 EDT FATAL:  could not receive data from WAL stream: FATAL:  requested WAL segment 0000000C0000035E000000F0 has already been removed

 

I am unable to start the DB as well.

Should I restore a fresh copy from production on to this standby to make things in order? If yes, how to go about it. I am quite new to Postgres. Any help/suggestions will be greatly appreciated.

 

 

Thanks

Kumar Ramalingam

Global Database Administration

Elavon, Atlanta , GA

 

678 731 5288

 

The information contained in this e-mail and in any attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. This message has been scanned for known computer viruses.

Re: postgres standby won't start

From
Andreas Kretschmer
Date:
Ramalingam, Sankarakumar <Sankarakumar.Ramalingam@elavon.com> wrote:

> We have a standby set up between two sites in two different locations. The
> replication was going on well and suddenly it stopped due to error
>
> 2015-09-08 16:07:51 EDT LOG:  streaming replication successfully connected to
> primary
>
> 2015-09-08 16:07:51 EDT FATAL:  could not receive data from WAL stream: FATAL:
> requested WAL segment 0000000C0000035E000000F0 has already been removed
>
>
>
> I am unable to start the DB as well.
>
> Should I restore a fresh copy from production on to this standby to make things
> in order? If yes, how to go about it. I am quite new to Postgres. Any help/
> suggestions will be greatly appreciated.

Yeah, the master doesn't have/contains the requested WAL-File, you have
to rebuild the replication with a fresh copy from the master. You can
use pg_basebackup, it's well documented.

For the future: increase wal_keep_segments and/or use physical
replications slots, if you are using 9.4 or higher.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


Re: postgres standby won't start

From
Adrian Klaver
Date:
On 10/05/2015 10:53 AM, Ramalingam, Sankarakumar wrote:
> We have a standby set up between two sites in two different locations.
> The replication was going on well and suddenly it stopped due to error
>
> 2015-09-08 16:07:51 EDT LOG:  streaming replication successfully
> connected to primary
>
> 2015-09-08 16:07:51 EDT FATAL:  could not receive data from WAL stream:
> FATAL:  requested WAL segment 0000000C0000035E000000F0 has already been
> removed

Best guess is you have wal_keep_segments set to low:

http://www.postgresql.org/docs/9.4/interactive/runtime-config-replication.html

wal_keep_segments (integer)

     Specifies the minimum number of past log file segments kept in the
pg_xlog directory, in case a standby server needs to fetch them for
streaming replication. Each segment is normally 16 megabytes. If a
standby server connected to the sending server falls behind by more than
wal_keep_segments segments, the sending server might remove a WAL
segment still needed by the standby, in which case the replication
connection will be terminated. Downstream connections will also
eventually fail as a result. (However, the standby server can recover by
fetching the segment from archive, if WAL archiving is in use.)

     This sets only the minimum number of segments retained in pg_xlog;
the system might need to retain more segments for WAL archival or to
recover from a checkpoint. If wal_keep_segments is zero (the default),
the system doesn't keep any extra segments for standby purposes, so the
number of old WAL segments available to standby servers is a function of
the location of the previous checkpoint and status of WAL archiving.
This parameter can only be set in the postgresql.conf file or on the
server command line.

>
> I am unable to start the DB as well.

Which one the primary, the standby or both?

>
> Should I restore a fresh copy from production on to this standby to make
> things in order? If yes, how to go about it. I am quite new to Postgres.
> Any help/suggestions will be greatly appreciated.

Depends did you have WAL archiving set up, where you could pull the
missing WAL file(s) from?

If not you will need to rebuild. Take a look at pg_basebackup:

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

>
> Thanks
>
> Kumar Ramalingam
>
> Global Database Administration
>
> Elavon, Atlanta , GA
>
> 678 731 5288
>
> The information contained in this e-mail and in any attachments is
> intended only for the person or entity to which it is addressed and may
> contain confidential and/or privileged material. Any review,
> retransmission, dissemination or other use of, or taking of any action
> in reliance upon, this information by persons or entities other than the
> intended recipient is prohibited. This message has been scanned for
> known computer viruses.
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: postgres standby won't start

From
"Ramalingam, Sankarakumar"
Date:
Thanks Adrian.

My primary is doing  fine. Only the standby. I am noticing it after a while..my Bad!!
Primary setting
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------

max_wal_senders = 5
wal_sender_delay = 1s
wal_keep_segments = 512
vacuum_defer_cleanup_age = 20
hot_standby = on

Since I am unable to bring standby up, would the pg_basebackup help me out here for a complete sync? Thanks again.



Thanks
Kumar Ramalingam
X6015288


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Monday, October 05, 2015 2:18 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/05/2015 10:53 AM, Ramalingam, Sankarakumar wrote:
> We have a standby set up between two sites in two different locations.
> The replication was going on well and suddenly it stopped due to error
>
> 2015-09-08 16:07:51 EDT LOG:  streaming replication successfully
> connected to primary
>
> 2015-09-08 16:07:51 EDT FATAL:  could not receive data from WAL stream:
> FATAL:  requested WAL segment 0000000C0000035E000000F0 has already
> been removed

Best guess is you have wal_keep_segments set to low:

http://www.postgresql.org/docs/9.4/interactive/runtime-config-replication.html

wal_keep_segments (integer)

     Specifies the minimum number of past log file segments kept in the pg_xlog directory, in case a standby server
needsto fetch them for streaming replication. Each segment is normally 16 megabytes. If a standby server connected to
thesending server falls behind by more than wal_keep_segments segments, the sending server might remove a WAL segment
stillneeded by the standby, in which case the replication connection will be terminated. Downstream connections will
alsoeventually fail as a result. (However, the standby server can recover by fetching the segment from archive, if WAL
archivingis in use.) 

     This sets only the minimum number of segments retained in pg_xlog; the system might need to retain more segments
forWAL archival or to recover from a checkpoint. If wal_keep_segments is zero (the default), the system doesn't keep
anyextra segments for standby purposes, so the number of old WAL segments available to standby servers is a function of
thelocation of the previous checkpoint and status of WAL archiving.  
This parameter can only be set in the postgresql.conf file or on the server command line.

>
> I am unable to start the DB as well.

Which one the primary, the standby or both?

>
> Should I restore a fresh copy from production on to this standby to
> make things in order? If yes, how to go about it. I am quite new to Postgres.
> Any help/suggestions will be greatly appreciated.

Depends did you have WAL archiving set up, where you could pull the missing WAL file(s) from?

If not you will need to rebuild. Take a look at pg_basebackup:

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

>
> Thanks
>
> Kumar Ramalingam
>
> Global Database Administration
>
> Elavon, Atlanta , GA
>
> 678 731 5288
>
> The information contained in this e-mail and in any attachments is
> intended only for the person or entity to which it is addressed and
> may contain confidential and/or privileged material. Any review,
> retransmission, dissemination or other use of, or taking of any action
> in reliance upon, this information by persons or entities other than
> the intended recipient is prohibited. This message has been scanned
> for known computer viruses.
>


--
Adrian Klaver
adrian.klaver@aklaver.com
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses. 



Re: postgres standby won't start

From
Adrian Klaver
Date:
On 10/05/2015 12:35 PM, Ramalingam, Sankarakumar wrote:
> Thanks Adrian.
>
> My primary is doing  fine. Only the standby. I am noticing it after a while..my Bad!!
> Primary setting
> #------------------------------------------------------------------------------
> # REPLICATION
> #------------------------------------------------------------------------------
>
> max_wal_senders = 5
> wal_sender_delay = 1s
> wal_keep_segments = 512
> vacuum_defer_cleanup_age = 20
> hot_standby = on
>
> Since I am unable to bring standby up, would the pg_basebackup help me out here for a complete sync? Thanks again.

The problem is that the WAL files got recycled on the primary and are no
longer available there, which is why the standby is failing. If you
where archiving the WAL files somewhere, you could fetch the missing WAL
files from there. If that is not the case then you will need to rebuild
the standby to the current state(more or less) of the primary. The
simplest way is to use pg_backup, but that means starting with either no
data directory or an empty one on the standby. The docs page steps you
through the procedure.

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com


Re: postgres standby won't start

From
"Ramalingam, Sankarakumar"
Date:
Hi Adrian,
Thanks for your prompt response. I used pg_basebackup to backup my source server. I am starting this exercise on our QA
sideso I can do a dry run before hitting production. Would I be able to restore that backup on my target standby? If
yes,would you guide me to the steps. Thanks a lot for your invaluable suggestions overall to this forum. 



Thanks
Kumar Ramalingam
X6015288


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Monday, October 05, 2015 7:35 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/05/2015 12:35 PM, Ramalingam, Sankarakumar wrote:
> Thanks Adrian.
>
> My primary is doing  fine. Only the standby. I am noticing it after a while..my Bad!!
> Primary setting
> #---------------------------------------------------------------------
> ---------
> # REPLICATION
> #---------------------------------------------------------------------
> ---------
>
> max_wal_senders = 5
> wal_sender_delay = 1s
> wal_keep_segments = 512
> vacuum_defer_cleanup_age = 20
> hot_standby = on
>
> Since I am unable to bring standby up, would the pg_basebackup help me out here for a complete sync? Thanks again.

The problem is that the WAL files got recycled on the primary and are no longer available there, which is why the
standbyis failing. If you where archiving the WAL files somewhere, you could fetch the missing WAL files from there. If
thatis not the case then you will need to rebuild the standby to the current state(more or less) of the primary. The
simplestway is to use pg_backup, but that means starting with either no data directory or an empty one on the standby.
Thedocs page steps you through the procedure. 

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses. 



Re: postgres standby won't start

From
Adrian Klaver
Date:
On 10/07/2015 10:02 AM, Ramalingam, Sankarakumar wrote:
> Hi Adrian,
> Thanks for your prompt response. I used pg_basebackup to backup my source server. I am starting this exercise on our
QAside so I can do a dry run before hitting production. Would I be able to restore that backup on my target standby? If
yes,would you guide me to the steps. Thanks a lot for your invaluable suggestions overall to this forum. 

The steps can be found here:

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

Look at the examples at the bottom and substitute the data directory for
the standby in the -D portion. Important:

-D directory
--pgdata=directory

     Directory to write the output to. pg_basebackup will create the
directory and any parent directories if necessary. The directory may
already exist, but it is an error if the directory already exists and is
not empty.

To be on safe side you could move the current files in the standby
directory to another location before pointing pg_basebackup at it.

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Monday, October 05, 2015 7:35 PM
> To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] postgres standby won't start
>
> On 10/05/2015 12:35 PM, Ramalingam, Sankarakumar wrote:
>> Thanks Adrian.
>>
>> My primary is doing  fine. Only the standby. I am noticing it after a while..my Bad!!
>> Primary setting
>> #---------------------------------------------------------------------
>> ---------
>> # REPLICATION
>> #---------------------------------------------------------------------
>> ---------
>>
>> max_wal_senders = 5
>> wal_sender_delay = 1s
>> wal_keep_segments = 512
>> vacuum_defer_cleanup_age = 20
>> hot_standby = on
>>
>> Since I am unable to bring standby up, would the pg_basebackup help me out here for a complete sync? Thanks again.
>
> The problem is that the WAL files got recycled on the primary and are no longer available there, which is why the
standbyis failing. If you where archiving the WAL files somewhere, you could fetch the missing WAL files from there. If
thatis not the case then you will need to rebuild the standby to the current state(more or less) of the primary. The
simplestway is to use pg_backup, but that means starting with either no data directory or an empty one on the standby.
Thedocs page steps you through the procedure. 
>
>>
>>
>>
>> Thanks
>> Kumar Ramalingam
>> X6015288
>>
>>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
> The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses. 
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: postgres standby won't start

From
"Ramalingam, Sankarakumar"
Date:
Ok, I did the pg_basebackup successfully and sent the file (.tar) to the target and expanded it over the data
directory.Looks like I am still out of sync when trying to open the DB(or start it). 

2015-10-07 16:04:13 EDT FATAL:  timeline 26 of the primary does not match recovery target timeline 22

Any ideas?



Thanks
Kumar Ramalingam
X6015288


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, October 07, 2015 1:25 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/07/2015 10:02 AM, Ramalingam, Sankarakumar wrote:
> Hi Adrian,
> Thanks for your prompt response. I used pg_basebackup to backup my source server. I am starting this exercise on our
QAside so I can do a dry run before hitting production. Would I be able to restore that backup on my target standby? If
yes,would you guide me to the steps. Thanks a lot for your invaluable suggestions overall to this forum. 

The steps can be found here:

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

Look at the examples at the bottom and substitute the data directory for the standby in the -D portion. Important:

-D directory
--pgdata=directory

     Directory to write the output to. pg_basebackup will create the directory and any parent directories if necessary.
Thedirectory may already exist, but it is an error if the directory already exists and is not empty. 

To be on safe side you could move the current files in the standby directory to another location before pointing
pg_basebackupat it. 

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Monday, October 05, 2015 7:35 PM
> To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] postgres standby won't start
>
> On 10/05/2015 12:35 PM, Ramalingam, Sankarakumar wrote:
>> Thanks Adrian.
>>
>> My primary is doing  fine. Only the standby. I am noticing it after a while..my Bad!!
>> Primary setting
>> #--------------------------------------------------------------------
>> -
>> ---------
>> # REPLICATION
>> #--------------------------------------------------------------------
>> -
>> ---------
>>
>> max_wal_senders = 5
>> wal_sender_delay = 1s
>> wal_keep_segments = 512
>> vacuum_defer_cleanup_age = 20
>> hot_standby = on
>>
>> Since I am unable to bring standby up, would the pg_basebackup help me out here for a complete sync? Thanks again.
>
> The problem is that the WAL files got recycled on the primary and are no longer available there, which is why the
standbyis failing. If you where archiving the WAL files somewhere, you could fetch the missing WAL files from there. If
thatis not the case then you will need to rebuild the standby to the current state(more or less) of the primary. The
simplestway is to use pg_backup, but that means starting with either no data directory or an empty one on the standby.
Thedocs page steps you through the procedure. 
>
>>
>>
>>
>> Thanks
>> Kumar Ramalingam
>> X6015288
>>
>>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
> The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses. 
>


--
Adrian Klaver
adrian.klaver@aklaver.com
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses. 



Re: postgres standby won't start

From
"Ramalingam, Sankarakumar"
Date:
Hello Adrian,
Can I use this command so that all the data is streamed directly to the target. Target is pretty much unusable now.
ShouldI get rid of all items under my $PGDATA and leave the empty before doing the following?  

pg_basebackup -h <my target hostname>  -D <my target host directory>  -P -U replication --xlog-method=stream



Thanks
Kumar Ramalingam
X6015288


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, October 07, 2015 1:25 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/07/2015 10:02 AM, Ramalingam, Sankarakumar wrote:
> Hi Adrian,
> Thanks for your prompt response. I used pg_basebackup to backup my source server. I am starting this exercise on our
QAside so I can do a dry run before hitting production. Would I be able to restore that backup on my target standby? If
yes,would you guide me to the steps. Thanks a lot for your invaluable suggestions overall to this forum. 

The steps can be found here:

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

Look at the examples at the bottom and substitute the data directory for the standby in the -D portion. Important:

-D directory
--pgdata=directory

     Directory to write the output to. pg_basebackup will create the directory and any parent directories if necessary.
Thedirectory may already exist, but it is an error if the directory already exists and is not empty. 

To be on safe side you could move the current files in the standby directory to another location before pointing
pg_basebackupat it. 

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Monday, October 05, 2015 7:35 PM
> To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] postgres standby won't start
>
> On 10/05/2015 12:35 PM, Ramalingam, Sankarakumar wrote:
>> Thanks Adrian.
>>
>> My primary is doing  fine. Only the standby. I am noticing it after a while..my Bad!!
>> Primary setting
>> #--------------------------------------------------------------------
>> -
>> ---------
>> # REPLICATION
>> #--------------------------------------------------------------------
>> -
>> ---------
>>
>> max_wal_senders = 5
>> wal_sender_delay = 1s
>> wal_keep_segments = 512
>> vacuum_defer_cleanup_age = 20
>> hot_standby = on
>>
>> Since I am unable to bring standby up, would the pg_basebackup help me out here for a complete sync? Thanks again.
>
> The problem is that the WAL files got recycled on the primary and are no longer available there, which is why the
standbyis failing. If you where archiving the WAL files somewhere, you could fetch the missing WAL files from there. If
thatis not the case then you will need to rebuild the standby to the current state(more or less) of the primary. The
simplestway is to use pg_backup, but that means starting with either no data directory or an empty one on the standby.
Thedocs page steps you through the procedure. 
>
>>
>>
>>
>> Thanks
>> Kumar Ramalingam
>> X6015288
>>
>>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
> The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses. 
>


--
Adrian Klaver
adrian.klaver@aklaver.com
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses. 



Re: postgres standby won't start

From
Adrian Klaver
Date:
On 10/07/2015 01:30 PM, Ramalingam, Sankarakumar wrote:
> Hello Adrian,
> Can I use this command so that all the data is streamed directly to the target. Target is pretty much unusable now.
ShouldI get rid of all items under my $PGDATA and leave the empty before doing the following? 
>
> pg_basebackup -h <my target hostname>  -D <my target host directory>  -P -U replication --xlog-method=stream

Just to be sure the target is the standby server, correct?

If so you do not want this:

-h <my target hostname>

-h is for the server you are taking the backup of, or to put it another
way the flow is from -h to -D.

If you are running pg_basebackup on the same machine as the primary then:

pg_basebackup  -D <my target host directory>  -P -U replication
--xlog-method=stream

This assumes there is only one instance of Postgres running on that
machine and it is running on port 5432. If not then you will need to
specify -p <correct_port>

pg_basebackup is a client program, so like other such programs you have
to tell it what database(cluster) to connect to using the normal
switches, see below. Actually pg_basebackup does not connect to a
specific database but to the database cluster, so any database name you
supply will be ignored.

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

"The following command-line options control the database connection
parameters."

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com


Re: postgres standby won't start

From
"Ramalingam, Sankarakumar"
Date:
Yes target is a standby on another machine. We have two sites (geographically separated)



Thanks
Kumar Ramalingam
X6015288


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, October 07, 2015 8:06 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/07/2015 01:30 PM, Ramalingam, Sankarakumar wrote:
> Hello Adrian,
> Can I use this command so that all the data is streamed directly to the target. Target is pretty much unusable now.
ShouldI get rid of all items under my $PGDATA and leave the empty before doing the following? 
>
> pg_basebackup -h <my target hostname>  -D <my target host directory>
> -P -U replication --xlog-method=stream

Just to be sure the target is the standby server, correct?

If so you do not want this:

-h <my target hostname>

-h is for the server you are taking the backup of, or to put it another way the flow is from -h to -D.

If you are running pg_basebackup on the same machine as the primary then:

pg_basebackup  -D <my target host directory>  -P -U replication --xlog-method=stream

This assumes there is only one instance of Postgres running on that machine and it is running on port 5432. If not then
youwill need to specify -p <correct_port> 

pg_basebackup is a client program, so like other such programs you have to tell it what database(cluster) to connect to
usingthe normal switches, see below. Actually pg_basebackup does not connect to a specific database but to the database
cluster,so any database name you supply will be ignored. 

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

"The following command-line options control the database connection parameters."

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses. 



Re: postgres standby won't start

From
"Ramalingam, Sankarakumar"
Date:
Hi Adrian,
After a few attempts I used the following (without method=stream as it is not working on 9.1) and restored the DB on
standbysite successfully. The log says it is connecting to primary 
streaming replication successfully connected to primary

but still complains about some log files.

2015-10-08 00:47:37 EDT LOG:  streaming replication successfully connected to primary
2015-10-08 00:53:53 EDT LOG:  received smart shutdown request
2015-10-08 00:53:53 EDT FATAL:  terminating walreceiver process due to administrator command
2015-10-08 00:53:53 EDT LOG:  shutting down
2015-10-08 00:53:53 EDT LOG:  database system is shut down
2015-10-08 00:54:04 EDT LOG:  database system was shut down in recovery at 2015-10-08 00:53:53 EDT
cp: cannot stat `/db_logs/pgsql/archive/0000001A.history': No such file or directory
2015-10-08 00:54:04 EDT LOG:  entering standby mode
cp: cannot stat `/db_logs/pgsql/archive/0000001A00000845000000C9': No such file or directory
2015-10-08 00:54:04 EDT LOG:  redo starts at 845/C9000020
2015-10-08 00:54:04 EDT LOG:  consistent recovery state reached at 845/CA000000
2015-10-08 00:54:04 EDT LOG:  database system is ready to accept read only connections
cp: cannot stat `/db_logs/pgsql/archive/0000001A00000845000000CA': No such file or directory
2015-10-08 00:54:04 EDT LOG:  streaming replication successfully connected to primary
2015-10-08 00:56:21 EDT LOG:  connection received: host=[local]
2015-10-08 00:56:21 EDT LOG:  connection authorized: user=postgres database=clients


Thanks
Kumar Ramalingam
X6015288


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, October 07, 2015 8:06 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/07/2015 01:30 PM, Ramalingam, Sankarakumar wrote:
> Hello Adrian,
> Can I use this command so that all the data is streamed directly to the target. Target is pretty much unusable now.
ShouldI get rid of all items under my $PGDATA and leave the empty before doing the following? 
>
> pg_basebackup -h <my target hostname>  -D <my target host directory>
> -P -U replication --xlog-method=stream

Just to be sure the target is the standby server, correct?

If so you do not want this:

-h <my target hostname>

-h is for the server you are taking the backup of, or to put it another way the flow is from -h to -D.

If you are running pg_basebackup on the same machine as the primary then:

pg_basebackup  -D <my target host directory>  -P -U replication --xlog-method=stream

This assumes there is only one instance of Postgres running on that machine and it is running on port 5432. If not then
youwill need to specify -p <correct_port> 

pg_basebackup is a client program, so like other such programs you have to tell it what database(cluster) to connect to
usingthe normal switches, see below. Actually pg_basebackup does not connect to a specific database but to the database
cluster,so any database name you supply will be ignored. 

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

"The following command-line options control the database connection parameters."

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses. 



Re: postgres standby won't start

From
Adrian Klaver
Date:
On 10/07/2015 10:01 PM, Ramalingam, Sankarakumar wrote:
> Hi Adrian,
> After a few attempts I used the following (without method=stream as it is not working on 9.1) and restored the DB on
standbysite successfully. The log says it is connecting to primary 
> streaming replication successfully connected to primary
>
> but still complains about some log files.
>
> 2015-10-08 00:47:37 EDT LOG:  streaming replication successfully connected to primary
> 2015-10-08 00:53:53 EDT LOG:  received smart shutdown request
> 2015-10-08 00:53:53 EDT FATAL:  terminating walreceiver process due to administrator command
> 2015-10-08 00:53:53 EDT LOG:  shutting down
> 2015-10-08 00:53:53 EDT LOG:  database system is shut down
> 2015-10-08 00:54:04 EDT LOG:  database system was shut down in recovery at 2015-10-08 00:53:53 EDT
> cp: cannot stat `/db_logs/pgsql/archive/0000001A.history': No such file or directory
> 2015-10-08 00:54:04 EDT LOG:  entering standby mode
> cp: cannot stat `/db_logs/pgsql/archive/0000001A00000845000000C9': No such file or directory
> 2015-10-08 00:54:04 EDT LOG:  redo starts at 845/C9000020
> 2015-10-08 00:54:04 EDT LOG:  consistent recovery state reached at 845/CA000000
> 2015-10-08 00:54:04 EDT LOG:  database system is ready to accept read only connections
> cp: cannot stat `/db_logs/pgsql/archive/0000001A00000845000000CA': No such file or directory
> 2015-10-08 00:54:04 EDT LOG:  streaming replication successfully connected to primary
> 2015-10-08 00:56:21 EDT LOG:  connection received: host=[local]
> 2015-10-08 00:56:21 EDT LOG:  connection authorized: user=postgres database=clients

So what is '/db_logs/pgsql/archive/'?

For the moment I going to guess it is a WAL archive location. If so, do
you have a recovery.conf on the standby and an restore_command in it
that points to that location?

>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Wednesday, October 07, 2015 8:06 PM
> To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] postgres standby won't start
>
> On 10/07/2015 01:30 PM, Ramalingam, Sankarakumar wrote:
>> Hello Adrian,
>> Can I use this command so that all the data is streamed directly to the target. Target is pretty much unusable now.
ShouldI get rid of all items under my $PGDATA and leave the empty before doing the following? 
>>
>> pg_basebackup -h <my target hostname>  -D <my target host directory>
>> -P -U replication --xlog-method=stream
>
> Just to be sure the target is the standby server, correct?
>
> If so you do not want this:
>
> -h <my target hostname>
>
> -h is for the server you are taking the backup of, or to put it another way the flow is from -h to -D.
>
> If you are running pg_basebackup on the same machine as the primary then:
>
> pg_basebackup  -D <my target host directory>  -P -U replication --xlog-method=stream
>
> This assumes there is only one instance of Postgres running on that machine and it is running on port 5432. If not
thenyou will need to specify -p <correct_port> 
>
> pg_basebackup is a client program, so like other such programs you have to tell it what database(cluster) to connect
tousing the normal switches, see below. Actually pg_basebackup does not connect to a specific database but to the
databasecluster, so any database name you supply will be ignored. 
>
> http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html
>
> "The following command-line options control the database connection parameters."
>
>>
>>
>>
>> Thanks
>> Kumar Ramalingam
>> X6015288
>>
>>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
> The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses. 
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: postgres standby won't start

From
"Ramalingam, Sankarakumar"
Date:
Yes it is and Yes I do have the recovery file.
Here is the scenario. It looks like the logs are not getting shipped over to the standby. After I dragged the WAL files
fromPrimary to Standby and restarted, I see some successful recovery messages with the file names mentioned. Right now
thestandby is now functional as READ ONLY. 
One question would be why logs are not getting shipped to standby even though my log say connection to primary
successful.

Another question, if I have to make it a standalone DB instead of standby, now that I have the required data in place,
whatchanges I need to do in Primary as well as here. 

Thanks a lot for your continuous response.

P.S. I did use pg_basebackup and moved the tar file to standby and overwrote (after cleaning up data folder) the data
folderand then started the db 



Thanks
Kumar Ramalingam
X6015288


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Thursday, October 08, 2015 9:31 AM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/07/2015 10:01 PM, Ramalingam, Sankarakumar wrote:
> Hi Adrian,
> After a few attempts I used the following (without method=stream as it
> is not working on 9.1) and restored the DB on standby site
> successfully. The log says it is connecting to primary streaming
> replication successfully connected to primary
>
> but still complains about some log files.
>
> 2015-10-08 00:47:37 EDT LOG:  streaming replication successfully
> connected to primary
> 2015-10-08 00:53:53 EDT LOG:  received smart shutdown request
> 2015-10-08 00:53:53 EDT FATAL:  terminating walreceiver process due to
> administrator command
> 2015-10-08 00:53:53 EDT LOG:  shutting down
> 2015-10-08 00:53:53 EDT LOG:  database system is shut down
> 2015-10-08 00:54:04 EDT LOG:  database system was shut down in
> recovery at 2015-10-08 00:53:53 EDT
> cp: cannot stat `/db_logs/pgsql/archive/0000001A.history': No such
> file or directory
> 2015-10-08 00:54:04 EDT LOG:  entering standby mode
> cp: cannot stat `/db_logs/pgsql/archive/0000001A00000845000000C9': No
> such file or directory
> 2015-10-08 00:54:04 EDT LOG:  redo starts at 845/C9000020
> 2015-10-08 00:54:04 EDT LOG:  consistent recovery state reached at
> 845/CA000000
> 2015-10-08 00:54:04 EDT LOG:  database system is ready to accept read
> only connections
> cp: cannot stat `/db_logs/pgsql/archive/0000001A00000845000000CA': No
> such file or directory
> 2015-10-08 00:54:04 EDT LOG:  streaming replication successfully
> connected to primary
> 2015-10-08 00:56:21 EDT LOG:  connection received: host=[local]
> 2015-10-08 00:56:21 EDT LOG:  connection authorized: user=postgres
> database=clients

So what is '/db_logs/pgsql/archive/'?

For the moment I going to guess it is a WAL archive location. If so, do you have a recovery.conf on the standby and an
restore_commandin it that points to that location? 

>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Wednesday, October 07, 2015 8:06 PM
> To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] postgres standby won't start
>
> On 10/07/2015 01:30 PM, Ramalingam, Sankarakumar wrote:
>> Hello Adrian,
>> Can I use this command so that all the data is streamed directly to the target. Target is pretty much unusable now.
ShouldI get rid of all items under my $PGDATA and leave the empty before doing the following? 
>>
>> pg_basebackup -h <my target hostname>  -D <my target host directory>
>> -P -U replication --xlog-method=stream
>
> Just to be sure the target is the standby server, correct?
>
> If so you do not want this:
>
> -h <my target hostname>
>
> -h is for the server you are taking the backup of, or to put it another way the flow is from -h to -D.
>
> If you are running pg_basebackup on the same machine as the primary then:
>
> pg_basebackup  -D <my target host directory>  -P -U replication --xlog-method=stream
>
> This assumes there is only one instance of Postgres running on that machine and it is running on port 5432. If not
thenyou will need to specify -p <correct_port> 
>
> pg_basebackup is a client program, so like other such programs you have to tell it what database(cluster) to connect
tousing the normal switches, see below. Actually pg_basebackup does not connect to a specific database but to the
databasecluster, so any database name you supply will be ignored. 
>
> http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html
>
> "The following command-line options control the database connection parameters."
>
>>
>>
>>
>> Thanks
>> Kumar Ramalingam
>> X6015288
>>
>>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
> The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses. 
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses. 



Re: postgres standby won't start

From
Adrian Klaver
Date:
On 10/08/2015 08:27 AM, Ramalingam, Sankarakumar wrote:
> Yes it is and Yes I do have the recovery file.

I would suggest you and the other folks involved take a  look here:

http://www.postgresql.org/docs/9.4/interactive/warm-standby.html

It will go a long way to explaining what you are seeing and a great deal
more. Once you have read through the above, then feel free to post any
questions that were not answered in the docs.

> Here is the scenario. It looks like the logs are not getting shipped over to the standby. After I dragged the WAL
filesfrom Primary to Standby and restarted, I see some successful recovery messages with the file names mentioned.
Rightnow the standby is now functional as READ ONLY. 
> One question would be why logs are not getting shipped to standby even though my log say connection to primary
successful.
>
> Another question, if I have to make it a standalone DB instead of standby, now that I have the required data in
place,what changes I need to do in Primary as well as here. 
>
> Thanks a lot for your continuous response.
>
> P.S. I did use pg_basebackup and moved the tar file to standby and overwrote (after cleaning up data folder) the data
folderand then started the db 
>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com