[GENERAL] Log all queries before migration ?

[ben.play]

Hi guys,

We have some storage problem on our dedicated server and we have to migrate.
Do you know how can I log all queries in order to have no downtime during
migration ?

I tried many options on the conf, but i never find the good one. I didn't
find out to generate .sql file with all queries.

Thank you a lot for your help !




--
View this message in context: http://postgresql.nabble.com/Log-all-queries-before-migration-tp5913922.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[CS DBA]

log_min_duration_statement = 0

Post by ben.play
Hi guys,
We have some storage problem on our dedicated server and we have to migrate.
Do you know how can I log all queries in order to have no downtime during
migration ?
I tried many options on the conf, but i never find the good one. I didn't
find out to generate .sql file with all queries.
Thank you a lot for your help !
--
View this message in context: http://postgresql.nabble.com/Log-all-queries-before-migration-tp5913922.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[David G. Johnston]

Post by ben.play
Hi guys,
We have some storage problem on our dedicated server and we have to
migrate.
Do you know how can I log all queries in order to have no downtime during
migration ?

​I don't understand how logging of queries has anything to do with downtime
during migration...

David J.​

[ben.play]

Hi guys,

In fact, I would like to copy a table and then, when everything is finish,
execute all inserts and updates since the beginning of the copy.

The biggest problem with the log at this time it's the query.
All my queries are not runnable.

When I log, I see this kind of queries :
"duration: 0.046 ms parse <unnamed>: UPDATE user SET money = money + $1
WHERE id = $2"

But I would like to have only : "UPDATE user SET money = money + 100 WHERE
id = 1281012"

This is our postgresql.conf :
log_destination = 'csvlog'
logging_collector = on
log_min_duration_statement = 0
log_statement = mod
log_directory = '/media/hdd/log_queries'
log_filename='log_insert.log'
log_file_mode = 0600
log_error_verbosity = verbose


What do we have to change ?

Thank you a lot for your help !



--
View this message in context: http://postgresql.nabble.com/Log-all-queries-before-migration-tp5913922p5914599.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[Charles Clavadetscher]

Hello

-----Original Message-----
Sent: Mittwoch, 3. August 2016 15:29
Subject: Re: [GENERAL] Log all queries before migration ?
Hi guys,
In fact, I would like to copy a table and then, when everything is finish, execute all inserts and updates since the
beginning of the copy.
The biggest problem with the log at this time it's the query.
All my queries are not runnable.

This may help you?

http://laurenz.github.io/pgreplay/

Regards
Charles

"duration: 0.046 ms parse <unnamed>: UPDATE user SET money = money + $1 WHERE id = $2"
But I would like to have only : "UPDATE user SET money = money + 100 WHERE id = 1281012"
log_destination = 'csvlog'
logging_collector = on
log_min_duration_statement = 0
log_statement = mod
log_directory = '/media/hdd/log_queries'
log_filename='log_insert.log'
log_file_mode = 0600
log_error_verbosity = verbose
What do we have to change ?
Thank you a lot for your help !
--
View this message in context: http://postgresql.nabble.com/Log-all-queries-before-migration-tp5913922p5914599.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
http://www.postgresql.org/mailpref/pgsql-general

[Melvin Davidson]

On Wed, Aug 3, 2016 at 10:10 AM, Charles Clavadetscher <

Hello

-----Original Message-----
Sent: Mittwoch, 3. August 2016 15:29
Subject: Re: [GENERAL] Log all queries before migration ?
Hi guys,
In fact, I would like to copy a table and then, when everything is

finish, execute all inserts and updates since the

beginning of the copy.
The biggest problem with the log at this time it's the query.
All my queries are not runnable.

This may help you?
http://laurenz.github.io/pgreplay/
Regards
Charles

"duration: 0.046 ms parse <unnamed>: UPDATE user SET money = money + $1

WHERE id = $2"

But I would like to have only : "UPDATE user SET money = money + 100

WHERE id = 1281012"

log_destination = 'csvlog'
logging_collector = on
log_min_duration_statement = 0
log_statement = mod
log_directory = '/media/hdd/log_queries'
log_filename='log_insert.log'
log_file_mode = 0600
log_error_verbosity = verbose
What do we have to change ?
Thank you a lot for your help !
--

http://postgresql.nabble.com/Log-all-queries-before-migration-tp5913922p5914599.html

Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
http://www.postgresql.org/mailpref/pgsql-general

--
http://www.postgresql.org/mailpref/pgsql-general

T








*here is no way to record queries and shut down the server at the same
time.That's like saying I want to shut off my car engine but keep driving.I
believe you are looking at the wrong solution. The best way to migrate and
keep all transactions is to use Slony
replication.http://slony.info/adminguide/2.2/doc/adminguide/slony.pdf
<http://slony.info/adminguide/2.2/doc/adminguide/slony.pdf>http://slony.info/downloads/2.2/
<http://slony.info/downloads/2.2/>You can start the migration to the new
server and when it's finished, you simply failover to the slave and
redirect the url to it. *