Обсуждение: archive_cleanup_command when is called?
archive_cleanup_command = 'pg_archivecleanup /mnt/db1/incoming %r'
I am setting up a PostgreSQL (9.3) standby server in Streaming replication.I configured the recovery.conf file and everything working well a part of the archive_cleanup_command.
The WAL files shipped from the master to the temporary folder with the archive_command are correctly saved, but are never deleted from the slave server.
archive_cleanup_command = 'pg_archivecleanup /mnt/db1/incoming %r'
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Вложения
I am setting up a PostgreSQL (9.3) standby server in Streaming replication.I configured the recovery.conf file and everything working well a part of the archive_cleanup_command.If you don't need PITR, avoid archive shipping. Streaming is recommended [#personalOpinion].
The WAL files shipped from the master to the temporary folder with the archive_command are correctly saved, but are never deleted from the slave server.How are you shipping the WAL from master to temporary location? your archive_command on Master is set to use rsync or scp or ftp to ship wal files?
archive_cleanup_command = 'pg_archivecleanup /mnt/db1/incoming %r'I think there could be some permission issues. Can you try to redirect the result to a log?'pg_archivecleanup -d /mnt/standby/archive %r 2>>cleanup.log'
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbizThis email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Вложения
the archive command is a cp command: 'test ! -f /bigpart/local/incoming/%f && cp %p /bigpart/local/incoming/%f && chmod a+rx /bigpart/local/incoming/%f'
I modified the command in the recovery.conf as follow, but no changes and no entry in the log file: archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup /mnt/db1_incoming_wals %r 2>>/tmp/archive_cleanup.log'
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Вложения
the archive command is a cp command: 'test ! -f /bigpart/local/incoming/%f && cp %p /bigpart/local/incoming/%f && chmod a+rx /bigpart/local/incoming/%f'Your primary and secondary instances/db clusters are on the same server?
I modified the command in the recovery.conf as follow, but no changes and no entry in the log file: archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup /mnt/db1_incoming_wals %r 2>>/tmp/archive_cleanup.log'Can you share your restore_command?
Also share anything unusual that you notice in your database log files [on both the instances]
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbizThis email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Вложения
2014/1/15 Sameer Kumar <sameer.kumar@ashnik.com>the archive command is a cp command: 'test ! -f /bigpart/local/incoming/%f && cp %p /bigpart/local/incoming/%f && chmod a+rx /bigpart/local/incoming/%f'Your primary and secondary instances/db clusters are on the same server?No, two servers: the temp partition (/bigpart/local/incoming) is on the master and is shared via NFS to the standby (/mnt/db1_incoming_wals). On the standby server the partition is mounted in rw mode and the postgres user can read and write (and delete) on the partition (verified now).I modified the command in the recovery.conf as follow, but no changes and no entry in the log file: archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup /mnt/db1_incoming_wals %r 2>>/tmp/archive_cleanup.log'Can you share your restore_command?restore_command = 'cp /mnt/db1_incoming_wals/%f %p'
Also share anything unusual that you notice in your database log files [on both the instances]I will check again, but no logs I found about it, maybe I need to enable in postgres.conf some logs details.
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbizThis email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Вложения
Sameer, since the issue happens in a production environment, I cannot perform these kind of tests. For the moment I will disable the PITR archiving and go on only with the Streaming Replica. I will try to replicate the issue in a DEV environment. Thanks. domenico 2014/1/16 Sameer Kumar <sameer.kumar@ashnik.com>: > On Wed, Jan 15, 2014 at 7:41 PM, DFE <mimmopasticcio@gmail.com> wrote: >> >> >> >> >> 2014/1/15 Sameer Kumar <sameer.kumar@ashnik.com> >>> >>> >>>> the archive command is a cp command: 'test ! -f >>>> /bigpart/local/incoming/%f && cp %p /bigpart/local/incoming/%f && chmod a+rx >>>> /bigpart/local/incoming/%f' >>> >>> >>> Your primary and secondary instances/db clusters are on the same server? >> >> No, two servers: the temp partition (/bigpart/local/incoming) is on the >> master and is shared via NFS to the standby (/mnt/db1_incoming_wals). On the >> standby server the partition is mounted in rw mode and the postgres user can >> read and write (and delete) on the partition (verified now). >> >>> >>> >>>> I modified the command in the recovery.conf as follow, but no changes >>>> and no entry in the log file: archive_cleanup_command = >>>> '/usr/pgsql-9.3/bin/pg_archivecleanup /mnt/db1_incoming_wals %r >>>> 2>>/tmp/archive_cleanup.log' >>> >>> >>> Can you share your restore_command? >> >> restore_command = 'cp /mnt/db1_incoming_wals/%f %p' >> > > > Note that the cleanup will happen only after "earliest file that must be > kept to allow a restore to be restartable" has been applied on secondary. > > Can you run pg_start_backup() and pg_stop_backup() a few times and see if > archives are getting cleaned or not? > > >>> >>> Also share anything unusual that you notice in your database log files >>> [on both the instances] >> >> I will check again, but no logs I found about it, maybe I need to enable >> in postgres.conf some logs details. >> >>> >>> > > Use these parameters and then restart your DB Server. (to be safe first stop > the primary --> Then Stop the secondary --> Then start the secondary --> > then Start the primary) > > log_rotation_age = 1day > log_rotation_size = 100MB > log_destination = stderr > logging_collector =on > log_directory = 'director_where you want logs' > log_filename = 'postgresql-%Y-%m-%d-%H-%M-%S.log' > > > >>> >>> >>> Best Regards, >>> Sameer Kumar | Database Consultant >>> ASHNIK PTE. LTD. >>> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533 >>> M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com >>> www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz >>> >>> This email may contain confidential, privileged or copyright material and >>> is solely for the use of the intended recipient(s). >> >> >
since the issue happens in a production environment, I cannot perform
these kind of tests.
For the moment I will disable the PITR archiving and go on only with
the Streaming Replica.
I will try to replicate the issue in a DEV environment.
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).