Обсуждение: Removing archived wal files on Master
Hi,
I recently acquired a legacy server. This is running Postgresql 9.3 on Ubuntu 16.04.
There is around 200GB worth of archived wal files (~12,500 files) located on /var/lib/postgresql/9.3/main/archive.
I have checked and can confirm that this is a standalone server without any Replication setup and no secondary/slave server talking to it.
Checking the /etc/postgresql/9.3/main/postgresql.conf file (write ahead log section), shows the following:
wal_level = minimal
archive_mode = off
archive_command = 'test ! -f /var/lib/postgresql/9.3/main/archive/%f && cp -i %p /var/lib/postgresql/9.3/main/archive/%f </dev/null'
Replication section shows all default values.
Could you please advice how I can cleanup all of these 200GB worth of files.
I've searched through numerous postgresql books/blogs/articles which all have very good advise on how to setup wal archiving but unfortunately not on how to disable it.
Kind regards,
Michael
1. archive_mode = off
2. comment or delete archive_command parameter
3. reload the server
If you don't want archive logs, just rm the directory /var/lib/postgresql/9.3/main/archive/ -> there's no harm in deleting them as you said there's no streaming replication configured.
all your data is in pg_base and and pg_wal/pg_xlog
On Fri, Feb 22, 2019 at 4:49 PM Michael King <michaelbking@hotmail.com> wrote:
Hi,I recently acquired a legacy server. This is running Postgresql 9.3 on Ubuntu 16.04.There is around 200GB worth of archived wal files (~12,500 files) located on /var/lib/postgresql/9.3/main/archive.I have checked and can confirm that this is a standalone server without any Replication setup and no secondary/slave server talking to it.Checking the /etc/postgresql/9.3/main/postgresql.conf file (write ahead log section), shows the following:wal_level = minimalarchive_mode = offarchive_command = 'test ! -f /var/lib/postgresql/9.3/main/archive/%f && cp -i %p /var/lib/postgresql/9.3/main/archive/%f </dev/null'Replication section shows all default values.Could you please advice how I can cleanup all of these 200GB worth of files.I've searched through numerous postgresql books/blogs/articles which all have very good advise on how to setup wal archiving but unfortunately not on how to disable it.Kind regards,Michael
can you please see the latest time stamp of the WAl archives as archive_mode is off now, i am suspecting these are the old archive files.
Thanks and Regards
Vikas Gupta PMP
+91-987181913
+91-9953999863
Vikas Gupta PMP
+91-987181913
+91-9953999863
On Fri, Feb 22, 2019 at 4:49 PM Michael King <michaelbking@hotmail.com> wrote:
Hi,I recently acquired a legacy server. This is running Postgresql 9.3 on Ubuntu 16.04.There is around 200GB worth of archived wal files (~12,500 files) located on /var/lib/postgresql/9.3/main/archive.I have checked and can confirm that this is a standalone server without any Replication setup and no secondary/slave server talking to it.Checking the /etc/postgresql/9.3/main/postgresql.conf file (write ahead log section), shows the following:wal_level = minimalarchive_mode = offarchive_command = 'test ! -f /var/lib/postgresql/9.3/main/archive/%f && cp -i %p /var/lib/postgresql/9.3/main/archive/%f </dev/null'Replication section shows all default values.Could you please advice how I can cleanup all of these 200GB worth of files.I've searched through numerous postgresql books/blogs/articles which all have very good advise on how to setup wal archiving but unfortunately not on how to disable it.Kind regards,Michael
Hi Vikta,
The archived wals timestamp are as of last month (20 January 2019).
I believe Prince Pathria’s advice makes sense (remove archive folder and all files within it). I’m still somewhat confused by the correct usage of pg_archivecleanup and whether or not I need to use this in my current scenario.
can you please see the latest time stamp of the WAl archives as archive_mode is off now, i am suspecting these are the old archive files.Thanks and Regards
Vikas Gupta PMP
+91-987181913
+91-9953999863On Fri, Feb 22, 2019 at 4:49 PM Michael King <michaelbking@hotmail.com> wrote:Hi,I recently acquired a legacy server. This is running Postgresql 9.3 on Ubuntu 16.04.There is around 200GB worth of archived wal files (~12,500 files) located on /var/lib/postgresql/9.3/main/archive.I have checked and can confirm that this is a standalone server without any Replication setup and no secondary/slave server talking to it.Checking the /etc/postgresql/9.3/main/postgresql.conf file (write ahead log section), shows the following:wal_level = minimalarchive_mode = offarchive_command = 'test ! -f /var/lib/postgresql/9.3/main/archive/%f && cp -i %p /var/lib/postgresql/9.3/main/archive/%f </dev/null'Replication section shows all default values.Could you please advice how I can cleanup all of these 200GB worth of files.I've searched through numerous postgresql books/blogs/articles which all have very good advise on how to setup wal archiving but unfortunately not on how to disable it.Kind regards,Michael
Hi Michael, archived WAL files are not only useful to a standby server, but are also used for Point In Time Recovery. regards, fabio On 22-02-19 12:19, Michael King wrote: > Hi, > I recently acquired a legacy server. This is running Postgresql 9.3 on Ubuntu 16.04. > There is around 200GB worth of archived wal files (~12,500 files) located on /var/lib/postgresql/9.3/main/archive. > I have checked and can confirm that this is a standalone server without any Replication setup and no secondary/slave servertalking to it. > > Checking the /etc/postgresql/9.3/main/postgresql.conf file (write ahead log section), shows the following: > wal_level = minimal > archive_mode = off > archive_command = 'test ! -f /var/lib/postgresql/9.3/main/archive/%f && cp -i %p /var/lib/postgresql/9.3/main/archive/%f</dev/null' > > Replication section shows all default values. > > Could you please advice how I can cleanup all of these 200GB worth of files. > I've searched through numerous postgresql books/blogs/articles which all have very good advise on how to setup wal archivingbut unfortunately not on how to disable it. > > Kind regards, > Michael >
Hi Fabio, Yes, For now I’m planning on using PITR approach until we can get a second server on which I will then introduce replication. I wish to start fresh on this current server so would like to first get rid of those 200GB files and then do a base backupfollowed by wal archival. I was a bit hesitant on whether deleting these 200gb files would cause unwanted side effects. Regards, Michael > On 22 Feb 2019, at 8:24 pm, Fabio Pardi <f.pardi@portavita.eu> wrote: > > Hi Michael, > > archived WAL files are not only useful to a standby server, but are also used for Point In Time Recovery. > > regards, > > fabio > >> On 22-02-19 12:19, Michael King wrote: >> Hi, >> I recently acquired a legacy server. This is running Postgresql 9.3 on Ubuntu 16.04. >> There is around 200GB worth of archived wal files (~12,500 files) located on /var/lib/postgresql/9.3/main/archive. >> I have checked and can confirm that this is a standalone server without any Replication setup and no secondary/slave servertalking to it. >> >> Checking the /etc/postgresql/9.3/main/postgresql.conf file (write ahead log section), shows the following: >> wal_level = minimal >> archive_mode = off >> archive_command = 'test ! -f /var/lib/postgresql/9.3/main/archive/%f && cp -i %p /var/lib/postgresql/9.3/main/archive/%f</dev/null' >> >> Replication section shows all default values. >> >> Could you please advice how I can cleanup all of these 200GB worth of files. >> I've searched through numerous postgresql books/blogs/articles which all have very good advise on how to setup wal archivingbut unfortunately not on how to disable it. >> >> Kind regards, >> Michael >> >
Hi Michael,
I suspect that your DB parameters are set to default and database is not running in an archive mode as archive_mode = off , I believe PostgreSQL won't write archives when archive_mode is off and WAL archival cannot be enabled when wal_level is "minimal".
I assume the existing 200GB worth of archived wal files are old where you can verify by checking the timestamp of the Wal files and remove.
If you want to disable/enable archive_command you can simply comment/uncomment parameter and reload the server to affect but for
enabling archive_mode = on server required a restart.
On Fri, Feb 22, 2019 at 4:49 PM Michael King <michaelbking@hotmail.com> wrote:
Hi,I recently acquired a legacy server. This is running Postgresql 9.3 on Ubuntu 16.04.There is around 200GB worth of archived wal files (~12,500 files) located on /var/lib/postgresql/9.3/main/archive.I have checked and can confirm that this is a standalone server without any Replication setup and no secondary/slave server talking to it.Checking the /etc/postgresql/9.3/main/postgresql.conf file (write ahead log section), shows the following:wal_level = minimalarchive_mode = offarchive_command = 'test ! -f /var/lib/postgresql/9.3/main/archive/%f && cp -i %p /var/lib/postgresql/9.3/main/archive/%f </dev/null'Replication section shows all default values.Could you please advice how I can cleanup all of these 200GB worth of files.I've searched through numerous postgresql books/blogs/articles which all have very good advise on how to setup wal archiving but unfortunately not on how to disable it.Kind regards,Michael
Thank you everyone for ther help.
I can confirm that my issue has now been resolved.
Just to recap:
- Set postgresql.conf (WAL section):
- wal_level = minimal
- archive_mode = off
- comment out the archive_command
- Restart postgresql server
- Ensure postgresql service is up and running
- Delete the folder archive folder within /var/lib/postgresql/9.3/main/
Regards,
Michael
From: Shreeyansh Dba <shreeyansh2014@gmail.com>
Sent: Friday, 22 February 2019 9:26 PM
To: Michael King
Cc: pgsql-admin
Subject: Re: Removing archived wal files on Master
Sent: Friday, 22 February 2019 9:26 PM
To: Michael King
Cc: pgsql-admin
Subject: Re: Removing archived wal files on Master
Hi Michael,
I suspect that your DB parameters are set to default and database is not running in an archive mode as archive_mode = off , I believe PostgreSQL won't write archives when archive_mode is off and WAL archival cannot be enabled when wal_level is "minimal".
I assume the existing 200GB worth of archived wal files are old where you can verify by checking the timestamp of the Wal files and remove.
If you want to disable/enable archive_command you can simply comment/uncomment parameter and reload the server to affect but for
enabling archive_mode = on server required a restart.
On Fri, Feb 22, 2019 at 4:49 PM Michael King <michaelbking@hotmail.com> wrote:
Hi,I recently acquired a legacy server. This is running Postgresql 9.3 on Ubuntu 16.04.There is around 200GB worth of archived wal files (~12,500 files) located on /var/lib/postgresql/9.3/main/archive.I have checked and can confirm that this is a standalone server without any Replication setup and no secondary/slave server talking to it.Checking the /etc/postgresql/9.3/main/postgresql.conf file (write ahead log section), shows the following:wal_level = minimalarchive_mode = offarchive_command = 'test ! -f /var/lib/postgresql/9.3/main/archive/%f && cp -i %p /var/lib/postgresql/9.3/main/archive/%f </dev/null'Replication section shows all default values.Could you please advice how I can cleanup all of these 200GB worth of files.I've searched through numerous postgresql books/blogs/articles which all have very good advise on how to setup wal archiving but unfortunately not on how to disable it.Kind regards,Michael