Обсуждение: Read data from WAL
I have been looking into the WAL to know if there is any way to read the data contained in it. I know that it stores binary data that is applied directly to the files in the Postgres cluster. I would like to get the "real" data, like it is written in the SQL queries, instead of the binary data.
Are there any projects or standard procedures for reading the data from the WAL to get a change log for the database (or without the WAL, using some other method)? I have searched for information about this quite thoroughly without luck, so I thought I'd try asking here before I started to patch Postgres :)Hi, 2013/07/15 21:45, Baldur Þór Emilsson wrote: > Are there any projects or standard procedures for reading the data from > the WAL to get a change log for the database (or without the WAL, using > some other method)? I have searched for information about this quite > thoroughly without luck, so I thought I'd try asking here before I > started to patch Postgres :) See xlogdump if you use 9.2 or earlier. https://github.com/snaga/xlogdump If you're going to use 9.3, you can find pg_xlogdump in the contrib. http://www.postgresql.org/docs/devel/static/pgxlogdump.html Regards, -- Satoshi Nagayasu <snaga@uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp
On Mon, Jul 15, 2013 at 1:45 PM, Baldur Þór Emilsson <baldur@baldur.biz> wrote: > Are there any projects or standard procedures for reading the data from the > WAL to get a change log for the database (or without the WAL, using some > other method)? I have searched for information about this quite thoroughly > without luck, so I thought I'd try asking here before I started to patch > Postgres :) I think that pg_xlogdump is what you're looking for: http://www.postgresql.org/docs/9.3/static/pgxlogdump.html It only became available with Postgres 9.3. On prior versions, you can use: https://github.com/snaga/xlogdump -- Regards, Peter Geoghegan
Thank you all for your responses. I'm aware of xlogdump but I'm afraid it does not help me with readign the data in the WAL. It is mainly "for debugging or educational purposes" (citing the docs) and it outputs a lot of information about the WAL records but not the contents of them (e.g. it says where an INSERT wrote the data, but not what the data is). Please correct me if I'm mistaken.
What I'm looking for is a way to read the data that is inserted into the database. I was hoping there was some known way of doing that to save me the time it takes to patch Postgres to do that.
Regards,
Baldur
Regards,
Baldur
On 15 July 2013 12:59, Peter Geoghegan <peter.geoghegan86@gmail.com> wrote:
On Mon, Jul 15, 2013 at 1:45 PM, Baldur Þór Emilsson <baldur@baldur.biz> wrote:> Are there any projects or standard procedures for reading the data from theI think that pg_xlogdump is what you're looking for:
> WAL to get a change log for the database (or without the WAL, using some
> other method)? I have searched for information about this quite thoroughly
> without luck, so I thought I'd try asking here before I started to patch
> Postgres :)
http://www.postgresql.org/docs/9.3/static/pgxlogdump.html
It only became available with Postgres 9.3. On prior versions, you can use:
https://github.com/snaga/xlogdump
--
Regards,
Peter Geoghegan
On Mon, Jul 15, 2013 at 01:34:01PM +0000, Baldur Þór Emilsson wrote: > Thank you all for your responses. I'm aware of xlogdump but I'm afraid it does > not help me with readign the data in the WAL. It is mainly "for debugging or > educational purposes" (citing the docs) and it outputs a lot of information > about the WAL records but not the contents of them (e.g. it says where an > INSERT wrote the data, but not what the data is). Please correct me if I'm > mistaken. > > What I'm looking for is a way to read the data that is inserted into the > database. I was hoping there was some known way of doing that to save me the > time it takes to patch Postgres to do that. There is no way to decode the WAL data as the decoding information is in the database and not easily available to an external tool. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On 2013-07-15 13:34:01 +0000, Baldur Þór Emilsson wrote: > Thank you all for your responses. I'm aware of xlogdump but I'm afraid it > does not help me with readign the data in the WAL. It is mainly "for > debugging or educational purposes" (citing the docs) and it outputs a lot > of information about the WAL records but not the contents of them (e.g. it > says where an INSERT wrote the data, but not what the data is). Please > correct me if I'm mistaken. > > What I'm looking for is a way to read the data that is inserted into the > database. I was hoping there was some known way of doing that to save me > the time it takes to patch Postgres to do that. There's an in-development project doing that... Submitted to the last commitfests and all. Search for changeset extraction et al. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services