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 :)
About the project I'm working on: it's a project for my master thesis at
the University of Iceland. I'm trying to build a framework for quick
creation of development and testing databases in Postgres. It's still in
early stages but the idea is to create the schema first and use views that
fetch the data as it is needed. I run a database in the middle of dev and
master that stores the history of the data in master, that way I can fetch
data for dev using the state of master as it was when dev was created. To
that end I need to monitor changes in master, and I would like to read the
WAL for that as it requires no changes to the master DB (unlike triggers).
Regards,
Baldur Þór
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
On 15 July 2013 12:59, Peter Geoghegan <peter.geoghegan86@gmail.com> wrote:
Show quoted text
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 quitethoroughly
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
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. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general