Обсуждение: Removing archived wal files on Master

Поиск
Список
Период
Сортировка

Removing archived wal files on Master

От
Michael King
Дата:
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

Re: Removing archived wal files on Master

От
Prince Pathria
Дата:
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

Happy to help :)
Prince Pathria Systems Architect Intern Evive +91 9478670472 goevive.com


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 = 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

Re: Removing archived wal files on Master

От
Vikas Gupta
Дата:
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


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 = 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

Re: Removing archived wal files on Master

От
Michael King
Дата:
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.

On 22 Feb 2019, at 8:10 pm, Vikas Gupta <vikasmanya1@gmail.com> wrote:

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


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 = 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

Re: Removing archived wal files on Master

От
Fabio Pardi
Дата:
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
> 


Re: Removing archived wal files on Master

От
Michael King
Дата:
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
>> 
> 

Re: Removing archived wal files on Master

От
Shreeyansh Dba
Дата:
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.

Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com


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 = 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

Re: Removing archived wal files on Master

От
Michael King
Дата:
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
 
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.

Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com


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 = 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