-------- Message transféré --------
Hello,
I can give ou a sh script ou delete wall older than 30 days.
1) I don't know if wall_buffer = wal_segment, sorry
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.
3) The first of the two parameters.
But if you want to keep wal until the slave standby server you
can do another way :
first possibility :
you don't change your parameters. And when slave doesn't found wal
for synchronize you push on it wal from archive_command.
look at that, you have configure your server to save wal in this
way :
archive_command = 'cp %p /opt/postgres/%f'
If the wall is recycling in pg_xlog directory you can find it
in
/opt/postgres/
you can duplicate it in
pg_xlog of your slave
sudo cp /opt/postgres/* login@255.255.255.255:/var/lib/postgresql/9.6/main/pg_xlog
second choise :
create a slot replication :
you define on the master a slot replication (postgresql.conf) and name it like this :
https://www.postgresql.org/docs/9.6/static/warm-standby.html#STREAMING-REPLICATION-SLOTS
1. on the master sql :
SELECT * FROM pg_create_physical_replication_slot('your_name_replica')
2. modify recovery.conf on the slave with :
primary_slot_name = 'your_name_replica'
and the master doesn't recycling wal until the slave come to take
it !
under you find sh script to delete wal older than 30 days :
----------------------------------------------
#!/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
find /mnt/sauvdump/replication/ -type f -mtime +30 -delete
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
------------------------------------------------------------
Hi,
I have following config
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 2GB
synchronous_commit = on
archive_mode = on
archive_command = 'cp %p /opt/postgres/%f'
max_wal_senders = 4
wal_keep_segments = 1024
synchronous_standby_names = 'pgslave01'
1. Can we raise wal segment size to more than
16MB and is it same as `wal_buffers` ?
2. During data loading I'm pushing 4GB data, and
taking an assumption that my network is slow,
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?
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?
Thanks!
Prince Pathria
Systems Architect Intern
Evive
+91 9478670472
goevive.com