Обсуждение: [ADMIN] Question about pg_xlog
Hi everyone,
I'm new to Postgresql and have a test environment with Postgresql 9.4.5 running on Linux.
Today I noticed that the pg_xlog was filled up to 18 Gb.
I have:
- wal_level=hot_standby
- wal_keep_segments = 100
- hot_standby = on
archive_mode = on
It's a test environment en I did a pg_resetxlogs() on the 6th of April.
I know that I shouldn't use this, and that it's only a last resort.
When I look in de pg_xlog directory I see xlog files that are kept sinds that time.
When i look at the archive_status directory I see that all of the files have the suffix of .done.
All the other xlogs from the time before the resetlogs are cleaned up as I expected.
But I don't understand why the other xlogs aren't recycled.
I am looking for ways to find out why my pg_xlog is filling up.
- I checked the alertlog of the postgresql instance --> no errors
- I checked the WAL file location to see if the xlogs are being archived --> they are present
Are there more ways to find out why my pg_xlog is filling up?
Is the pg_resetxlogs the reason why the older xlogs are not recycled?
I Hope that someone can help me.
Thanks in advance.
Regards,
Mariane
M Kiesewetter <mskiesewetter@gmail.com> writes: > Hi everyone, > > I'm new to Postgresql and have a test environment with Postgresql > 9.4.5 running on Linux. > Today I noticed that the pg_xlog was filled up to 18 Gb. > I have: > - wal_level=hot_standby > - wal_keep_segments = 100 > - hot_standby = on > archive_mode = on > > It's a test environment en I did a pg_resetxlogs() on the 6th of > April. > I know that I shouldn't use this, and that it's only a last resort. > When I look in de pg_xlog directory I see xlog files that are kept > sinds that time. > When i look at the archive_status directory I see that all of the > files have the suffix of .done. > All the other xlogs from the time before the resetlogs are cleaned up > as I expected. > But I don't understand why the other xlogs aren't recycled. I'd start by looking for dormant replication slots. select * from pg_replication_slots; HTH > I am looking for ways to find out why my pg_xlog is filling up. > - I checked the alertlog of the postgresql instance --> no errors > - I checked the WAL file location to see if the xlogs are being > archived --> they are present > > Are there more ways to find out why my pg_xlog is filling up? > Is the pg_resetxlogs the reason why the older xlogs are not recycled? > > I Hope that someone can help me. > Thanks in advance. > Regards, > Mariane > > > -- Jerry Sievers e: jerry.sievers@comcast.net p: 312.241.7800
Jerry Sievers wrote: > M Kiesewetter <mskiesewetter@gmail.com> writes: > > I'm new to Postgresql and have a test environment with Postgresql > > 9.4.5 running on Linux. > > Today I noticed that the pg_xlog was filled up to 18 Gb. > > I have: > > - wal_level=hot_standby > > - wal_keep_segments = 100 > > - hot_standby = on > > archive_mode = on > > > > It's a test environment en I did a pg_resetxlogs() on the 6th of > > April. > > I know that I shouldn't use this, and that it's only a last resort. > > When I look in de pg_xlog directory I see xlog files that are kept > > sinds that time. > > When i look at the archive_status directory I see that all of the > > files have the suffix of .done. > > All the other xlogs from the time before the resetlogs are cleaned up > > as I expected. > > But I don't understand why the other xlogs aren't recycled. > > I'd start by looking for dormant replication slots. > > select * from pg_replication_slots; Don't forget to check pg_prepared_xacts if there are any prepared transactions that have not been committed or rolled back. Also, if archive_mode is on, check if archive_command might have had a non-zero return code. You should find messages in the PostgreSQL log. Yours, Laurenz Albe