up vote 0 down vote favorite

I just recently switched from MySQL to PostgreSQL for a database that is on my local machine. I export the entire database to another machine (laptop) when I do on-site research where I don't have access to Internet. Previously, I had used the binary log to update changes to the primary version of the database, and I was attempting to use PostgreSQL's mod log as a substitute for binary log. I was successful with queries I entered using PgAdmin directly; however, for queries where I used a frontend (Wavemaker) to process, the log is showing the statement in two columns, such as:

execute <unnamed>: insert into schema.table (col1, col2, col3) values ($1,$2,$3)
parameters: $1 = 'value1', $2 = 'value2', $3 = 'value3'

OR:

execute &lt;unnamed&gt;: delete from schema.table where key=$1
parameters: $1 = 'keyvalue'

I checked out information concerning PREPARE in PostgreSQL; however, it looks like I would have to define the data types to do this (which the CSV report does not list).

Is there any way to convert the log contents as to make the query readable for PostgreSQL to process in the above cases?

Thank you for any help any of you can provide.

share|improve this question
1  
So the underlying goal here is to have two DBs, where you one-way sync from the master to the slave at intermittent user-controlled times, and where you must be able to run commands on the slave between syncs. Right? If so, a trigger-based audit log accumulator like mod_log does seem like a good option, you just need to get it to normalise parameterised statements. Have you tried to see if it's improved in 9.1 or 9.2 ? – Craig Ringer Sep 19 '12 at 4:34
Yes, that's correct. I have not tried 9.1 or 9.2 at this point. I'm using 8.4 now because it's included with OpenGeo Server (right now I'm not using the geographic capabilities, but I will be soon, which is the major reason for the shift from MySQL). – Mark Connelly Sep 19 '12 at 11:16
In your position I'd probably just stop the slave, pg_start_backup() on the master, rsync the datadir from master to slave, pg_stop_backup(), copy the last WAL and start the slave. – Craig Ringer Sep 19 '12 at 12:26
feedback

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.