Re: [GENERAL] Help on recovering my standby

[Patrick B]

I had the same issue... A slave server had missing wal_files... and it
wasn't synced.

I had to re-sync all the DB, by running the pg_basebackup command

So.. basically, what I did is:

1 - Ensure that the wal_files are being inserted into the slave
2 - Backup the recovery.conf, postgresql.conf and pg_hba.conf
3 - Delete all the current data folder, by doing: rm -rf
/var/lib/pgsql/9.2/data/*
4 - Running the pg_basebackup command to re-sync the DB from another slave
to the slave that I wanna fix
5 - Replace the .conf backup files into the new data folder
6 - Start postgres

And it worked nice

Patrick

Re: [GENERAL] Help on recovering my standby

[Melvin Davidson]

On Wed, Jun 22, 2016 at 12:22 PM, Alan Hodgson 
wrote:

> On Tuesday 21 June 2016 19:34:18 Ramalingam, Sankarakumar wrote:
> > Hi I have my standby (streaming replication) down due to missing wal
> files.
> > You would see the same error in the logs stating "cannot find the wal
> file
> > ..." What is the best way to get it going so that when we switch between
> > standby and primary once in a while they are in sync?
> >
> > Currently I am working on a CERT server and hence there is no outage
> > concerns. I need to repeat the same process on prod once I get it going
> > successfully. Any help is appreciated.
> >
>
> You should keep your WAL files from the master for at least as long as the
> slave might be offline (plus startup time), somewhere the slave can copy
> them
> from when needed (shared file system, object store, scp target, whatever).
>
> See the postgresql.conf parameter archive_command and the corresponding
> recovery.conf parameter restore_command.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

It would be really helpful if you included PostgreSQL version and O/S in
your problem description, but since you have not, I will give a "generic"
fix.

It is doubtful, but you can check the pg_xlog on the master for the
"missing" WAL files and if they are there, simply rsync them to the standby.
If you are truly missing WAL files in your slave/standy, then  you need to
rebuild the slave as per standard procedures.
Make sure you change wal_keep_segments value on the master to be
sufficiently highly so that the problem does not occur again.
Once you make the change, be sure to reload the config file on the master
Either
SELECT pg_reload_conf();
or
pg_ctl reload -D your_data_dir



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Help on recovering my standby

[Alan Hodgson]

On Tuesday 21 June 2016 19:34:18 Ramalingam, Sankarakumar wrote:
> Hi I have my standby (streaming replication) down due to missing wal files.
> You would see the same error in the logs stating "cannot find the wal file
> ..." What is the best way to get it going so that when we switch between
> standby and primary once in a while they are in sync?
> 
> Currently I am working on a CERT server and hence there is no outage
> concerns. I need to repeat the same process on prod once I get it going
> successfully. Any help is appreciated.
> 

You should keep your WAL files from the master for at least as long as the 
slave might be offline (plus startup time), somewhere the slave can copy them 
from when needed (shared file system, object store, scp target, whatever).

See the postgresql.conf parameter archive_command and the corresponding  
recovery.conf parameter restore_command.



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

[GENERAL] Help on recovering my standby

[Ramalingam, Sankarakumar]

Hi I have my standby (streaming replication) down due to missing wal files. You 
would see the same error in the logs stating "cannot find the wal file ..."
What is the best way to get it going so that when we switch between standby and 
primary once in a while they are in sync?

Currently I am working on a CERT server and hence there is no outage concerns. 
I need to repeat the same process on prod once I get it going successfully. Any 
help is 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.