| From: | "CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG" <yann(dot)convers(at)developpement-durable(dot)gouv(dot)fr> |
|---|---|
| To: | pgsql-admin(at)postgresql(dot)org |
| Subject: | Tr: Re: WAL segments held for replcation |
| Date: | 2018-09-24 07:47:11 |
| Message-ID: | 8312dc8a-c359-c745-c9c7-bdcd30b6a9ea@developpement-durable.gouv.fr |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
sorry I forgot to post it here too...<i> </i><i></i><br>
<div class="moz-forward-container"><br>
<br>
-------- Message transféré --------
<table class="moz-email-headers-table" border="0" cellpadding="0"
cellspacing="0">
<tbody>
<tr>
<th align="RIGHT" nowrap="nowrap" valign="BASELINE">Sujet :
</th>
<td>Re: WAL segments held for replcation</td>
</tr>
<tr>
<th align="RIGHT" nowrap="nowrap" valign="BASELINE">Date : </th>
<td>Mon, 24 Sep 2018 09:08:40 +0200</td>
</tr>
<tr>
<th align="RIGHT" nowrap="nowrap" valign="BASELINE">De : </th>
<td>CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG
<a class="moz-txt-link-rfc2396E" href="mailto:yann(dot)convers(at)developpement-durable(dot)gouv(dot)fr"><yann(dot)convers(at)developpement-durable(dot)gouv(dot)fr></a></td>
</tr>
<tr>
<th align="RIGHT" nowrap="nowrap" valign="BASELINE">Organisation :
</th>
<td>DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG</td>
</tr>
<tr>
<th align="RIGHT" nowrap="nowrap" valign="BASELINE">Pour : </th>
<td>Prince Pathria <a class="moz-txt-link-rfc2396E" href="mailto:prince(dot)pathria(at)goevive(dot)com"><prince(dot)pathria(at)goevive(dot)com></a></td>
</tr>
</tbody>
</table>
<br>
<br>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
Hello,<br>
<br>
I can give ou a sh script ou delete wall older than 30 days.<br>
<br>
1) I don't know if wall_buffer = wal_segment, sorry<br>
<br>
2) I think master can't push either streaming in replicat
syncrhone and standby replicat because your server must only keep
2GB of wall_segment.<br>
<br>
3) The first of the two parameters.<br>
<br>
But if you want to keep wal until the slave standby server you
can do another way :<br>
<br>
first possibility :<br>
<br>
you don't change your parameters. And when slave doesn't found wal
for synchronize you push on it wal from archive_command.<br>
<br>
look at that, you have configure your server to save wal in this
way :<br>
<br>
<b> archive_command = 'cp %p /opt/postgres/%f'<br>
<br>
</b>If the wall is recycling in pg_xlog directory you can find it
in<b> /opt/postgres/</b><b><span style="white-space:pre">
</span></b><span style="white-space:pre"></span>you can duplicate it in
pg_xlog of your slave<br>
<pre>sudo cp <b>/opt/postgres/</b><b><span style="white-space:pre"></span></b>* <a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:login(at)255(dot)255(dot)255(dot)255:/var/lib/postgresql/9.6/main/pg_xlog">login(at)255(dot)255(dot)255(dot)255:/var/lib/postgresql/9.6/main/pg_xlog</a>
second choise :
create a slot replication :
you define on the master a slot replication (postgresql.conf) and name it like this :
<a moz-do-not-send="true" class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/9.6/static/warm-standby.html#STREAMING-REPLICATION-SLOTS">https://www.postgresql.org/docs/9.6/static/warm-standby.html#STREAMING-REPLICATION-SLOTS</a>
<b>1. on the master sql :</b>
SELECT * FROM pg_create_physical_replication_slot('your_name_replica')
<b>2. modify recovery.conf on the slave with :</b>
primary_slot_name = 'your_name_replica'
</pre>
and the master doesn't recycling wal until the slave come to take
it !<br>
<br>
<br>
under you find sh script to delete wal older than 30 days :<br>
----------------------------------------------<br>
<pre>#!/bin/bash
# variables
#bash visant a supprimer les wal datant de plus de 30 jours généré par la commande archive_command de postgres
#maj du 7/12/2017 par yc rajoutant le chemin/nom des fichiers wal supprimés dans le log
journal=/home/sigdreal/log/journal_menage_wal.log
ST=${journal:0:${#journal}-3}"st"
if [ -e $journal ]; then mv $journal /home/sigdreal/log/sauv_n1/
fi
if [ -e $ST ]; then mv $ST /home/sigdreal/log/sauv_n1/
fi
date>>$journal
date>>$ST
echo "liste des fichiers à supprimer">>$journal
echo "---------------------------------------">>$journal
find /mnt/sauvdump/replication/ -type f -mtime +25>>$journal
<b>find /mnt/sauvdump/replication/ -type f -mtime +30 -delete</b>
tp=$((tp+`echo $?`))
if [ $tp = 0 ]; then echo "---suppresion effectuée---------------">>$journal
else echo "---suppression non réalisée------------------------">>$journal
fi
echo $tp>>$ST
date>>$ST</pre>
------------------------------------------------------------<br>
<br>
<div class="moz-cite-prefix">Le 21/09/2018 à 23:11, "> Prince
Pathria (par Internet, dépôt <a moz-do-not-send="true"
class="moz-txt-link-abbreviated"
href="mailto:pgsql-admin-owner+m63745-89324(at)lists(dot)postgresql(dot)org">pgsql-admin-owner+m63745-89324(at)lists(dot)postgresql(dot)org</a>)"
a écrit :<br>
</div>
<blockquote
cite="mid:CAON0DawEmb-cBqBwzk2-dXgvVqNa137sZsqJXPmuTAWH_BGPGQ(at)mail(dot)gmail(dot)com"
type="cite">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">Hi,
<div><br>
</div>
<div>I have following config</div>
<div>
<div>wal_buffers = 16MB</div>
<div>
<div>min_wal_size = 1GB</div>
<div>max_wal_size = 2GB</div>
</div>
<div>
<div>synchronous_commit = on<span style="white-space:pre"> </span>
</div>
<div>archive_mode = on<span style="white-space:pre"> </span></div>
<div>archive_command = 'cp %p /opt/postgres/%f'<span style="white-space:pre"> </span></div>
<div>max_wal_senders = 4<span style="white-space:pre"> </span></div>
<div>wal_keep_segments = 1024<span style="white-space:pre"> </span></div>
<div>synchronous_standby_names = 'pgslave01'</div>
</div>
<div><br>
</div>
<div><br>
</div>
<div>1. Can we raise wal segment size to more than
16MB and is it same as `wal_buffers` ?</div>
<div><br>
</div>
<div>2. During data loading I'm pushing 4GB data, and
taking an assumption that my network is slow,</div>
<div>Will master keep the segments in pg_wal more than
wal_keep_segment value such that it's pulled by
standby nodes or it will wipe if off?</div>
<div><br>
</div>
<div>3. 1024 wal_keep_segments of 16 mb each makes
16GB of segments kept but I have kept max_wal_size =
2GB. Which one will be prioritised max_wal_size or
wal_keep_segment?</div>
<div><br>
</div>
<div>Thanks! </div>
<div>
<div dir="ltr" class="gmail_signature">
<div dir="ltr">
<div>
<div dir="ltr">
<div>
<div dir="ltr">
<div><span style="font-size:11.3333px;white-space:pre-wrap;color:rgb(136,136,136);font-family:Arial">
</span></div>
<div><span style="font-size:11.3333px;white-space:pre-wrap;color:rgb(136,136,136);font-family:Arial">Prince Pathria</span><br>
</div>
<div><font face="Arial" color="#888888"><span style="font-size:11.3333px;white-space:pre-wrap">Systems Architect Intern
Evive
+91 9478670472
<a moz-do-not-send="true" href="http://goevive.com" target="_blank">goevive.com</a></span></font><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</blockquote>
<br>
</div>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 8.6 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG | 2018-09-24 07:53:34 | Re: Archive clean up command in recovery.conf |
| Previous Message | Laurenz Albe | 2018-09-24 06:22:04 | Re: Archive clean up command in recovery.conf |