Обсуждение: REVOKE command not working as expected
Hi everyone,
--
We have a small script that we use to replicate our database from our production server to a stage one, where we do the development. More than a "replication" is a copy, so what it does is deletes everything on stage and then copies everything from production to stage. We do this at the beginning of the week to keep both databases in synch whilst during the week the developers can experiment. Here is the script: https://github.com/guillermo-carrasco/scilifelab/blob/master/scripts/dbsync.sh
The problem is that sometimes (apparently random), even after having invoked EVOKE CONNECT ON DATABASE \"$DB\" FROM PUBLIC;, when the scripts starts dumping, it fails because it says that there are some active connections.
I do not understand what's going on here, theoretically, if I prevent connections and after that I revoke all the existing ones, I shouldn't see that problem, right?
Any help is very appreciated.
Thanks in advance!
------------------------------------------------------------------------
Guillermo Carrasco Hernández, Computer ScientistLinkedIn: http://linkd.in/XeFUSB
Personal blog: http://mussolblog.wordpress.com/
------------------------------------------------------------------------
On Tue, Nov 12, 2013 at 7:58 AM, Guillermo Carrasco <guillermo.carrasco@scilifelab.se> wrote: > Hi everyone, > > The problem is that sometimes (apparently random), even after having invoked > EVOKE CONNECT ON DATABASE \"$DB\" FROM PUBLIC;, when the scripts starts > dumping, it fails because it says that there are some active connections. Revoking connection does not disconnect currently connected clients.
Hi,
Thanks for your answer, I know, that's why we have this statement: https://github.com/guillermo-carrasco/scilifelab/blob/master/scripts/dbsync.sh#L20
On Tue, Nov 12, 2013 at 4:01 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Tue, Nov 12, 2013 at 7:58 AM, Guillermo Carrasco
<guillermo.carrasco@scilifelab.se> wrote:
> Hi everyone,>Revoking connection does not disconnect currently connected clients.
> The problem is that sometimes (apparently random), even after having invoked
> EVOKE CONNECT ON DATABASE \"$DB\" FROM PUBLIC;, when the scripts starts
> dumping, it fails because it says that there are some active connections.
------------------------------------------------------------------------
Guillermo Carrasco Hernández, Computer ScientistLinkedIn: http://linkd.in/XeFUSB
Personal blog: http://mussolblog.wordpress.com/
------------------------------------------------------------------------
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Tue, Nov 12, 2013 at 7:58 AM, Guillermo Carrasco
> <guillermo.carrasco@scilifelab.se> wrote:
>> The problem is that sometimes (apparently random), even after having invoked
>> EVOKE CONNECT ON DATABASE \"$DB\" FROM PUBLIC;, when the scripts starts
>> dumping, it fails because it says that there are some active connections.
> Revoking connection does not disconnect currently connected clients.
My money is on autovacuum.
regards, tom lane
Why not just shut down the staging server, pg_start_backup on production, and rsync the db??? On Nov 12, 2013, at 8:02 AM, Guillermo Carrasco <guillermo.carrasco@scilifelab.se> wrote: > Thanks for your answer, I know, that's why we have this statement: https://github.com/guillermo-carrasco/scilifelab/blob/master/scripts/dbsync.sh#L20 > -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
Hi Scott,
We want to make an exact copy of production, even if someone have made changes in stage, we want to remove all the changes and leave stage as an exact copy of production, will pg_start_backup do the work?
On Tue, Nov 12, 2013 at 4:19 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
Why not just shut down the staging server, pg_start_backup on production, and rsync the db???--
On Nov 12, 2013, at 8:02 AM, Guillermo Carrasco <guillermo.carrasco@scilifelab.se> wrote:
> Thanks for your answer, I know, that's why we have this statement: https://github.com/guillermo-carrasco/scilifelab/blob/master/scripts/dbsync.sh#L20
>
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
------------------------------------------------------------------------
Guillermo Carrasco Hernández, Computer ScientistLinkedIn: http://linkd.in/XeFUSB
Personal blog: http://mussolblog.wordpress.com/
------------------------------------------------------------------------
On Nov 13, 2013, at 12:42 AM, Guillermo Carrasco <guillermo.carrasco@scilifelab.se> wrote: > We want to make an exact copy of production, even if someone have made changes in stage, we want to remove all the changesand leave stage as an exact copy of production, will pg_start_backup do the work? Copying the files pg uses does the work ;-) pg_start_backup just enables you to copy a live database and get a consistent result. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice