January 18, 2017 at 17:02An example of restoring PostgreSQL tables using the new mega features pg_filedump
Let me tell you about one cool feature that my colleagues from Postgres Pro recently copied to the pg_filedump utility . This feature allows you to partially recover data from the database, even if the database was badly damaged and you can’t start the PostgreSQL instance with such a database. Of course, I want to believe that the need for such functionality is extremely rare. But just in case, I would like to have something similar at hand. Read on and you'll find out how this feature looks in action.
Partial data recovery was presented in commit 52fa0201 : Let's say there is some kind of table:
commit 52fa0201f97808d518c64bcb9696f2a350678aa5
Author: Teodor Sigaev
Date: Tue Jan 17 16:01:12 2017 +0300
Partial data recovery (-D flag).
This feature allows to partially recover data from a given segment file
in format suitable for using in COPY FROM statement. List of supported
data types is currently not full and TOAST is not yet supported, but
it's better than nothing. Hopefully data recovery will be improved in
the future.
Implemented by Aleksander Alekseev, reviewed by Dmitry Ivanov, tested
by Dmitry Ivanov and Grigoriy Smolkin.create table tt (x int, y bool, z text, w timestamp);... filled with some data:
insert into tt values(123, true, 'Text test test', now());
insert into tt values(456, null, 'Ололо трооло', null);
checkpoint;Here I say checkpoint so that the data will necessarily go to disk. Otherwise, they will go to WAL, but the buffer manager will keep them in memory until the tapla (tuple, tuple, row in the table) are replaced by newer and / or frequently used tapla. Or checkpoint on timeout / accumulation max_wal. I think this is the most common scenario for syncing a page to disk. - approx. Stas Kelvich .
We also find out the name of the segment corresponding to the table:
select relfilenode from pg_class where relname = 'tt';In my case, the relfilenode of the table was 16393. We will find this segment (or segments, if the table is larger than 1 GB) on disk:
find /path/to/db/ -type f | grep 16393We copy it somewhere and imagine that we want to restore the data with only a segment file on hand.
To do this, collect the latest version of pg_filedump:
git clone git://git.postgresql.org/git/pg_filedump.git
cd pg_filedump
make
Typically, the base scheme is known, since there is an application on hand that works with it. So, we know the types of columns in the table and can decode them this way:
./pg_filedump -D int,bool,text,timestamp /path/to/db/base/16384/16393Output Example:
******************************************************************* * PostgreSQL File/Block Formatted Dump Utility - Version 9.6.0 * * File: /home/eax/work/postgrespro/postgresql-install/data-master/base/16384/16393 * Options used: -D int,bool,text,timestamp * * Dump created on: Tue Jan 17 16:28:07 2017 ******************************************************************* Block 0 ********************************************************----- Block Offset: 0x00000000 Offsets: Lower 32 (0x0020) Block: Size 8192 Version 4 Upper 8080 (0x1f90) LSN: logid 0 recoff 0x0301e4c0 Special 8192 (0x2000) Items: 2 Free Space: 8048 Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 () Length (including item array): 32 ------ Item 1 -- Length: 56 Offset: 8136 (0x1fc8) Flags: NORMAL COPY: 123 t Text test test 2017-01-17 16:25:03.448488 Item 2 -- Length: 52 Offset: 8080 (0x1f90) Flags: NORMAL COPY: 456 \N Ололо трооло \N *** End of File Encountered. Last Block Read: 0 ***
There is quite a lot of data, since pg_filedump displays information about each page in a segment and decodes the title of each tapla. Fortunately, you can quite easily separate flies from cutlets, for example, like this:
pg_fiedump -D ...как..раньше... | grep COPY | perl -lne 's/^COPY: //g; print;' > /tmp/copy.txt
cat /tmp/copy.txtThe contents of the copy.txt file:
123 t Text test test 2017-01-17 16:25:03.448488
456 \N Ололо трооло \NThis is our table data in a format suitable for use in a COPY FROM query. We check:
create table tt2 (x int, y bool, z text, w timestamp);
copy tt2 from '/tmp/copy.txt';
select * from tt2;Result:
x | y | z | w
-----+---+----------------+----------------------------
123 | t | Text test test | 2017-01-17 16:25:03.448488
456 | | Ололо трооло |
(2 rows)As you can see, all data has been successfully restored.
Naturally, this was a somewhat simplified example and in practice everything is more complicated. Firstly, the list of supported types is currently somewhat limited:
static ParseCallbackTableItem callback_table[] = {
{ "smallserial", &decode_smallint },
{ "smallint", &decode_smallint },
{ "int", &decode_int },
{ "serial", &decode_int },
{ "bigint", &decode_bigint },
{ "bigserial", &decode_bigint },
{ "time", &decode_time },
{ "timetz", &decode_timetz },
{ "date", &decode_date },
{ "timestamp", &decode_timestamp },
{ "float4", &decode_float4 },
{ "float8", &decode_float8 },
{ "float", &decode_float8 },
{ "bool", &decode_bool },
{ "uuid", &decode_uuid },
{ "macaddr", &decode_macaddr },
/* internally all string types are stored the same way */
{ "char", &decode_string },
{ "varchar", &decode_string },
{ "text", &decode_string },
{ "json", &decode_string },
{ "xml", &decode_string },
{ NULL, NULL},
};Secondly, TOAST is currently not supported. If the string is stored uncompressed or compressed on the in-place page, pg_filedump will successfully restore it (if the compressed data has not been corrupted). However, if the row was transferred to an external TOAST table, instead of the row you will get just "(TOASTED)". In principle, TOAST support is not an unsolvable task. You only need to teach pg_filedump to parse the directory and find the corresponding TOAST table. It's just that nobody has done this yet. Perhaps TOAST support will be added in future versions of pg_filedump.
Finally, in practice, the database schema sometimes changes; columns in the table appear and disappear. Removing columns is not such a big problem, because physically this column remains in tapla, it just always is null. But with the addition, it’s a bit more complicated, because because of it, tapla within the same table can have a variable number of attributes. If the number of attributes in the tapla does not match the number of attributes specified by the user, pg_filedump simply displays a warning with partially decoded data, and proceeds to the next tapla. This means that in practice parsing the output of pg_filedump will be a little more complicated, well, or that you will have to run it several times with different lists of attributes.
In my opinion, and not only mine, as extremedata recovery tool, it is better to have at least one than not to have any :) If you have ideas for further improvement of the functionality presented, and indeed any comments and additions, it will be extremely interesting for me to read them in the comments!
You may also be interested in articles:
Continuation - Another new feature pg_filedump: restore the PostgreSQL directory .
