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

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"'