Re: [GENERAL] pg_xlog on a hot_stanby slave
On 16/06/2015 10:55, Xavier 12 wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' How can I reduce the number of WAL files on the hot_stanby slave ? Thanks Regards. Xavier C. I wonder why you are doing cp in your recovery.conf on the slave. That is quite correct when the streaming can't get WAL from the master. But cp is probably not the right tool. You also cp from the master archive directory, and are cleaning on that directory as well. You don't clean up the standby xlog directory. And cp may copy incomplete WAL files. The streaming replication can take care of your xlog clean up, until you introduce WAL files by another mean (manual cp for instance). S. -- Stéphane Schildknecht Contact régional PostgreSQL pour l'Europe francophone Loxodata - Conseil, expertise et formations 06.17.11.37.42 -- 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] pg_xlog on a hot_stanby slave
On 29/06/2015 11:38, Stéphane Schildknecht wrote: On 16/06/2015 10:55, Xavier 12 wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' How can I reduce the number of WAL files on the hot_stanby slave ? Thanks Regards. Xavier C. I wonder why you are doing cp in your recovery.conf on the slave. That is quite correct when the streaming can't get WAL from the master. But cp is probably not the right tool. You also cp from the master archive directory, and are cleaning on that directory as well. You don't clean up the standby xlog directory. And cp may copy incomplete WAL files. The streaming replication can take care of your xlog clean up, until you introduce WAL files by another mean (manual cp for instance). S. cp because /var/lib/postgresq/9.1/wal_archive/ is a temporary directory. I use it to build the replication (copy wal with rsync from the master). The slave use it to start, then next wal are written ton /var/lib/postgresql/9.1/main/pg_xlog. Xavier C. -- 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] pg_xlog on a hot_stanby slave
On 19/06/2015 03:31, Sameer Kumar wrote: On Thu, 18 Jun 2015 15:17 Xavier 12 mania...@gmail.com mailto:mania...@gmail.com wrote: On 18/06/2015 04:00, Sameer Kumar wrote: On Wed, 17 Jun 2015 15:24 Xavier 12 mania...@gmail.com mailto:mania...@gmail.com wrote: On 17/06/2015 03:17, Sameer Kumar wrote: On Tue, 16 Jun 2015 16:55 Xavier 12 mania...@gmail.com mailto:mania...@gmail.com wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f mailto:bar...@nas.lan:/data/pgbarman/psql01/incoming/%25f' max_wal_senders = 5 wal_keep_segments = 64 What's this parameter's value on Slave? Hm... You have a point. That autovacuum parameter seems to be useless on a slave. I'll try to remove it and check pg_xlog. That was not my point. I was actually asking about wal_keep_segment. Nevermind I found that I had misses the info (found it below. Please see my response). Besides I try to keep my master and standby config as same as possible(so my advise ia to not switchoff autovacuum). The parameters which are imeffective on slave anyways won't have an effect. Same goes for parameters on master. This helps me when I swap roles or do a failover. I have less parameters to be worried about. Okay Can you check the pg_log for log files. They may have se info? I am sorry if you have already provided that info (after I finish I will try to look at your previous emails on this thread) Nothing... /var/log/postgresql/postgresql-2015-06-17_31.log is empty (except old messages at the begining related to a configuration issue - which is now solved - after rebuilding the cluster yesterday). /var/log/syslog has nothing but these : Jun 18 09:10:11 Bdd02 postgres[28400]: [2-1] 2015-06-18 09:10:11 CEST LOG: paquet de d?marrage incomplet Jun 18 09:10:41 Bdd02 postgres[28523]: [2-1] 2015-06-18 09:10:41 CEST LOG: paquet de d?marrage incomplet Jun 18 09:11:11 Bdd02 postgres[28557]: [2-1] 2015-06-18 09:11:11 CEST LOG: paquet de d?marrage incomplet Jun 18 09:11:41 Bdd02 postgres[28652]: [2-1] 2015-06-18 09:11:41 CEST LOG: paquet de d?marrage incomplet Jun 18 09:12:11 Bdd02 postgres[28752]: [2-1] 2015-06-18 09:12:11 CEST LOG: paquet de d?marrage incomplet Jun 18 09:12:41 Bdd02 postgres[28862]: [2-1] 2015-06-18 09:12:41 CEST LOG: paquet de d?marrage incomplet Jun 18 09:13:11 Bdd02 postgres[28891]: [2-1] 2015-06-18 09:13:11 CEST LOG: paquet de d?marrage incomplet Jun 18 09:13:40 Bdd02 postgres[28987]: [2-1] 2015-06-18 09:13:40 CEST LOG: paquet de d?marrage incomplet These messages are related to Zabbix (psql port check). You sure these are the only messages you have in the log files? Also can you share the vacuum cost parameters in your environm en t? I don't understand that part... is this in postgresql.conf ? There are vacuum cost parameters in postgresql.conf http://www.postgresql.org/docs/9.4/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 Sorry I missed this somehow earlier. Any reason why you think you need to retain 32 wal files on slave? No but I get the feeling that the parameter is ignored by my slave... should I try another value ? AFAIK you don't nees this parameter to set to 0 unless you have cascaded replica pull wal
Re: [GENERAL] pg_xlog on a hot_stanby slave
On Thu, 18 Jun 2015 15:17 Xavier 12 mania...@gmail.com wrote: On 18/06/2015 04:00, Sameer Kumar wrote: On Wed, 17 Jun 2015 15:24 Xavier 12 mania...@gmail.com wrote: On 17/06/2015 03:17, Sameer Kumar wrote: On Tue, 16 Jun 2015 16:55 Xavier 12 mania...@gmail.com wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 What's this parameter's value on Slave? Hm... You have a point. That autovacuum parameter seems to be useless on a slave. I'll try to remove it and check pg_xlog. That was not my point. I was actually asking about wal_keep_segment. Nevermind I found that I had misses the info (found it below. Please see my response). Besides I try to keep my master and standby config as same as possible(so my advise ia to not switchoff autovacuum). The parameters which are imeffective on slave anyways won't have an effect. Same goes for parameters on master. This helps me when I swap roles or do a failover. I have less parameters to be worried about. Okay Can you check the pg_log for log files. They may have se info? I am sorry if you have already provided that info (after I finish I will try to look at your previous emails on this thread) Nothing... /var/log/postgresql/postgresql-2015-06-17_31.log is empty (except old messages at the begining related to a configuration issue - which is now solved - after rebuilding the cluster yesterday). /var/log/syslog has nothing but these : Jun 18 09:10:11 Bdd02 postgres[28400]: [2-1] 2015-06-18 09:10:11 CEST LOG: paquet de d?marrage incomplet Jun 18 09:10:41 Bdd02 postgres[28523]: [2-1] 2015-06-18 09:10:41 CEST LOG: paquet de d?marrage incomplet Jun 18 09:11:11 Bdd02 postgres[28557]: [2-1] 2015-06-18 09:11:11 CEST LOG: paquet de d?marrage incomplet Jun 18 09:11:41 Bdd02 postgres[28652]: [2-1] 2015-06-18 09:11:41 CEST LOG: paquet de d?marrage incomplet Jun 18 09:12:11 Bdd02 postgres[28752]: [2-1] 2015-06-18 09:12:11 CEST LOG: paquet de d?marrage incomplet Jun 18 09:12:41 Bdd02 postgres[28862]: [2-1] 2015-06-18 09:12:41 CEST LOG: paquet de d?marrage incomplet Jun 18 09:13:11 Bdd02 postgres[28891]: [2-1] 2015-06-18 09:13:11 CEST LOG: paquet de d?marrage incomplet Jun 18 09:13:40 Bdd02 postgres[28987]: [2-1] 2015-06-18 09:13:40 CEST LOG: paquet de d?marrage incomplet These messages are related to Zabbix (psql port check). You sure these are the only messages you have in the log files? Also can you share the vacuum cost parameters in your environm en t? I don't understand that part... is this in postgresql.conf ? There are vacuum cost parameters in postgresql.conf http://www.postgresql.org/docs/9.4/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 Sorry I missed this somehow earlier. Any reason why you think you need to retain 32 wal files on slave? No but I get the feeling that the parameter is ignored by my slave... should I try another value ? AFAIK you don't nees this parameter to set to 0 unless you have cascaded replica pull wal from stand by or you have backup jobs running to backup from standby. Set it to 0 on the standby and check. hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' Also consider setting hot_standby_feesback to on. I will check that parameter in the documentation, Thanks How can I reduce the number of WAL files on the hot_stanby slave ?
Re: [GENERAL] pg_xlog on a hot_stanby slave
On 18/06/2015 04:00, Sameer Kumar wrote: On Wed, 17 Jun 2015 15:24 Xavier 12 mania...@gmail.com mailto:mania...@gmail.com wrote: On 17/06/2015 03:17, Sameer Kumar wrote: On Tue, 16 Jun 2015 16:55 Xavier 12 mania...@gmail.com mailto:mania...@gmail.com wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f mailto:bar...@nas.lan:/data/pgbarman/psql01/incoming/%25f' max_wal_senders = 5 wal_keep_segments = 64 What's this parameter's value on Slave? Hm... You have a point. That autovacuum parameter seems to be useless on a slave. I'll try to remove it and check pg_xlog. That was not my point. I was actually asking about wal_keep_segment. Nevermind I found that I had misses the info (found it below. Please see my response). Besides I try to keep my master and standby config as same as possible(so my advise ia to not switchoff autovacuum). The parameters which are imeffective on slave anyways won't have an effect. Same goes for parameters on master. This helps me when I swap roles or do a failover. I have less parameters to be worried about. Okay Can you check the pg_log for log files. They may have se info? I am sorry if you have already provided that info (after I finish I will try to look at your previous emails on this thread) Nothing... /var/log/postgresql/postgresql-2015-06-17_31.log is empty (except old messages at the begining related to a configuration issue - which is now solved - after rebuilding the cluster yesterday). /var/log/syslog has nothing but these : Jun 18 09:10:11 Bdd02 postgres[28400]: [2-1] 2015-06-18 09:10:11 CEST LOG: paquet de d?marrage incomplet Jun 18 09:10:41 Bdd02 postgres[28523]: [2-1] 2015-06-18 09:10:41 CEST LOG: paquet de d?marrage incomplet Jun 18 09:11:11 Bdd02 postgres[28557]: [2-1] 2015-06-18 09:11:11 CEST LOG: paquet de d?marrage incomplet Jun 18 09:11:41 Bdd02 postgres[28652]: [2-1] 2015-06-18 09:11:41 CEST LOG: paquet de d?marrage incomplet Jun 18 09:12:11 Bdd02 postgres[28752]: [2-1] 2015-06-18 09:12:11 CEST LOG: paquet de d?marrage incomplet Jun 18 09:12:41 Bdd02 postgres[28862]: [2-1] 2015-06-18 09:12:41 CEST LOG: paquet de d?marrage incomplet Jun 18 09:13:11 Bdd02 postgres[28891]: [2-1] 2015-06-18 09:13:11 CEST LOG: paquet de d?marrage incomplet Jun 18 09:13:40 Bdd02 postgres[28987]: [2-1] 2015-06-18 09:13:40 CEST LOG: paquet de d?marrage incomplet These messages are related to Zabbix (psql port check). Also can you share the vacuum cost parameters in your environment? I don't understand that part... is this in postgresql.conf ? autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 Sorry I missed this somehow earlier. Any reason why you think you need to retain 32 wal files on slave? No but I get the feeling that the parameter is ignored by my slave... should I try another value ? hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' Also consider setting hot_standby_feesback to on. I will check that parameter in the documentation, Thanks How can I reduce the number of WAL files on the hot_stanby slave ? Thanks Regards. Xavier C. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription:
Re: [GENERAL] pg_xlog on a hot_stanby slave
On Wed, 17 Jun 2015 15:24 Xavier 12 mania...@gmail.com wrote: On 17/06/2015 03:17, Sameer Kumar wrote: On Tue, 16 Jun 2015 16:55 Xavier 12 mania...@gmail.com wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 What's this parameter's value on Slave? Hm... You have a point. That autovacuum parameter seems to be useless on a slave. I'll try to remove it and check pg_xlog. That was not my point. I was actually asking about wal_keep_segment. Nevermind I found that I had misses the info (found it below. Please see my response). Besides I try to keep my master and standby config as same as possible(so my advise ia to not switchoff autovacuum). The parameters which are imeffective on slave anyways won't have an effect. Same goes for parameters on master. This helps me when I swap roles or do a failover. I have less parameters to be worried about. Can you check the pg_log for log files. They may have se info? I am sorry if you have already provided that info (after I finish I will try to look at your previous emails on this thread) Also can you share the vacuum cost parameters in your environment? autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 Sorry I missed this somehow earlier. Any reason why you think you need to retain 32 wal files on slave? hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' Also consider setting hot_standby_feesback to on. How can I reduce the number of WAL files on the hot_stanby slave ? Thanks Regards. Xavier C. -- 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] pg_xlog on a hot_stanby slave
On 17/06/2015 03:17, Sameer Kumar wrote: On Tue, 16 Jun 2015 16:55 Xavier 12 mania...@gmail.com mailto:mania...@gmail.com wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 What's this parameter's value on Slave? Hm... You have a point. That autovacuum parameter seems to be useless on a slave. I'll try to remove it and check pg_xlog. Xavier C. autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' How can I reduce the number of WAL files on the hot_stanby slave ? Thanks Regards. Xavier C. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_xlog on a hot_stanby slave
On 16/06/2015 22:28, Guillaume Lelarge wrote: 2015-06-16 15:55 GMT+02:00 Xavier 12 mania...@gmail.com mailto:mania...@gmail.com: I don't think so. There is no archive_command and the master doesn't ship its wal here. But how can I check that ? What's the complete path to the directory on the salve that contains 951 files? what does PostgreSQL say on its log files? in /var/lib/postgresql/9.1/main/pg_xlog/ 1059 files today. Too much to copy/paste here. Here are the first ones : -rw--- 1 postgres postgres 16777216 Jun 9 08:40 0004040B007E -rw--- 1 postgres postgres 16777216 Jun 9 08:41 0004040B007F -rw--- 1 postgres postgres 16777216 Jun 9 08:42 0004040B0080 -rw--- 1 postgres postgres 16777216 Jun 9 08:44 0004040B0081 There are no .done or .ready and archive_status is empty. Nothing critical in the logs : Jun 17 08:55:11 psql02 postgres[4231]: [2-1] 2015-06-17 08:55:11 CEST LOG: paquet de d?marrage incomplet Jun 17 08:55:41 psql02 postgres[4322]: [2-1] 2015-06-17 08:55:41 CEST LOG: paquet de d?marrage incomplet Jun 17 08:56:11 psql02 postgres[4356]: [2-1] 2015-06-17 08:56:11 CEST LOG: paquet de d?marrage incomplet Jun 17 08:56:41 psql02postgres[4460]: [2-1] 2015-06-17 08:56:41 CEST LOG: paquet de d?marrage incomplet Jun 17 08:56:55 psql02postgres[4514]: [2-1] 2015-06-17 08:56:55 CEST ERREUR: restauration en cours Jun 17 08:56:55 psql02postgres[4514]: [2-2] 2015-06-17 08:56:55 CEST ASTUCE : les fonctions de contr?le des journaux de transactions ne peuvent pas Jun 17 08:56:55 psql02postgres[4514]: [2-3] #011?tre ex?cut?es lors de la restauration. Jun 17 08:56:55 psql02postgres[4514]: [2-4] 2015-06-17 08:56:55 CEST INSTRUCTION : select pg_current_xlog_location() pg_current_xlog_location() is for a zabbix check, ERREUR is because that server is readyonly. Xavier C. 2015-06-16 12:41 GMT+02:00 Guillaume Lelarge guilla...@lelarge.info mailto:guilla...@lelarge.info: Le 16 juin 2015 10:57 AM, Xavier 12 mania...@gmail.com mailto:mania...@gmail.com a écrit : Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' How can I reduce the number of WAL files on the hot_stanby slave ? Depends on what you're talking about. If they are archived wal, pg_archive_cleanup is what you're looking for. -- Guillaume. http://blog.guillaume.lelarge.info http://www.dalibo.com
Re: [GENERAL] pg_xlog on a hot_stanby slave
On 17/06/2015 02:44, Venkata Balaji N wrote: On Tue, Jun 16, 2015 at 6:55 PM, Xavier 12 mania...@gmail.com mailto:mania...@gmail.com wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' How can I reduce the number of WAL files on the hot_stanby slave ? If the number of WAL files in pg_xlog are growing, then you need to look at why the files are not getting deleted. Do you see master and standby in sync ? You can check that by getting the current pg_xlog position in standby. Regards, Venkata Balaji N Fujitsu Australia I have a Zabbix check for pg_xlog in master/slave indeed. Xavier C.
Re: [GENERAL] pg_xlog on a hot_stanby slave
I don't think so. There is no archive_command and the master doesn't ship its wal here. But how can I check that ? 2015-06-16 12:41 GMT+02:00 Guillaume Lelarge guilla...@lelarge.info: Le 16 juin 2015 10:57 AM, Xavier 12 mania...@gmail.com a écrit : Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' How can I reduce the number of WAL files on the hot_stanby slave ? Depends on what you're talking about. If they are archived wal, pg_archive_cleanup is what you're looking for. -- 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] pg_xlog on a hot_stanby slave
2015-06-16 15:55 GMT+02:00 Xavier 12 mania...@gmail.com: I don't think so. There is no archive_command and the master doesn't ship its wal here. But how can I check that ? What's the complete path to the directory on the salve that contains 951 files? what does PostgreSQL say on its log files? 2015-06-16 12:41 GMT+02:00 Guillaume Lelarge guilla...@lelarge.info: Le 16 juin 2015 10:57 AM, Xavier 12 mania...@gmail.com a écrit : Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' How can I reduce the number of WAL files on the hot_stanby slave ? Depends on what you're talking about. If they are archived wal, pg_archive_cleanup is what you're looking for. -- Guillaume. http://blog.guillaume.lelarge.info http://www.dalibo.com
Re: [GENERAL] pg_xlog on a hot_stanby slave
On Tue, Jun 16, 2015 at 6:55 PM, Xavier 12 mania...@gmail.com wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' How can I reduce the number of WAL files on the hot_stanby slave ? If the number of WAL files in pg_xlog are growing, then you need to look at why the files are not getting deleted. Do you see master and standby in sync ? You can check that by getting the current pg_xlog position in standby. Regards, Venkata Balaji N Fujitsu Australia
Re: [GENERAL] pg_xlog on a hot_stanby slave
On Tue, 16 Jun 2015 16:55 Xavier 12 mania...@gmail.com wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 What's this parameter's value on Slave? autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' How can I reduce the number of WAL files on the hot_stanby slave ? Thanks Regards. Xavier C. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_xlog on a hot_stanby slave
Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' How can I reduce the number of WAL files on the hot_stanby slave ? Thanks Regards. Xavier C. -- 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] pg_xlog on a hot_stanby slave
Le 16 juin 2015 10:57 AM, Xavier 12 mania...@gmail.com a écrit : Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days only, it keeps growing up until disk space is full). I have found documentation and tutorials, mailing list, but I don't know what is suitable for a Slave. Leads I've found : - checkpoints - archive_command - archive_cleanup Master postgresq.conf : [...] wal_level = 'hot_standby' archive_mode = on archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' max_wal_senders = 5 wal_keep_segments = 64 autovacuum = on Slave postgresql.conf : [...] wal_level = minimal wal_keep_segments = 32 hot_standby = on Slave recovery.conf : standby_mode = 'on' primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' trigger_file = '/var/lib/postgresql/9.1/triggersql' restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/wal_archive/ %r' How can I reduce the number of WAL files on the hot_stanby slave ? Depends on what you're talking about. If they are archived wal, pg_archive_cleanup is what you're looking for.
