HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

WAL files are not copied

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
arefileswalnotcopied

Problem

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?

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#84091, answer score: 2

Revisions (0)

No revisions yet.