Re: Schema-only dump dumps no constraints, no triggers

Hi,

Dnia 28 lipca 2012 1:10 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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 they were, we didn't pay that much attention to it then - there were some WAL files but I can't tell what the actual structure was. 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" client application. Immediately after primary server restart this does not add much overhead, though. I can't tell whether this, 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"'