Advertisement
jboldt

postgres pitr

Nov 22nd, 2019
133
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.74 KB | None | 0 0
  1. from psql windows:
  2. ===================
  3.  
  4. alter system set archive_mode = 'on';
  5. alter system set archive_command = 'copy "%p" "C:\\temp\\%f"';
  6. select pg_reload_conf();
  7. show archive_command; -- should match path specified above
  8. create table test(id int);
  9. insert into test values (1);
  10. select count(*) from test; -- equals 1
  11. select pg_switch_wal(); -- NOTE: for an existing database should I copy the prior wal files from pg_wal into my archive dir?
  12. select now(); -- capture current time for pitr
  13. truncate table test;
  14. select pg_switch_wal();
  15. show data_directory; -- capture path for recovery.conf
  16.  
  17. from windows command line:
  18. ==========================
  19.  
  20. sc stop postgresql-x64-11
  21.  
  22. (
  23. echo restore_command = 'copy "%p" "C:\\temp\\%f"'
  24. echo recovery_target_time = '2019-11-22 10:31:06'
  25. )>"C:\test\data\recovery.conf"
  26.  
  27. sc start postgresql-x64-11
  28.  
  29. postgres log:
  30. =============
  31.  
  32. 2019-11-22 10:33:00.845 PST [9724] LOG: database system was shut down at 2019-11-22 10:32:03 PST
  33. 2019-11-22 10:33:00.845 PST [9724] LOG: starting point-in-time recovery to 2019-11-22 10:31:06-08
  34. 2019-11-22 10:33:00.881 PST [9724] LOG: consistent recovery state reached at 17/8F000098
  35. 2019-11-22 10:33:00.881 PST [9724] LOG: invalid record length at 17/8F000098: wanted 24, got 0
  36. 2019-11-22 10:33:00.881 PST [9724] LOG: redo is not required
  37. 2019-11-22 10:33:00.890 PST [9224] LOG: database system is ready to accept read only connections
  38. 2019-11-22 10:33:00.930 PST [9724] LOG: selected new timeline ID: 2
  39. 2019-11-22 10:33:01.051 PST [9724] LOG: archive recovery complete
  40. 2019-11-22 10:33:01.315 PST [9224] LOG: database system is ready to accept connections
  41.  
  42. from psql windows:
  43. ==================
  44.  
  45. select count(*) from test; -- equals 0, why is the truncate included?
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement