Re: Schema-only dump dumps no constraints, no triggers - Mailing list pgsql-general
From | Marek Kielar |
---|---|
Subject | Re: Schema-only dump dumps no constraints, no triggers |
Date | |
Msg-id | 99f8f43.4f879e8c.501510a4.632d8@go2.pl Whole thread Raw |
Responses |
Re: Clogging problem (was: Schema-only dump dumps no constraints, no triggers)
|
List | pgsql-general |
Hi, Dnia 28 lipca 2012 1:10 Adrian Klaver <adrian.klaver@gmail.com> napisał(a): > What where the deleted files? > WAL, Logs, other? > What type of WAL replication are you doing? > Streaming, log shipping, etc? > What are your settings for the WAL replication? > In particular wal_keep_segments ? > Is the WAL replication actually working? at this time - a couple days after restart, the clog hasn't re-formed yet. Thus, I am unable to tell you what files theywere, we didn't pay that much attention to it then - there were some WAL files but I can't tell what the actual structurewas. I'll provide this information whenever possible. The WAL replication is a streaming replication with a hot standby server. The servers have a direct connection with one another.Configuration appended. The replication is working fine. The primary server also has a single mostly-"idle" transaction from any client node - a trait of an "always online" clientapplication. Immediately after primary server restart this does not add much overhead, though. I can't tell whetherthis, in the long run, might cause the problem. Configuration follows: - primary server postgresql.conf - WAL section - standby server postgresql.conf - WAL section - standby server recovery.conf The primary server WAL configuration is: #------------------------------------------------------------------------------ # WRITE AHEAD LOG #------------------------------------------------------------------------------ # - Settings - wal_level = hot_standby # minimal, archive, or hot_standby # (change requires restart) #fsync = on # turns forced synchronization on or off #synchronous_commit = on # immediate fsync at commit #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 = 1MB # min 32kB # (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 = 30 # 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 = 'rsync %p <CUT>/%f' # command to use to archive a logfile segment archive_timeout = 0 # force a logfile segment switch after this # number of seconds; 0 disables # - Streaming Replication - max_wal_senders = 5 # max number of walsender processes # (change requires restart) #wal_sender_delay = 200ms # walsender cycle time, 1-10000 milliseconds wal_keep_segments = 32 # in logfile segments, 16MB each; 0 disables #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed # - Standby Servers - #hot_standby = off # "on" allows queries during recovery # (change requires restart) #max_standby_archive_delay = 30s # max delay before canceling queries # when reading WAL from archive; # -1 allows indefinite delay #max_standby_streaming_delay = 30s # max delay before canceling queries # when reading streaming WAL; # -1 allows indefinite delay The standby server WAL configuration is: #------------------------------------------------------------------------------ # WRITE AHEAD LOG #------------------------------------------------------------------------------ # - Settings - #wal_level = minimal # minimal, archive, or hot_standby # (change requires restart) #fsync = on # turns forced synchronization on or off #synchronous_commit = on # immediate fsync at commit #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 = 64kB # min 32kB # (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 = 3 # 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 = 'cp %p /backup/repl/%f' # command to use to archive a logfile segment #archive_timeout = 0 # force a logfile segment switch after this # number of seconds; 0 disables # - Streaming Replication - #max_wal_senders = 5 # max number of walsender processes # (change requires restart) #wal_sender_delay = 200ms # walsender cycle time, 1-10000 milliseconds #wal_keep_segments = 32 # in logfile segments, 16MB each; 0 disables #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed # - Standby Servers - hot_standby = on # "on" allows queries during recovery # (change requires restart) #max_standby_archive_delay = 30s # max delay before canceling queries # when reading WAL from archive; # -1 allows indefinite delay #max_standby_streaming_delay = 30s # max delay before canceling queries # when reading streaming WAL; # -1 allows indefinite delay The standby server recovery.conf file: standby_mode = 'on' primary_conninfo = 'host=<CUT> port=<CUT> user=<CUT>' trigger_file = '<CUT>/repl_trigger' restore_command = 'rsync <CUT>/%f "%p"'
pgsql-general by date: