Обсуждение: approach to logging complete statements
Hello. I have a web surveying system backed by PostgreSQL. A common use case is for internal staff to make a series of changes to some survey templates (questions, question order, calculations based on answers, translations, etc...) through the web interface. Then those changes are tested, and then manually reproduced through the web interface on production servers. The manual reproduction wastes time and causes errors, so instead I would like to capture all of the SQL that affects the modified tables on development machines so we can run it on the production servers. I have come up with three approaches to do this: 1. Configure my persistence layer to log all of the prepared statements and parameters it executes against the database on the development machines. Write an application to trawl the logs and capture all of the statements affecting the relevant tables and convert them into regular SQL. 2. Patch the persistence layer to log the exact SQL I want. 3. For the development machines, attach triggers to all of the relevant tables so that on update, delete, or insert it inserts an equivalent SQL statement to a separate table along with an incrementing primary key. Then just select the stored statements in order by primary key to get SQL. I can do any of the three, but they're all time consuming, and I imagine what I am trying to do is not that rare. Is there an easier way I missed? Thank you in advance for any help. -Mike
Hi, On Mon, May 20, 2013 at 11:18 AM, Michael Swierczek <mike.swierczek@gmail.com> wrote: > Hello. I have a web surveying system backed by PostgreSQL. A common > use case is for internal staff to make a series of changes to some > survey templates (questions, question order, calculations based on > answers, translations, etc...) through the web interface. Then those > changes are tested, and then manually reproduced through the web > interface on production servers. The manual reproduction wastes time > and causes errors, so instead I would like to capture all of the SQL > that affects the modified tables on development machines so we can run > it on the production servers. You can use a replication tool, eg. londiste, to replicate changes of particular tables to the production database. Keep the replication service stopped all the time when you do not need to mirror your changes, it will still collect the changes in the internal tables on the testing server side. When you are ready to apply the changes to the production start the replication, wait until all the data is replicated and stop it again. Note, that this approach requires you to have separate tables for metadata (ones that only internal stuff will change) and user tables, so you are assumed to replicate metadata tables from testing to production. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray.ru@gmail.com
On Mon, May 20, 2013 at 4:11 PM, Sergey Konoplev <gray.ru@gmail.com> wrote: > Hi, > > On Mon, May 20, 2013 at 11:18 AM, Michael Swierczek > <mike.swierczek@gmail.com> wrote: >> Hello. I have a web surveying system backed by PostgreSQL. A common >> use case is for internal staff to make a series of changes to some >> survey templates (questions, question order, calculations based on >> answers, translations, etc...) through the web interface. Then those >> changes are tested, and then manually reproduced through the web >> interface on production servers. The manual reproduction wastes time >> and causes errors, so instead I would like to capture all of the SQL >> that affects the modified tables on development machines so we can run >> it on the production servers. > > You can use a replication tool, eg. londiste, to replicate changes of > particular tables to the production database. Keep the replication > service stopped all the time when you do not need to mirror your > changes, it will still collect the changes in the internal tables on > the testing server side. When you are ready to apply the changes to > the production start the replication, wait until all the data is > replicated and stop it again. > > Note, that this approach requires you to have separate tables for > metadata (ones that only internal stuff will change) and user tables, > so you are assumed to replicate metadata tables from testing to > production. > > -- > Kind regards, > Sergey Konoplev > PostgreSQL Consultant and DBA > > Profile: http://www.linkedin.com/in/grayhemp > Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 > Skype: gray-hemp > Jabber: gray.ru@gmail.com Sergey, Thank you for your response. I was hoping there was an "out-of-the-box" solution I missed, but I guess not. I will look at Iondiste, thank you for the suggestion. -Mike