Re: [GENERAL] archive_command not being executed

2017-11-14 Thread Eric D 
Hi Jeremy,


Thanks for the info on 9.3 vs 9.5.  While searching for Paul's suggestion I'd 
seen the same thing, but didn't "reply all" so my response didn't make it into 
the mailing list.


Regarding your question, as I understand the process, you need to get the WAL 
files being shipped over to the standby before you start streaming replication. 
 The sequence of events I think is supposed to happen is:


  1.  Start WAL file shipping from master->standby
  2.  Run pg_basebackup from master->standby
  3.  With a recovery.conf in place, start the standby.  The recovery.conf 
needs the connection info for the master for the streaming replication, but it 
also needs the location of the WAL files, to replay what's been going on during 
the pg_basebackup.

I don't think streaming replication has a way to pick up the activity covered 
in the WAL files.



From: Jeremy Schneider <schnei...@ardentperf.com>
Sent: Monday, November 13, 2017 3:56 PM
To: eric...@hotmail.com
Cc: PostgreSQL General; Paul Jungwirth
Subject: Re: [GENERAL] archive_command not being executed

Hi Eric,

Thanks for using PostgreSQL!

On Fri, Nov 10, 2017 at 9:26 AM, Paul Jungwirth
<p...@illuminatedcomputing.com> wrote:
> Oh this has happened to me before. :-) On SB1 you need to set
> archive_mode to always (not on). Otherwise it is ignored when running as a
> standby.

It looks to me like this feature was not added until 9.5 and Eric is
running 9.3  :(

> On 11/10/2017 09:10 AM, Eric D wrote:
>> I have a standby db server (SB1) that will soon become the master.  SB1
>> is set up with streaming replication from the current master.  I'm
>> trying to set up a third server (SB2) as a slave/standby to SB1, so that
>> when SB1 becomes the master, there will be a standby for it.  First step
>> is to get WAL files shipped from SB1->SB2.

Eric,

>From my reading of the docs and commit logs, standby databases
couldn't archive their WALs until 9.5.

https://www.postgresql.org/message-id/cankgpbs7qgakgq-opzy0esam6+wue5mgpyehcgo_eoq7tjv...@mail.gmail.com
http://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-archive-mode-always/
Postgres 9.5 feature highlight - archive_mode = 
always<http://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-archive-mode-always/>
paquier.xyz
Postgres 9.5 feature highlight - archive_mode = always




There is a section in the 9.3 docs about cascading replication:
https://www.postgresql.org/docs/9.3/static/warm-standby.html#CASCADING-REPLICATION
PostgreSQL: Documentation: 9.3: Log-Shipping Standby 
Servers<https://www.postgresql.org/docs/9.3/static/warm-standby.html#CASCADING-REPLICATION>
www.postgresql.org
25.2. Log-Shipping Standby Servers. Continuous archiving can be used to create 
a high availability (HA) cluster configuration with one or more standby servers 
ready ...




It seems to me this is based on streaming replication only.  Looks
like it's not designed (in 9.3) to start archiving until it becomes a
primary.  "You will also need to set primary_conninfo in the
downstream standby to point to the cascading standby."

Are you able to configure a cascading replica by using streaming
replication on your 9.3 system, without WAL archiving on the standby?

-Jeremy

--
http://about.me/jeremy_schneider
[https://aboutme.imgix.net/background/users/j/e/r/jeremy_schneider_1364416900_46.jpg?q=80=1=format=max=250=140=0,0,1396,732]<http://about.me/jeremy_schneider>

Jeremy Schneider on about.me<http://about.me/jeremy_schneider>
about.me
I am an Engineer, consultant, and Trainer in the United States. Read my blog.

Re: [GENERAL] archive_command not being executed

2017-11-13 Thread Eric D 
I'd come to the same conclusion Jeremy had about 9.5 being the first version 
with the 'always' option for archive_mode.   Looking at pg_receivexlog, that 
might work, but with me being a total noob I'm wary of the various steps I'd 
have to take in going from:


Master -> streaming replication to -> SB1 -> pg_receivexlog to -> SB2


to:


New Master (Old SB1) -> streaming replication to -> SB2


And whether or not the conversion from pg_receivexlog to normal streaming 
replication would maintain data integrity.  I need to skew this towards 
simplicity or I'll likely screw it up.


My current thought is to cut off master, promote SB1, set up WAL file shipping 
to SB2, start a pg_basebackup, make SB1 live, then run for a couple days with 
no backup as the pg_basebackup runs.   Far from ideal but at least I have gone 
through most of this before.



From: Michael Paquier <michael.paqu...@gmail.com>
Sent: Monday, November 13, 2017 6:01 PM
To: Jeremy Schneider
Cc: eric...@hotmail.com; PostgreSQL General; Paul Jungwirth
Subject: Re: [GENERAL] archive_command not being executed

On Tue, Nov 14, 2017 at 8:56 AM, Jeremy Schneider
<schnei...@ardentperf.com> wrote:
> From my reading of the docs and commit logs, standby databases
> couldn't archive their WALs until 9.5.

pg_receivexlog is available in 9.3. You could leverage your archives
with it easily, by for example connecting it to a standby you'd like
to get the archives from.
--
Michael

Re: [GENERAL] archive_command not being executed

2017-11-13 Thread Michael Paquier 
On Tue, Nov 14, 2017 at 8:56 AM, Jeremy Schneider
 wrote:
> From my reading of the docs and commit logs, standby databases
> couldn't archive their WALs until 9.5.

pg_receivexlog is available in 9.3. You could leverage your archives
with it easily, by for example connecting it to a standby you'd like
to get the archives from.
-- 
Michael


-- 
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] archive_command not being executed

2017-11-13 Thread Jeremy Schneider 
Hi Eric,

Thanks for using PostgreSQL!

On Fri, Nov 10, 2017 at 9:26 AM, Paul Jungwirth
 wrote:
> Oh this has happened to me before. :-) On SB1 you need to set
> archive_mode to always (not on). Otherwise it is ignored when running as a
> standby.

It looks to me like this feature was not added until 9.5 and Eric is
running 9.3  :(

> On 11/10/2017 09:10 AM, Eric D wrote:
>> I have a standby db server (SB1) that will soon become the master.  SB1
>> is set up with streaming replication from the current master.  I'm
>> trying to set up a third server (SB2) as a slave/standby to SB1, so that
>> when SB1 becomes the master, there will be a standby for it.  First step
>> is to get WAL files shipped from SB1->SB2.

Eric,

>From my reading of the docs and commit logs, standby databases
couldn't archive their WALs until 9.5.

https://www.postgresql.org/message-id/cankgpbs7qgakgq-opzy0esam6+wue5mgpyehcgo_eoq7tjv...@mail.gmail.com
http://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-archive-mode-always/

There is a section in the 9.3 docs about cascading replication:
https://www.postgresql.org/docs/9.3/static/warm-standby.html#CASCADING-REPLICATION

It seems to me this is based on streaming replication only.  Looks
like it's not designed (in 9.3) to start archiving until it becomes a
primary.  "You will also need to set primary_conninfo in the
downstream standby to point to the cascading standby."

Are you able to configure a cascading replica by using streaming
replication on your 9.3 system, without WAL archiving on the standby?

-Jeremy

-- 
http://about.me/jeremy_schneider


-- 
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] archive_command not being executed

2017-11-10 Thread Paul Jungwirth 

On 11/10/2017 09:10 AM, Eric D wrote:

I have a standby db server (SB1) that will soon become the master.  SB1
is set up with streaming replication from the current master.  I'm
trying to set up a third server (SB2) as a slave/standby to SB1, so that
when SB1 becomes the master, there will be a standby for it.  First step
is to get WAL files shipped from SB1->SB2.


Oh this has happened to me before. :-) On SB1 you need to set
archive_mode to always (not on). Otherwise it is ignored when running as 
a standby.


Btw just in case you are using Ansible here is an unmerged PR for the 
postgres role: https://github.com/ANXS/postgresql/pull/254


Paul


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

[GENERAL] archive_command not being executed

2017-11-10 Thread Eric D 
I have a standby db server (SB1) that will soon become the master.  SB1 is set 
up with streaming replication from the current master.  I'm trying to set up a 
third server (SB2) as a slave/standby to SB1, so that when SB1 becomes the 
master, there will be a standby for it.  First step is to get WAL files shipped 
from SB1->SB2.


Problem is I can't get the archive_command of SB1 to execute.  The same 
archive_command on our current master calls into a script that scp's the WAL 
files to SB1.  This works.  I've also confirmed the script works from SB1->SB2 
when run from the command line, as the postgres user, w/o a password request.


Furthermore, on SB1 I replaced the call to the script with a touch command, to 
see if archive_command was getting called at all:


# This script does the scp of the WAL file to the designated server/directory
#archive_command = '/opt/printra/sbin/handle_wal_files.sh %p db-backup 
/backup/WAL_ARCHIVE/auto'
archive_command = 'touch /var/lib/pgsql/testtouch'


and the testtouch file did not appear.


Here are some relevant settings from SB1:


postgres=# select * from pg_settings where name like 'archive%';

-[ RECORD 1 
]--

name   | archive_command

setting| touch /var/lib/pgsql/testtouch

unit   |

category   | Write-Ahead Log / Archiving

short_desc | Sets the shell command that will be called to archive a WAL file.

extra_desc |

context| sighup

vartype| string

source | configuration file

min_val|

max_val|

enumvals   |

boot_val   |

reset_val  | touch /var/lib/pgsql/testtouch

sourcefile | /var/lib/pgsql/9.3/data/postgresql.conf

sourceline | 607

-[ RECORD 2 
]--

name   | archive_mode

setting| on

unit   |

category   | Write-Ahead Log / Archiving

short_desc | Allows archiving of WAL files using archive_command.

extra_desc |

context| postmaster

vartype| bool

source | configuration file

min_val|

max_val|

enumvals   |

boot_val   | off

reset_val  | on

sourcefile | /var/lib/pgsql/9.3/data/postgresql.conf

sourceline | 604

-[ RECORD 3 
]--

name   | archive_timeout

setting| 0

unit   | s

category   | Write-Ahead Log / Archiving

short_desc | Forces a switch to the next xlog file if a new file has not been 
started within N seconds.

extra_desc |

context| sighup

vartype| integer

source | default

min_val| 0

max_val| 1073741823

enumvals   |

boot_val   | 0

reset_val  | 0

sourcefile |

sourceline |



postgres=# select version();

-[ RECORD 1 
]---

version | PostgreSQL 9.3.19 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 
4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit


I've bumped up logging to debug1 and have not seen anything that looks related 
to the archive_command.  Bumping up to debug2 was really noisy and didn't seem 
to yield any useful info either.  Any ideas?