patternsqlMinor
WAL files are not copied
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 ]
I did a 'sudo service postgresql restart' after changing the config and there are wal files generated:
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?
[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.readyI 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:
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.
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.