Restore PostgreSQL database from WAL-backup skip parts of entries
introduction
In PostgreSQL, there is an interesting technical solution — before actually start to change something in the files of the database itself, the DBMS writes is translated into an internal format command in a special log — Write-Ahead Log, and after successful completion of the transaction, makes the journal a note. This was done for disaster recovery, but in the end the inquisitive mind of the developers came to the idea to use this log for backup and replication. In principle, logically, all the moves it contains, moreover can not simply restore the data from backup and recover the database state at a specific point in time, interrupting the playback of the records WAL-log at the right time.
However, let's consider this scenario — let's say on Monday you did a basic backup and started the archiving of WAL logs, in the environment you made the request to remove the erroneous mask, and only found out about it on Friday, when the Manager reported the disappearance of what he needed records. In this situation, we can only restore from a backup, by Wednesday, lost all the work managers on Thursday and Friday.
A logical question arises, is it possible to make the playing WAL-logs from Monday to Friday, while eliminating our erroneous request?
Normally, I would limit the question to the forum, but I had 2 of the FreeBSD distribution, 10 worked with the PostgreSQL source code of different versions, 10Gb of space on the screw, gcc, two relatively unproductive week as well as tequila, rum, beer and fragmentary memories of the language syntax C. Not that it was necessary reserve for the decision, but once I looked into source codes, it is difficult to stop.
So, for the experiments taken FreeBSD 10 and PostgreSQL 9.2.8 of its ports. The client the appropriate version can be supplied with pkg, it does not need to change. I apologize in advance for the possible captaincy, but the text was written for both beginners and for a quick freshen up in the head if necessary, so all the commands are written in detail.
Installation and basic server configuration
the
root@leninzhiv> cd /usr/ports/databases/postgresql92-server
root@leninzhiv> make fetch
root@leninzhiv> make extract
The downloaded file, the source folder is set in the work directory of the port. I honestly did not understand how to rebuild after source code changes, a rebuild make, like no, a make clean in turn just blowing away the folder with all the changes. So I just copied the work folder in your home directory, make changes there and then copied to the folder of the port and run make install.
Yet not changing anything, just put Postgres:
the
root@leninzhiv> make install
Create a folder for the archives:
the
root@leninzhiv> mkdir -p /usr/db_archive/wal
root@leninzhiv> mkdir -p /usr/db_archive/data
root@leninzhiv> chown-R pgsql:wheel /usr/pg_archive
Postgres requires that the data directory was the only access for the user thats why change of law:
the
root@leninzhiv> chmod 0700 /usr/pg_archive/data
Make a primitive setting. It makes sense to go under postgresol uchetku pgsql to have less fuss with the rights to files.
the
root@leninzhiv> su - pgsql
pgsql@leninzhiv> initdb -D /usr/local/pgsql/data
Uncomment and edit settings of the archived WAL logs in /usr/local/pgsql/data/postgresql.conf:
archive_mode=on
wal_level = archive
archive_command = 'test! -f /usr/db_archive/wal/%f &&cp %p /usr/db_archive/wal/%f'
(there is an example next to the comments)
max_wal_senders = 1
In /usr/local/pgsql/data/pg_hba.conf uncomment the line
local replication pgsql trust
Start the server
the
pgsql@leninzhiv> /usr/local/etc/rc.d/postgresql start
Do the base backup
the
pgsql@leninzhiv> pg_basebackup -D /usr/db_archive/data/
Check in /usr/db_archive/data/ should be the copy of the directory data in /usr/db_archive/wal/ should lie the WAL files that look approximately 000000010000000000000003
Copy to folder with backup of directory data the config to restore
the
cp /usr/local/share/postgresql/recovery.conf.sample /usr/db_archive/data/recovery.conf
and it recommenthe and run the restore command (for example, too close in the comments).
restore_command = 'cp /usr/db_archive/data/%f %p'
Make a recording:
the pgsql@leninzhiv> psql -U pgsql -d postgres
the postgres=# CREATE TABLE z (z_id serial, z_text character(50));
postgres=# INSERT INTO z (z_text) VALUES ('Karlin');
postgres=# INSERT INTO z (z_text) VALUES ('Petrov');
postgres=# INSERT INTO z (z_text) VALUES ('Ivanov');
postgres=# INSERT INTO z (z_text) VALUES ('Kaplan');
postgres=# INSERT INTO z (z_text) VALUES ('Karas');
postgres=# INSERT INTO z (z_text) VALUES ('Bukova');
postgres=# INSERT INTO z (z_text) VALUES ('Sidorova');
postgres=# INSERT INTO z (z_text) VALUES ('Karman');
postgres=# INSERT INTO z (z_text) VALUES ('Dammam');
Deleted post:
the postgres=# DELETE FROM z WHERE z_text ILIKE 'Ka%';
Change the entries new, disco
the postgres=# UPDATE z SET z_text='Petrova' WHERE z_text='Sidorova';
postgres=# INSERT INTO z (z_text) VALUES ('Kruglov');
postgres=# UPDATE z SET z_text='Alexeeva' WHERE z_text='Bukova';
postgres=# INSERT INTO z (z_text) VALUES ('Kvadrat');
Find that delete records on the mask was not a good idea, and with Karlin we removed Kaplan, Carp and Pockets.
Stopping server
the pgsql@leninzhiv> /usr/local/etc/rc.d/postgresql stop
pgsql@leninzhiv> exit
root@leninzhiv>
and start thinking what to do.
Go to the source
As you remember, after make extract I have copied the folder of the work directory of the port to your home folder, and make changes to it. So turn there. If anyone can suggest how to make changes in the source code in the folder of port to good personeros after in changes to the code I would be very grateful.
First, I set a goal to find the place where it is read from a file write WAL logs.
The code file related to WAL I found using the search string "WAL" in the contents of files in the directory work/postgresql-9.2.8/src and common sense, it was the xlog file.c
I don't know how to trace programs in C, so just at the beginning of each function, added a record of its name in the file, assemble and run.
In the file turned out to be so here is the result:
the bool check_wal_buffers(int *newval, void **extra, GucSource source)
void assign_xlog_sync_method(new_sync_method int, void *extra)
Size XLOGShmemSize(void)
static int XLOGChooseNumBuffers(void)
bool check_wal_buffers(int *newval, void **extra, GucSource source)
void XLOGShmemInit(void)
Size XLOGShmemSize(void)
static void ReadControlFile(void)
void StartupXLOG(void)
static void ReadControlFile(void)
static char * str_time(pg_time_t tnow)
static void ValidateXLOGDirectoryStructure(void)
static void readRecoveryCommandFile(void)
static List * readTimeLineHistory(TimeLineID targetTLI)
static bool read_backup_label(XLogRecPtr *checkPointLoc, bool *backupEndRequired, bool *backupFromStandby)
static XLogRecord * ReadCheckpointRecord(XLogRecPtr RecPtr, int whichChkpt)
static XLogRecord * ReadRecord(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt)
static bool XLogPageRead(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt, bool randAccess)
static int XLogFileReadAnyTLI(uint32 log, uint32 seg, int emode, int sources)
...
static XLogRecord * ReadRecord(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt)
static bool XLogPageRead(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt, bool randAccess)
static bool RecordIsValid(XLogRecord *record, XLogRecPtr recptr, int emode)
static bool recoveryStopsHere(XLogRecord *record, bool *includeThis)
static void CheckRecoveryConsistency(void)
static XLogRecord * ReadRecord(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt)
static bool XLogPageRead(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt, bool randAccess)
...
In General, I was under the impression that the main action occurs in a loop ReadRecord -> XLogPageRead -> RecordIsValid -> RecoveryStopsHere -> CheckRecoveryConsistency.
Closer znakomtsva with fungica ReadRecord showed that it returns the record in two places — as record and return as return (XLogRecord *) buffer, the above simple way, we specify that in the recovery process with WAL-log the return is via the return (XLogRecord *) buffer. Great! Write the result to a file.
A structure of type XLogRecord you can see in the xlog file.h and it is quite concise:
the typedef struct XLogRecord
{
pg_crc32 xl_crc; /* CRC for this record */
XLogRecPtr xl_prev; /* ptr to previous record in log */
Xl_xid TransactionId; /* xact id */
xl_tot_len uint32; /* total len of entire record */
xl_len uint32; /* total len of rmgr data */
uint8 xl_info; /* flag bits, see below */
RmgrId xl_rmid; /* resource manager for this record */
/* ACTUAL LOG DATA FOLLOWS AT END OF STRUCT */
} XLogRecord;
Well, if we have a length, and use it to output the contents of file writes, before return (XLogRecord *) buffer add:
the FILE *pf2 = fopen("/usr/local/pgsql/data/log3.txt", "a"); char *buf_poi = buffer;
for (uint32 i=0; i < record>xl_tot_len; i++) {fputc(*buf_poi, pf2); buf_poi++;}
fprintf(pf2, "\n crc32: %u \n xl_xid=%i \n", record->xl_crc, record->xl_xid);
fclose(pf2);
We carry old Postgres collected and ustanavlivaem new:
the root@leninzhiv> cd /usr/ports/databases/postgresql92-server
root@leninzhiv> make deinstall
Remember that we copied the directory work home folder and all the code changes made there. Now copy it to the location of the folder you work in the port's directory.
the root@leninzhiv> rm-R /usr/ports/databases/postgresql92-server/work
root@leninzhiv> cp-R ~/work /usr/ports/databases/postgresql92-server/work
root@leninzhiv> make install
Delete the database files and copy in their place the base backup. WAL-files will come on their own.
the root@leninzhiv> su - pgsql
pgsql@leninzhiv> /usr/local/etc/rc.d/postgresql stop
pgsql@leninzhiv> rm -R /usr/local/pgsql/data
pgsql@leninzhiv> cp-R /usr/db_archive/data /usr/local/pgsql/data
pgsql@leninzhiv> /usr/local/etc/rc.d/postgresql start
pgsql@leninzhiv> psql -U pgsql -d postgres
the postgres=# select * from z;
postgres=# \q
the pgsql@leninzhiv> /usr/local/etc/rc.d/postgresql stop
Watching the contents of the file log3.txt first go a lot of great records, probably creating service tables and data closer to the end see:
the G#{S####RT#####r###R####
##########0###@###### #e######## ###gNikolaev
crc32: 3682278083
l_xid=1002
W#
M#####U#####,###
###`#######T"D%ž######
crc32: 3423214679
xl_xid=1002
r"X ####xU##l###5########
##########0###@########I##
crc32: 2698322546
xl_xid=1003
#U%2####EU##l###5########
##########0###@########I##
crc32: 841341184
xl_xid=1003
ü#Wз####аU##l###5########
##########0###@########I##
crc32: 3881244668
xl_xid=1003
Z7#R#####V##l###5########
##########0###@########I##
crc32: 4028315482
xl_xid=1003
.####PV##l###,###
###`########S%ž######
crc32: 2426645173
xl_xid=1003
U-B####€V##m###y###Y###@
##########0###@########I#####
####Â#(###### gPetrova
crc32: 1110285523
xl_xid=1004
We see that between familiar names and Nikolaev Petrov has 4 similar records and one dissimilar, under the same transaction number. Apparently, this is the delete command, then to WAL-log have written commands like "delete line 50 in table 64822". In principle, as expected. Add a check that if the value xl_xid=1003 instead of writing returns NULL.
Again remove the old Postgres, collect and install new, and start the recovery...
Deleted records on the spot! Really all that had to happen after the removal did not happen :( well, hurriedly take failed. In General it is clear, after all, before playing the recording are integrity and all that.
So goal number 2 is to find where there is a "running" record. Quick search use readRecord in the same file led me to a function void StartupXLOG(void)... And here I realized that until now were not that way, because almost immediately after the second or third appearance in the function call readRecord (they're next) is just gorgeous in the first diagnostic piece, and the second, immediately after the comment "Now apply the WAL record itself" — the team losing record RmgrTable[record->xl_rmid].rm_redo(EndRecPtr, record);
Change this piece of code
the if (record->xl_xid==1003)
{}
else RmgrTable[record->xl_rmid].rm_redo(EndRecPtr, record);
Again, rebuild, run, check... Victory! Deleted records in place and changes made after the removal, too!
Orientirueshsya on the ground
Well, it is certainly good, but the problem we decided on a very limited dataset, but how to find the right entry in the logs of a working database?
Back to the gorgeous piece of diagnostic functions in StartupXLOG:
the #ifdef WAL_DEBUG
if (XLOG_DEBUG ||
(rmid == RM_XACT_ID && trace_recovery_messages <= DEBUG2) ||
(rmid != RM_XACT_ID && trace_recovery_messages <= DEBUG3))
{
StringInfoData buf;
initStringInfo(&buf);
appendStringInfo(&buf, "REDO @ %X/%X; LSN %X/%X: ",
ReadRecPtr.xlogid, ReadRecPtr.xrecoff,
EndRecPtr.xlogid, EndRecPtr.xrecoff);
xlog_outrec(&buf, record);
appendStringInfo(&buf, " - ");
RmgrTable[record->xl_rmid].rm_desc(&buf,
record- > xl_info,
XLogRecGetData(record));
elog(LOG, "%s", buf.data);
pfree(buf.data);
}
#endif
You can just include the output in the logs, recommendyou #define WAL_DEBUG in pg_config_manual.h and adding wal_debug=on in postgresql.conf, but I, out of habit, sent the output to a separate file. This piece, as I understand it, displays the description of the command using the function rm_desc (in this case RmgrTable is an array of functions?), it looks something like this:
the REDO @ 0/3015500; LSN 0/3015578: prev 0/30154D0; xid 1002; len 82: Heap - insert: rel 1663/12318/16386; tid 0/9
REDO @ 0/3015578; LSN 0/30155A8: prev 0/3015500; xid 1002; len 12: Transaction - commit: 2014-06-06 08:38:27.537874+00
REDO @ 0/30155A8; LSN 0/30155E0: prev 0/3015578; xid 1003; len 21: Heap - delete: rel 1663/12318/16386; tid 0/1
REDO @ 0/30155E0; LSN 0/3015618: prev 0/30155A8; xid 1003; len 21: Heap - delete: rel 1663/12318/16386; tid 0/4
REDO @ 0/3015618; LSN 0/3015650: prev 0/30155E0; xid 1003; len 21: Heap - delete: rel 1663/12318/16386; tid 0/5
REDO @ 0/3015650; LSN 0/3015688: prev 0/3015618; xid 1003; len 21: Heap - delete: rel 1663/12318/16386; tid 0/8
REDO @ 0/3015688; LSN 0/30156B8: prev 0/3015650; xid 1003; len 12: Transaction - commit: 2014-06-06 08:38:27.54153+00
REDO @ 0/30156B8; LSN 0/3015738: prev 0/3015688; xid 1004; len 89: Heap - hot_update: rel 1663/12318/16386; tid 0/7; new 0/10
This familiar piece with a transaction number 1003, and according to it, we can see that Yes, it is four teams and delete one confirmation of the transaction. In the commands for deletion, we see a rel — table identifier in the format "oid namespace/oid/database oid of the table". The corresponding numbers can be obtained by queries
SELECT oid, spcname FROM pg_catalog.pg_tablespace;
SELECT oid, datname FROM pg_catalog.pg_database;
and suddenly
SELECT oid, relname FROM pg_catalog.pg_class;
The second landmark in the description of the transaction is time stamped. Well, it's not necessary to explain anything, if we know this when the crime was commited, then the corresponding records will find it.
Well, as an alternative way, you can return to viewing records in the gibberish and focus on fragments of texts which were passed as parameters to the commands INSERT and UPDATE, if we remember the query parameters was done shortly before or after the search the "incorrect" query. In the case of UPDATE, however, you can find only those rows which were used as the new value if the string was used to search for records, then to WAL-log it is not found.
And finally, I can say that in contribo PostgreSQL 9.3 appeared pg_xlogdump utility, which is, like, just focused on the task of providing content to the WAL-log chelovekochasov. If you are interested in some features it makes sense to write to the developers.
It is possible that the use of this method in the archives of the working DB would have some pitfalls. For example how to work an UPDATE, if we "skip" the removal of a part of the records on a database which uses frequent vacuuming? I haven't checked. But in any case it is better to have at least some hope to fix the error than just no.
Комментарии
Отправить комментарий