Secondary Postgres Node occupying more disk space

[adevegowda]

Looking at disk space utilization i see that our secondary postgres server is using a lot more space than the master in /opt/apigee directory as shown below:

[root@pg1 opt]du -sh *
58G apigee
16K lost+found
32M nimsoft
4.0K rh

[root@pg2 opt]# du -sh *
75G apigee
16K lost+found
27M nimsoft
4.0K rh

Can you please help me to determine the cause for this difference in the sizes ?

[adevegowda · Accepted solution]

1. Checked the sub directories within the /opt/apigee folder and found that the major difference was seen in /opt/apigee/data/apigee-postgresql/pgdata directory. Within this directory, we found that the sub directory pg_xlog was around 7.9GB on Master node, while it was around 24GB on the secondary node as shown below:

[root@pg1 pg_xlog]# du -h
4.0K ./archive_status
7.9G .

[root@pg2 pg_xlog]# du -h
96K ./archive_status
24G .

2. Checked the files in pg_xlog directories of both master and slave nodes and noticed the below :

a) Master had files from Aug 17

b) Slave had files from Aug 1

(Each file size was 16 MB)

3. Basically the pg_xlog directory contains the Postgres Write Ahead Logs (WAL, Postgres implementation of transaction logging) files (normally 16MB in size, each).

4. So, checked the WAL settings in /opt/apigee/apigee-postgresql-9.4.9-0.0.942/conf/postgresql.conf for both Master and Slave nodes and noticed the following differences:

Master Node

max_wal_senders=5 # max number of walsender processes
# (change requires restart)


wal_keep_segments=500 # in logfile segments, 16MB each; 0 disables
#wal_sender_timeout=60s # in milliseconds; 0 disables 

Slave Node

max_wal_senders=1
# (change requires restart)


wal_keep_segments=1500 # in logfile segments, 16MB each; 0 disables
#wal_sender_timeout=60s # in milliseconds; 0 disables

Note:

wal_keep_segments (integer)

• Specifies the minimum number of past log file segments kept in the pg_xlog directory, in case a standby server needs to fetch them for streaming replication. Each segment is normally 16 megabytes.

6. This clearly indicated that the reason for having less number of files on the Master Node is that wal_keep_segments is set to a lower value of Master.

7. We did a quick check by running the following command on both Master and Slave nodes:

ls -la | wc -l 

8. This showed that Master had around 506 files (Out of this 500 were WAL files), while the Slave had 1506 files (1500 WAL files).

The resolution of this issue was to modify the wal_keep_segments on one of the nodes to ensure that the number of files and size were same on both the Postgres nodes.

Postgres Properties Documentation