0

I'm trying to force WAL files to be flushed on to the disk so that I can copy them somewhere else. I believe CHECKPOINT is the command I'm interested in but it doesn't seem to work.

I have a docker container of Postgres running and I use adminer to add new records to a table I created. I tried to flush the WAL files manually:

$ docker exec db psql pgtest -U postgres -c "CHECKPOINT;"
CHECKPOINT

I believe that should flush the data and I would see a new WAL file created, however there are no new files - I only see the old WAL files. When I restart the container:

$ docker restart db
db

Then I see a new WAL file.

This is what my postgresql.conf looks like:

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

wal_level = logical                   # minimal, replica, or logical
                                      # (change requires restart)
fsync = on                            # flush data to disk for crash safety
                                      # (turning this off can cause
                                      # unrecoverable data corruption)
synchronous_commit = local            # synchronization level;
                                      # off, local, remote_write, remote_apply, or on
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_compression = off                # enable compression of full-page writes
#wal_log_hints = off                  # also do full page writes of non-critical updates
                                      # (change requires restart)
#wal_buffers = -1                     # min 32kB, -1 sets based on shared_buffers
                                      # (change requires restart)
#wal_writer_delay = 200ms             # 1-10000 milliseconds
wal_writer_flush_after = 1MB          # measured in pages, 0 disables

#commit_delay = 0                     # range 0-100000, in microseconds
#commit_siblings = 5                  # range 1-1000

# - Checkpoints -

#checkpoint_timeout = 1min            # range 30s-1d
#max_wal_size = 1GB
#min_wal_size = 4MB
#checkpoint_completion_target = 0.5   # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB       # measured in pages, 0 disables
#checkpoint_warning = 30s             # 0 disables

# - Archiving -

archive_mode = on                     # enables archiving; off, on, or always
                                      # (change requires restart)
archive_command = 'test ! -f /var/lib/postgresql/data/wal/%f && cp %p /var/lib/postgresql/data/wal/%f'
                                      # placeholders: %p = path of file to archive
                                      #               %f = file name only
                                      # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0                  # force a logfile segment switch after this
                                      # number of seconds; 0 disables
3
  • ... but if you think that gets you one step closer to replication, you are mistaken. Feb 13 at 7:29
  • @LaurenzAlbe care to explain? Sorry I've never used Postgres so I'm not really familiar with what I need to do.
    – dokgu
    Feb 13 at 14:58
  • I'd have to quote larger parts of the documentation to explain in detail. My answer to your other question contains more information. Feb 13 at 15:16

1 Answer 1

2

For postgreSQL 9.6 and bellow, you can use pg_switch_xlog()

pg_switch_xlog() Force switch to a new transaction log file.

docker exec db psql pgtest -U postgres -c "select pg_switch_xlog();"

For postgreSQL 10 and above, use pg_switch_wal()

3
  • Was this introduced on version 9.27? I am using an older version (9.6.16) and I get the following error: ERROR: function pg_switch_wal() does not exist.
    – dokgu
    Feb 12 at 18:16
  • See new edit for your version.
    – Astora
    Feb 12 at 18:32
  • 1
    Thank you, that worked!
    – dokgu
    Feb 12 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.