1)
Postgres recien instalado - POSTGRES NEW INSTALL
2)
Creando db de Pruebas - - CREATE TESTING DATABASE
# createdb Test
3) iniciamos procedimiento de respaldo - START BACKUP PROCEDURE
# SELECT pg_start_backup('1');
copiamos wal otro lado - COPY WAL FILES SOMEWHERE ELSE
# SELECT pg_stop_backup();
4)
populamos Test - INSERT A LOT OF DATA INTO 'Test'
# insert into users values (1 ,'name','800122','2 someones name',3,1);
# insert into users values (2 ,'name','800122','2 someones name',3,1);
...cincuenta mil veces con su id unico - FIFTY THOUSAND TIMES WITH UNIQUE ID'S
5)
# mkdir /home/postgres/backup
# mkdir /home/postgres/backup/oficial
# cd /home/postgres/backup
# tar zcf oficial-sin-drop-17-10-01-05.tgz oficial
6)
borramos tabla Test - ERASE 'Test' DB
# dropdb Test
7)
modificamos postgresql.conf en las siguientes lineas - UNCOMMENTED postgresql.conf THESE LINES
fsync = on
wal_sync_method = fsync
full_page_writes = on
wal_buffers = 8
commit_delay = 0
commit_siblings = 5
checkpoint_segments = 3
checkpoint_timeout = 300
checkpoint_warning = 30
archive_command = 'cp -i %p /home/postgres/respaldos/oficial/%f </dev/null'
8)
CREATE THIS FILE WITH THE NEXT LINES /usr/local/pgsql/share/recovery.conf
name = '5'
restore_command = 'cp /home/postgres/respaldos/oficial/%f %p'
recovery_target_timeline = 'latest' # number or 'latest'
regresamos wal files a pg_xlog - COPY BACKED UP WAL FILES BACK TO pg_xlog DIRECTORY
pwd
/home/postgres/backup
mv oficial oficial-con-drop-17-10-01-05
tar zxf oficial-sin-drop-17-10-01-05.tgz ; mkdir pg_xlog-17-10-01-05
mv /usr/local/pgsql/data/pg_xlog/* pg_xlog-17-10-01-05
cp -R oficial/* /usr/local/pgsql/data/pg_xlog/
mkdir /usr/local/pgsql/data/pg_xlog/archive_status
reiniciamos posgtres - RESTART POSTGRES
archive_command = 'cp -i %p /home/postgres/respaldos/oficial/%f
recuperamos - RECOVERY
recovery.conf
name = '5'
restore_command = 'cp /home/postgres/respaldos/oficial/%f %p'
recovery_target_timeline = 'latest'
1.
LOG: transaction ID wrap limit is 2147484146, limited by database "postgres"
2.
LOG: received fast shutdown request
3.
LOG: shutting down
4.
LOG: database system is shut down
5.
LOG: database system was shut down at 2007-02-15 13:45:24 CST
6.
LOG: could not open file "pg_xlog/000000010000000000000005" (log file 0, segment 5): No such file or directory
7.
LOG: invalid primary checkpoint record
8.
LOG: could not open file "pg_xlog/000000010000000000000005" (log file 0, segment 5): No such file or directory
9.
LOG: invalid secondary checkpoint record
10.
PANIC: could not locate a valid checkpoint record
11.
LOG: startup process (PID 13581) was terminated by signal 6
12.
LOG: aborting startup due to startup process failure