Re: Reduce the size of the archived-log directory

[Vasilis Ventirozos <v.ventirozos@xxxxxxxxx>]

The size of your directory will increase by 16mb on every checkpoint (16Mb or 5 mins) so, even if your DB is completely idle you will get 16Mb per 5 mins, make the calculations and you'll see the space you need per day.


Vasilis Ventirozos

On Thu, Mar 28, 2013 at 8:54 AM, Hoàng Thanh Toàn - DB <toanht@xxxxxxxxxxx> wrote:

Hello Vasilis,

Thanks for your reply,

I thinks your archiving is successful for transfering WAL segments.

archive_command = 'test ! -f /mnt/FRA/ArchivedLog/%f && cp %p /mnt/FRA/ArchivedLog/%f'                # command to use to archive a logfile segment

You are right that I am archiving at a local disk, but what it shall affect the size of the archived WAL directory.

 

############################## postgresql.conf (standalone server)###########################################

# - Settings -

 

wal_level = archive                             # minimal, archive, or hot_standby

                                                            # (change requires restart)

#fsync = on                                         # turns forced synchronization on or off

#synchronous_commit = on               # synchronization level; on, off, or local

#wal_sync_method = fsync               # the default is the first option

                                                            # supported by the operating system:

                                                            #   open_datasync

                                                            #   fdatasync (default on Linux)

                                                            #   fsync

                                                            #   fsync_writethrough

                                                            #   open_sync

#full_page_writes = on                                    # recover from partial page writes

#wal_buffers = -1                                # min 32kB, -1 sets based on shared_buffers

                                                            # (change requires restart)

#wal_writer_delay = 200ms                # 1-10000 milliseconds

 

#commit_delay = 0                             # range 0-100000, in microseconds

#commit_siblings = 5                          # range 1-1000

# - Checkpoints -

 

checkpoint_segments = 7                  # in logfile segments, min 1, 16MB each

#checkpoint_timeout = 5min              # range 30s-1h

#checkpoint_completion_target = 0.5            # checkpoint target duration, 0.0 - 1.0

#checkpoint_warning = 30s                # 0 disables

 

# - Archiving -

 

archive_mode = on                 # allows archiving to be done

                                                # (change requires restart)

archive_command = 'test ! -f /mnt/FRA/ArchivedLog/%f && cp %p /mnt/FRA/ArchivedLog/%f'                # command to use to archive a logfile segment

archive_timeout = 180                        # force a logfile segment switch after this

                                                # number of seconds; 0 disables

# - Master Server -

 

# These settings are ignored on a standby server

 

#max_wal_senders = 0                      # max number of walsender processes

                                                # (change requires restart)

#wal_sender_delay = 1s                     # walsender cycle time, 1-10000 milliseconds

#wal_keep_segments = 0                   # in logfile segments, 16MB each; 0 disables

#vacuum_defer_cleanup_age = 0     # number of xacts by which cleanup is delayed

#replication_timeout = 60s      # in milliseconds; 0 disables

#synchronous_standby_names = ''    # standby servers that provide sync rep

                                                # comma-separated list of application_name

                                                # from standby(s); '*' = all

--

 

From: Vasilis Ventirozos [mailto:v.ventirozos@xxxxxxxxx]
Sent: Thursday, March 28, 2013 1:29 PM
To: Hoàng Thanh Toàn - DB
Subject: Re: Reduce the size of the archived-log directory

 

Hello,

its either your archiving is failing to transfer wal segments (check archiving_command) , or you are archiving at a local disk (you shouldnt, check archiving command) or you have an astronomical archive_keep_segments value

 

 

Vasilis Ventirozos

 

 

On Thu, Mar 28, 2013 at 8:10 AM, Hoàng Thanh Toàn - DB <toanht@xxxxxxxxxxx> wrote:

Dear all,

Please help me.

Please show me how to reduce the size of the archived-log directory.

My postgres database only get 4gbyte but the archived-log directory reaches several tens of gigabyte in one day.

please give me some advice.

Thanks a lot.