2

I'm using postgres 9.3 on Ubuntu 14.04 and I'm currently setting up a WAL backup. These are my changes to the postgresql.conf: [as described here: http://www.postgresql.org/docs/9.3/static/continuous-archiving.html ]

# - Settings -
wal_level = archive                     # minimal, archive, or hot_standby
                                        # (change requires restart)
# - Archiving -
archive_mode = on               # allows archiving to be done
archive_command = 'cp %p /home/userName/Documents/WALS'             

I did a 'sudo service postgresql restart' after changing the config and there are wal files generated:

root@control:/var/lib/postgresql/9.3/main/pg_xlog# ls -hlrt
total 49M
-rw------- 1 postgres postgres  16M Dez  2 14:24 000000010000000000000002
-rw------- 1 postgres postgres  16M Dez  2 14:24 000000010000000000000003
drwx------ 2 postgres postgres 4,0K Dez  2 14:24 archive_status
-rw------- 1 postgres postgres  16M Dez  2 14:25 000000010000000000000004



root@control:/var/lib/postgresql/9.3/main/pg_xlog# ls archive_status/
000000010000000000000002.ready  000000010000000000000003.ready

I assume that the archive_status means that WAL segments 2 and 3 are ready to be archived. But I don't find anything in the Documents/WALS (which exists).

Could someone give me a hint?

1

The wal files are archived when they are no longer needed in the pg_xlog directory (usually when there are 16 segments and another one is needed - the one that is rolled over and reused is first archived). You can force the system to archive some files to verify manually if you want - to do this run the following:

select pg_start_backup('testing_archiving');
select pg_stop_backup();

Once pg_stop_backup() is finished, you should see a few files in the archive directory.

Postgres uses the archive status folder to record "notes" about archiving attempts and so on with regard to the WAL files.

  • The pg_stop_backup failed for me saying "NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be archived". I solved that by writing the WALS to a directory where the postgres-user has write access. – FooBar Dec 2 '14 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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